RE: Dynamic SQL Cursor Problem -- RPG400-L

Title: RE: Dynamic SQL Cursor Problem

Just a guess but it looks like your *inzsr refers to the cursor before it is declared.

-----Original Message-----
From: Wills, Mike N. (TC) [mailto:MNWills@taylorcorp.com]
Sent: Thursday, June 14, 2001 6:31 PM
To: 'RPG400-L@midrange.com'
Subject: Dynamic SQL Cursor Problem

I am getting into the wonderful world of SQL. I know how to set up a normal
cursor and process it, but I thought I would use a dynamic SQL statement so
I don't have to deal with 2 cursors that are a bit different, but mostly the
same. However I am having problems. When I run the prepare statement, I get
an SQLCOD error of -312. Which means, "Host variable &1 not defined or not
usable." What am I doing wrong?

Here is the relevant code:

* Dynamic SQL Statements
D VendorSel       S            256

* Build the Vendor Cursor
C/EXEC SQL
C+ DECLARE C1 CURSOR FOR S1
C/END-EXEC

**** Other code including the fetches from the cursors

C     *INZSR        BegSR
*
C                   EXSR      $BuildSQL
*
* Prepare the Vendor Cursor
C/EXEC SQL
C+ PREPARE S1 FROM :VendorSel           <<<<< SQLCOD = -312 Here after it
executes
C/END-EXEC
* Open the Vendor Cursor
C/EXEC SQL
C+ OPEN C1
C/END-EXEC
*
C                   EndSR

C $BuildSQL BegSR

* Build the SQL statement for the VendorFile Cursor

C                   Eval      VendorSel = 'SELECT * FROM FILENAME'
*
C                   If        Vendor <> *Blanks
C                   Eval      VendorSel = %TRIM(VendorSel) +
C                             ' WHERE VENDOR = :Vendor'
C                   EndIf
*
C                   Eval      VendorSel = %TRIM(VendorSel) +
C                             ' ORDER BY VENDOR'

C EndSR

'Vendor' is defined in the 'D' specs, it is inputed with several other
variables. The SQL statement that is built is correct, I can use it as a
static SQL statement.

Any help would be appreciated...

Mike Wills
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---