CODEWITHSUNDEEP
javaoracle-databasejdbcoracle11gora-00911
jack
jack

Reputation: 1942

JDBC: complains about invalid sign but seems fine

I have to use JDBC to write to a database (hibernate/ibatis is not an option) and my database is Oracle 11g.

I create the following query: insert into user(user_id, username, age, creation_ts) values(seq_userid.NEXTVAL, 'Jack', 19,TO_TIMESTAMP('14/12/2010 15/09/46', 'DD/MM/RR HH24/MI/SS'));

However my statetement.execeuteUpdate(above sql). generates an invalid sign exception. But when I perform the query in squirrel it gets commited just fine. Does anyone know why this is happening?

Edit:
user table:
id: number : not null
username varchar2(30) not null
age number(10) not null
creation_ts timestamp not null

Error:
ORA-00911: invalid character

Java snippet:
try
        {       
            DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
            String url = "privatized";
            Connection conn = DriverManager.getConnection(url, "username", "password");

            Statement st = conn.createStatement();

            Format formatter = new SimpleDateFormat(dateTimeFormatString);
            String formattedDate = formatter.format(Calendar.getInstance(TimeZone.getDefault()).getTime()); 

            StringBuilder insertQuery = new StringBuilder("insert into user(user_id, username, age, creation_ts) values(seq_userid.NEXTVAL,");
                insertQuery.append(username);
                insertQuery.append(",");
            insertQuery.append(age);
            insertQuery.append(",TO_TIMESTAMP('");
            insertQuery.append(formattedDate);
            insertQuery.append("', 'DD/MM/RR HH24/MI/SS'));");
            System.err.println(insertQuery.toString());
            st.executeUpdate(insertQuery.toString());

            conn.close();
        } catch (SQLException ex){
            System.err.println(ex.getMessage());
            System.err.println(ex.getCause().toString());
            ex.printStackTrace();
            System.out.println("=========================================");
        } catch(Exception ex) {
            System.err.println(ex.getMessage());
        }

Upvotes: 1

Views: 1028

Answers (3)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

Are you sure the value of the username variable is 'Jack' and not Jack? (the ORA-00911 error doesn't look like a typical date format error).

Also you should learn about PreparedStatement. They are more efficient, easier to read and debug and not susceptible to SQL injection.

My java is a bit rusty, but this would look something like this with a PreparedStatement:

String query = "insert into user(user_id, username, age, creation_ts) values "
              + "(seq_userid.NEXTVAL, ?, ?, ?)";

Statement st = conn.prepareStatement(query);

st.setString(1, username);
st.setInt(2, age);
st.setTimestamp(3, new java.sql.Timestamp(
                          Calendar.getInstance(
                             TimeZone.getDefault()).getTimeMillis()));

st.executeUpdate(insertQuery.toString());

This way you don't need to convert a date to a string to get it converted back by the DB. Also you may find the statement easier to read and you will never have to worry about user's naming their account with a ' (single-quote) :)

Upvotes: 0

Sean
Sean

Reputation: 7737

As I put in a comment above, the issue could be due to the extra Semicolon at the end of your SQL statement. see this article

You may also want to look at PreparedStatments to make your life easier. Here would be a rough translation of your above code. I have left some parts, and there are most likely errors.

String query = "insert into user(user_id, username, age, creation_ts) values(?,?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(query);
... //fill in all your parameters
pstmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()) );
... //execute here

Upvotes: 2

Andreas Dolk
Andreas Dolk

Reputation: 114757

TO_TIMESTAMP('14/12/2010 15/09/46', 'DD/MM/RR HH24/MI/SS')

You send a 4-digit year but the format string defines a 2-digit year (no century)

Give this a try:

insertQuery.append("', 'DD/MM/RRRR HH24/MI/SS'));");

Upvotes: 0

Related Questions