IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQLCODE 518 while executing DB2 module.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore

PostPosted: Wed Feb 07, 2018 10:46 am
Reply with quote

Hi,

I am trying to build the sql's dynamically via my program and trying to execute but I am getting 518 sqlcode. Could you please help me resolve this.

Details are as follows:
Working storage declaration:
Code:
****************************************************************
*                 SQL CURSORS AND STATEMENTS                     
****************************************************************
*                                                               
       EXEC SQL                                                 
           DECLARE STMT-SELECT STATEMENT                         
       END-EXEC.                                                 
                                                                 
       EXEC SQL                                                 
           DECLARE C1 CURSOR                                     
           FOR STMT-SELECT                                       
       END-EXEC.                                                 


Building the sql:
Code:

INITIALIZE WS-DAILY-SELECT.                         
STRING                                             
 'SELECT  XS_SHIP_NO'           DELIMITED BY SIZE   
        ',XS_VESSEL_NAME'       DELIMITED BY SIZE   
        ',XS_CAPACITY'          DELIMITED BY SIZE   
        ',XS_VESSEL_TYPE'       DELIMITED BY SIZE   
        ',XS_SERVICE_CD'        DELIMITED BY SIZE   
        ',XS_SERVICE_NAME'      DELIMITED BY SIZE   
        ' FROM VEXPORT.TSHIP'   DELIMITED BY SIZE   
        ' WHERE'                DELIMITED BY SIZE   
 INTO WS-DAILY-SELECT                               
 END-STRING.                                       
                                                   
INITIALIZE WS-WHERE-CLAUSE-01                       
STRING                                             
'(XS_SHIP_NO >=:WS-SHIP-NUM'   DELIMITED BY SIZE   
' OR XS_SHIP_NO LIKE ('          DELIMITED BY SIZE 
''''                            DELIMITED BY SIZE   
WS-PERCENT                      DELIMITED BY SIZE   
''''                            DELIMITED BY SIZE   
'|| RTRIM(:WS-SHIP-NUM) ||'     DELIMITED BY SIZE   
''''                            DELIMITED BY SIZE   
WS-PERCENT                      DELIMITED BY SIZE   
'''))'                          DELIMITED BY SIZE   
' AND (XS_VESSEL_NAME LIKE ('    DELIMITED BY SIZE 
''''                            DELIMITED BY SIZE   
WS-PERCENT                      DELIMITED BY SIZE   
''''                            DELIMITED BY SIZE   
'|| RTRIM(:WS-SHIP-NAME) ||'    DELIMITED BY SIZE   
''''                            DELIMITED BY SIZE   
WS-PERCENT                      DELIMITED BY SIZE   
'''))'                          DELIMITED BY SIZE   
' AND (XS_SERVICE_CD LIKE'       DELIMITED BY SIZE 
'(RTRIM(:WS-SHIP-LINE-CODE) ||' DELIMITED BY SIZE   
''''                            DELIMITED BY SIZE   
WS-PERCENT                      DELIMITED BY SIZE   
'''))'                          DELIMITED BY SIZE   
  INTO WS-WHERE-CLAUSE-01   
  END-STRING.               
                             

INITIALIZE WS-ORDER-CLAUSE-01                     
STRING                                           
    'ORDER BY XS_SERVICE_CD' DELIMITED BY SIZE   
   INTO WS-ORDER-CLAUSE-01                       
END-STRING.                                       


Preparing the sql to execute it:
Code:


Working storage declaration:
01  WS-STMTBUF.                                               
    49  WS-STMTLEN                   PIC S9(04) COMP VALUE +0.
    49  WS-STMTTEXT                  PIC X(2500) VALUE SPACES.

Actual piece of code where the prepare happens.
MOVE 2500                        TO WS-STMTLEN   
                                                 
EXEC SQL                                         
    PREPARE STMT-SELECT                           
    FROM :WS-STMTBUF                             
END-EXEC                                         
                                                 
 DISPLAY 'SQLCODE:' SQLCODE                       
                                                 
IF  SQLCODE = +0                                 
OR  SQLCODE = +100                               
    NEXT SENTENCE                                 
ELSE                                             
    MOVE 'BAD SQLCODE IN PREPARE STMT-SELECT'     
      TO VD2140I-PGMMSG                           
END-IF.                                           


After execution:
1. I am able to get the query which I built inside. Could see it in displays I gave. Was unable to copy everything, hence pasted what I could.
Code:

WS-STMTBUF:  SELECT  XS_SHIP_NO,XS_VESSEL_NAME,XS_CAPACITY,XS_VESSEL_TYPE,XS
IP WHERE(XS_SHIP_NO >=:WS-SHIP-NUM OR XS_SHIP_NO LIKE ('%'|| RTRIM(:WS-SHIP-
 RTRIM(:WS-SHIP-NAME) ||'%')) AND (XS_SERVICE_CD LIKE ('%'|| (RTRIM(:WS-SHIP
        ORDER BY XS_SHIP_NO                                                 


I am getting SQLCODE:000000031K. This is when I execute the prepare statement which I mentioned above.
I am getting the below error,
Code:

<H4> SQLCODE -518 Explanation                                                 
                                                                               
  -518 THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT     
                                                                               
  Explanation: One of the following conditions exists:                         
                                                                               
  * The statement named in the EXECUTE statement has not been prepared.       
                                                                               
  * The statement named in the EXECUTE statement identifies a SELECT, or       
    ASSOCIATE LOCATORS statement                                               
                                                                               
  * The statement named in the EXECUTE IMMEDIATE statement identifies a SELECT
      or ASSOCIATE LOCATORS statement.                                         
                                                                               
  System action: The statement cannot be processed.                           
                                                                               
  Programmer response: Ensure that you prepare the statement prior to EXECUTE.
  Also, ensure that the statement you prepare is not a SELECT or VALUES INTO   
  statement.                                                                   
                                                                               
  SQLSTATE: 07003                                                             



Could you please help me resolve this ?. I tried my best to resolve it, but still I am facing this issue.
Back to top
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore

PostPosted: Wed Feb 07, 2018 10:49 am
Reply with quote

After this prepare statement, I am trying to fetch the data using a cursor where I am unable to perform the fetch. Under programmer response, its mentioned that "ensure that the statement you prepare is not a SELECT", but did not get this statement. Do let me know how could I resolve this.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Wed Feb 07, 2018 4:53 pm
Reply with quote

What? It simply says that you cannot prepare a SELECT statement or a statement that has "VALUES INTO".
Back to top
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore

PostPosted: Wed Feb 07, 2018 5:49 pm
Reply with quote

Yes. I dint get that statement. My query has SELECT with WHERE clause. Can I know if I cannot give my PREPARE statement just like what I have given ?. Can't I PREPARE it using SELECT statements. If so is there any other way to dynamically define the SQL's.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2591
Location: NYC,USA

PostPosted: Wed Feb 07, 2018 10:59 pm
Reply with quote

Try declaring SELECT with "WITH HOLD" option
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 701
Location: Whitby, ON, Canada

PostPosted: Thu Feb 08, 2018 1:17 am
Reply with quote

You are using Dynamic SQL. One of the rules is that all SELECT statements must be coded as a CURSOR. SELECT......INTO is not allowed, even if you only expect 1 row to be returned.
Back to top
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore

PostPosted: Thu Feb 08, 2018 11:25 am
Reply with quote

So you meant to say that I should use "DECLARE CURSOR" statements instead of "SELECT ... INTO" statements ?.
Back to top
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore

PostPosted: Thu Feb 08, 2018 1:09 pm
Reply with quote

I could see my existing production modules having SELECT statement and they are building the SQL'S dynamically. I do not see that they build only DECLARE CURSOR statement. Please correct me if I am wrong.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1348
Location: Israel

PostPosted: Thu Feb 08, 2018 8:47 pm
Reply with quote

abdulrafi wrote:
...but I am getting 518 sqlcode...
...
I am getting SQLCODE:000000031K. This is when I execute the prepare statement which I mentioned above...

First you receive -312 for the PREPARE, and because of this you receive -518 in following statements.
This is what you have to fix:
Code:
-312                                                                         
                                                                             
 VARIABLE variable-name IS NOT DEFINED OR NOT USABLE                         
                                                                             
 Explanation                                                                 
 ~~~~~~~~~~~                                                                 
                                                                             
 A variable is referenced in the SQL statement, but it either is not defined 
 or cannot be used.                                                           
                                                                             
      variable-name                                                           
          The name of the variable that is referenced in the statement.       
                                                                             
 One of the following conditions exists:                                     
                                                                             
     The variable is not declared.                                           
                                                                             
     The attributes of the variable are inconsistent with its usage in the   
      SQL statement.                                                         
                                                                             
     The host variable was specified in a dynamic SQL statement. Host         
      variables are not valid in dynamic SQL statements.                     
     A host variable array is used in a context in which it is unsupported.   
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts problem in select max when executing ... IMS DB/DC 6
No new posts How to list BMS MAP screens in BMS Ph... CICS 2
No new posts Is SQLCODE -811 possible while fetchi... DB2 1
No new posts unable to compile module PL/I & Assembler 6
No new posts SQLCODE=-204 SQLSTATE=42704 DB2 4
Search our Forums:

Back to Top