SQLdb: open vs execsql
lazarus / 16-Databases

----------------------------------------------------------------------
mangakissa · 2020-03-24 13:13

When your updating or inserting a record, you have to use
TSQLQuery.execsql.
Now I need a primary key back from a table. On firebird this query
will be used:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
TABLE (field1, field2) VALUES (value1,value2) RETURNING id But
TSQLQuery.execsql is doesn't returning a value back, because no
dataset will be created.
To get a new primary key value TSQLQuery.open must be used.

But when you're updating a record, TSQLQuery.open will not work.
TSQLQuery.open opens a new
dataset but there are no fields and values and an AV occurs.

Now I'm working with objects and save my record the following way:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
TDBLanden.SaveRecord(const aNewRecord : boolean; aObjlanden:
tObjlanden;                               aRow: TLand) : boolean;var
MySQL : TStrings;begin  MySQL := TStringlist.create;  try    try     
if aNewRecord  then        MySQL.Add('INSERT INTO landen (code,naam)
VALUES (:code, :naam) returning ID')      else       
MySQL.Add('UPDATE landen SET code = :code, naam = :naam WHERE id =
:id');      fSQLQuery.SQL.Text := MySQL.Text;     
fSQLQuery.ParamByName('code').AsString := aRow.code;     
fSQLQuery.ParamByName('naam').AsString := aRow.naam;      if
aNewRecord = false then      begin       
fSQLQuery.ParamByName('id').Asinteger := aRow.id;       
fSQLQuery.execsql;      end else        fSQLQuery.Open;      result :=
fSQLQuery.RowsAffected >= 1;      if aNewRecord then      begin      
lastid := fSQLQuery.Fields[0].AsInteger;        aRow.id := lastid;   
   aObjlanden.Landenlijst.Add(aRow);      end;      if result then   
   fSQLTransaction.Commit      else        fSQLTransaction.Rollback; 
 except      fSQLTransaction.Rollback;      result := false;    end; 
finally    MySQL.Free  end;end; 
For me it's a workaround, but it should be easier.
Is this the only to accomplish a primary key value or is there another
way?
UPDATE and INSERT query should only use TSQLQuery.execsql, beacouse no
data will be
returned to a dataset.

For my connection I use TSQLConnector

----------------------------------------------------------------------
rvk · 2020-03-24 13:28

Quote from: mangakissa on March 24, 2020, 02:13:42 pmFor me it's a
workaround, but it should be easier.
Why should it be easier?

Use .Open when there is a resultset. Use .ExecSQL when there is no
resulset.
A Insert without "returning" has no resultset so use .ExecSQL
A Update without "returning" has no resultset so use .ExecSQL
A Insert with "returning" has a resultset so use .Open
A Select has a resultset so use .Open

----------------------------------------------------------------------
mangakissa · 2020-03-25 07:42

To save the returning ID into a property

----------------------------------------------------------------------
egsuh · 2020-03-25 08:07

If you assign "returning" clause, then you can open the query.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
AQry.SQL.Text :=  'UPDATE or INSERT INTO landen (code,naam) VALUES
(:code, :naam) returning ID';  // assign values to parameters  
AQry.Open;
You may refer:
https://forum.lazarus.freepascal.org/index.php/topic,47718.msg342274.h
tml#msg342274