IBM DB2 JDBC Driver CallableStatement update count

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Otto Carl Marte - 14 Nov 2007 14:45 GMT

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

Reply to this Message

joeNOSPAM@bea.com - 17 Nov 2007 16:53 GMT

> Hi,
>
[quoted text clipped - 18 lines]
> 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

Reply to this Message

Mark A - 17 Nov 2007 19:48 GMT

> Hi,
>
[quoted text clipped - 18 lines]
> 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.

Reply to this Message

Otto Carl Marte - 21 Nov 2007 14:15 GMT

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

Reply to this Message

Serge Rielau - 21 Nov 2007 14:39 GMT

>> 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
[quoted text clipped - 17 lines]
> 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/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.r
ef.doc/doc/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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

joeNOSPAM@bea.com - 21 Nov 2007 16:05 GMT

> >> 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
[quoted text clipped - 36 lines]
> 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

Reply to this Message

Serge Rielau - 22 Nov 2007 00:53 GMT

> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Otto Carl Marte - 25 Nov 2007 23:25 GMT

> 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/infocenter/db2luw/v9r5/topic/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.

Reply to this Message

Serge Rielau - 26 Nov 2007 02:17 GMT

>> To make a long story short DB2 does not provide this information for a
>> CALL statement.
[quoted text clipped - 7 lines]
>
> 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/infocenter/db2luw/v9r5/topic/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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Otto Carl Marte - 26 Nov 2007 14:30 GMT

> >> To make a long story short DB2 does not provide this information for a
> >> CALL statement.
[quoted text clipped - 9 lines]
>
> Found it. Pretty fuzzy desription :-(

Indeed. The JDBC spec is not very clear :-( But this is inferred in
the call hierarchy.
Statement -> PreparedStatement -> CallableStatement

Easy to do (from the DB side) for Statement and PreparedStatement
(parameterised statement), less so for CallableStatement.

> >> 3. What do you expect to get: ERRD(3) or ERRD(5) see:http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db...
>
[quoted text clipped - 4 lines]
> I'd have guessed that it shoudl contain the SUM of all UDI statement
> rowcounts. Can you clarify?

The LAST UDI statement rowcount for the procedure is returned. Not the
sum.

Reply to this Message

Knut Stolze - 27 Nov 2007 13:16 GMT

>> > ERRD(3) is what i am after, the rowcount of the last INSERT/DELETE/
>> > UPDATE statement.
[quoted text clipped - 5 lines]
> The LAST UDI statement rowcount for the procedure is returned. Not the
> sum.

That doesn't make much sense to me. Why the last and not the first? Or the
3rd?

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Otto Carl Marte - 27 Nov 2007 16:31 GMT

> >> > ERRD(3) is what i am after, the rowcount of the last INSERT/DELETE/
> >> > UPDATE statement.
[quoted text clipped - 13 lines]
> DB2 z/OS Utilities Development
> IBM Germany

Yeah, it isn't very convincing, but its the truth :-)

Reply to this Message

Knut Stolze - 29 Nov 2007 09:44 GMT

>> >> > ERRD(3) is what i am after, the rowcount of the last INSERT/DELETE/
>> >> > UPDATE statement.
[quoted text clipped - 10 lines]
>
> Yeah, it isn't very convincing, but its the truth :-)

...which doesn't make it any better. So why use such a crappy "feature" in
the first place?

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Otto Carl Marte - 21 Nov 2007 14:11 GMT

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

Reply to this Message