SQLite - Loading from StringGrid - Nothing is saved to table.
lazarus / 16-Databases

----------------------------------------------------------------------
ocpaul20 · 2015-05-11 11:11

Hi,
I wonder if you can help me please?

I am reading a csv file into a stringgrid and then writing it away to
a sqlite database table.
Everything is working fine as I have try...except..showmessage...end
around all my sql statements and there are no errors
BUT... nothing is ending up in the database.

What am I missing out?

the code goes like this
Code: [Select]    SQLite3Conn.Close;
    SQLTrans1.Active := False;

    try
      SQLite3Conn.Open;
    except
      showmessage('SQLite Open problem');
      exit;
    end;

    try
      SQLTrans1.StartTransaction;
    except
      showmessage('SQLite start transaction problem');
      exit;
    end;

    for row := 1 to rowmax-1 do
    begin

          Extract the values from the stringgrid
          Create the SQL Insert statement with the values just read
from stringgrid
  
      sql := insert statement here

      try
         SQLQuery1.SQL.Text := sql;
      except
        on E: Exception do
        begin
          ShowMessage('An exception was raised: ' + E.Message);
          showmessage('aaaghhh');
          exit;
        end;
      end;
      try
          SQLQuery1.ExecSQL;
      except
          showmessage('Error inserting record into table
tb_pressure');
          Exit
      end;

    end; // Loop on grid rows

         try
                SQLTrans1.EndTransaction;
         except
                showmessage('SQLite end transaction problem');
                exit;
         end;

    // comit all at once
    try
        SQLTrans1.Commit;
        ShowMessage('Succesfully  inserted records into table.'+
sLineBreak +
        'Check message area for duplicates found.');
    except
      begin
        ShowMessage('Unable to insert some records into table'+
sLineBreak +
      'Check message area for duplicates found.');
      end;
    end;

    SQLQuery1.Close;
    SQLTrans1.Active := False;
    SQLite3Conn.Close;

        Load the previous (first) form where the new records should be
shown in a DBGrid
        ===========================================

NOTE: this is pseudo code and I have omitted the sql statement as it
is long and there is no problem with this since no exceptions are
raised when the program runs.

----------------------------------------------------------------------
totya · 2015-05-11 21:56

Hi!

Your code is partial only, well, I don't see the value assigment of
sql variable, so this variable is empty.

Quote from: ocpaul20 on May 11, 2015, 01:11:01 pmCode: [Select] try
   SQLQuery1.SQL.Text := sql;
 except

----------------------------------------------------------------------
ocpaul20 · 2015-05-12 01:00

Yes, I did not include it because it is long and because the sql is
not the problem - otherwise I would get an exception on the try
statement.

so I put
Quote     Extract the values from the stringgrid
     Create the SQL Insert statement with the values just read from
stringgrid

to show that this is where it is happening.
If I can edit my original post I will add sql := insert statement
here   

The code is executing properly but just not saving to the table so I
think I must be missing an essential statement somewhere. There are no
exceptions raised and therefore no messageboxes shown when this
program runs.

----------------------------------------------------------------------
rvk · 2015-05-12 06:14

Am I seeing correctly that you are Ending the transaction and then do
a Commit??
I don't think the commit does anything if the transaction is already
ended.

You can use SQLTrans1.CommitRetaining; (to commit and keep a
transaction open)
or SQLTrans1.Commit; (to commit and close the transaction)
and leave the SQLTrans1.EndTransaction completely out.

----------------------------------------------------------------------
ocpaul20 · 2015-05-12 09:24

rvk - yes I wondered about that so I will remove it thanks.

Apart from that, the code is OK and should save to disk?

I get the feeling that something is making the whole transaction
rollback and so I am not seeing the records inserted on disk, because
by the time I get there to look, the rollback has been done.

----------------------------------------------------------------------
rvk · 2015-05-12 09:30

Quote from: ocpaul20 on May 12, 2015, 11:24:11 amI get the feeling
that something is making the whole transaction rollback and so I am
not seeing the records inserted on disk, because by the time I get
there to look, the rollback has been done.
Your feeling should be correct   When doing an EndTransaction the
default action will be performed. My guess that is a RollBack. So,
yes, that rollback is occurring due to your EndTransaction. When you
manually call Commit (or CommitRetaining) that's not a problem
anymore.

B.T.W. you can also remove then SQLQuery1.Close;
You're executing ExecSQL with insert statements so there is no "open"
select statement. So there is no need for .Close;

----------------------------------------------------------------------
ocpaul20 · 2015-05-12 10:25

OK, thanks.