IBM DB2 JDBC Driver CallableStatement update count

[Otto Carl Marte]

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

[joeNOSPAM]

On Nov 14, 6:45 am, Otto Carl Marte <[e-mail]:

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

[Mark A]

"jo*******@bea.com" <jo***********@gmail.comwrote in message
news:95**********************************@e10g2000 prf.googlegroups.com...

On Nov 14, 6:45 am, Otto Carl Marte <[e-mail]:
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.

[Otto Carl Marte]

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

[Otto Carl Marte]

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.

[Knut Stolze]

Otto Carl Marte wrote:

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?

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?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Otto Carl Marte]

On Nov 27, 8:16 am, Knut Stolze <[e-mail]:

Otto Carl Marte wrote:

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?

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?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

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

[Knut Stolze]

Otto Carl Marte wrote:

On Nov 27, 8:16 am, Knut Stolze <[e-mail]:

>Otto Carl Marte wrote:

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?

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?

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?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany