Recent

Author Topic: INSERT INTO, SQL command using sqlite  (Read 9795 times)

puredeadoblivion

  • Guest
INSERT INTO, SQL command using sqlite
« on: February 24, 2017, 06:36:18 pm »
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][+][-]
  1.   frmPracDrafting.SQLQuery1.close;
  2.   frmPracDrafting.SQLQuery1.SQL.clear;
  3.   deckName:=inputBox('Deck Name','Type in your deck name!', 'Type Here');
  4.   repeat  //repeats for every card
  5.     x:=x+1;
  6.     frmPracDrafting.SQLQuery1.close;
  7.      frmPracDrafting.SQLQuery1.SQL.Add('INSERT INTO Deck (DeckNum, DeckName, CardName, Username)VALUES(:DECKN,:DECKNME, :CARDNME, :USR)');   //inserts data into the deck table
  8.     frmPracDrafting.SQLQuery1.Params.ParamByName('DECKN').AsInteger:=deckNumComparison+1;   //inserts a larger deckNum(increments the previous deck)
  9.     frmPracDrafting.SQLQuery1.Params.ParamByName('DECKNME').AsString:=deckName;
  10.     frmPracDrafting.SQLQuery1.Params.ParamByName('CARDNME').AsString:=deck[x,1];    //inserts the card's name
  11.     frmPracDrafting.SQLQuery1.Params.ParamByName('USR').AsString:=frmLogin.txtUsername.text;       //inserts the username of the user who is making the deck
  12.     frmPracDrafting.SQLQuery1.execSQL;
  13.     frmPracDrafting.SQLTransaction1.Commit;
  14.     frmPracDrafting.SQLTransaction1.action:= caCommit;
  15.     frmPracDrafting.DBConnection.Open;
  16.     frmPracDrafting.SQLTransaction1.Active:=true;
  17.   until x=30;
  18.  

There probably is some unnecessary code and a tiny error that I cannot see

Some help would be appreciated thanks

balazsszekely

  • Guest
Re: INSERT INTO, SQL command using sqlite
« Reply #1 on: February 24, 2017, 06:59:24 pm »
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][+][-]
  1.   deckName:=inputBox('Deck Name','Type in your deck name!', 'Type Here');
  2.   frmPracDrafting.SQLQuery1.Close;
  3.   frmPracDrafting.SQLQuery1.SQL.Clear;
  4.   frmPracDrafting.DBConnection.Open;
  5.   frmPracDrafting.SQLTransaction1.action := caCommit;
  6.   frmPracDrafting.SQLTransaction1.Active := True;  
  7.   frmPracDrafting.SQLQuery1.close;
  8.   frmPracDrafting.SQLQuery1.SQL.Add('INSERT INTO Deck (DeckNum, DeckName, CardName, Username)VALUES(:DECKN,:DECKNME, :CARDNME, :USR)');   //inserts data into the deck table
  9.   repeat  //repeats for every card
  10.     x := x + 1;  
  11.     frmPracDrafting.SQLQuery1.Params.ParamByName('DECKN').AsInteger := deckNumComparison+1;   //inserts a larger deckNum(increments the previous deck)
  12.     frmPracDrafting.SQLQuery1.Params.ParamByName('DECKNME').AsString := deckName;
  13.     frmPracDrafting.SQLQuery1.Params.ParamByName('CARDNME').AsString := deck[x,1];    //inserts the card's name
  14.     frmPracDrafting.SQLQuery1.Params.ParamByName('USR').AsString := frmLogin.txtUsername.text;       //inserts the username of the user who is making the deck
  15.     frmPracDrafting.SQLQuery1.ExecSQL;  
  16.  until x=30;
  17.  frmPracDrafting.SQLTransaction1.Commit;

kapibara

  • Hero Member
  • *****
  • Posts: 610
Re: INSERT INTO, SQL command using sqlite
« Reply #2 on: February 24, 2017, 10:58:24 pm »
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.
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

kapibara

  • Hero Member
  • *****
  • Posts: 610
Re: INSERT INTO, SQL command using sqlite
« Reply #3 on: February 26, 2017, 02:23:22 pm »
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.
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

JanRoza

  • Hero Member
  • *****
  • Posts: 672
    • http://www.silentwings.nl
Re: INSERT INTO, SQL command using sqlite
« Reply #4 on: February 26, 2017, 08:40:40 pm »
I always put this in the OnCreate procedure of my mainform:

Code: Pascal  [Select][+][-]
  1.        // Open database
  2.        DataModule1.DbConnection.Connected := False;
  3.        DataModule1.DbConnection.DatabaseName := SysUtils.ExtractFilePath(ParamStr(0)) + 'databasename.db';
  4.        DataModule1.DbConnection.Connected := True;
  5.  
  6.        if DataModule1.DbConnection.Transaction.Active = False
  7.        then DataModule1.DbConnection.Transaction.StartTransaction;
  8.  

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.
OS: Windows 10 (64 bit) / Linux Mint (64 bit)
       Lazarus 3.2 FPC 3.2.2
       CodeTyphon 8.40 FPC 3.3.1

 

TinyPortal © 2005-2018