1

I am trying to run a query in java that uses a java.sql.Timestamp object as the date to compare with in the where clause.

Here is how the query string that is built in Java

 String getTransactionsSQL =  "Select transaction_seq " +
    "From transactions ct " +
    "Where id = 'ABCD'" + 
    "And ct.out_msg_timestamp" +
    "<= to_date('" + parseDate.getTimeStamp() +"','YYYY-MM-DD HH:MI:SS..')" +
    "order by transaction_seq";

The statement parseDate.getTimeStamp() returns a java.sql.TimeStamp object that contains a date. Here is an example output of System.out.println(parseDate.getTimeStamp());

2011-03-07 05:47:57.0

When i run the above query i get this error

 java.sql.SQLException: ORA-01843: not a valid month

Any clues?

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
ziggy
  • 15,677
  • 67
  • 194
  • 287

1 Answers1

7

Use PreparedStatement: http://download.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html

Never use string concatenation to pass arguements to SQL commands (security risk: SQL injection)!

Puce
  • 37,247
  • 13
  • 80
  • 152
  • Here's the tutorial: http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html – Puce Mar 10 '11 at 19:50
  • I have to copy that! You should not use String concatenation in SQL like that – Lukas Eder Mar 10 '11 at 19:53
  • 2
    To be more precise, you should use `preparedStatement.setTimeStamp(index, timeStamp);`. – BalusC Mar 10 '11 at 19:56
  • Thanks guys but is this not only a problem for web based applications? Is it also possible for daemon type applications? – ziggy Mar 10 '11 at 20:05
  • 2
    @Ziggy: you should never use string literals for dates/timestamps. Always use a PreparedStatement, it's not only about SQL injenction but also about maintainability and robustness. When converting a date/timestamp to a string you are always influenced by locale settings (and maybe even different JDK versions) –  Mar 10 '11 at 21:38
  • @Ziggy, most importantly, it's about performance. When you hard-wire parameters to Oracle, Oracle will need to do a hard-parse of your SQL statement every time and re-calculate the query execution plan. A prepared statement is in fact a cursor shared by many database calls and sessions... – Lukas Eder Jul 08 '11 at 07:27