DB2 query cannot work using preparedstatement

[vivien siu]

hi, I'm trying to pass in a string object into the PreparedStatement below:

String s = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
 
PreparedStatement pstmt = con.prepareStatement("select IRUNCOUNT from TBL_EXCEPTIONALLOG where DATE(dRunDate) = cast(? as Date)");
 
pstmt.setString(1,s);

This is the exception I got:

com.ibm.db2.jcc.b.SQLException: The value of input host variable or parameter number "1" cannot be used because of its data type.

The query will smoothly if I use Statement object instead. Please let me know what is wrong with using PreparedStatement?

[Jeanne Boyarsky]

One of the big advantages of using Prepared Statements is not to have to worry about date formats and casting. If you really want to use it, see what type the cast function is expecting.

This query could be written as:
where date between ? and ?. This may be more efficient as the database doesn't need to run functions for every single row.

[vivien siu]

I want to select the values where condition will be "drundate"(value from db2) = a certain date.

however the "drundate" inside the database is a timestamp. I only need to compare the date value and not the whole timestamp.

how can I pass in a string then cast it to a date object correctly?

[Adeel Ansari]

There are a few ways.

- Use database specific function to format your timestamp into date
- split your condition into two like less than <late-day-timestamp> and
greater than <early-day-timestamp>
[ April 26, 2006: Message edited by: Adeel Ansari ]