RE: Embedded SQL does not work all the time with a page-at-a-time SFL -- RPG400-L

Matt,

Do you use aggregate functions and "group by" to generate summary data?  I
had issues with memory corruption, where SQL sized the aggregate values too
large to fit in the field defined on the fetch.  No hard errors were
reported, but the results were clearly in error on *some* transactions.  

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Tyler, Matt [mailto:mattt@xxxxxxxxxxxxxx]
Sent: Wednesday, November 10, 2004 6:08 PM
To: 'rpg400-l@xxxxxxxxxxxx'
Subject: Embedded SQL does not work all the time with a page-at-a-time
SFL


To all thanks for any help and apologize for the very long message.

 

I have a SQLRPGLE program
(http://archive.midrange.com/rpg400-l/200409/msg00254.html
<http://archive.midrange.com/rpg400-l/200409/msg00254.html>  this is a link
to pertinent code excerpts) that runs on an OS400 version 5.1.   

 

I have a situation where my program will not page down to then next page of
detail.  I can recreate the issue but I am not sure why it happens nor how
to solve.  I use the same RPG logic for different SQL statements.  When I
format my SQL statement under certain conditions the code in subroutine will
not work appropriately.   The condition occurs in subroutine SQLFETCH.  This
subroutine is used by the page down and anywhere the screen data needs to be
reloaded from the database.

 

The program fetches the first page of data (15 rows), it checks for EOF and
then sets up the page keys and MORE.../BOTTOM text.  If EOF was not found
after getting a full 15 rows it performs a FETCH NEXT FROM SQLCSR to
position SQLCSR only and see if is at least one more row.  If I find EOF
then I set on indicators to ensure that BOTTOM is shown, page down will not
work and make SQLCSR point to just before the last record in the query
result set.  Otherwise I position back up one row back to where I was after
the initial fetch of 15 rows.  

 

I believe the problem lies in the statement of "FETCH PRIOR FROM SQLCSR".
Under normal conditions the function returns SQLST='00000', but under this
one condition the function returns SQLSTT='02000'.  

 

Debugging the program I get the following for normal conditions:

15 rows fetched from cursor SQLCSR.

0 rows fetched from cursor SQLCSR.

0 rows fetched from cursor SQLCSR.

 

Debugging the program I get the following for the one condition:

15 rows fetched from cursor SQLCSR.

0 rows fetched from cursor SQLCSR.

Row not found for SQLCSR.

 

First call to SQLFETCH SQLCA is the following:

SQLAID OF SQLCA = 'SQLCA   '                                          

SQLABC OF SQLCA = 000000136.                                          

SQLCOD OF SQLCA = 000000000.                                          

SQLERL OF SQLCA = 0022.                                               

SQLERM OF SQLCA =                                                     

          ....5...10...15...20...25...30...35...40...45...50...55...60

     1   'SQLCSR                                                     '

    61   '          '                                                 

SQLERP OF SQLCA = 'QSQROUTE'                                          

SQLER1 OF SQLCA = 000000000.                                          

SQLERR OF SQLCA = '                  À7963'                           

SQLERRD OF SQLCA(1) = 000000000.                                      

SQLERRD OF SQLCA(2) = 000000000.                                      

SQLERRD OF SQLCA(3) = 000000015. 

SQLERRD OF SQLCA(4) = 000000521. 

SQLERRD OF SQLCA(5) = 000000000. 

SQLERRD OF SQLCA(6) = -134613261.

SQLER2 OF SQLCA = 000000000.     

SQLER3 OF SQLCA = 000000015.     

SQLER4 OF SQLCA = 000000521.     

SQLER5 OF SQLCA = 000000000.     

SQLER6 OF SQLCA = -134613261.    

SQLWRN OF SQLCA = '           '  

SQLWN0 OF SQLCA = ' '            

SQLWN1 OF SQLCA = ' '            

SQLWN2 OF SQLCA = ' '            

SQLWN3 OF SQLCA = ' '            

SQLWN4 OF SQLCA = ' '    

SQLWN5 OF SQLCA = ' '    

SQLWN6 OF SQLCA = ' '    

SQLWN7 OF SQLCA = ' '    

SQLWN8 OF SQLCA = ' '    

SQLWN9 OF SQLCA = ' '    

SQLWNA OF SQLCA = ' '    

SQLSTT OF SQLCA = '00000'

 

After paging down (once) it is:

SQLAID OF SQLCA = 'SQLCA   '                                          

SQLABC OF SQLCA = 000000136.                                          

SQLCOD OF SQLCA = 000000000.                                          

SQLERL OF SQLCA = 0022.                                               

SQLERM OF SQLCA =                                                     

          ....5...10...15...20...25...30...35...40...45...50...55...60

     1   'SQLCSR                                                     '

    61   '          '                                                 

SQLERP OF SQLCA = 'QSQROUTE'                                          

SQLER1 OF SQLCA = 000000000.                                          

SQLERR OF SQLCA = '                  À7963'                           

SQLERRD OF SQLCA(1) = 000000000.                                      

SQLERRD OF SQLCA(2) = 000000000.                                      

SQLERRD OF SQLCA(3) = 000000015. 

SQLERRD OF SQLCA(4) = 000000521. 

SQLERRD OF SQLCA(5) = 000000100. 

SQLERRD OF SQLCA(6) = -134613261.

SQLER2 OF SQLCA = 000000000.     

SQLER3 OF SQLCA = 000000015.     

SQLER4 OF SQLCA = 000000521.     

SQLER5 OF SQLCA = 000000100.   <- notice this

SQLER6 OF SQLCA = -134613261.    

SQLWRN OF SQLCA = '           '  

SQLWN0 OF SQLCA = ' '            

SQLWN1 OF SQLCA = ' '            

SQLWN2 OF SQLCA = ' '            

SQLWN3 OF SQLCA = ' '            

SQLWN4 OF SQLCA = ' '    

SQLWN5 OF SQLCA = ' '    

SQLWN6 OF SQLCA = ' '    

SQLWN7 OF SQLCA = ' '    

SQLWN8 OF SQLCA = ' '    

SQLWN9 OF SQLCA = ' '    

SQLWNA OF SQLCA = ' '    

SQLSTT OF SQLCA = '00000'

 

I can change only the order by clause values and have it start working.  

 

SQL that does not work:

Select * From RIMVISQV WHERE SRVND='    31618' AND SRUPC=2840002843 AND
SRSTN IN (1,6,7,10,12,17,21,24,25,26,31,36,37,52,53,55,56,60 )  ORDER BY
SRUPC FOR FETCH ONLY OPTIMIZE FOR 15 ROWS    

 

SQL that does work (a condition I am calling normal above):

Select * From RIMVISQV WHERE SRVND='    31618' AND SRUPC=2840002843 AND
SRSTN IN (1,6,7,10,12,17,21,24,25,26,31,36,37,52,53,55,56,60 )  ORDER BY
SRDPT, SRCAT, SRSUB, SRVND, SRUPC, SRSTN  FOR FETCH ONLY  OPTIMIZE FOR 15
ROWS                                                   

 

RIMVISQV is a SQLVIEW inner joining three tables.

 

I tried to pad out the SQL statement with 20 blanks between clauses, but
that did not help.  I can add a lot more store numbers to the IN condition
for SRSTN and still get the same results.

 

Thanks in advance for any help that you can provide.

Thank you,

Matt Tyler

WinCo Foods, Inc

mattt@xxxxxxxxxxxxxx

 

 

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.