Helen,
SQL Explorer does support reading return values - as long as you use SUSPEND
or EXIT :-)
and BTW - the old docs are quite clear - and I quote "SUSPEND should not be
used in an executable procedure. Use EXIT instead..." page 161 Language
Reference
I grant you that I still refer to old docs (Version 5.0) but my previous
post is also taken from this section and it has never produced errors or
wrong results for me.

Alan
-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Tuesday, 27 August 2002 23:14
To: ib-support@yahoogroups.com
Subject: RE: [ib-support] Re: problem with very simple Stored Procedure


At 10:14 PM 27-08-02 +1000, you wrote:

>you should always have the suspend; in there
>even SQL Explorer does not like the absence of suspend

You need suspend only if you want to return a multi-row dataset, i.e. your
stored procedure outputs potentially multiple rows. The purpose of
SUSPEND
is literally to suspend processing whilst the current output row from a
cursor operation is passed to the buffer.

In the case of a SP which is executed and returns a set of output
parameters, it is expected that your client will read the output
parameters
returned from an EXECUTE PROCEDURE statement in the data parameter buffer,
not try to use the dataset buffer to acquire the result as a singleton
select.

The advice to always use SUSPEND on queries that return output is
therefore
a bit misplaced. In an EXECUTE procedure, SUSPEND means exactly the same
thing as EXIT.

AFAIK, SQL Explorer doesn't support reading return parameters.

>the unexpected end of command is the END statement since there is nothing
>telling the parse to actually return or what to return

CREATE PROCEDURE SET_TIMEOUT
AS
begin
update online_ set timeout = current_timestamp;
end

If this was the stored procedure, submitted interactively, then the parser
of your client program can not dealing properly with the SET TERM
statements in order to present a complete CREATE PROCEDURE statement. A
SP
is a set of statements within a statement, that begins with CREATE
PROCEDURE and ends with the END ^ statement on the outermost BEGIN/END
block (or whatever terminator you set with SET TERM). You need the
alternative terminator character because, within the CREATE PROCEDURE
statement, the PSQL statements must be terminated with semicolons. As it
is, it appears that the compiler reaches the semicolon and barfs because
the terminator wasn't set to tell it that CREATE PROCEDURE statement would
be terminated by ^.

I think you also reported an Unknown Token error when submitting the
statement after setting the terminator. That will be the END statement
floating about with no terminator.

In a script or in ISQL, you would present the CREATE PROCEDURE statement
like this:

SET TERM ^; /* changes the terminator for the next statement to '^') */
CREATE PROCEDURE SET_TIMEOUT
AS
begin
/* statement within a statement, uses the semicolon terminator as required
by the compiler */
update online_ set timeout = current_timestamp;
end ^ /* terminator of the CREATE PROCEDURE statement */

SET TERM ; ^ /* resets the terminator to semicolon */

So, the question remaining is: were you trying to submit the statement
interactively or as a script?
-- If interactively, then the parser of your query tool might be broken
(it's failing to perform a SET TERM); or it is expecting you to perform a
SET TERM yourself before submitting the CREATE PROCEDURE statement.
-- If you were using Script Executive, then you just have some omissions
to
rectify.

heLen



Yahoo! Groups Sponsor
ADVERTISEMENT



To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]