Dynamic SQL help

David Joyce · Posted: Fri Nov 13, 2009 1:24 am

I am trying to write a DB2/Cobol program using the SQLDA that executes dynamic SQL with a fixed-list of SELECT columns but a variable list of paramater markers. I keep receiving a -313 on the OPEN CURSOR and am stumped as to why this is happening. We are running DB2 v8 and IBM Cobol 3.3.
After each SQL statement I do check the SQLCODE and stop the program if anything other than 0 is found.

Here is a basic structure of my program:

SQL statement
SELECT COL1,COL2 FROM TABLE1 WHERE COL3 = ?

Move the SQL statement to a ws-variable

EXEC SQL
DECLARE STMT STATEMENT
END-EXEC

EXEC SQL
DECLARE ACMCUR CURSOR FOR STMT
END-EXEC

move 5 to SQLN

EXEC SQL
PREPARE STMT INTO :SQLDA FROM :STMTBUF
END-EXEC

SET SQLDATA(1) TO ADDRESS OF COL1
SET SQLDATA(1) TO ADDRESS OF COL2

*** FOR EACH PARAMETER MARKER
*** SET VALUES IN SQLDA

MOVE 453 TO SQLTYPE(3)
MOVE 4 TO SQLLEN(3)
MOVE 'ACT ' TO COL3
SET SQLDATA(3) TO ADDRESS OF COL3

The OPEN fails with a -313
EXEC SQL
OPEN ACMCUR USING DESCRIPTOR :SQLDA
END-EXEC

Any help, suggestions would be greatly appreciated. Thanks.

dbzTHEdinosauer · Posted: Fri Nov 13, 2009 2:28 am

Sqlcode -313 THE NUMBER OF HOST VARIABLES SPECIFIED IS NOT EQUAL TO THE NUMBER OF PARAMETER MARKERS

Redbook Dynamic SQL

David Joyce · Posted: Fri Nov 13, 2009 3:24 am

Dick,
Thanks for your reply but I am familiar with a -313 sqlcode. What I am hoping is someone to look at my code and let me know if I have something in the code I shouldn't or I am missing something in the code. Thanks.

Akatsukami · Posted: Fri Nov 13, 2009 3:27 am

Have you checked the value of STMTBUF at the time of the PREPARE?

David Joyce · Posted: Fri Nov 13, 2009 3:32 am

Yes. Here is the value of STMTBUF

SELECT COL1,COL2 FROM TABLE1 WHERE COL3 = ?

GuyC · Posted: Tue Nov 17, 2009 6:54 pm

SET SQLDATA(1) TO ADDRESS OF COL1
SET SQLDATA(1) TO ADDRESS OF COL2