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
+---