Re: Wrong Return Value from statement.executeUpdate(query)
- From: Robert Klemme <shortcutter@xxxxxxxxxxxxxx>
- Date: Fri, 20 Apr 2007 18:06:37 +0200
On 20.04.2007 17:50, Joe Weinstein wrote:
Robert Klemme wrote:
Hi Robert! Actually, most of them do. Sybase and SQLServer would
do it exactly like this. Oracle would need a "BEGIN delete ...; delete ...; END;".
I forget the DB2 syntax... This has provided efficient 'home-made' batches
since JDBC 1.0.
Ah! Good to know. Still I'd prefer the batch as access to update counts is easier and you can uniformly use it with multiple DB. JDBC 1.0 is really ancient. :-)
So am I! ;)
*Your* words. ;-)
Unfortunately some drivers can't populate the array with actual
update counts if they really send the batch in one packet, so
they just deliver an array filled with -2! :( Also, some drivers
make suprising tradeoffs to implement the later calls (JDBC 2.0
is so *new*. ;) ). I just captured and examined the network packets
between the latest Oracle 10g driver and the DBMS, doing:
s.addBatch("delete joe where bar = 0");
s.addBatch("delete joe where bar = 1");
s.addBatch("delete joe where bar = 0");
int[] foo = s.executeBatch();
for (int i = 0; i < foo.length; i++) System.out.println("array has " + foo[i] );
It prints out the right numbers, but the network
sniffing proves that the driver does *not* send
the batch in one submission. In order to get the
numbers for the array, the client-DBMS protocol
for Oracle requires that they be sent and executed
individually! Whereas, sniffing:
s.execute("BEGIN delete joe where bar = 0; delete joe where bar = 1; delete joe where bar = 0; END;");
does go and get executed in one packet. So the older
code (as long as you have the DBMS-specific SQL) is
guaranteed to be as fast as it can be, but the later
style may not get the benefit you hope for.
Aaargh! As soon as you start doing things a bit more complex than a simple SQL statement or SP call, all bets are off. One more compatibility quirk in JDBC land. For example accessing meta data - although there is a clear defined interface drivers still choose to interpret arguments differently. These are the kinds of things that defy the purpose of a standard. Oh well...
Thanks for sharing this insight!
Grumbling off into the weekend
robert
.
- References:
- Re: Wrong Return Value from statement.executeUpdate(query)
- From: Joe Weinstein
- Re: Wrong Return Value from statement.executeUpdate(query)
- From: Robert Klemme
- Re: Wrong Return Value from statement.executeUpdate(query)
- From: Joe Weinstein
- Re: Wrong Return Value from statement.executeUpdate(query)
- From: Robert Klemme
- Re: Wrong Return Value from statement.executeUpdate(query)
- From: Joe Weinstein
- Re: Wrong Return Value from statement.executeUpdate(query)
- Prev by Date: Re: Wrong Return Value from statement.executeUpdate(query)
- Next by Date: Re: Wrong Return Value from statement.executeUpdate(query)
- Previous by thread: Re: Wrong Return Value from statement.executeUpdate(query)
- Next by thread: Re: Wrong Return Value from statement.executeUpdate(query)
- Index(es):
Relevant Pages
|
