Again Insert/Edit in SQLite DB

[amedeo]

Last day I posted a question about Insert/Update.
To try to solve problem with forms I changed setup of my program: not more forms but one form with one Page-control and more TabSheet. However I still have again problems with Insert/Update istructions, Instead with Select istructions it is ok.
The situation is: I write this

  SQLQuery1.Close;
  SQLQuery1.SQL.Clear ;
  SQLQuery1.SQL.Add(update MyTab set Field1=:l_Field1, Field2=:l_Field2, .... where KeyTab=v_Key) ;
  SQLQuery1.Params.ParamByName('l_Field1').AsString:=val_Field1;
  SQLQuery1.Params.ParamByName('l_Field2').AsString:=val_Field2;
  .... and so on

  SQLQuery1.ExecSQL;
  SQLQuery1.Open;
  SQLQuery1.Edit;
  SQLQuery1.Post;
  SQLQuery.ApplyUpdates;
  SQLTransaction1.Commit;

This is told from the Sacred Texts (I think)

But when program arrives at Open instruction it stops because "can not open a non-select statement".

Istructions Edit, Post, ApplyUpdates must have SQLQuery opened but I can not open it!!!
Where is the error? someone can help me?

P.S.
1   I have read a lot of documentation and tried many situations before this one
2   I used for long time, but many years ago, Delphi with Interbase/Firebird and I did not have all this difficults

[valdir.marcos]

Last day I posted a question about Insert/Update.
To try to solve problem with forms I changed setup of my program: not more forms but one form with one Page-control and more TabSheet. However I still have again problems with Insert/Update istructions, Instead with Select istructions it is ok.
The situation is: I write this

  SQLQuery1.Close;
  SQLQuery1.SQL.Clear ;
  SQLQuery1.SQL.Add(update MyTab set Field1=:l_Field1, Field2=:l_Field2, .... where KeyTab=v_Key) ;
  SQLQuery1.Params.ParamByName('l_Field1').AsString:=val_Field1;
  SQLQuery1.Params.ParamByName('l_Field2').AsString:=val_Field2;
  .... and so on

  SQLQuery1.ExecSQL;
  SQLQuery1.Open;
  SQLQuery1.Edit;
  SQLQuery1.Post;
  SQLQuery.ApplyUpdates;
  SQLTransaction1.Commit;

This is told from the Sacred Texts (I think)

But when program arrives at Open instruction it stops because "can not open a non-select statement".

Istructions Edit, Post, ApplyUpdates must have SQLQuery opened but I can not open it!!!
Where is the error? someone can help me?

P.S.
1   I have read a lot of documentation and tried many situations before this one
2   I used for long time, but many years ago, Delphi with Interbase/Firebird and I did not have all this difficults

First , it would be easier to understand what you mean if you attach a small sample project.

Second, try:

Code: Pascal  [Select][+][-]

1.   SQLQuery1.Close;
2.   SQLQuery1.SQL.Clear ;
3.  
4.   // SQLQuery1.SQL.Add(update MyTab set Field1=:l_Field1, Field2=:l_Field2, .... where KeyTab=v_Key) ;
5.   SQLQuery1.SQL.Add('update MyTab set Field1 = :l_Field1, Field2 = :l_Field2, .... where KeyTab = :v_Key') ;
6.  
7.   SQLQuery1.Params.ParamByName('l_Field1').AsString:=val_Field1;
8.   SQLQuery1.Params.ParamByName('l_Field2').AsString:=val_Field2;
9.  
10.   SQLQuery1.Params.ParamByName('KeyTab').AsString:= val_key;
11.  
12.   .... and so on
13.  
14.   SQLQuery1.ExecSQL;
15.  
16.   // SQLQuery1.Open;
17.   // SQLQuery1.Edit;
18.   // SQLQuery1.Post;
19.   // SQLQuery.ApplyUpdates;
20.  
21.   SQLTransaction1.Commit;

[dsiders]

Last day I posted a question about Insert/Update.
To try to solve problem with forms I changed setup of my program: not more forms but one form with one Page-control and more TabSheet. However I still have again problems with Insert/Update istructions, Instead with Select istructions it is ok.
The situation is: I write this

  SQLQuery1.Close;
  SQLQuery1.SQL.Clear ;
  SQLQuery1.SQL.Add(update MyTab set Field1=:l_Field1, Field2=:l_Field2, .... where KeyTab=v_Key) ;
  SQLQuery1.Params.ParamByName('l_Field1').AsString:=val_Field1;
  SQLQuery1.Params.ParamByName('l_Field2').AsString:=val_Field2;
  .... and so on

  SQLQuery1.ExecSQL;
  SQLQuery1.Open;
  SQLQuery1.Edit;
  SQLQuery1.Post;
  SQLQuery.ApplyUpdates;
  SQLTransaction1.Commit;

This is told from the Sacred Texts (I think)

But when program arrives at Open instruction it stops because "can not open a non-select statement".

Istructions Edit, Post, ApplyUpdates must have SQLQuery opened but I can not open it!!!
Where is the error? someone can help me?

P.S.
1   I have read a lot of documentation and tried many situations before this one
2   I used for long time, but many years ago, Delphi with Interbase/Firebird and I did not have all this difficults

First, a SQL UPDATE statement returns no result set.

Also, TSQLQuery has multiple SQL statements: SQL, InsertSQL, DeleteSQL, and UpdateSQL. You need to set the correct one(s).

[amedeo]

As indicated I commented but before Commit program stop with message "database is locked";

DB Connection and Transaction are active ... for the update operation I do not have a Datasource, I do not know to do with it

[egsuh]

You should understand that Database operated through SQL and TDataSet descendents (TSQLQuery, etc.) are separate layers.

Once you execute SQL statements, like update, insert, select, etc. then it operates directly with the SQLite database file (still temporarily. It is changed permanently when you 'commit' the operations).

When you open SQLQuery with SELECT statements, you'd better think that a copy of the resultant content is created on your local application, which looks like a temporary table. Let's call this dataset, as TSQLQuery is a TDataSet descendent. But still this dataset is invisible.

In order to see the content, you have to use DBControls --- TDBEdit, TDBGrid, etc. Datasource links the previous dataset to these visual controls.  If you change any content (or position) in the DataSet, the visual control reflects it via datasource, and once you change content at the visual controls, the content of dataset is modified.

The commands like SQLQuery1.Edit,  SQLQuery1.Insert, SQLQuery1.Post, etc. operate on this dataset, not SQLite database itself. Originally Insert, Post, etc. were to operate on local tables like FireFox table, etc.

Any changes to this local dataset is updated to the server file when you call  "ApplyUpdates". 



So,

once you have Executed 'update ....' SQL (plus transaction.commit), then it's just done.

In order to see the updated content, you have to "OPEN" SQLQuery with 'select.... '.  And if you do modify something on the "opened" sqlquery, then ApplyUpdates must be called.

There are some more functions in the TSQLQuery, like using separate SQL statements for insert, update, etc. But I think the you'd better understand what I said first.

[valdir.marcos]

How was your experience trying the examples I said before?
Insert/Edit in SQLite

Please, google "YouTube Lazarus SQLdb".

As indicated I commented but before Commit program stop with message "database is locked";
DB Connection and Transaction are active ... for the update operation I do not have a Datasource, I do not know to do with it

Please, attach a small sample project.

[amedeo]

Thanks egsuh for your explanation, now I'll try to applicate it https://forum.lazarus.freepascal.org/Smileys/ExcellentSmileys1/smile.gif

[amedeo]

Now it works in this way

  SQLTransaction.Active:=True;
  SQLQuery.Active:=False;
  SQLQry.ReadOnly:=False;
  SQLQry.SQL.Text:='select * from TbTab1 where v_key='+inttostr(v_Unique)+'';
  SQLQuery.Active:=True;
  SQLQry.Edit;            // .Insert      to insert;
  SQLQuery.FieldByName('Field1').AsString:=v_field1;
  SQLQuery.FieldByName('Field2').AsString:=v_field2;

  ... and so on

  SQLQuery.Post;
  SQLQuery.ApplyUpdates;
  SQLTransaction.Commit;
  SQLQuery.Active:=False;
  SQLTransacaction.Active:=False;

Done!!  but it's not intuitive

Thanks to everyone 

Best wishes for the new year