Unable to fetch data from an sqlrpgle SQL query

Question

I'm trying to run a SQL query in my RPG program, however I am unable to fetch the result into my variable (a data-structure).

Here's the code for what I've tried so far.

Dsassure          DS                  QUALIFIED
Dasnste                          1a            
Dasngrp                          4s 0          
Dasnind                          5s 0          
Dasnra3                          1s 0          
Dasnnss                         13a            
Dasncss                          2s 0          
Dasnreg                          2s 0          
Dasnorg                          3s 0          
Dasncen                          3s 0          
Dasnclo                          1s 0          
Dasnpmu                          1a            
Dasnnoa                         25a            
Dasnpra                         15a  
Dasnnob                         25a  
Dasnprb                         15a  
Dasnnas                          2s 0
Dasnnaa                          2s 0
Dasnnam                          2s 0
Dasnnaj                          2s 0
Dasnrng                          1s 0
Dasnefs                          2s 0
Dasnefa                          2s 0
Dasnefm                          2s 0
Dasnefj                          2s 0
Dasnras                          2s 0
Dasnraa                          2s 0
Dasnram                          2s 0
Dasnraj                          2s 0
Dasnctn                          1a  
Dasnnmu                          8s 0
Dasnst3                          3a  

 //*-----------------------------------------------------------------  
C     *ENTRY        PLIST                                                
C                   PARM                    x1secu           13          
C                   PARM                    x1reg             2 0        
C                   PARM                    x1caisse          3          
C                   PARM                    x1datea           2 0        
C                   PARM                    x1datem           2 0        
C                   PARM                    x1datej           2 0                
C/exec sql                  
C+ set option commit=*none, 
C+ datfmt=*iso              
C/end-exec                  
 *                                                                                     
 /Free                                                                                 
                                                                                       
   //*-----------------------------------------------------------------
     exec sql                                                                          
         DECLARE c1 CURSOR FOR                                                         
                 SELECT asste,  asgrp,  asind,                                         
                        asran3, assecu, asscle,                                        
                        asnom,  aspre,  asnom,                                         
                        aspre,  asnais, asnaia,                                        
                        asnaim, asnaij, asrsec,                                        
                        aseffs, aseffa, aseffm,                                        
                        aseffj, asrads, asrada,                                        
                        asradm, asradj, asctns                                         
                                                                                       
         FROM QPURGE.SASSURE                                                           
         WHERE ASSECU = :x1secu;

    exec sql                                                           
        OPEN c1;                                                       
                                                                   
If   SQLSTATE = '00000';                                           
                                                                   
    exec sql                                                       
        FETCH c1 INTO                                    
                 :sassure.asnste, :sassure.asngrp, :sassure.asnind,
                 :sassure.asnra3, :sassure.asnnss, :sassure.asncss,
                 :sassure.asnraj, :sassure.asnnoa, :sassure.asnpra,
                 :sassure.asnnob, :sassure.asnprb, :sassure.asnnas,
                 :sassure.asnnaa, :sassure.asnnam, :sassure.asnnaj,
                 :sassure.asnrng, :sassure.asnefs, :sassure.asnefa,
                 :sassure.asnefm, :sassure.asnefj, :sassure.asnras,
                 :sassure.asnraa, :sassure.asnram, :sassure.asnctn;

    exec sql   
       CLOSE c1;

I tried using the data-structure as the INTO target, didn't work.

FETCH c1 INTO :sassure;

I also tried doing it without a cursor, specifying subfields

VALUES (SELECT asste,  asgrp,  asind,                          
               asran3, assecu, asscle,                         
               asnom,  aspre,  asnom,                          
               aspre,  asnais, asnaia,                         
               asnaim, asnaij, asrsec,                         
               aseffs, aseffa, aseffm,                         
               aseffj, asrads, asrada,                         
               asradm, asradj, asctns                          
                                                               
FROM QPURGE.SASSURE                                            
WHERE ASSECU = :x1secu) INTO                                   
             :sassure.asnste, :sassure.asngrp, :sassure.asnind,
             :sassure.asnra3, :sassure.asnnss, :sassure.asncss,
             :sassure.asnraj, :sassure.asnnoa, :sassure.asnpra,
             :sassure.asnnob, :sassure.asnprb, :sassure.asnnas,
             :sassure.asnnaa, :sassure.asnnam, :sassure.asnnaj,
             :sassure.asnrng, :sassure.asnefs, :sassure.asnefa,
             :sassure.asnefm, :sassure.asnefj, :sassure.asnras,
             :sassure.asnraa, :sassure.asnram, :sassure.asnctn;

Nothing seems to work, I used the debugger to get a sample and see if I my request returns me anything and it does.

I am really out of ideas on this one..

Any thoughts?

Answer 1

Note that you could have used SELECT INTO rather than VALUES INTO here since you've got static SQL rather than dynamic SQL.

In either case, be aware that the results of the select statement must return a single row. Otherwise you'll get a SQLSTATE=21000 (if I recall correctly). If you need to rad more than one row, you'd have to use a cursor.

You must check SQLSTATE (or SQLCODE) after running embedded SQL.

SQLSTATE --> description
'00000' --> unqualified success
'01xxx' --> successful with warnings
'02000' --> no data

You should have been able to use the data structure as the return variable for SELECT INTO VALUES INTO or FETCH from a cursor.

SELECT asste,  asgrp,  asind,                          
       asran3, assecu, asscle,                         
       asnom,  aspre,  asnom,                          
       aspre,  asnais, asnaia,                         
       asnaim, asnaij, asrsec,                         
       aseffs, aseffa, aseffm,                         
       aseffj, asrads, asrada,                         
       asradm, asradj, asctns                          
INTO :sassure                                                               
FROM QPURGE.SASSURE                                            
WHERE ASSECU = :x1secu;

Note that if any of the values could be NULL, you'd need an indicator array with DIM(24), so that there's an null indicator flag available for each returned column.