Oracle PreparedStatement Batch Insert problem

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 - 05:26:50 CDT