2nd Table added to existing SQLite file - not being saved
lazarus / 16-Databases

----------------------------------------------------------------------
bobonwhidbey · 2014-11-07 05:56

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;

----------------------------------------------------------------------
BigChimp · 2014-11-07 10:13

Shouldn't you commit your transactions?

----------------------------------------------------------------------
bobonwhidbey · 2014-11-07 17:23

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

----------------------------------------------------------------------
bobonwhidbey · 2014-11-07 17:44

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?

----------------------------------------------------------------------
howardpc · 2014-11-07 17:54

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 · 2014-11-07 18:17

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