Prepared Statement and Date class

[Kajol Singh]

Hi all,
Could someone tell me the error in the following code and a way to solve the problem.
example:
String sDate = "to_date('"+newstartDate+"','MM/DD/YYYY HH24:MI:SS')";
String eDate = "to_date('"+newendDate+"','MM/DD/YYYY HH24:MI:SS')";
StringBuffer query = new StringBuffer("select *
from abc
where code = ?
and add_dttm between ? and ?")

java.sql.Connection dbConn = null;
try {

dbConn = aarPool.getConnection();
PreparedStatement stmt = dbConn.prepareStatement(query.toString());
stmt.setString(1, sDate);
stmt.setDate(2, eDate);
ResultSet rs = stmt.executeQuery();
Thanks,
Kaj

[Krishnan Rambadhran]

StringBuffer query = new StringBuffer("select *
from abc
where code = ?
and add_dttm between ? and ?")

Hi
First of all, in the prepared statement u have written, there are three parameters that needs to be passed 1st being code 2nd and 3rd being date objects.
where as only 2 of them are being set currently and that too mis-matched.
>> stmt.setString(1, sDate);
>> stmt.setDate(2, eDate);
so usage would be
>>>> stmt.setString(1, cCode);
>>>> stmt.setDate(2, sDate);
>>>> stmt.setDate(3, eDate);
And as the 2nd and 3rd parameter required would be dates.. so u need to pass java.sql.Date object as parameters rather than a string object.
Rgds
Kris

[sameer s]

Hi,
Krishnan is right. U have set only two parameters.
In addition to that I would like to say that u can pass String objects for the 2nd and 3rd parameters, if desired because that would definitely be an easier way to do it. Just use the to_date() function in the SQL Query if u want to pass the String objects.
Regards,
Sfs

[Kajol Singh]

Hi all,
Thanks for responding to my mail.
My problem is still not solved:
I changed the code to the following, but it still does not work:
String startDate = "to_date('"+newstartDate+"','MM/DD/YYYY HH24:MI:SS')";
String endDate = "to_date('"+newendDate+"','MM/DD/YYYY HH24:MI:SS')";
StringBuffer query = new StringBuffer("select *
from abc
where code = ?
and add_dttm between ? and ?")
PreparedStatement stmt = dbConn.prepareStatement(query.toString());
stmt.setString(1, newvehCode);
stmt.setString(2, startDate);
stmt.setString(3,endDate);
ResultSet rs = stmt.executeQuery();
Could someone help me in solving this problem.
Thanks for your help,
Kaj