Recent

Author Topic: Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?  (Read 4551 times)

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?
« 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?

Remy Lebeau

  • Hero Member
  • *****
  • Posts: 1312
    • Lebeau Software
Re: Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?
« Reply #1 on: November 07, 2018, 07:44:32 pm »
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.
Remy Lebeau
Lebeau Software - Owner, Developer
Internet Direct (Indy) - Admin, Developer (Support forum)

young_nandy

  • New Member
  • *
  • Posts: 41
Re: Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?
« Reply #2 on: December 06, 2018, 01:02:27 am »
open ==> select

execute ==> delete,insert,update

 8-)

dsiders

  • Hero Member
  • *****
  • Posts: 1052
Re: Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?
« Reply #3 on: December 06, 2018, 01:11:43 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.
Preview Lazarus 3.99 documentation at: https://dsiders.gitlab.io/lazdocsnext

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?
« Reply #4 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.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?
« Reply #5 on: December 06, 2018, 10:32:07 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.

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?
« Reply #6 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?
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Any difference between TSQLQuery.Open and TSQLQuery.ExecSQL?
« Reply #7 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-)

 

TinyPortal © 2005-2018