Re: Java stored procedure performing insert/update/delete

Thu, 01 Aug 2013 10:05:15 -0700 

On 8/1/13 8:27 AM, Steve Ebersole wrote:
As far as I could tell, the embedded driver was always returning zero. That happens from both executeUpdate() and getUpdateCount(). Now its very possible that I did not code the stored procedure properly in terms of how Derby expects to "see" the update count. I followed examples, but maybe I missed something. 


And the options of using a function nor an (IN)OUT parameter is not an 
option unfortunately.



I am trying to spin up unit tests here, so have only used in-process 
dbs thus far for testing this.  Specifically H2 and then Derby.  
Neither returned what I expect based on JDBC.  Thomas agrees H2 does 
not do this properly.



In terms of expectation, the execute() call should of course execute 
the proc.  Since this proc does not return results, execute() should 
return false.  Then getUpdateCount() ought to return the number of 
rows "modified" by the call, or -1 if no modification was done.  0 and 
-1 are distinctly different according to the spec.  I hate "magic 
values" as much as anyone, but the spec says what it says ;)

Hi Steve,


Can you point me at the sections of the JDBC spec which you feel support 
this interpretation? I am concerned that this part of the spec may be 
undefined and I am seeing a range of opinions, including:



1) Should return -1 because a procedure call is not an 
INSERT/UPDATE/DELETE statement.



2) Should return the update count of the last INSERT/UPDATE/DELETE 
statement executed by the procedure.



3) Should return the sum of the update counts of all 
INSERT/UPDATE/DELETE statements executed by the procedure.


Thanks,
-Rick



On Thu 01 Aug 2013 09:56:21 AM CDT, Rick Hillegas wrote:

Hi Steve,

I'm not sure that DERBY-211 addresses the functionality you want. That
issue seems to me to be a discussion about whether 0 or -1 is the
correct value for CallableStatement.getUpdateCount() when the stored
procedure does NOT return any ResultSets. It sounds as though you want
getUpdateCount() to return a positive number in that case, maybe the
sum of the update counts for all INSERT, UPDATE, and DELETE statements
executed inside the procedure.

It also sounds as though you are experimenting with other databases
for which CallableStatement.getUpdateCount() returns a positive
number. Can you describe the behavior of the other databases? I would
like to take your evidence to the JDBC expert group in order to
determine what the correct behavior should be. As a result, we may
need to open a new Derby issue.

Note that the solution which I suggested ought to be portable across
all JDBC databases.

Thanks,
-Rick

On 8/1/13 7:08 AM, Steve Ebersole wrote:

Dag, Rick..  thanks for your replies.  I am not subscribed to the
list, but saw them in archive.

Unfortunately a workaround won't work.  This is support for stored
procedures across all databases that I am adding in Hibernate.  So
there has to be a certain level of consistency.  I'll just add a note
that stored procedures doing manipulation will not work correctly
with Derby (in terms of getting the "affect rows" count) when used in
embedded mode and reference to DERBY-211.

On Thu 01 Aug 2013 09:02:15 AM CDT, Steve Ebersole wrote:

Looks like I am seeing https://issues.apache.org/jira/browse/DERBY-211

On Wed 31 Jul 2013 07:02:01 PM CDT, Steve Ebersole wrote:

I am trying to work out how to define a Java stored procedure using
Derby that performs a insert/update/delete and results in the proper
"update count" on the JDBC client.  But I have so far been
unsuccessful.

Here is what I have...

First, through JDBC I execute:

create procedure deleteAllUsers()
language java
external name 'TheClass.deleteAllUsers'
parameter style java

TheClass.deleteAllUsers looks like:

public static void deleteAllUsers() {
    Connection conn = DriverManager.getConnection(
"jdbc:default:connection" );
    PreparedStatement ps = conn.prepareStatement( "delete from
t_user" );
    int count = ps.executeUpdate();
    System.out.println( "Count : " + count );
    ps.close();
    conn.close();
}

And on the JDBC client side:

Connection conn = ...;
CallableStatement stmnt = conn.prepareCall( "{call
deleteAllUsers()}" );
// yes I know this could be stmnt.executeUpdate()...
stmnt.execute();
int count = stmnt.getUpdateCount();


So the deleteAllUsers() prints the correct count.  But on the client,
I always get zero (and not -1).

Obviously I am doing something wrong.  Any pointers?

Thanks,
Steve






























					Reply via email to