CODEWITHSUNDEEP
javasqloracle-databasejdbcprepared-statement
arnehehe
arnehehe

Reputation: 1398

"Invalid column index" SqlException error when trying to insert a row

I'm having a problem with setting up a PreparedStatement to insert a new row into my table. I've tested the query in my SQL editor and it worked succesfully but I can't get this PreparedStatement to work.

String sql = "INSERT INTO table game(gamedate, type, world) values (TIMESTAMP '?', ?, '?');"
runQuery(sql, date, type, world);

...

protected runQuery(String sql, Object... params){
try {
  initiateConnection();
  PreparedStatement statement = connection.PrepareStatement(sql);

    int i = 1;
    for (Object p : params){
       statement.setObject(i, p);
       i++;
     }
   statement.executeUpdate();
} catch (Exception ex){

} finally {
 //close up things
}
}

I added in some println() to test the output and it seemed to all be pretty okay

sql: INSERT INTO game(gamedate, type, world) values(TIMESTAMP '?', ?, '?');
date: 2012-03-13 21:42:14
type: 1
world: test

The error I get is

java.sql.SQLException invalid column index

I'm really quite stumped here. Any idea what's the culprit here?

Upvotes: 0

Views: 4156

Answers (2)

Jim Garrison
Jim Garrison

Reputation: 86774

Your SQL statement has only one substitutable placeholder. The ones in single quotes are literal question marks.

Upvotes: 1

Bozho
Bozho

Reputation: 597106

Two guesses:

  • you don't need quotes around the question marks. Get rid of those
  • the TIMESTAMP function might be problematic. Try converting the date to a timestamp before setting it in the prepared statement. (date.getTime() / 1000)

Upvotes: 1

Related Questions