Re: Get the row count from the SQLCA

This is true - sometimes....

if you doing a left outer join, SQLER3 is unreliable.

If you fetch 10 rows, and the 4th row fetched is an unmatched row with
the right half of the join, then SQLER3 = 3, even though you actually
recieved 10 rows from the left side of the join.

that's been my experience anyway.

On 8/17/06, HauserSSS <Hauser@xxxxxxxxxxxxxxx> wrote:

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.




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