aspose file tools*
The moose likes JDBC and the fly likes Why does a JDBC batch update return a -2 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Why does a JDBC batch update return a -2" Watch "Why does a JDBC batch update return a -2" New topic
Author

Why does a JDBC batch update return a -2

Konda Golamaru
Ranch Hand

Joined: Dec 12, 2005
Posts: 36
I am using Oracle 9i.
Database details are: DB Product Name: Oracle
DB Product Version: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options JServer Release 9.2.0.5.0 - Production
DRIVER MAJOR VERSION: 8
DRIVER MINOR VERSION: 1
DRIVER NAME: Oracle JDBC driver
DRIVER Version: 8.1.6.0.0

While I am doing batch insert I receive -2 as return status. Not sure what is the problem? Can somebody throw a pointer..
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61437
    
  67

KONDA GOLAMARU, please do everyone a favor and change your display name to mixed case like everyone else. Our eyes will thank you.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Konda Golamaru
Ranch Hand

Joined: Dec 12, 2005
Posts: 36
I have updated my profile.

Thank You
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by KONDA GOLAMARU:
I am using Oracle 9i.
Database details are: DB Product Name: Oracle
DB Product Version: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options JServer Release 9.2.0.5.0 - Production
DRIVER MAJOR VERSION: 8
DRIVER MINOR VERSION: 1
DRIVER NAME: Oracle JDBC driver
DRIVER Version: 8.1.6.0.0

While I am doing batch insert I receive -2 as return status. Not sure what is the problem? Can somebody throw a pointer..


It's not a "problem", it's the documented way Oracle works.
See:
http://download-east.oracle.com/docs/cd/B19306_01/java.102/b14355/oraperf.htm#i1059053
(free registration required).

In there, it says:
If a statement batch is processed successfully, then the integer array, or update counts array, returned by the statement executeBatch call will always have one element for each operation in the batch. In the Oracle implementation of standard update batching, the values of the array elements are as follows:

* For a prepared statement batch, it is not possible to know the number of rows affected in the database by each individual statement in the batch. Therefore, all array elements have a value of -2. According to the JDBC 2.0 specification, a value of -2 indicates that the operation was successful but the number of rows affected is unknown.

* For a generic statement batch the array contains the actual update counts indicating the number of rows affected by each operation. The actual update counts can be provided only in the case of generic statements in Oracle implementation of standard batching.

* For a callable statement batch, the server always returns the value 1 as the update count, irrespective of the number rows affected by each operation.

In your code, upon successful processing of a batch, you should be prepared to handle either -2, 1, or true update counts in the array elements. For a successful batch processing, the array contains either all -2, 1, or all positive integers.



This is allowed by the JDBC standard, which says:
The elements in the array returned by the method executeBatch may be one of the following:

1. A number greater than or equal to zero -- indicates that the command was processed successfully and is an update count giving the number of rows in the database that were affected by the command's execution
2. A value of SUCCESS_NO_INFO -- indicates that the command was processed successfully but that the number of rows affected is unknown
3. ....

http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html

Note that if you use generic statement batching, you get the update counts, but you don't get the performance improvement that most people want with batching; Oracle executes the statements in the batch one at a time, just as if you had use Statement normally.
Konda Golamaru
Ranch Hand

Joined: Dec 12, 2005
Posts: 36
Hiderby,

Thanks for the information. I am clear..
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Why does a JDBC batch update return a -2