retrieving value from dynamic SQL statament

Hi, i am preparing a dynamic SELECT (*) with dynamic where part.

It looks like SELECT .... into :hostvar where ..........

Now, how to retrieve this value after executing this statement ? . My hostvar is always 0

RozamundaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
MurpheyConnect With a Mentor Application ConsultantCommented:
Hi Rozamunda,

here a overview how to work with SQL with getting the result in a DS You can even prefix the ds so that the SQLed data has different field names then the original file (could be useful)

     D MyDs          E DS                  EXTNAME(MyFile)              
                                                                        
     C                   EVAL      SQLCMD = 'Select * from MyFile ' +   
     C                                       'where MyField = ' + Field1
     C                   ExSr      OpenSql                              
                                                                        
     C     SQLCOD        DOWEQ     0                                    
     C                   EXSR      ReadSql                              
     C                   ENDDO                                          
     C                   ExSr      CloseSql                             
                                                                        
     C     OpenSql       BegSr                                          
                                                                        
     C/Exec Sql                                                         
     C+ Prepare  SQLSLT   from :SQLCMD                               
     C/End-Exec                                                         
     C*                                                                 
     C/Exec Sql                                                         
     C+ Declare CC1  Cursor for  SQLSLT                                 
     C/End-Exec                    
     C*                            
     C/Exec Sql                    
     C+ Open  CC1                  
     C/End-Exec                    
     C                   ENDSR     
                                   
     C     ReadSql       BegSr     
                                   
     C/EXEC SQL                    
     C+ FETCH CC1 INTO :MyDs       
     C/END-EXEC                    
                                   
     C                   ENDSR     
                                   
     C     CloseSql      BegSr     
                                   
     C/Exec Sql                    
     C+ Close CC1                  
     C/End-Exec                    
                                    
     C                   ENDSR      

Open in new window

0
 
MurpheyApplication ConsultantCommented:
Is it Stand alone SQL or included in RPG ???
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

Since Rozamunda is using dynamic SQL, it kind of has to be embedded in a high-level language, right? I don't believe the AS/400 can handle dynamic SQL interactively.

You say your host variable is always coming back with zero. What value do you get if you run the query interactively? You could always run the program in debug-mode, that at the point of running the query, and run the query manually.

Also, if I were you, I'd check the SQLCODE after the query since it may have gotten an error.

HTH,
DaveSlash
0
 
MurpheyApplication ConsultantCommented:
If it is embedded SQL, the Select statment will NEVER return a value, AFAIK you need the fetch to get the data.


How it works the most flexible way:
- Define a variable containing your SQL statement (compose it by concatenating all constant and variable parts)
- Declare a cursor with this variable
- Open the Cursor
- Create a loop where you use Fetch Next from cursor into DS.
  Where DS is a datastructure containing all your fields.
  If you use "Select *", you can use a external defined DS using your file as external definition
- At the end of the loop do a close of the cursor.

If you like I can post you an example when I'm in the office tomorrow



0
 
tliottaConnect With a Mentor Commented:
I'm aware of two general methods of returning SELECTed data into a RPG program with dynamic SQL. First is SQL CLI, which might be more than is wanted. Second is Murph's suggestion of FETCHing a prepared SELECT through a cursor. The Embedded SQL manual has an example -- Example: Dynamic SQL in an ILE RPG application that uses SQL.

If anyone can show an example of a different method, I'd be as interested as anyone else to see it.

Tom
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.