number of rows while opening the cursor

parikshit123 · Active User Joined: 01 Jul 2005 Posts: 269 Location: India

Hi,

In DB2 application program , can we know the number of rows upfront at the time of opening the cursor?

Thanks,
Parikshit.

ranjitbhingare · New User Joined: 30 Nov 2005 Posts: 94 Location: PUNE

Hi,

Could you please explain more and what number of rows you want to count?

As far as what I understand :

1. you can know how many rows match your query by using SELECT COUNT(*) ........................

2. CURSOR will fetch only 1 row at a time.

Correct me if I am wrong.

Thanks & Regards,

Ranjit...

getdpk · New User Joined: 06 Feb 2006 Posts: 2

It is not possible to get the Count of rows while opening a Cursor.

The Only way to do is, Use COUNT(*) before using the cursor.

Thanks,
Deepak

Brian's_song · New User Joined: 27 Dec 2005 Posts: 21

you create a variable and then add 1 to variable every time you get a rec by the cursor.

Gurmeet · New User Joined: 22 Feb 2006 Posts: 46 Location: Pune

You might be using a where clause to define your cursor, use the same where clause with Select count(*), should give u the count...

~Gurmeet

vijayamadhuri · Active User Joined: 06 Apr 2005 Posts: 180

When you execute a SELECT statement, you retrieve a set of rows. That set
of rows is called the result table. In an application program, you need a
way to retrieve one row at a time from the result table into host
variables. A cursor performs that function.

parikshit123 · Active User Joined: 01 Jul 2005 Posts: 269 Location: India

Hi all,

Thanks for your response.
It seems to me that there is no way to find out the number of rows the cursor has selected (in the result table) at the time of opening the cursor.

I have put count(*) before opening the cursor to solve my problem.

I was interested if there is any variable in SQLCA which gives the number of rows as well.

Thanks for your help.

Hanfur · Active User Joined: 21 Jun 2006 Posts: 104

Hi,

When you perform a cursor controlled INSERT,UPDATE,DELETE
after the operation the number of rows affected would be updated in the
SQLERRD(3) field of SQLCA.

But not sure of any /meachanism/method ; which would give the number of rows in result set at the time when a Cursor Opened.

-Han.

parikshit123 · Active User Joined: 01 Jul 2005 Posts: 269 Location: India

Great,
It seems to me that there is no mechanism available in DB2 application program that give you the number of rows in the cursor at the time of opening it.
Its safe to use a select count(*) query before opening the cursor.

Thanks a lot to all of you for your help though !

ravi17s · New User Joined: 15 Aug 2003 Posts: 57

Oops!

Actually GET DIAGNOSTICS v_rowcount = ROW_COUNT;

gets the number of rows retrived when u open the cursor!

Try this and let us all know how it worked!

Thanks,
Ravi

Hanfur · Active User Joined: 21 Jun 2006 Posts: 104

Ravi,
Is it a Cobol compiler directive ?

Where this has to be coded to get the data on ROW_COUNT?

-Han.

parikshit123 · Active User Joined: 01 Jul 2005 Posts: 269 Location: India

Hi Ram,

I think your program will give you an error while BIND. I think -204. Invalid token.

Ram, I would suggest you not to post any irrrelevent question ( in the context of this topic, your question is irrelevent). You could have opened another topic instead.

Thanks,
Parikshit

ravi17s · New User Joined: 15 Aug 2003 Posts: 57

Hans,

GET_DIAGNOSTIC is not an cobol directives.The GET DIAGNOSTICS statement provides diagnostic information about the last SQL statement
similar to SQLCA and SQLCODE

Work fine with V7 and higher version.

row_count should be of type Integer.

EXEC SQL GET_DIAGNOSTIC :row_count =ROW_COUNT;

Thanks,
Ravi

Hanfur · Active User Joined: 21 Jun 2006 Posts: 104

Ravi,
Thanks for your reply..that makes sense...

-Han.