Recent

Author Topic: Returning No Value from procedure  (Read 377 times)

egsuh

  • Sr. Member
  • ****
  • Posts: 271
Returning No Value from procedure
« on: December 10, 2019, 04:25:43 am »
Hi,

Following is a Firebird procedure, just with headings (returning no value).

Quote
SET TERM ^ ;
ALTER PROCEDURE ZZZ_TEST
RETURNS (
    RES integer )
AS
BEGIN
END^
SET TERM ; ^

But when I execute following statement in Lazarus,

Quote
   query1.SQL.Text:= 'execute procedure zzz_test';
   query1.Open;
   if query1.IsEmpty then showmessage('Empty') else ShowMessage('Not empty');

The result is "Not empty".  I don't think this is a bug, but still feel a little unnatural.  To check whether the result is null, I have to do something like

   
Quote
if query1.Fields[0].AsString='' then {  something     }; 

Is there any other way to check emptiness of the query?   Is this the issue of Firebird procedure or Lazarus TSQLQuery?  I'm not to do any blame.

GetMem

  • Hero Member
  • *****
  • Posts: 3564
Re: Returning No Value from procedure
« Reply #1 on: December 10, 2019, 06:09:03 am »
@egsuh

If you wish to return something you need a "suspend", like this:
Code: MySQL  [Select]
  1. SET TERM ^ ;
  2.     RES integer )
  3.   Res = 0;
  4.   Suspend;
  5. SET TERM ; ^

Then from lazarus use a select not an execute:

Code: Pascal  [Select]
  1. query1.SQL.Text:= 'select res from zzz_test';
  2. query1.Open;
  3. ShowMessage(query1.FieldByName('RES').AsString);
  4.  


egsuh

  • Sr. Member
  • ****
  • Posts: 271
Re: Returning No Value from procedure
« Reply #2 on: December 10, 2019, 07:51:04 am »
@GetMem,

Thank you for your clarification.

Well returning any value is not a problem. "Executing" a procedure without SUSPEND works as expected.

I tried your approach, without assigning any value.

    SET TERM ^ ;
    CREATE OR ALTER PROCEDURE ZZZ_TEST
    RETURNS (
        RES integer )
    AS
    BEGIN
      Suspend;
    END^
    SET TERM ; ^

But still the query is not empty when opened, i.e. query1.IsEmpty is false. 
Problem is that this is not consistent with other query results. For example, if following query does not return any record then query1.IsEmpty is true.
 
   query1.sql.text := 'select from testtable where id is null';



GetMem

  • Hero Member
  • *****
  • Posts: 3564
Re: Returning No Value from procedure
« Reply #3 on: December 10, 2019, 08:37:01 am »
Quote
Well returning any value is not a problem. "Executing" a procedure without SUSPEND works as expected.
You must use suspend if you wish to return something. There is no way arround.

Quote
But still the query is not empty when opened, i.e. query1.IsEmpty is false. 
Ok, I can confirm this. It's a bug. The same db, same stored procedure, when queried with IBObjects(another db. component), returns isempty= true.
As a workaround when querying stored procedures, don't use IsEmpty, but check if recordcount is 1 and the fieldname is null, like this:
Code: Pascal  [Select]
  1. Query1.Open;
  2. if (Query1.RecordCount = 1) and (Query1.FieldByName('RES').IsNull) then
  3.   ShowMessage('is empty')
  4. else
  5.   ShowMessage('not empty');
  6.  

and the stored procedure
Code: MySQL  [Select]
  1.     RES integer)
  2. declare variable dummy integer;
  3.   /*when you wish to  signal isempty*/
  4.   res = null;
  5.   suspend;

 

egsuh

  • Sr. Member
  • ****
  • Posts: 271
Re: Returning No Value from procedure
« Reply #4 on: December 10, 2019, 09:36:28 am »
Quote
You must use suspend if you wish to return something. There is no way arround.

Well following works fine.

Code: Pascal  [Select]
  1. SET TERM ^ ;
  2. ALTER PROCEDURE ZZZ_TEST
  3. RETURNS (
  4.     RES integer )
  5. AS
  6. BEGIN
  7.   :res = 999;
  8. END^
  9. SET TERM ; ^
  10. ////////////////////////////////////
  11. query1.SQL.Text:= 'execute procedure zzz_test';
  12. query1.Open;
  13. ShowMessage(query1.fields[0].AsString);
  14.  

tonyw

  • Full Member
  • ***
  • Posts: 155
    • MWA Software
Re: Returning No Value from procedure
« Reply #5 on: December 10, 2019, 11:01:50 am »
With an issue like this, it is always worth being aware of how the underlying database API works.

When stored procedures use SUSPEND then they should be called using an SQL Select Query. The Firebird API is used to open a cursor and then you read in the result row by row in the same way as you would any other select query. Your stored procedure returns another row everytime it calls SUSPEND. If the stored procedure exits before calling SUSPEND then the results set should be empty i.e. record count = 0.

When a stored procedure does not use SUSPEND then the procedure should be "executed" using an EXEC PROCEDURE statement and may return a singleton row if any output parameters are specified in the procedure declaration. The Firebird API does not allocate a cursor and the singleton row is returned by the execute statement. In this case, you always get a row with a column for each of the output parameters. Their values may be null, but the columns are always present.

You can execute (EXEC PROCEDURE)  a stored procedure with a SUSPEND statement but you can never return more than one row.

As a general point, if you are not calling a stored procedure with a SUSPEND statement using a SELECT query then why have you put a SUSPEND statement in it? SUSPEND is only useful when you are returning more than one row from the stored procedure.