SQLException while trying to insert a double value into Oracle database

Question

I have to develop a small program that inserts some data into an Oracle database. Unfortunately I have some trouble with a SQL Statement and the execution of it. This is the code I am using:

db.execute(
    String.format("INSERT INTO tops VALUES (%d, '%s', %d, %f.00, '%s', TO_TIMESTAMP('%s', 'YYYY-MM-DD HH24:MI:SS.FF'))", 
        item.getID(),
        item.getTitle(),
        this.elements,
        item.getSize(),
        item.getEntity(),
        timestamp.toString()));

This is the part where the execution should work but I get the following error:

java.sql.SQLException: ORA-00913: Zu viele Werte

Google Translate for exception is:

java.sql.SQLException: ORA-00913: Too many values

Answer 1

You can use prepared statements like this as suggested by Guallaume on the comment;

PreparedStatement pstmt = null;
Connection conn = null;

try{
     //if you have a method that creates a connection for you.
     conn = getConnection();
     pstmt = conn.prepareStatement("INSERT INTO tops(id, title, elements, size, entity, timeStamp) VALUES(?,?,?,?,?,?)");
     pstmt.setInt(1,item.getID());

     //Assuming that title is a String data type
     pstmt.setString(2,item.getTitle());
     pstmt.setString(3,this.elements);
     pstmt.setDouble(4,item.getSize()); // <--- JDBC will make sure this works

     //assuming Entity data type is String
     pstmt.setString(5,item.getEntity());

     //if your timestamp's string format is 
     //well formed, you may insert as a string.
     pstmt.setString(6,timestamp.toString());
     pstmt.executeUpdate();
}catch(Exception e){
     e.printStackTrace();
}finally{  
     try{
         pstmt.close();
     }catch(Exception e){}

     try{
         conn.close();
     }catch(Exception e){}
 }

Answer 2

Don't use this syntax

INSERT INTO table VALUES (val1, val2, ...)

Use this one instead

INSERT INTO table (col1, col2, ...) VALUES (val1, val2, ...)

Tables may change. Fields may get added / removed / reordered - in case of which your INSERT statement would break again.

Of course, as others suggest, you should use prepared statements to avoid SQL injection and syntax errors... Imagine, item.getTitle() was any of these

"a', 'b";
"a'); DROP TABLE tops;' ...";

Answer 3

You really should use PreparedStatements, believe us...

In this case, however, the problem is very likely, that your locale uses the comma (,) character for the decimal point..

So 1/4 becomes: 0,25, not 0.25 as the DB would like!

Why is this a problem?

Look at this:

INSERT INTO SOMETABLE VALUES ( 0,25 );
INSERT INTO SOMETABLE VALUES ( 0, 25);

Both are treated as having 2 values, just the first one is not obvious for us, who use the comma as a decimal point... So you have to change the comma to a dot, or change the locale to US.

Correct:

INSERT INTO SOMETABLE VALUES ( 0.25);

You can specify the locale of the string formatting using String.format(Locale l, String format, Object... args) by supplying an appropriate locale.

Answer 4

This format string corrected.

"INSERT INTO tops VALUES (%f, '%s', %f, %.2f, '%s', TO_TIMESTAMP('%s', 'YYYY-MM-DD HH24:MI:SS.FF'))"

Don't use many values for each argument, use exactly one for each one, total six values.