Re: Creating Stored procedure on DB2/400 (System i5, OS400 etc )

On 7/3/07, Robert Manning <robert.m.manning@xxxxxxxxx> wrote:
> On 7/3/07, Robert Manning <robert.m.manning@xxxxxxxxx> wrote:
> > framework.  Until then, you should still be able to use SQuirreL in
> > the manner that you did previously - at least that was our intent.
>
> Well now it appears we have a bug from the addition of the custom
> query tokenizer framework.  We are now caching the original query
> tokenizer and not updating it's properties each time it is requested.
> What this means is that you cannot set the session properties for
> query tokenizer (statement sep, comment, multi-line comment) and
> expect that to apply to the current session.  You need to set the "New
> Session Properties" and restart the session to have them take effect.
> Not so convenient now is it?  We'll get that fixed up quickly.
> However, I can see that the stored procedure that you sent is still
> rejected by my DB2 V9 on Linux.  The error message seems to point to
> the DECLARE statement:

Yes, indeed there is something wrong with the procedure definition you
sent.  I can set the statement separator to "|", restart the DB2
session then execute the following just fine (in the DB2 Control
Center as well as in SQuirreL):

create table employee ( midinit CHAR, empno CHAR(6) )
|

create procedure simple_error ( IN p_midinit CHAR
                                ,IN p_empno CHAR(6) )
specific simple_error
LANGUAGE SQL
se: BEGIN
        DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
        DECLARE SQLCODE INT DEFAULT 0;
        UPDATE employee
                SET midinit = p_midinit
                WHERE empno = p_empno;
        IF SUBSTR(SQLSTATE, 2) NOT in ('00', '01', '02') THEN
        END IF;
END se
|

I obtained this sample procedure def from the sample chapter (no. 5)
of the book "DB2 SQL Procedure Language for Linux, UNIX, and Windows"
by Drew Bradstock, et. al., which is available on IBM's website
(http://www.tinyurl.com/ys66u3)

Try setting your File -> "New Session Properties" statement separator
to "|" (pipe) and then reconnect to DB2 and execute the above.  Let me
know how it goes.  Oh, and we'll have a fix for the bug in a jiffy
(snapshot release and 2.6 - we are not planning on a 2.5.2 release at
this point)

Rob

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/