INSERT INTO, SQL command using sqlite
lazarus / 16-Databases

----------------------------------------------------------------------
puredeadoblivion · 2017-02-24 17:36

So,

I have a database that will store some values in my database, except
whatever I do an error is thrown, the most recent being that the
database is locked!
Here is my code:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
frmPracDrafting.SQLQuery1.close; 
frmPracDrafting.SQLQuery1.SQL.clear;  deckName:=inputBox('Deck
Name','Type in your deck name!', 'Type Here');  repeat  //repeats for
every card    x:=x+1;    frmPracDrafting.SQLQuery1.close;   
 frmPracDrafting.SQLQuery1.SQL.Add('INSERT INTO Deck (DeckNum,
DeckName, CardName, Username)VALUES(:DECKN,:DECKNME, :CARDNME,
:USR)');   //inserts data into the deck table    frmPracDrafting.SQLQu
ery1.Params.ParamByName('DECKN').AsInteger:=deckNumComparison+1;  
//inserts a larger deckNum(increments the previous deck)    frmPracDra
fting.SQLQuery1.Params.ParamByName('DECKNME').AsString:=deckName;    f
rmPracDrafting.SQLQuery1.Params.ParamByName('CARDNME').AsString:=deck[
x,1];    //inserts the card's name    frmPracDrafting.SQLQuery1.Params
.ParamByName('USR').AsString:=frmLogin.txtUsername.text;      
//inserts the username of the user who is making the deck   
frmPracDrafting.SQLQuery1.execSQL;   
frmPracDrafting.SQLTransaction1.Commit;   
frmPracDrafting.SQLTransaction1.action:= caCommit;   
frmPracDrafting.DBConnection.Open;   
frmPracDrafting.SQLTransaction1.Active:=true;  until x=30; 
There probably is some unnecessary code and a tiny error that I cannot
see

Some help would be appreciated thanks

----------------------------------------------------------------------
balazsszekely · 2017-02-24 17:59

You don't need to close the query, rebuild the SQL each time in the
loop, commiting only once is also enough + it's a lot faster.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
deckName:=inputBox('Deck Name','Type in your deck name!', 'Type
Here');  frmPracDrafting.SQLQuery1.Close; 
frmPracDrafting.SQLQuery1.SQL.Clear; 
frmPracDrafting.DBConnection.Open; 
frmPracDrafting.SQLTransaction1.action := caCommit; 
frmPracDrafting.SQLTransaction1.Active := True;   
frmPracDrafting.SQLQuery1.close; 
frmPracDrafting.SQLQuery1.SQL.Add('INSERT INTO Deck (DeckNum,
DeckName, CardName, Username)VALUES(:DECKN,:DECKNME, :CARDNME,
:USR)');   //inserts data into the deck table  repeat  //repeats for
every card    x := x + 1;      
frmPracDrafting.SQLQuery1.Params.ParamByName('DECKN').AsInteger :=
deckNumComparison+1;   //inserts a larger deckNum(increments the
previous deck)   
frmPracDrafting.SQLQuery1.Params.ParamByName('DECKNME').AsString :=
deckName;   
frmPracDrafting.SQLQuery1.Params.ParamByName('CARDNME').AsString :=
deck[x,1];    //inserts the card's name   
frmPracDrafting.SQLQuery1.Params.ParamByName('USR').AsString :=
frmLogin.txtUsername.text;       //inserts the username of the user
who is making the deck    frmPracDrafting.SQLQuery1.ExecSQL;   until
x=30; frmPracDrafting.SQLTransaction1.Commit;

----------------------------------------------------------------------
fpcoder · 2017-02-24 21:58

If you are connected at design time and then start your program, the
SQLite database will be locked. Close the designtime connection before
running the app and see if that helps.

----------------------------------------------------------------------
fpcoder · 2017-02-26 13:23

Its a common mistake so its worth checking. Click on your connection
on the form and make sure that Connected = False. It doesn't hurt if
the path to the database is filled out. Otherwise you have to set it
by code at runtime instead.

The connection and transaction is opened automatically when you call
ExecSQL. As far as I understand you dont have to do that manually. In
some situations it might be useful though, not sure.

So set the SQL statement, assign the param values and then call
ExecSQL and Commit.

----------------------------------------------------------------------
JanRoza · 2017-02-26 19:40

I always put this in the OnCreate procedure of my mainform:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i        //
Open database       DataModule1.DbConnection.Connected := False;     
 DataModule1.DbConnection.DatabaseName :=
SysUtils.ExtractFilePath(ParamStr(0)) + 'databasename.db';     
 DataModule1.DbConnection.Connected := True;        if
DataModule1.DbConnection.Transaction.Active = False       then
DataModule1.DbConnection.Transaction.StartTransaction; 
This example assumes that your database is in the same folder as the
program (always make the database connection inactive and the
databasename empty when you compile your program to exe-file). This
way it doesn't matter where the program is saved (as long as the
database is saved in the same folder).
Hope this may help you.