1

I am working on an old struts application, and I have a need to save data in a database through java batch (cause we may add thousands of lines at a time).

Therefore, I use this kind of code :

Connection c = null;
PreparedStatement batch = null, truncate=null;
String sql = "INSERT INTO ? VALUES (?)";
try {
    c = getConnection();
    c.setAutoCommit(false);
    batch = c.prepareStatement(sql);
    for (ClassContainingData bean : values) {
        batch.setString(1, bean.getTableName());
        batch.setString(2, bean.getAllFieldsValues());
        batch.addBatch();
    }
    int[] resultats = batch.executeBatch();
    for(int res : resultats) {
        if(res == PreparedStatement.EXECUTE_FAILED) {
            batch.close();
            c.close();
            throw new SQLException("L'ajout des valeurs en base de données a échoué pour au moins une valeur.");
        }
    }
    c.commit();
} 
catch (BatchUpdateException b) {
    throw new ServiceException("Erreur lors de l'exécution du batch", b);
} 
catch (SQLException s) {
    throw new ServiceException("Impossible de sauvegarder les beans en base.", s);
} 
finally {
    getManager().close(batch);
    freeConnection(c);
}

The error messages are in French, cause I work in a french company, sorry about that.

So this code is pretty basic, as it is inspired by all the examples I found here and on the web, but I have some obscure bug that I can found no information on.

When I run as is, I get an exception 

CAUSE : java.sql.BatchUpdateException: ORA-00903: invalid table name.

This points out a mistake in my data, so I double-checked but my input seemed alright.

To figure it out, I used Eclipse debugger to see runtime values of my preparedStatement, and I found out that addBatch() adds an empty set of parameters to my preparedStatement !

Following is a screenshot of the runtime values : left picture is before running the addBatch() statement, right picture is right after this statement.

Screenshot of runtime values

As you can see, the array containing my String parameters now hold a second row, which only contains null values.

Note on data :

  • This was the first iteration of the for-loop, so it should hold only one line
  • The SQL statement has two parameters : a table name, like TABLE_NAME, and a formatted list of values to insert, like 'Here', 'are', 'values'.

I have the same issue whether I use a 1-line input or a 15-lines input (yeah I talked about thousands of lines but I am in testing phase so far), if that may be relevant.

Any idea of what is going wrong here ? I don't think I misused the addBatch() statement, I rather think I'm passing inappropriate data to it.

EDIT :

I withdrew the table_name from the parameters, and the sql command is now like "INSERT INTO " + table_name + " VALUES (?)"

This led to a different error, but it DID NOT solve the issue !

Indeed, the table_name is now correct, but the [null] line is still added to the batch. Therefore, there are empty VALUES in the batch, which throws "java.sql.BatchUpdateException: ORA-00947: not enough values"

My question is NOT a duplicate from the question marked by AlexH, please withdraw this mark.

Heratom
  • 35
  • 12
  • Or [How to use a tablename variable for a java prepared statement insert](https://stackoverflow.com/questions/11312155/how-to-use-a-tablename-variable-for-a-java-prepared-statement-insert) to match your query – AxelH Dec 27 '17 at 14:18
  • The reason is that the prepared statement will be parsed and optimzed before any values are set. This is why a _PS_ is faster. The query is parsed once and the values are simply proposed later so that the DBMS no what to do with that optimized query received earlier. For the solution, it depends on where that `bean.tableName` comes from to see what could be done. – AxelH Dec 27 '17 at 14:22
  • this first link you gave has interesting information : table-name cannot be parametized in a preparedStatement ! I'll try this fix, i'll keep you informed. – Heratom Dec 27 '17 at 14:49

1 Answers1

1

This problem has been linked to another problem I had with the same program. Discussion and solution is here.

As a summary, one should know that a preparedStatement can't use the table's name as a parameter, nor can you place multiple values inside a parameter.

For example in my case, I tried to do something like this :

String sql = "INSERT INTO (?) VALUES (?)"
preparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, "myTableName");
ps.setString(2, "'Value1', 'Value2', 3");

As you see, I wanted 3 arguments to be passed as one, without differenciating Strings and integers (and dates).

The only format a preparedStatement can handle is the following :

String sql = "INSERT INTO " + tableName + " VALUES (?, ?, ?)";

If you don't know the number of parameters before runtime, you have to build the query at runtime. An example is given in the link above.

Heratom
  • 35
  • 12