Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?
lazarus / 16-Databases

----------------------------------------------------------------------
egsuh · 2018-11-07 08:32

Firebird now supports 'insert ... RETURNING'.  For example, following
SQL statement is valid.

insert into testtable (code, text) values (1, 'code 1') returning code

In Lazarus, I can see the result by SQLQuery1.Open (assuming that
content of SQLQuery1.SQL is the above statement).  When there were no
'returning', I would use TSQLQuery.ExecSQL. This does not return any
value.

Is there any other difference between ExecSQL and Open of TSQLQuery?

----------------------------------------------------------------------
Remy Lebeau · 2018-11-07 18:44

Quote from: egsuh on November 07, 2018, 09:32:13 amIs there any other
difference between ExecSQL and Open of TSQLQuery?

Open() allows you to access result sets returned by SQL queries. 
ExecSQL() does not, as it is intended to execute SQL queries that do
not return result sets.

----------------------------------------------------------------------
young_nandy · 2018-12-06 00:02

open ==> select

execute ==> delete,insert,update

----------------------------------------------------------------------
dsiders · 2018-12-06 00:11

Quote from: egsuh on November 07, 2018, 09:32:13 amFirebird now
supports 'insert ... RETURNING'.  For example, following SQL statement
is valid.

insert into testtable (code, text) values (1, 'code 1') returning code

In Lazarus, I can see the result by SQLQuery1.Open (assuming that
content of SQLQuery1.SQL is the above statement).  When there were no
'returning', I would use TSQLQuery.ExecSQL. This does not return any
value.

Is there any other difference between ExecSQL and Open of TSQLQuery?

I was looking at components that supported Firebird 3 features a few
months ago. I found IBX for Lazarus (https://mwasoftware.co.uk/ibx) to
be the most comprehensive.

At the time, sqldb did not support the "RETURNING" clause. No sure if
it does at the moment.

----------------------------------------------------------------------
mangakissa · 2018-12-06 07:28

But you don't really need RETURNING in your query. You can call the
last id before inserting a record.

----------------------------------------------------------------------
rvk · 2018-12-06 09:32

Quote from: mangakissa on December 06, 2018, 08:28:25 amBut you don't
really need RETURNING in your query. You can call the last id before
inserting a record.
Does that work with triggers? I can't imagine it does.
How does SQLdb know which field it needs to put in last_id?
Fields I increment via triggers are not even mentioned in an INSERT so
there is no way to determine the new value after the trigger.

Retrieving the last value isn't a good idea in a multi-user
environment and getting the value before hand with a generator can
work but is an extra call.

INSERT ... RETURNING would return the value after the trigger and is
the most efficient way.

----------------------------------------------------------------------
mangakissa · 2018-12-07 08:14

QuoteRetrieving the last value isn't a good idea in a multi-user
environment and getting the value before hand with a generator can
work but is an extra call.
.
That's what I meant.
But is the returing key not a parameter back to the client?
What's paramcheck() saying after inserting a record?

----------------------------------------------------------------------
rvk · 2018-12-07 10:50

Ah, it seems INSERT RETURNING is supported by SQLdb already.

But you need to retrieve the value by using TSQLQuery.Open and not
TSQLQuery.ExecSQL.

So
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
SQLQuery1.SQL.Text := 'INSERT INTO TABLENAME (CODE) VALUES(''test'')
RETURNING ID;';SQLQuery1.Open;will work and result in a resultset with
one record and one field ID.

So it is:
open ==> select or "insert ... returning"
execute ==> delete, insert (without returning), update