Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
4 replies Latest Post - ‏2014-07-01T14:58:30Z by krmilligan
Ajay Kulkarni
Ajay Kulkarni
19 Posts
ACCEPTED ANSWER

Pinned topic Stored Procedure - 7.1 TR7 default values

‏2014-06-24T20:22:16Z |
Hello DB2 for i experts, 

We are on 7.1 TR7 and i am not able to use => operator when using default values in stored procedure.

Below is the stored procedure interface

================================================

Create Or Replace Procedure Sp_ChkPOErr                       
     (                                                        
       Out   OP_Err_DD    char(4) ,                           
       Out   OP_Err_Desc  char(40) ,                          
       Out   OP_Err_Fld   char(1) ,                           
                                                              
       In    ErrType      char(10)     Default  ' ',          
       In    Input1       char(25)     Default  ' ',          
       In    Input2       char(25)     Default  ' ',          
       In    Input3       char(25)     Default  ' ',          
       In    Input4       char(25)     Default  ' ',          
       In    Input5       char(25)     Default  ' ',          
       In    Input6       char(25)     Default  ' ',          
       In    Input7       char(25)     Default  ' '           
      )                                                       
Specific Qgpl.Sp_ChkPOErr                                     
Language  Rpgle                                               
NOT DETERMINISTIC                   
NO SQL                              
External  Name 'AJAY/P55SPPOERR'    
Parameter Style General             
===================================================

 

When we try to test this using Run SQL script we are getting an error:

 

Example CALL Sp_ChkPOErr(?,?,?,ErrType=>'SBL',Input2=>'12345');
 
Error
SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token ` was not valid. Valid tokens: ( CL END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN. Cause . . . . . :   A syntax error was detected at token `.  Token ` is not a valid token.  A partial list of valid tokens is ( CL END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN.  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 `. 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.
==========================
 
 
*entry for the RPGLE program
 
      dcl-pi  P55SPPOERR  ;                    
          OP_Err_DD   char(4)  ;               
          OP_Err_Desc char(40) ;               
          OP_Err_Fld  char(1) ;                
          ErrType     char(10) ;               
          Input1      char(25) ;               
          Input2      char(25) ;               
          Input3      char(25) ;               
          Input4      char(25) ;               
          Input5      char(25) ;               
          Input6      char(25) ;               
          Input7      char(25) ;               
      end-pi;                                  
=====================================

If we don't use the operator => and instead call using traditional approach we are getting the results

CALL Sp_ChkPOErr(?,?,?,'SBL',' ','12345 ''); 

  • krmilligan
    krmilligan
    447 Posts
    ACCEPTED ANSWER

    Re: Stored Procedure - 7.1 TR7 default values

    ‏2014-06-30T17:16:07Z  in response to Ajay Kulkarni

    Did you verify that you didn't accidentally use the wrong single quote character?  The error message says that ` is being used instead of '.  I frequently hit this mistake when copying and pasting SQL examples from Microsoft documents.

    If you really are using ', then you'll probably need to open a PMR with IBM Support.

    • Ajay Kulkarni
      Ajay Kulkarni
      19 Posts
      ACCEPTED ANSWER

      Re: Stored Procedure - 7.1 TR7 default values

      ‏2014-06-30T23:19:16Z  in response to krmilligan
      Thanks Kent, but looks like there is something going on if the length of the second parameter is greater tehn the first
      Look at this simple example, it fails, (but it works if @OUT1 is 25 character long)
       
      ==================================================
      CREATE OR REPLACE PROCEDURE QGPL.TESTOUT (      
      @OUT1 OUT CHAR(5),                              
      @OUT2 OUT CHAR(50),                             
      @A   IN  INT  DEFAULT 5,                        
      @B   IN  CHAR(2) DEFAULT '')                    
      BEGIN                                           
          SET @OUT1='a'||VARCHAR(@A)||@B;             
          SET @OUT2='testing large'    ;              
      END                                             
      ==========================================
       
      CALL QGPL.TESTOUT (?,?,@A=>2,@B=>'a');
       
      =========================================
      Error
      SQL State: 01004
      Vendor Code: 445
      Message: [SQL0445] Value of parameter 2 in procedure TESTOUT in QGPL too long. Cause . . . . . :   Parameter 2, which is declared as OUT or INOUT, contains a value that is longer than the maximum length string that can be stored in host variable *N. Parameter 2 is being returned from procedure TESTOUT in QGPL to host variable *N. Trailing blanks are not included in the length of the string for character values.  Trailing hex zeros are not included in the length of the string for binary values. The length of the parameter is 50 and the length of the host variable is 5. Recovery  . . . :   Increase the length of the host variable from 5 to 50. Try the request again.
       
      Statement ran successfully, with warnings   (64 ms)
      ===============================
  • Ajay Kulkarni
    Ajay Kulkarni
    19 Posts
    ACCEPTED ANSWER

    Re: Stored Procedure - 7.1 TR7 default values

    ‏2014-06-30T23:19:17Z  in response to Ajay Kulkarni
    Thanks Kent, but looks like there is something going on if the length of the second parameter is greater tehn the first
    Look at this simple example, it fails, (but it works if @OUT1 is 25 character long)
     
    ==================================================
    CREATE OR REPLACE PROCEDURE QGPL.TESTOUT (      
    @OUT1 OUT CHAR(5),                              
    @OUT2 OUT CHAR(50),                             
    @A   IN  INT  DEFAULT 5,                        
    @B   IN  CHAR(2) DEFAULT '')                    
    BEGIN                                           
        SET @OUT1='a'||VARCHAR(@A)||@B;             
        SET @OUT2='testing large'    ;              
    END                                             
    ==========================================
     
    CALL QGPL.TESTOUT (?,?,@A=>2,@B=>'a');
     
    =========================================
    Error
    SQL State: 01004
    Vendor Code: 445
    Message: [SQL0445] Value of parameter 2 in procedure TESTOUT in QGPL too long. Cause . . . . . :   Parameter 2, which is declared as OUT or INOUT, contains a value that is longer than the maximum length string that can be stored in host variable *N. Parameter 2 is being returned from procedure TESTOUT in QGPL to host variable *N. Trailing blanks are not included in the length of the string for character values.  Trailing hex zeros are not included in the length of the string for binary values. The length of the parameter is 50 and the length of the host variable is 5. Recovery  . . . :   Increase the length of the host variable from 5 to 50. Try the request again.
     
    Statement ran successfully, with warnings   (64 ms)
    ===============================
    • krmilligan
      krmilligan
      447 Posts
      ACCEPTED ANSWER

      Re: Stored Procedure - 7.1 TR7 default values

      ‏2014-07-01T14:58:30Z  in response to Ajay Kulkarni

      Looks like you have possibly found some defects that should be reported to IBM Support.