Lazarus

Programming => Databases => Topic started by: egsuh on November 07, 2018, 09:32:13 am

Title: Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?
Post by: egsuh on November 07, 2018, 09:32:13 am

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?

Title: Re: Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?
Post by: Remy Lebeau on November 07, 2018, 07:44:32 pm

Quote from: egsuh on November 07, 2018, 09:32:13 am

Is 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.

Title: Re: Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?
Post by: young_nandy on December 06, 2018, 01:02:27 am

open ==> select

execute ==> delete,insert,update

 8-)

Title: Re: Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?
Post by: dsiders on December 06, 2018, 01:11:43 am

Quote from: egsuh on November 07, 2018, 09:32:13 am

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?

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.

Title: Re: Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?
Post by: mangakissa on December 06, 2018, 08:28:25 am

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

Title: Re: Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?
Post by: rvk on December 06, 2018, 10:32:07 am

Quote from: mangakissa on December 06, 2018, 08:28:25 am

But 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.

Title: Re: Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?
Post by: mangakissa on December 07, 2018, 09:14:29 am

Quote

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.

.
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?

Title: Re: Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?
Post by: rvk on December 07, 2018, 11:50:32 am

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][+][-]

1. SQLQuery1.SQL.Text := 'INSERT INTO TABLENAME (CODE) VALUES(''test'') RETURNING ID;';
2. 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
 8-)