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

Is this something that could have been handled with CAST?
Then I suppose not only do you monitor for EOF on your fetch, but now you 
have to monitor for overflow.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
11/12/2004 04:28 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"'RPG programming on the AS400 / iSeries'" <rpg400-l@xxxxxxxxxxxx>
cc

Fax to

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






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.



--
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.