Fetch into variable

[michal2442]

Hi i consider fetching sql query into variables. SQL returns 2 columns with 3 records (aaaaaaaa, 2; bbbbbbbb,2 and cccccccc, 2)

im doing this:

     d  xumowa         S                   like(UMOWA)
     d  xxx            S              2P 0
     d*
     d*****************************************************************
     D Ok              C                   0
     D
     D
      /free
       Exec Sql Declare kursor Cursor For

          SELECT UMOWA, count(*) LiczbaProp
          FROM library/physical_file where STATUS = 20
          GROUP BY UMOWA
          HAVING COUNT(*) > 1
          ORDER BY COUNT(*) DESC
          For Fetch Only;

       Exec Sql Open kursor;
       Exec Sql Fetch Into :xumowa, :xxx;

       Exec Sql Close kursor;
        dsply xumowa;
       *Inlr = *On;
        Return;
      /end-free

how to do it correctly ? program compiles but displays empty field.

[TedHolt]

Re: Fetch into variable

Maybe you're getting null values, Michal. What's the value of SQLSTATE?

Also, if the result set will only have one row at most, you can use SELECT with INTO instead of a cursor.

[tomholden]

Re: Fetch into variable

also instead of using a packed decimal field for the count(*) i would highly recommend using an integer!

[michal2442]

Re: Fetch into variable

Maybe you're getting null values, Michal. What's the value of SQLSTATE?

Also, if the result set will only have one row at most, you can use SELECT with INTO instead of a cursor.

SQLSTATE ? whats this ?
now i tried simply version with select into and still displays nothing

d  xumowa         S                   like(UMOWA)
/free
       Exec Sql 

          SELECT UMOWA  
          into :xumowa  
          FROM physical_file
          where STID = 2;
       
         dsply xumowa;  
         *inlr = *on;
          return;             
/end-free

[jtaylor___]

Re: Fetch into variable

SQLSTATE is like %found() or %eof(). A SQLSTATE of '00000' means found, anything else is not so good. You can look up their meanings here (http://publib.boulder.ibm.com/infoce...zas2finder.htm).

[michal2442]

Re: Fetch into variable

and question how to compile sqlrpgle ? it is enough to use crtsqlrpgi (option 14) ?

i still display nothing, any help ?

[vikramx]

Re: Fetch into variable

1.) Check if you have data which would be retrieved by your SQL statement.
2.) If 1.) is yes, check if the SQL statements are throwing any errors. Either check SQLSTT and SQLCODE values in debug OR check DSPJOBLOG to see if any messages crop up.

[michal2442]

Re: Fetch into variable

the same statement works in STRSQL command.
dspjoblog also shows nothing

maybe i use bad syntax ? and compiler doesnt see sql query ? please look on it

[B.Hauser]

Re: Fetch into variable

What's the SQLCODE or SQLSTATE immediately after the SELECT ... INTO Statement?
SQLCODE and SQLSTATE are subfields of the SQLCA (SQL Communications Area) which is automatically included by the SQL precompiler and which is updated with each SQL statements that is executed.

Birgitta

[Scott M]

Re: Fetch into variable

As others have mentioned you need to be checking for SQL errors with SqlState after each statement, that will probably point out what you are doing wrong.

[michal2442]

Re: Fetch into variable

but how to check it ? any tutorial ?

[Viking]

Re: Fetch into variable

Right after you execute an SQL statement, you should check to see if there were any errors, something like this:

     If sqlSTT <> sqlOK;                                              
       jobLog('Open cursor myCursor failed due to %s' : sqlSTT);
       *inLR = *on;                                                   
     Endif;

You really should check the SQLSTATE after every SQL statement you do, like after your open, then after your fetch, etc.

You can define your SQL error constants something like this (I put mine in a /copy member and include it in my SQLRPGLE programs):

  //-- SQLState (SQLStt) Codes                       
D sqlOK           c                   Const('00000') 
D sqlTrunc        c                   Const('01004') 
D sqlNullCol      c                   Const('01003') 
D sqlNoRow        c                   Const('02000') 
D sqlMultiRow     c                   Const('21000') 
D sqlNotOpen      c                   Const('24501') 
D sqlDupRow       c                   Const('42910') 
D sqlDupKey       c                   Const('23505')

You can look up what all the codes are and what they mean, but basically if you get '00000' then everything's ok. Errors like '01xxx' are warnings, and errors like '02xxx' are more serious errors.

If you do any inserts/updates, your first executable statements should be setting up some SQL options, especially turning off commitment control, lke this:

       //-- Set SQL options
       Exec sql
         SET OPTION commit = *none,
                    datfmt = *iso;

[michal2442]

Re: Fetch into variable

Thanks for help, today it worked i havent change anything in simpler (2nd version) version.

[DeadManWalks]

Re: Fetch into variable

Ah, I dont buy it.

have you or someone added data to the table?

[tomliotta]

Re: Fetch into variable

Thanks for help, today it worked i havent change anything in simpler (2nd version) version.

That's easily possible if you didn't sign off/on after changing the program earlier in your testing, or if you didn't reclaim an activation group. When you started a new session the next day, you could get the latest version of your program.