|
From: Forder, N. <Nic...@su...> - 2007-07-03 13:38:24
|
After upgrading to V2.5 I can no longer create a stored procedure in
DB2/400.
I used to change the statement separator in the session properties to
something other than ; and I could get by, by highlighting the text and
running it.
But sadly no longer. I get this sql exception suggesting malformed sql
syntax when I terminate a stored procedure statement line with ;
I guess this is result from fixing a bug in the DB2 LUW or Z/OS
environments.
Any suggestions from the OS/400 guys would be great I have made SQuirrel
my shop standard and have loved it up to now.
CREATE PROCEDURE NICKLIB.SP_GETWODET_FROM_POITMN (
IN P_PO CHARACTER ( 6 ) ,
IN P_ITMN CHARACTER ( 4 ) ,
OUT P_STATUS INTEGER ,
OUT P_STATUS_TEXT VARCHAR ( 100 ) ,
OUT P_JOB CHARACTER ( 8 ) ,
OUT P_RLWO CHARACTER ( 14 ) ,
OUT P_DESC CHARACTER ( 40 ) ,
OUT P_QTYT DECIMAL ( 11,3 ) ,
OUT P_LC CHARACTER ( 2 ) ,
OUT P_IND CHARACTER ( 2 ) ,
OUT P_CMPL CHARACTER ( 2 ) ,
OUT P_BIN CHARACTER ( 10 ) ,
OUT P_NOTE VARCHAR ( 1000 )
)
LANGUAGE SQL
SPECIFIC NICKLIB.SP_GETWODET_FROM_POITMN
DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
BEGIN
DECLARE v_counter INT Default 1 ;
END ;
Error: java.sql.SQLException: [SQL0104] Token <END-OF-STATEMENT> was not
valid. Valid tokens: ;. Cause . . . . . : A syntax error was detected
at token <END-OF-STATEMENT>. Token <END-OF-STATEMENT> is not a valid
token. A partial list of valid tokens is ;. This list assumes that the
statement is correct up to the token. The error may be earlier in the
statement, but the syntax of the statement appears to be valid up to
this point. Recovery . . . : Do one or more of the following and try
the request again: -- Verify the SQL statement in the area of the token
<END-OF-STATEMENT>. Correct the statement. The error could be a missing
comma or quotation mark, it could be a misspelled word, or it could be
related to the order of clauses. -- If the error token is
<END-OF-STATEMENT>, correct the SQL statement because it does not end
with a valid clause., SQL State: 42601, Error Code: -104
Error occured in:
CREATE PROCEDURE NICKLIB.SP_GETWODET_FROM_POITMN (
IN P_PO CHARACTER ( 6 ) ,
IN P_ITMN CHARACTER ( 4 ) ,
OUT P_STATUS INTEGER ,
OUT P_STATUS_TEXT VARCHAR ( 100 ) ,
OUT P_JOB CHARACTER ( 8 ) ,
OUT P_RLWO CHARACTER ( 14 ) ,
OUT P_DESC CHARACTER ( 40 ) ,
OUT P_QTYT DECIMAL ( 11,3 ) ,
OUT P_LC CHARACTER ( 2 ) ,
OUT P_IND CHARACTER ( 2 ) ,
OUT P_CMPL CHARACTER ( 2 ) ,
OUT P_BIN CHARACTER ( 10 ) ,
OUT P_NOTE VARCHAR ( 1000 )
)
LANGUAGE SQL
SPECIFIC NICKLIB.SP_GETWODET_FROM_POITMN
DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
BEGIN
DECLARE v_counter INT Default 1
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.
|
|
From: Robert M. <rob...@gm...> - 2007-07-03 16:19:39
|
On 7/3/07, Forder, Nick <Nic...@su...> wrote: > > > > > After upgrading to V2.5 I can no longer create a stored procedure in > DB2/400. > > > > I used to change the statement separator in the session properties to > something other than ; and I could get by, by highlighting the text and > running it. > > But sadly no longer. I get this sql exception suggesting malformed sql > syntax when I terminate a stored procedure statement line with ; > > I guess this is result from fixing a bug in the DB2 LUW or Z/OS > environments. > > > > Any suggestions from the OS/400 guys would be great I have made SQuirrel my > shop standard and have loved it up to now. 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. I'll run your sample procedure through the debugger and see if I can't figure out why it isn't working, and get back to you if I find anything. In the meantime, if you have any of the above plugins installed, could you check there configuration panel in Global Preferences and try turning off the custom tokenizer (should have no effect but just in case a bug is causing a plugin to think that your DB2 session is one that it should modify). Also, I suppose you are not using ";" as the statement separator in session properties, correct? ( if you are, then that would be a problem you can fix by picking a different sequence of character(s) beside ";" that aren't found in your procedure) Rob |
|
From: Robert M. <rob...@gm...> - 2007-07-04 00:56:22
|
On 7/3/07, Robert Manning <rob...@gm...> 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 |
|
From: Robert M. <rob...@gm...> - 2007-07-04 01:41:02
|
On 7/3/07, Robert Manning <rob...@gm...> wrote:
> On 7/3/07, Robert Manning <rob...@gm...> 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
|
|
From: Forder, N. <Nic...@su...> - 2007-07-04 08:28:26
|
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:rob...@gm...]
Sent: Wednesday, July 04, 2007 2:41 AM
To: Forder, Nick
Cc: squ...@li...
Subject: Re: [Squirrel-sql-users] Creating Stored procedure on DB2/400
(System i5, OS400 etc )
On 7/3/07, Robert Manning <rob...@gm...> wrote:
> On 7/3/07, Robert Manning <rob...@gm...> 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.
|
|
From: Robert M. <rob...@gm...> - 2007-07-04 10:21:44
|
On 7/4/07, Forder, Nick <Nic...@su...> wrote: > 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. > Very good. I have a fix in CVS, which will be available in this weekends snapshot if you want to try it out. It should allow you to change statement separator temporarily for the current session without having to restart (just as it worked in versions prior to 2.5) Rob |
