Returning No Value from procedure
lazarus / 16-Databases

----------------------------------------------------------------------
egsuh · 2019-12-10 03:25

Hi,

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

QuoteSET 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

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

----------------------------------------------------------------------
balazsszekely · 2019-12-10 05:09

@egsuh

If you wish to return something you need a "suspend", like this:
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SET TERM ^
;CREATE OR ALTER PROCEDURE ZZZ_TESTRETURNS (    RES integer )ASBEGIN 
Res = 0;  Suspend;END^SET TERM ; ^
Then from lazarus use a select not an execute:

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
query1.SQL.Text:= 'select res from
zzz_test';query1.Open;ShowMessage(query1.FieldByName('RES').AsString);

----------------------------------------------------------------------
egsuh · 2019-12-10 06:51

@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';

----------------------------------------------------------------------
balazsszekely · 2019-12-10 07:37

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

QuoteBut 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Query1.Open;if (Query1.RecordCount = 1) and
(Query1.FieldByName('RES').IsNull) then  ShowMessage('is empty')else 
ShowMessage('not empty'); 
and the stored procedure
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i create or
alter procedure ZZZ_TESTreturns (    RES integer)ASdeclare variable
dummy integer;begin  /*when you wish to  signal isempty*/  res =
null;  suspend;end

----------------------------------------------------------------------
egsuh · 2019-12-10 08:36

QuoteYou must use suspend if you wish to return something. There is no
way arround.
Well following works fine.

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 SET TERM ^
;ALTER PROCEDURE ZZZ_TESTRETURNS (    RES integer )ASBEGIN  :res =
999;END^SET TERM ;
^////////////////////////////////////query1.SQL.Text:= 'execute
procedure
zzz_test';query1.Open;ShowMessage(query1.fields[0].AsString);

----------------------------------------------------------------------
tonyw · 2019-12-10 10:01

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.