Recent

Author Topic: 2nd Table added to existing SQLite file - not being saved  (Read 3288 times)

bobonwhidbey

  • Hero Member
  • *****
  • Posts: 590
    • Double Dummy Solver - free download
2nd Table added to existing SQLite file - not being saved
« on: November 07, 2014, 06:56:27 am »
I load a DBGrid with the first table in FormCreate. On a button click I create a NewData table and replace the contents of the DBGrid with this new table. All works well. When I close the program, the .sql file does not include this 2nd table. What do I need to do?  Here's my code for creating that 2nd table.
Code: [Select]
  SQLQuery1.SQL.Text := 'DROP TABLE IF EXISTS NewData';
  SQLQuery1.ExecSQL;

  SQL := 'CREATE TABLE NewData ([ID] INTEGER PRIMARY KEY';
  SQL := SQL + ', tContract INT';
  SQL := SQL + ', tResult INT';
  SQL := SQL + ');';
  SQLQuery1.SQL.Text := SQL;
  SQLQuery1.ExecSQL;

  SQL := 'CREATE INDEX AContract ON NewData (tcontract);';
  SQLQuery1.SQL.Text := SQL;
  SQLQuery1.ExecSQL;

  with  Datasource1.dataset do begin // from the first table
    First;
    i := 0;
    while not EOF do begin
      c := Cont2Num(Fields[1].AsString);
      r := Fields[2].AsInteger; // result
      SQL := 'INSERT INTO NewData VALUES(' + IntToStr(i) + ',' +
        IntToStr(r) + ',' + IntToStr(c) + ');';
      SQLQuery1.SQL.Text := SQL;
      SQLQuery1.ExecSQL;
      Inc(i);
      Next;
    end;// while
  end; // with dataset

// now replace the DBGrid with this NewData
  SQLQuery1.Close;
  SQLQuery1.SQL.Text := 'select * from NewData';
  SQLQuery1.Open;       
                                 
« Last Edit: November 08, 2014, 04:20:43 pm by bobonwhidbey »
Lazarus 3.0RC2, FPC 3.2.2 x86_64-win64-win32/win64

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: 2nd Table added to existing SQLite file - not being saved
« Reply #1 on: November 07, 2014, 11:13:19 am »
Shouldn't you commit your transactions?
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

bobonwhidbey

  • Hero Member
  • *****
  • Posts: 590
    • Double Dummy Solver - free download
Re: 2nd Table added to existing SQLite file - not being saved
« Reply #2 on: November 07, 2014, 06:23:39 pm »
I don't know how to do that. I would have assumed that BeginTransaction and Commit statements would be something like this - but of course this doesn't compile.
Code: [Select]
  with  Datasource1.dataset do begin
    First;
    i := 0;
    SQLite3Connection.BeginTransaction;
    while not EOF do begin
      c := Cont2Num(Fields[1].AsString);
      r := Fields[2].AsInteger; // result
      SQL := 'INSERT INTO NewData VALUES(' + IntToStr(i) + ',' +
        IntToStr(r) + ',' + IntToStr(c) + ');';
      SQLQuery1.SQL.Text := SQL;
      SQLQuery1.ExecSQL;
      Inc(i);
      Next;
    end;// while
    SQLQuery1.Commit;
  end; // with dataset
« Last Edit: November 08, 2014, 04:21:02 pm by bobonwhidbey »
Lazarus 3.0RC2, FPC 3.2.2 x86_64-win64-win32/win64

bobonwhidbey

  • Hero Member
  • *****
  • Posts: 590
    • Double Dummy Solver - free download
Re: 2nd Table added to existing SQLite file - not being saved
« Reply #3 on: November 07, 2014, 06:44:09 pm »
NOW I see that my commit statement should be formatted:

     SQLite3Connection.Transaction.Commit;

My problem now is that the number of times my While not EOF loop executes seems to depend on the number of rows visible, rather than the number of rows in the table. What could be casing this?
Lazarus 3.0RC2, FPC 3.2.2 x86_64-win64-win32/win64

howardpc

  • Hero Member
  • *****
  • Posts: 4144
Re: 2nd Table added to existing SQLite file - not being saved
« Reply #4 on: November 07, 2014, 06:54:47 pm »
If you install the Cody package, you will have some useful additional menu functions.
If you right-click inside a "with" block you can choose (under Refactoring->) "Explode a "with" block.

This can be very useful if it shows you (for instance) that your EOF does not refer to the dataset you thought it did.

guest48180

  • Guest
Re: 2nd Table added to existing SQLite file - not being saved
« Reply #5 on: November 07, 2014, 07:17:03 pm »
bobonwhidbey:

One other thing that may interest you is that your primary key autoincrements. There's no need to insert an integer to increment this.

Code: [Select]
SQL := 'INSERT INTO NewData VALUES(' + IntToStr(i) + ',' +
        IntToStr(r) + ',' + IntToStr(c) + ');';

Try this instead:

Code: [Select]
SQL := 'INSERT INTO NewData VALUES(NULL, ' +
        IntToStr(r) + ',' + IntToStr(c) + ');';

https://www.sqlite.org/autoinc.html
http://www.sqlite.org/faq.html#q1

Good luck,
Landslyde

 

TinyPortal © 2005-2018