Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 2211
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: 1640
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: 2211
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: 1640
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
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 Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts Sort two file having same field, one ... himanshu malik DFSORT/ICETOOL 7 Thu Feb 02, 2017 10:09 am
No new posts Calling procedure with multiple entri... steve-myers PL/I & Assembler 5 Fri Jan 27, 2017 3:33 pm
No new posts SYNCSORT SEQNUM and FI fields. nartcr SYNCSORT 7 Sat Jan 21, 2017 4:02 am
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us