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

Access SQLCA fields inside a stored procedure accessing Db2


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

New User


Joined: 04 Oct 2016
Posts: 2
Location: India

PostPosted: Thu Oct 27, 2016 10:20 am
Reply with quote

Hi,

We have a console application written in .NET accessing DB2 tables using stored procedures. I need to know the # of rows returned by SQL query. Currently we are using SELECT COUNT (*). Is there a better way of doing this? Something like SQLERRD3 equivalent? Please help
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Oct 27, 2016 6:51 pm
Reply with quote

Is there a problem in returning SQLERRD(3) from your stored procedure, through one of the output parameters?
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2627
Location: NYC,USA

PostPosted: Thu Oct 27, 2016 9:28 pm
Reply with quote

Quote:
Is there a problem in returning SQLERRD(3) from your stored procedure, through one of the output parameters?
After opening the cursor, you will get 0 for SQLERRD(3). Because, rows are fetched only after FETCH statement is executed but this may or may not be true for multi row fetch cursors so it can not be used since the fetch is done by front end.
Welcome!!
Quote:
SELECT COUNT (*). Is there a better way of doing this?

It looks to me that, your design is poor because if you wanted total count of the rows then you let you design change to pass you back the count from one of the parameter but you want some short cuts? what it makes difficult to get count(*) from SP to change? or why do you need that count(*) now and not before?
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Oct 27, 2016 9:48 pm
Reply with quote

Rohit Umarjikar wrote:
After opening the cursor
I could not find the OP mentioning that his stored procedure has a cursor in it.
EDIT : On a second thought, since multiple rows are involved, yes but still if fetch is happening outside the stored procedure, then finding the count should not be that hard.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2627
Location: NYC,USA

PostPosted: Thu Oct 27, 2016 10:05 pm
Reply with quote

It is indeed a cursor otherwise for a single select query why would he need count(*)? It is better to wait otherwise to get more clarity on stated problem.
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 691
Location: Earth

PostPosted: Thu Jan 26, 2017 6:36 am
Reply with quote

Quote:
Currently we are using SELECT COUNT (*). Is there a better way of doing this?

How about counting rs (ResultSet object) hasNext() condition is true? You may as well add your row processing logic in this loop.

rs.last().getRow(); may NOT always work as most sites I've worked with use third party ODBC drivers to connect to DB2.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Mar 13, 2017 4:30 pm
Reply with quote

GET DIAGNOSTICS ?
www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_sql_getdiagnostics.html
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
This topic is locked: you cannot edit posts or make replies. SUM FIELDS=NONE in reverse - Get dupl... DFSORT/ICETOOL 9
This topic is locked: you cannot edit posts or make replies. how to extract a portion (e.g. fields... SYNCSORT 8
No new posts Checking the total counts inside a re... JCL & VSAM 5
No new posts Using REXX to access SDSF; What am I ... CLIST & REXX 38
No new posts Break a record into fields, each fiel... SYNCSORT 2
Search our Forums:

Back to Top