Recent

Author Topic: [SOLVED] Difference between TSQLQuery from FPC and IBX TIBQuery  (Read 1133 times)

egsuh

  • Hero Member
  • *****
  • Posts: 1273
[SOLVED] Difference between TSQLQuery from FPC and IBX TIBQuery
« on: November 03, 2020, 04:14:38 am »
I found a difference in reading records returned from stored procedures. FPC stuff (i.e. TSQLQuery) is generous. It can open from "execute procedure procedure_name", but TIBquery cannot.

Code: SQL  [Select][+][-]
  1.    CREATE PROCEDURE ReturnAValue
  2.    RETURNS (
  3.         AValue INTEGER
  4.    )
  5.    AS
  6.    BEGIN
  7.       :AValue=1;
  8.    END

Here,

Code: Pascal  [Select][+][-]
  1.    SQLQuery1.SQL.Text := 'execute procedure returnavalue';
  2.    SQLQuery1.Open;
  3.  

will display single row. But TIBQuery will return null dataset (even without field names).

For TIBQuery to work, following works. (And this will work for TSQLQuery as well).

Code: SQL  [Select][+][-]
  1.    CREATE PROCEDURE ReturnAValue
  2.    RETURNS (
  3.         AValue INTEGER
  4.    )
  5.    AS
  6.    BEGIN
  7.       :AValue=1;
  8.       suspend;
  9.    END

Code: Pascal  [Select][+][-]
  1.    IBQuery1.SQL.Text := 'Select * from returnavalue';
  2.    IBQuery1.Open;
  3.  

I haven't tested these codes themselves. But other codes work in that way. I think writing stored procedures in the second way is better for (possible) later compatibility.
« Last Edit: November 03, 2020, 10:57:34 am by egsuh »

rvk

  • Hero Member
  • *****
  • Posts: 6110
Re: Difference between TSQLQuery from FPC and IBX TIBQuery
« Reply #1 on: November 03, 2020, 10:27:17 am »
...will display single row. But TIBQuery will return null dataset (even without field names).
And TIBQuery does it the correct way.

I haven't tested these codes themselves. But other codes work in that way. I think writing stored procedures in the second way is better for (possible) later compatibility.
The correct way is to work with suspend. That's not just a compatibility issue, that's the correct way.

http://www.janus-software.com/fbmanual/manual.php?book=PSQL&topic=104
Quote
Use SUSPEND to return a row of data from a procedure to its caller.

http://www.firebirdfaq.org/faq202/
Quote
If you write a SELECT that should return some rows, you need to use the same syntax as with stored procedures. That means that you must declare the columns in RETURNS clause, use INTO or assignment to supply values to those fields and use SUSPEND to inform the client that record is available.

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: Difference between TSQLQuery from FPC and IBX TIBQuery
« Reply #2 on: November 03, 2020, 10:57:21 am »
Thank you rvk,
I don't care much about in/correctness, but if you say so, it would be so.

tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: [SOLVED] Difference between TSQLQuery from FPC and IBX TIBQuery
« Reply #3 on: November 06, 2020, 02:00:17 pm »
I think the underlying problem here is that while TSQLQuery and TIBQuery have similar sounding names, they are not necessarily intended to do the same job.

Firebird DML statements can be roughly divided up into those which return or use a cursor and those which don't. You get a cursor as the result of a select query execution. It is a handle on the results of the query and repeated calls to the Fetch API (with that handle) return successive rows of the dataset (query result). The data in the columns of each row can then be read. On the other hand, statements such as UPDATE and EXECUTE PROCEDURE do not return a cursor. They can (e.g. UPDATE ... RETURNING) return a singleton row, but the underlying API is not the same as when you have a cursor.

A stored procedure can be run either with the EXECUTE PROCEDURE statement or from a SELECT statement.

* In the first case, only a singleton row, at most, is returned. If the procedure includes a SUSPEND statement ("not recommended" by the Firebird docs) then it is treated as if it is an EXIT statement and execution stops the first time the SUSPEND is executed (which is how the example in the original post worked).

* In the second case, the stored procedure is being executed in a context where multiple rows are being returned. It must include a SUSPEND statement and typically as part of a loop. Every time the SUSPEND is executed, the current contents of the output variables are returned as the next row. The SUSPEND returns once the row has been fetched by the client, and the stored procedure can go on to prepare the next row, and so on.

The low level IBX component TIBSQL does its best to harmonise these two types of statement. When a cursor is returned, it will by default also fetch the first row and you can get straight to the row's data (e.g. by using TIBSQL.FieldByName to get a specific column's value). You can then read successive rows calling TIBSQL.Next until TIBSQL.EOF is true. When a singleton row is returned, it simply looks like the last row in a dataset i.e. EOF is true, and calling NEXT results in an error.

I can't really speak for TSQLQuery, but TIBQuery is a higher level component and intended to be used with a query that returns a cursor (i.e. a select statement), and to buffer the rows fetched from the database so that they can be displayed in (e.g.) a TDBGrid. TIBQuery is not intended to be used with SQL statements that return a single row (e.g. Execute Procedure). It will execute them (using its internal TIBSQL component), but will ignore any singleton row returned - if for no better reason other than it does not set up a buffer pool for non-select queries.

If you want to execute an SQL statement that can return a singleton row then you should use TIBSQL to do this. If you would rather not write an SQL statement, you can use TIBStoredProc to execute a stored procedure (with a higher overhead - but hey you don't have to learn SQL).

I suppose that TIBQuery could be made to set up a buffer pool for a singleton row result. However, all that means is that you end with a higher overhead - and have to write an SQL statement. Use TIBSQL for "Execute Procedure", and TIBQuery as a source for a TDBGrid and you won't go far wrong.
« Last Edit: November 06, 2020, 02:02:01 pm by tonyw »

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: [SOLVED] Difference between TSQLQuery from FPC and IBX TIBQuery
« Reply #4 on: November 22, 2020, 03:00:15 am »
Code: Pascal  [Select][+][-]
  1. The low level IBX component TIBSQL does its best to harmonise these two types of statement. When a cursor is returned, it will by default also fetch the first row and you can get straight to the row's data (e.g. by using TIBSQL.FieldByName to get a specific column's value). You can then read successive rows calling TIBSQL.Next until TIBSQL.EOF is true. When a singleton row is returned, it simply looks like the last row in a dataset i.e. EOF is true, and calling NEXT results in an error.

I've compared TIBQuery and TSQLQuery because this did not work. I'll try again. Basically I hate overhead (my head is over already). Actually I use suspend in the stored procedure just like exit. Once a condition is met, it returns singlenton row and that's it. But it's little bit strange suspend is not recommended. Anyway the basic use is  for...select...suspend;

 

TinyPortal © 2005-2018