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

Robert,

Bang on the nail!
"New Session Properties" statement separator to "~" (I do a little
concatenating of fields) new session and a slight tweak to the syntax
(DB2/400 has always got to be a little bit more pedantic than the rest!)
And hey presto a nice new stored proc; tried it on my original proc and
that works as well.

Thanks
Nick

p.s. There is a 2nd edition of "DB2 SQL Procedure Language for Linux,
UNIX, Windows, i5/OS, and z/OS" couldn't have survived without it. You
don't get  
DB2 Control Center with i5/OS, we have iSeries Navigator and very little
help!

drop table employee
|
create table employee ( midinit CHAR, empno CHAR(6) )
|
create procedure simple_error ( IN p_midinit CHAR
                                ,IN p_empno CHAR(6) )
LANGUAGE SQL
specific simple_error
se: BEGIN
        DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
        DECLARE X CHAR(1);
        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
                SET X = 'x';
        END IF;
END se
|

-----Original Message-----
From: Robert Manning [mailto:robert.m.manning@xxxxxxxxx] 
Sent: Wednesday, July 04, 2007 2:41 AM
To: Forder, Nick
Cc: squirrel-sql-users@xxxxxxxxxxxxxxxxxxxxx
Subject: Re: [Squirrel-sql-users] 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


CONFIDENTIALITY NOTICE
The information in this email may be confidential and/or privileged. 
This email is intended to be reviewed by only the addressee(s) named 
above. If you are not the intended recipient, you are hereby notified 
that any review, dissemination, copying, use or storage of this email 
and its attachments, if any, or the information contained herein is 
prohibited. If you have received this email in error, please 
immediately notify the sender by return email and delete this email 
from your system. Thank you.

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