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.