At 04:05 AM 17/03/2006, 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..

Stop beating your head! The answer is simple.

1. In dynamic SQL (a.k.a. DSQL), which is the flavour that you use in
your applications, you need a syntactically valid SP
declaration. You don't have it. Amongst other things, SELECT
statements in procedures have a special required syntax (details below).

FYI, in DSQL, the semi-colon is used *only* as a statement terminator
and *only* when defining PSQL objects (stored procedures or triggers
or, in Firebird 2, within the new CREATE BLOCK construct). Placing a
semicolon following any END statement is illegal syntax. Still, it's
not the whole story of your failed attempts.

>I have tried this statement (and many variations) using all of the
>following:
>
>ISQL

ISQL is a tool, that has some special rules (q.v.)

>Squirrel 2.1 final
>MyEclipse IDE Database Explorer
>Direct JDBC access from my Java code

These three are all DSQL interfaces.

>I get consistent failures from all..

Nope. You get the same exception for different reasons. We've
established that the exception you get in your DSQL interfaces is due
to the semi-colon following the END statement.

Now, let's look at the ISQL attempts.

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

This one has two causes. First, you didn't switch the ISQL
terminator to "anything but a semi-colon". SET TERM needed
here. But, even with the correct terminator this would be wrong,
because of the semicolon following END.

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

Now (with no alt terminator) we have a PSQL statement with no
terminator at the end of the SELECT statement. We also have an
illegal semicolon following the END statement, of course.

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

Now, we have a legal terminator for the enclosing statement but we
are getting a syntax error for the colon at the end of the SELECT
statement. Why? Because SELECT statements in PSQL must pass their
output to variables. The variables can be local or they can be
assigned to declared output arguments.

So, here's a valid do-nothing test for you using correct syntax for
both ISQL and PSQL:

SQL> SET TERM ^^;
SQL> CREATE PROCEDURE SELTEST
SQL> RETURNS (AVAR SMALLINT) AS
CON> DECLARE VARIABLE BVAR CHAR;
CON> BEGIN SELECT 1, 'A' FROM RDB$DATABASE
CON> INTO :AVAR, :BVAR;
CON> SUSPEND;
CON> END ^^
SQL> SET TERM ;^^
SQL> SELECT * FROM SELTEST;

AVAR BVAR
===========
1 A
SQL>

To make this into a valid string for your DSQL...first, forget SET
TERM, it's valid only inside ISQL (which includes scripts).

This statement will created your procedure:

"CREATE PROCEDURE SELTEST RETURNS (AVAR SMALLINT) AS DECLARE VARIABLE
BVAR CHAR; BEGIN SELECT 1, 'A' FROM RDB$DATABASE INTO :AVAR, :BVAR;
SUSPEND; END"

I expect you have a tidy way to construct statements for
Java/JDBC. In Pascal, we break them up into tidy pieces and poke
them into a stringlist. :-)

Helen