On Thu, Mar 16, 2006 at 11:05:20AM -0600, Jef Gearhart wrote:

> I've searched long and hard, but nobody seems to have had the same
> consistent difficulty with creating a simple stored procedure with
> JayBird/Firebird. I feel like I'm beating my head against a wall..
>
> I have tried this statement (and many variations) using all of the
> following:
>
> ISQL
> Squirrel 2.1 final
> MyEclipse IDE Database Explorer
> Direct JDBC access from my Java code
>
> I get consistent failures from all..
>

That's probably making a strong case for this not being a
Jaybird-specific problem, however...

> ------- ISQL session...
>
> -- attempt 1
>
> SQL> CREATE PROCEDURE SELTEST AS BEGIN SELECT * FROM COMPUTER; END
> CON> ;
> Statement failed, SQLCODE = -104
>
> Dynamic SQL Error
> -SQL error code = -104
> -Token unknown - line 1, char 57
> -;

The problem is that you can't just run a select query within a procedure
like that without doing something with the results. If, for example, you
had an ID field in your COMPUTER table and you wanted to return all IDs
from your procedure, you could do the following:

CREATE PROCEDURE SELTEST RETURNS (ID INTEGER)
AS BEGIN
FOR SELECT ID FROM COMPUTER INTO :ID
DO
SUSPEND;
END


If you're doing this in ISQL, you'll have to set the TERM value properly
(as you were already doing in one of your examples).

For a lot more information, see the Language Reference at
<http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_download_documentation>


- Gabriel