Hi,

When using a CallableStatement with the IBM DB2 Universal JDBC Driver
the executeUpdate and getUpdateCount() methods on CallableStatement
always return -1. According to the JDBC specification it should return
the number of rows affected by the stored procedure and 0 if no rows
are affected (and it does this for all other database's jdbc drivers).

I opened a PMR with IBM and received the following explanation. IBM
claims that stored procedures on DB2 do not return this update value.
The database itself does not return an update value. So, there is no
way to return this value to the JDBC driver, instead of returning an
incorrect value, they have chosen to always return -1.

I am prepared to accept this explanation, but unfortunately it makes
it difficult to write generic cross-database platform code. Is there
any way of detemining the number of rows that a stored procedure
affects(perhpas from the catalog tables) without actually writing
extra code in the SQL Stored Procedure.

Cheers
Otto

On Nov 14, 6:45 am, Otto Carl Marte <Otto.Ma...@gmail.com> wrote:
> Hi,
>
> When using a CallableStatement with the IBM DB2 Universal JDBC Driver
> the executeUpdate and getUpdateCount() methods on CallableStatement
> always return -1. According to the JDBC specification it should return
> the number of rows affected by the stored procedure and 0 if no rows
> are affected (and it does this for all other database's jdbc drivers).
>
> I opened a PMR with IBM and received the following explanation. IBM
> claims that stored procedures on DB2 do not return this update value.
> The database itself does not return an update value. So, there is no
> way to return this value to the JDBC driver, instead of returning an
> incorrect value, they have chosen to always return -1.
>
> I am prepared to accept this explanation, but unfortunately it makes
> it difficult to write generic cross-database platform code. Is there
> any way of detemining the number of rows that a stored procedure
> affects(perhpas from the catalog tables) without actually writing
> extra code in the SQL Stored Procedure.
>
> Cheers
> Otto

nope. Any attempt to write cross-database platform code will
collapse as soon as it has to either run fast or address stored
procedures. DBMSes are mutually alien and disjoint in their
needs and implementations surrounding those issues.
HTH,
Joe Weinstein at BEA Systems

"joeNOSPAM@bea.com" <joe.weinstein@gmail.com> wrote in message
news:9516820e-abc6-4999-b118-d35db85b1927@e10g2000prf.googlegroups.com...
> On Nov 14, 6:45 am, Otto Carl Marte <Otto.Ma...@gmail.com> wrote:
> Hi,
>
> When using a CallableStatement with the IBM DB2 Universal JDBC Driver
> the executeUpdate and getUpdateCount() methods on CallableStatement
> always return -1. According to the JDBC specification it should return
> the number of rows affected by the stored procedure and 0 if no rows
> are affected (and it does this for all other database's jdbc drivers).
>
> I opened a PMR with IBM and received the following explanation. IBM
> claims that stored procedures on DB2 do not return this update value.
> The database itself does not return an update value. So, there is no
> way to return this value to the JDBC driver, instead of returning an
> incorrect value, they have chosen to always return -1.
>
> I am prepared to accept this explanation, but unfortunately it makes
> it difficult to write generic cross-database platform code. Is there
> any way of detemining the number of rows that a stored procedure
> affects(perhpas from the catalog tables) without actually writing
> extra code in the SQL Stored Procedure.
>
> Cheers
> Otto

I am not sure how JDBC can return the number of rows updated (or
inserted/deleted) in a stored procedure because there are typically (or at
least possible) multiple SQL statements in a Stored Procedure.

Within a DB2 stored procedure, it can be written to capture and return the
number of rows updated for a specific SQL statement, and then return it as
an out parameter (which I do frequently). This can done with GET_DIAGNOSTICS
ROW_COUNT in a SQL Stored Procedure, or examining the ERRD(3) field in the
SQLCA for a SP written in C, etc.

Thanks for this, but I beg to differ on this. Considering we have
successfully written cross-database platform high performance stored
procedures :-)
The one thing it does take is a lot of time and effort. We have
written wrappers that ensure that JDBC drivers do behave similarly.
Within constraints cross-platform database access is possible...

> nope. Any attempt to write cross-database platform code will
> collapse as soon as it has to either run fast or address stored
> procedures. DBMSes are mutually alien and disjoint in their
> needs and implementations surrounding those issues.
> HTH,
> Joe Weinstein at BEA Systems

> I am not sure how JDBC can return the number of rows updated (or
> inserted/deleted) in a stored procedure because there are typically (or at
> least possible) multiple SQL statements in a Stored Procedure.
>

Sure, we are interested in the last updated/inserted/deleted statement
typically.

> Within a DB2 stored procedure, it can be written to capture and return the
> number of rows updated for a specific SQL statement, and then return it as
> an out parameter (which I do frequently). This can done with GET_DIAGNOSTICS
> ROW_COUNT in a SQL Stored Procedure, or examining the ERRD(3) field in the
> SQLCA for a SP written in C, etc.

This is what i am trying to avoid, having to explicit code for this on
DB2 in the application code.
Applicaton code writters need to be immune from this (it all happens
magically for them :-) )
I would likes some JDBC wrapping code to determine the number of rows
affected and mimic the
stored proc update count value functionality that is supplied by other
DB vendors.

Otto Carl Marte wrote:
>> I am not sure how JDBC can return the number of rows updated (or
>> inserted/deleted) in a stored procedure because there are typically (or at
>> least possible) multiple SQL statements in a Stored Procedure.
>>
>
> Sure, we are interested in the last updated/inserted/deleted statement
> typically.
>
>> Within a DB2 stored procedure, it can be written to capture and return the
>> number of rows updated for a specific SQL statement, and then return it as
>> an out parameter (which I do frequently). This can done with GET_DIAGNOSTICS
>> ROW_COUNT in a SQL Stored Procedure, or examining the ERRD(3) field in the
>> SQLCA for a SP written in C, etc.
>
> This is what i am trying to avoid, having to explicit code for this on
> DB2 in the application code.
> Applicaton code writters need to be immune from this (it all happens
> magically for them :-) )
> I would likes some JDBC wrapping code to determine the number of rows
> affected and mimic the
> stored proc update count value functionality that is supplied by other
> DB vendors.
To make a long story short DB2 does not provide this information for a
CALL statement.
Now, we're always thriving to improve the product, so I'm curious:
1. Which other products support this fucntionality?
2. is there any documentation in the JDBC client (standard/proprietary,
....) or any where else describing it?
3. What do you expect to get: ERRD(3) or ERRD(5) see:
http://publib.boulder.ibm.com/infoce.../r0002212.html
4. And most importantly: What do you use this information for (i.e.
what's your business case to pipe back this information through an API
other than an OUT parameter of a procedure which will truly work cross
platform.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

On Nov 21, 6:39 am, Serge Rielau <srie...@ca.ibm.com> wrote:
> Otto Carl Marte wrote:
> >> I am not sure how JDBC can return the number of rows updated (or
> >> inserted/deleted) in a stored procedure because there are typically (or at
> >> least possible) multiple SQL statements in a Stored Procedure.
>
> > Sure, we are interested in the last updated/inserted/deleted statement
> > typically.
>
> >> Within a DB2 stored procedure, it can be written to capture and return the
> >> number of rows updated for a specific SQL statement, and then return it as
> >> an out parameter (which I do frequently). This can done with GET_DIAGNOSTICS
> >> ROW_COUNT in a SQL Stored Procedure, or examining the ERRD(3) field in the
> >> SQLCA for a SP written in C, etc.
>
> > This is what i am trying to avoid, having to explicit code for this on
> > DB2 in the application code.
> > Applicaton code writters need to be immune from this (it all happens
> > magically for them :-) )
> > I would likes some JDBC wrapping code to determine the number of rows
> > affected and mimic the
> > stored proc update count value functionality that is supplied by other
> > DB vendors.
>
> To make a long story short DB2 does not provide this information for a
> CALL statement.
> Now, we're always thriving to improve the product, so I'm curious:
> 1. Which other products support this fucntionality?
> 2. is there any documentation in the JDBC client (standard/proprietary,
> ...) or any where else describing it?
> 3. What do you expect to get: ERRD(3) or ERRD(5) see:http://publib.boulder.ibm.com/infoce...pic/com.ibm.db...
> 4. And most importantly: What do you use this information for (i.e.
> what's your business case to pipe back this information through an API
> other than an OUT parameter of a procedure which will truly work cross
> platform.
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

Hi. Sybase and MS SQLServer can/do return any mixed series of result
sets and/or update counts
in the order they are obtained at the DBMS, from any SQL batch or
stored procedure.
Joe Weinstein at BEA Systems

joeNOSPAM@bea.com wrote:
> Hi. Sybase and MS SQLServer can/do return any mixed series of result
> sets and/or update counts
> in the order they are obtained at the DBMS, from any SQL batch or
> stored procedure.
Joe,

I don't think (??) this is what the OP is asking for.
The OP seems to ask that CALL returns a "grand total".
Resultsets and their associated fetch(!?) counts are a different topic,
IMHO.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

> To make a long story short DB2 does not provide this information for a
> CALL statement.
> Now, we're always thriving to improve the product, so I'm curious:
> 1. Which other products support this fucntionality?

This is definitely supported by MS SQL Server.

> 2. is there any documentation in the JDBC client (standard/proprietary,
> ...) or any where else describing it?

The JDBC 3.0 specification (pg 106) says so :-)

> 3. What do you expect to get: ERRD(3) or ERRD(5) see:http://publib.boulder.ibm.com/infoce...pic/com.ibm.db...

ERRD(3) is what i am after, the rowcount of the last INSERT/DELETE/
UPDATE statement.

> 4. And most importantly: What do you use this information for (i.e.
> what's your business case to pipe back this information through an API
> other than an OUT parameter of a procedure which will truly work cross
> platform.

Sure, the alternative is to use OUT parameters in our application
code. But this is what I am trying to avoid.
We have ported our application suite from MS SQL Server to DB2. We
are able to do this generically as we have a layer (lets call it the
JDBC SDK) that sits between the JDBC Driver and the application code.
Application programs use this JDBC SDK when accessing the database,
and are unaware whether the underlying database is MS SQL Server or
DB2. So, the first prize would be to fix it in the JDBC SDK and then
application programmers would reap the benefits.

Otto Carl Marte wrote:
>> To make a long story short DB2 does not provide this information for a
>> CALL statement.
>> Now, we're always thriving to improve the product, so I'm curious:
>> 1. Which other products support this fucntionality?
>
> This is definitely supported by MS SQL Server.
>
>> 2. is there any documentation in the JDBC client (standard/proprietary,
>> ...) or any where else describing it?
>
> The JDBC 3.0 specification (pg 106) says so :-)
Found it. Pretty fuzzy desription :-(
>
>> 3. What do you expect to get: ERRD(3) or ERRD(5) see:http://publib.boulder.ibm.com/infoce...pic/com.ibm.db...
>
> ERRD(3) is what i am after, the rowcount of the last INSERT/DELETE/
> UPDATE statement.
The LAST UDI statement of the procedure only?
I'd have guessed that it shoudl contain the SUM of all UDI statement
rowcounts. Can you clarify?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab