Re: Oracle PreparedStatement Batch Insert problem

it looks like something weird happens when it tries to bind the parameters with the way you have written your sql (i managed to reproduce, 8.1.6.1.0 server, 8.1.7 jdbc drivers). no idea why. but try writing the sql as

"insert into list (id1, id2) select ?, id2 from list2 where name = ?"

that made it work for me.

hth.

ps - you should measure your performace gains with the batch updates. according to the 8.1.x docs, you're supposed to use the proprietary oracle way to get the best performance gains.

pps - the code you posted isn't exactly what you're doing, is it? you show String sQuery but you do prepareStatement(sCredentialListQuery)

facboy_at_hotmail.com (Chairman) wrote in message news:<b27b3b9b.0208130226.40bb4719_at_posting.google.com>...
> Hi all,
>
> I'm trying to do a batch insert of various values into a two column
> table, eg:
>
> sQuery = "INSERT INTO list (id1, " +
> id2) " +
> "VALUES (?, " +
> "(SELECT id2 " +
> "FROM list2 " +
> "WHERE name = ?) " +
> ")";
>
> // Prepare the create statement
> PreparedStatement createStatement =
> prepareStatement(sCredentialListQuery);
>
> for (i = 0; i < 1000; i++)
> {
> // Set the parameters to create the credential list
> createStatement.setInt(1, id);
> createStatement.setString(2, name[i]);
>
> // add to batch - doesn't work (driver bug?)
> createStatement.addBatch();
> }
>
> createStatement.executeBatch();
>
> The table has only two columns, which are both foreign keys, and
> together make up the primary key. The problem is that everytime I try
> to execute the batch, I get a UNIQUE constraint violation. I disabled
> the PK constraint, and then observed that the batch tries to insert
> identical values (ie, even though name[i] is presenting different
> values, the batch tries to insert the same value for each iteration).
>
> The problem doesn't happen if I do the updates individually using
> executeUpdate(). It also occurs using Oracle's proprietary batching
> (i.e. set the executeBatch to something other than 1). If I add a
> clearParameters() in anywhere, I get the 'Missing IN or OUT parameter
> at index:: 1' exception.
>
> I'm using Oracle JDBC 9.2.0.1 Drivers with Sun JDK1.4, but I had the
> same problems with the 8.1.7.1. This is connecting to ORacle 8.1.7
> btw.
>
> Any ideas?
>
> tia,
>
> chris.
Received on Tue Aug 13 2002 - 13:53:03 CDT