Using preparedStatement containing Oracle's to_date

[Sara Watson]

The following query runs fine within sql navigator but when the METHOD containing this query is executed, an empty result set is always returned....any ideas as to why the ResultSet is always empty when this executes?
st = con.prepareStatement("SELECT id " +
"FROM partpohdr " +
"WHERE tradepartnerid = ? " +
"AND dealerid = ? " +
"AND dateentered = TO_DATE(?,'DD-MON-YYYY') " +
"AND ponumber = ? " +
"AND delete_flag = 0");
st.setInt(1, tradePartnerID);
st.setInt(2,dealerID);
SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MMM-yyyy");
st.setString(3, dateFormat.format(dateEntered));
st.setString(4,poNumber);
rs = st.executeQuery();

[Avi Abrami]

Hi Sara,
Personally, with my environment -- SUN [sparc] Solaris 7, Oracle 8i (8.1.7.4), J2SE 1.4.2_02 and Oracle's JDBC (thin) driver -- I use the "setTimestamp()" method.
Although it was not clear to me from your post, I assume that "dateEntered"
(from the following line of your posted code)

st.setString(3, dateFormat.format(dateEntered));

is an instance of class "java.util.Date". If that is correct, then I would use the following code:

st.setTimestamp(3, new Timestamp(dateEntered.getTime()));

Good Luck,
Avi.

[Sara Watson]

Hi Avi, thanks for the advice....