RE: Dynamic SQL Cursor Problem

["Wills, Mike N. (TC)" <MNWills@xxxxxxxxxxxxxx>]

I have tried in inline (Prepare then Declare, then Open) but I still get
that message so I know it is not that.


Mike Wills
IT Corporate Support
MNWills@taylorcorp.com

-----Original Message-----
From: Rich Davidson [mailto:rdavidson@ddilink.com] 
Sent: Friday, June 15, 2001 7:20 AM
To: 'RPG400-L@midrange.com'
Subject: 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 
+--- 
+---
| 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
+---