* * *

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

puredeadoblivion

  • New member
  • *
  • Posts: 18
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

GetMem

  • Hero Member
  • *****
  • Posts: 2074
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;

puredeadoblivion

  • New member
  • *
  • Posts: 18
Re: INSERT INTO, SQL command using sqlite
« Reply #2 on: February 24, 2017, 09:46:36 pm »
Still says that the database is locked

kapibara

  • Sr. Member
  • ****
  • Posts: 454
Re: INSERT INTO, SQL command using sqlite
« Reply #3 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.0 / Debian Stretch 64-bit

puredeadoblivion

  • New member
  • *
  • Posts: 18
Re: INSERT INTO, SQL command using sqlite
« Reply #4 on: February 26, 2017, 11:10:15 am »
How would I go about doing this would I set the dbconnection(SQLite3Connection) database to blank, and then link it during run-time

kapibara

  • Sr. Member
  • ****
  • Posts: 454
Re: INSERT INTO, SQL command using sqlite
« Reply #5 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.0 / Debian Stretch 64-bit

puredeadoblivion

  • New member
  • *
  • Posts: 18
Re: INSERT INTO, SQL command using sqlite
« Reply #6 on: February 26, 2017, 04:05:57 pm »
Okay thanks

JanRoza

  • Sr. Member
  • ****
  • Posts: 442
    • http://www.silentwings.nl
Re: INSERT INTO, SQL command using sqlite
« Reply #7 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.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus