Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of rick.hillegas@oracle.com
 designates 156.151.31.81 as permitted sender)
Message-ID: <51FA94F8.2050103@oracle.com>
Date: Thu, 01 Aug 2013 10:03:52 -0700
From: Rick Hillegas <rick.hillegas@oracle.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.7; en-US;
 rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
CC: Steve Ebersole <steve@hibernate.org>
Subject: Re: Java stored procedure performing insert/update/delete
References: <51F9A579.5060204@hibernate.org> <51FA6A67.6030401@hibernate.org>
 <51FA6BEA.3080807@hibernate.org> <51FA7715.3080607@oracle.com>
 <51FA7E5A.60404@hibernate.org>
In-Reply-To: <51FA7E5A.60404@hibernate.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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