Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Access SQLCA fields inside a stored procedure accessing Db2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Access SQLCA fields inside a stored procedure accessing Db2
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: 2287
Location: @my desk

PostPosted: Thu Oct 27, 2016 6:51 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1827
Location: NY,USA

PostPosted: Thu Oct 27, 2016 9:28 pm    Post subject:
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: 2287
Location: @my desk

PostPosted: Thu Oct 27, 2016 9:48 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1827
Location: NY,USA

PostPosted: Thu Oct 27, 2016 10:05 pm    Post subject:
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: 686
Location: Earth

PostPosted: Thu Jan 26, 2017 6:36 am    Post subject: Reply to: Access SQLCA fields inside a stored procedure accessing Db2
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    Post subject:
Reply with quote

GET DIAGNOSTICS ?
https://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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SORT BUT RETAIN HIGHEST VALUE ON NON-... leondan22 DFSORT/ICETOOL 0 Thu Dec 14, 2017 8:13 pm
No new posts Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 2 Wed Dec 06, 2017 1:50 am
No new posts Subtract the ZD fields to provide neg... Balaryan DFSORT/ICETOOL 4 Wed Oct 11, 2017 10:51 pm
No new posts sending email from jcl using the mail... niloyarp JCL & VSAM 5 Tue Oct 10, 2017 12:32 pm
No new posts Accessing CICS tran with map from JCL... navdeepaggarwal CICS 5 Tue Oct 03, 2017 6:15 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us