Re: Get the row count from the SQLCA

Hi!

In my example, my host data structure was not a multiple occurence data
structure, so the SQLER3 field reported the number of rows that have been
fetched at that time and the rows were read one at the time. With a multiple
occurence data structure, the situation is not the same because the Fetch
statement tries to read up to X rows (X -> the maximum number of occurences
of the data structure). In that case, SQLER3 reports the number of rows that
have been read in the multiple occurence data structure. All I have to do is
to declare a data structure with my maximum subfile size and populate my
subfile with my result set...

Simple, isn't it?

Charles

"McGovern, Sean"
<Sean.McGovern@xxxxxxxxxxxxxxxxxxxxxxx> a écrit
dans le message de
news:D0BF1924AE5102408F091535623E32C00C60348C@xxxxxxxxxxxxxxxxxxxxxxx
You may find the following article helpful:

How Many Rows Did SQL Fetch?:
http://www.itjungle.com/fhg/fhg032906-story02.html

Apologies if I misled you earlier. I may be wrong, but from memory I thought
that checking SQLER3 following an OPEN statement gave an 'estimated number
of rows' value.



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Charles St-Laurent
Sent: 17 August 2006 18:13
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Get the row count from the SQLCA

Hi!

In the IBM documentation, is there any specification about the maximum value
of a COUNT query?

Charles


<Michael_Schutte@xxxxxxxxxxxx> a écrit dans le
message de
news:OF22F6B37C.0881F11A-ON852571CD.005D5F1D-852571CD.005D5D33@xxxxxxxxxxxxx
..
I'd imagine big enough to fit a count of all records.


Try our New Country Benedicts -- Breakfast with a Bob Evans twist!


rpg400-l-bounces@xxxxxxxxxxxx wrote on 08/17/2006
12:57:28 PM:

Hi!

What must be the length of my host variable that receives the result
of

the

COUNT query?

Charles


"McGovern, Sean"
<Sean.McGovern@xxxxxxxxxxxxxxxxxxxxxxx> a écrit

dans le message de

news:D0BF1924AE5102408F091535623E32C00C60348B@xxxxxxxxxxxxxxxxxxxxxxx

I don't believe so, unfortunately. You would need to perform a COUNT.


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Charles St-Laurent
Sent: 17 August 2006 16:21
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Get the row count from the SQLCA

Hi!

So, is there any SQLCA field that can indicate the EXACT number of
rows returned by my SELECT query before I try to use it to fill my

subfile?

Charles


"McGovern, Sean"
<Sean.McGovern@xxxxxxxxxxxxxxxxxxxxxxx> a écrit

dans le message de

news:D0BF1924AE5102408F091535623E32C00C603486@xxxxxxxxxxxxxxxxxxxxxxx

SQLER3 is an 'estimated number of rows' value.



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx

[mailto:rpg400-l-bounces@xxxxxxxxxxxx]

On Behalf Of Charles St-Laurent
Sent: 17 August 2006 15:23
To: rpg400-l@xxxxxxxxxxxx
Subject: 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 e-mail is confidential, intended only for the named
recipient(s)

above,

and may well also be legally privileged. If you have received it in
error, you are on notice of its status. Please notify us immediately
by reply e-mail and then delete this message from your system.
Please do not copy

it

or use it for any purposes, or disclose its contents to any other

person:

to

do so could be a breach of confidence. Please contact us on +44 (0)
1329 224000 or email

postmaster@xxxxxxxxxxxxxxxxxxxxxxx if you need

assistance.

Thank you for your co-operation.

****************************************************************************

--
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 e-mail is confidential, intended only for the named recipient(s)

above,

and may well also be legally privileged. If you have received it in

error,

you are on notice of its status. Please notify us immediately by reply
e-mail and then delete this message from your system. Please do not
copy

it

or use it for any purposes, or disclose its contents to any other person:

to

do so could be a breach of confidence. Please contact us on +44 (0)
1329 224000 or email

postmaster@xxxxxxxxxxxxxxxxxxxxxxx

if you need assistance.
Thank you for your co-operation.

****************************************************************************

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

--
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 e-mail is confidential, intended only for the named recipient(s) above,
and may well also be legally privileged. If you have received it in error,
you are on notice of its status. Please notify us immediately by reply
e-mail and then delete this message from your system. Please do not copy it
or use it for any purposes, or disclose its contents to any other person: to
do so could be a breach of confidence. Please contact us on +44 (0) 1329
224000 or email postmaster@xxxxxxxxxxxxxxxxxxxxxxx
if you need assistance.
Thank you for your co-operation.

****************************************************************************