[solved] ExecSQL and Applyupdates problem
lazarus / 16-Databases

----------------------------------------------------------------------
oldcoder · 2015-10-20 13:28

I have an invoice type string grid in my program, and I have got it
saving the contents of the grid to the database OK, but I want now to
add error checking code.

Having read forum messages and tutorials over the last 2 days I
thought the right thing to do was add all the data in the grid to the
SQLQuery using the code below, and when it has all the data from the
grid, it should write it to the database with the Applyupdates
command.

While the code is reading the grid lines and there is data to process,
the ExecSQL statement works fine, but after it has found the last
valid data line, ExecSQL appears to close the query, so that when
Applyupdates is called after the i loop has finished, it causes the
error 'Operation cannot be performed on an inactive dataset'. If I try
to open or activate the query before the ApplyUpdates call I get a
'Cannot open a non-select statement' error message.

I have tried all sorts of things from forums and tutorials but I can't
get this to work. I'm sure it's something very simple but I just can't
find what the answer is.

Can anyone help please?

  for i := 0 to SGrid1.Rowcount - 1 do 
  begin
       If SGrid1.Cells[0,i] <> '' then
       begin
          SQLQuery3.SQL.Clear;
          SQLQuery3.SQL.Lines.Add('INSERT INTO `itemsales`(`CUSCODE`,
`DOCTYPE`,`DOCNUM`,');
          SQLQuery3.SQL.Lines.Add('`QTY`, `ITEMCODE`, `DISCOUNT`,
`PRICE`,`ORDERDATE`)');
          SQLQuery3.SQL.Lines.Add('VALUES
(+CHAR(39)+code.text+CHAR(39)+',');
          SQLQuery3.SQL.Lines.Add(CHAR(39)+'Cash Sale'+CHAR(39)+',');
          SQLQuery3.SQL.Lines.Add(CHAR(39)+docnum.text+CHAR(39)+',');
         
SQLQuery3.SQL.Lines.Add(CHAR(39)+SGrid1.Cells[5,i]+CHAR(39)+','); 
//QTY
         
SQLQuery3.SQL.Lines.Add(CHAR(39)+SGrid1.Cells[0,i]+CHAR(39)+','); 
//ITEMCODE
         
SQLQuery3.SQL.Lines.Add(CHAR(39)+discount.text+CHAR(39)+',');
//DISCOUNT
         
SQLQuery3.SQL.Lines.Add(CHAR(39)+SGrid1.Cells[3,i]+CHAR(39)+','); 
//PRICE
          SQLQuery3.SQL.Lines.Add(CHAR(39)+docdate.text+CHAR(39)+')');
//ORDERDATE
          DM1.SQLQuery3.ExecSQL;
       end;
    end;

    try
    if DM2.SQLTransaction1.Active then
    begin
       DM1.SQLQuery3.ApplyUpdates;
       DM2.SQLTransaction1.Commit;
    end;
    except
    on E: Exception do
    begin
       DM2.SQLTransaction1.Rollback;
       ShowMessage( E.Message );
    end;
    end;

----------------------------------------------------------------------
LacaK · 2015-10-21 05:38

If you construct SQL INSERT statement and execute it using ExecSQL
then you do not need ApplyUpdates. (ApplyUpdates expects active
dataset ... in your case SQLQuery3)

ApplyUpdates send to SQL server updates done in local dataset by
TDataSet.Post, TDataSet.Delete etc.

----------------------------------------------------------------------
oldcoder · 2015-10-21 21:32

Thanks for your reply LacaK. It helped to clarify what I was doing,
and I was able to get the code working as I wanted, but I am still a
little confused by the two methods of saving data.
Is it generally thought better to save to a database with SQL Insert
or Post statements?

----------------------------------------------------------------------
LacaK · 2015-10-22 05:15

Depend on your application and your needs, usage scenario.
May be the common situation is:

1. you select data using SQLQuery, you get local dataset
2. you browse localy this dataset and/or modify data (add new records,
change fields in existing etc.)
3. you ApplyUpdates (send all locally cached changes to database)
4. you Commit changes

So all is handled by one dataset and you are not required to manually
construct update statements.

----------------------------------------------------------------------
oldcoder · 2015-10-22 10:19

Not having to write complicated SQL update statements is a definite
bonus.
Thanks again for your help LacaK.