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

On 7/3/07, Robert Manning <robert.m.manning@xxxxxxxxx> wrote:
> > After upgrading to V2.5 I can no longer create a stored procedure in
> > DB2/400.
>
> Nick,
>
> In 2.5 we introduced a framework to allow plugins to install a custom
> QueryTokenizer (the class responsible for chopping up a selection in a
> script into individual statements).  This has been used in the Oracle
> plugin and the SQLServer plugins (MS and Sybase) to enable them to
> handle tokenizing procedural blocks into complete statements without
> forcing the user to manipulate the statement separator.  The DB2
> plugin has not yet been updated to use this new framework, so I am at
> a loss as to why there would be a change in behavior (the old behavior
> was kept as the default when not overridden by a session plugin).
> There are plans to update each of the plugins that don't yet use this
> 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:

An unexpected token "END" was found following "nter INT Default 1".
Expected tokens may include:  "
". SQL Code: -104, SQL State: 42601

In the debugger I see both semi-colons in one long string being sent
to the server.  I tested this in the DB2 control center and got the
same result (I used "|" as the statement separator instead of ";").
How can I verify that this stored procedure is valid?

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/