|
View previous topic :: View next topic
|
| Author |
Message |
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
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 |
|
 |
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
| 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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| What? It simply says that you cannot prepare a SELECT statement or a statement that has "VALUES INTO". |
|
| Back to top |
|
|
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Try declaring SELECT with "WITH HOLD" option |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| 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 |
|
|
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
| So you meant to say that I should use "DECLARE CURSOR" statements instead of "SELECT ... INTO" statements ?. |
|
| Back to top |
|
|
abdulrafi
Active User
Joined: 14 Sep 2009
Posts: 184
Location: Coimbatore
|
|
|
|
| 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 |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
| 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 |
|
|
|
|
