IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

number of rows while opening the cursor


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
parikshit123

Active User


Joined: 01 Jul 2005
Posts: 269
Location: India

PostPosted: Sat May 13, 2006 3:29 pm
Reply with quote

Hi,

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

Thanks,
Parikshit.
Back to top
View user's profile Send private message
ranjitbhingare

New User


Joined: 30 Nov 2005
Posts: 94
Location: PUNE

PostPosted: Mon May 15, 2006 11:45 pm
Reply with quote

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...
Back to top
View user's profile Send private message
getdpk

New User


Joined: 06 Feb 2006
Posts: 2

PostPosted: Tue May 16, 2006 11:58 am
Reply with quote

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
Back to top
View user's profile Send private message
Brian's_song

New User


Joined: 27 Dec 2005
Posts: 21

PostPosted: Sun May 21, 2006 3:04 pm
Reply with quote

you create a variable and then add 1 to variable every time you get a rec by the cursor.
Back to top
View user's profile Send private message
Gurmeet

New User


Joined: 22 Feb 2006
Posts: 46
Location: Pune

PostPosted: Tue May 23, 2006 10:47 pm
Reply with quote

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
Back to top
View user's profile Send private message
vijayamadhuri

Active User


Joined: 06 Apr 2005
Posts: 180

PostPosted: Tue May 23, 2006 11:56 pm
Reply with quote

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.
Back to top
View user's profile Send private message
parikshit123

Active User


Joined: 01 Jul 2005
Posts: 269
Location: India

PostPosted: Thu Jun 08, 2006 12:51 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Hanfur

Active User


Joined: 21 Jun 2006
Posts: 104

PostPosted: Mon Jul 03, 2006 11:08 am
Reply with quote

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.
Back to top
View user's profile Send private message
parikshit123

Active User


Joined: 01 Jul 2005
Posts: 269
Location: India

PostPosted: Mon Jul 03, 2006 12:52 pm
Reply with quote

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 !
Back to top
View user's profile Send private message
ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Wed Jul 05, 2006 5:09 pm
Reply with quote

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
Back to top
View user's profile Send private message
Hanfur

Active User


Joined: 21 Jun 2006
Posts: 104

PostPosted: Thu Jul 06, 2006 10:39 am
Reply with quote

Ravi,
Is it a Cobol compiler directive ?

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


-Han.
Back to top
View user's profile Send private message
parikshit123

Active User


Joined: 01 Jul 2005
Posts: 269
Location: India

PostPosted: Thu Jul 06, 2006 10:58 am
Reply with quote

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

Thanks,
Parikshit
Back to top
View user's profile Send private message
ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Thu Jul 06, 2006 3:33 pm
Reply with quote

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
Back to top
View user's profile Send private message
Hanfur

Active User


Joined: 21 Jun 2006
Posts: 104

PostPosted: Thu Jul 06, 2006 5:08 pm
Reply with quote

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

-Han.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Substring number between 2 characters... DFSORT/ICETOOL 2
No new posts Generate random number from range of ... COBOL Programming 3
No new posts Increase the number of columns in the... IBM Tools 3
Search our Forums:

Back to Top