AW: Get the row count from the SQLCA

Hi,

SQLER3 represents the number of rows fetched and not the number of total
rows in the result set.
You only fetched a single row, that's why SQLER3 is one.
When using a multiple row fetch into either a multi occurence data structure
or an array data structure, SQLER3 returns the number or row fetched with a
single fetch. To check if you fetched all rows including the last one, you
have to check SQLER5. SQLER5 returns 100 if the last row was fetched with
the multiple row fetch.

    MaxNbrOfRows = %Elem(MyArrayDS);
 /End-Free
C/EXEC SQL  Fetch From CsrC1 For :MaxNbrOfRows Rows
C+                into :MyArrayDS
C/END-EXEC
 /Free
   If SQLER3 = *Zeros  // *Zero Rows are Fetched
   If SQLER5 = 100     // The last row of the result set was fetched.

Mit freundlichen Gru?en / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Charles St-Laurent
Gesendet: Donnerstag, 17. August 2006 16:23
An: rpg400-l@xxxxxxxxxxxx
Betreff: Get the row count from the SQLCA


Hi!

I try to get the row count from the SQLCA in a SQLRPGLE program in which I
build my Select statement dynamically in a string. The program works fine
but I want to get the row count from the SQLCA before trying to fill my
subfile with my Select result.

I know that row count is usually in SQLER3 (I think so) but when I try to
get this value before my FETCH loop, the value is 1.

This is what I do:

Eval SQLSTR = 'SELECT MY_DATA FROM MY_TABLE'
C/EXEC SQL PREPARE S1 FROM :SQLSTR
C/END-EXEC
C/EXEC SQL DECLARE C1 CURSOR FOR S1
C/END-EXEC
C/EXEC SQL OPEN C1
C/END-EXEC
C/EXEC SQL FETCH C1 INTO :SQLREC
C/END-EXEC
C                   DUMP

When I look at the result of the DUMP operation, SQLER3 = 1 but in fact the
result should be 2... why?

Thanks for your help
Charles



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