Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Prepared Statement and Date class - Problem still exists

 
Kajol Singh
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
JeanLouis Marechaux
Ranch Hand
Posts: 906
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kajol,
Could you post the error you obtain ?
By the way, I'm sure you'll have more answers to your question if you follow the JavaRanch Naming Policy...
 
Kajol Singh
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The error message I'm getting is:
"A non numeric found where numeric was expected."
Thanks,
Kaj
 
Stu Glassman
Ranch Hand
Posts: 91
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kaj, try writing startDate and endDate to System.out and make sure that the values are correct.
What types are startDate and endDate? Why don't you just convert startDate and endDate to java.sql.Date or Timestamp and use stmt.setDate(~~)?
-Stu
 
Kajol Singh
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,
Could someone tell me, how to convert java.util.Date to java.sql.Date
Thanks,
Kaj
 
Stu Glassman
Ranch Hand
Posts: 91
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try this:
//assuming java.util.Date uDate exists
java.sql.Date sDate = new java.sql.Date(uDate.getTime());
-Stu
 
Stu Glassman
Ranch Hand
Posts: 91
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This method also works for java.sql.Timestamp
 
Kajol Singh
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hello all,
Here is some code and its output. I don't understand how month is always getting formatted to '00'.
java.sql.Date startdate = java.sql.Date.valueOf("2002-05-22");
java.text.SimpleDateFormat df1 = new java.text.SimpleDateFormat("yyyy-mm-dd h:mm:ss");
String a = df1.format(startdate);
System.out.println("startDate = " + a);
Output: startDate = 2002-00-22 12:00:00
I appreaciate your help.
Thanks,
Kaj
 
Stu Glassman
Ranch Hand
Posts: 91
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The SimpleDateFormat is incorrect. "mm" denotes minutes. For months, use "MM"
-Stu
 
Ruilin Yang
Ranch Hand
Posts: 150
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
StringBuffer query = new StringBuffer("select *
from abc
where code = ?
and add_dttm between ? and ?")
Please check the data type: add_dttm, startDate and endDate.
 
Kajol Singh
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your reply glassman, your code worked.
Here is the code:
java.sql.Date sdate = java.sql.Date.valueOf(temp);
java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat("yyyy-MM-dd h:mm:ss");
String startDate = formatter.format(sdate);
I have one more question:
How do I convert the formatted String back to java.sql.Date.
I tried:
java.sql.Date sdate1 = java.sql.Date.valueOf(startDate);
but this does not work.
Thanks,
Kaj
 
Ruilin Yang
Ranch Hand
Posts: 150
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You may need to use the following before your above statement.
Connection conn=null;
Statement stmt=null;

try
{
stmt = conn.createStatement();
stmt.setEscapeProcessing (true);
java.sql.Date sdate1 = java.sql.Date.valueOf(startDate);
}
[ July 24, 2002: Message edited by: Ruilin Yang ]
 
Raj Birru
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try this

public class DateFormat {

public static void main ( String[] args )
{
java.sql.Date startdate = java.sql.Date.valueOf("2002-05-22");
System.out.println(" StartDate is "+startdate);
java.text.SimpleDateFormat df1 = new java.text.SimpleDateFormat("yyyy-MM-dd h:mm:ss");
String a = df1.format(startdate);
System.out.println("startDate = " + a);
java.util.Date startDate1 = null;
try {
startDate1 = df1.parse(a) ;
}catch(Exception e){
}
java.sql.Date date1 = new java.sql.Date(startDate1.getTime());
System.out.println("startDate1 = " + startDate1);
System.out.println("date1 = " + date1);
}
}
 
Kajol Singh
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks everybody for all the suggestions, my issue regarding formatting date and using it in a prepared statement is solved.
Based on the responce I got, I am posting another question I have and hope I will get a good solution for this issue too.
Question: Is it possible to use a 'prepared statement' with an 'in' clause in the query.
example:
String empcode = "xyz";
String allempid = "('12', '13', '14', '15','16')";
String query =("select emp_id from abc where code = ? and emp_id in (?)");


PreparedStatement stmt = dbConn.prepareStatement(query.toString());
stmt.setString(1, empcode);
stmt.setString(2, allempid);
ResultSet rs = stmt.executeQuery();
I appreciate your help,
Thanks,
Kaj
 
sachin venugopal
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
try this
newdate is a string.
//whatever format you have the date in
SimpleDateFormat newformat =
new SimpleDateFormat("dd.MM.yyyy");
java.util.Date sDate = newformat.parse(newdate);
Calendar cal= Calendar.getInstance();
cal.setTime(sDate);
java.sql.Date startD = new java.sql.Date(cal.getTime().getTime() );

Sachin Venugopal
SCJP2
Wannabe SWCD...
 
Stu Glassman
Ranch Hand
Posts: 91
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would expect that you can use "in" in a PreparedStatement, however, I'm not sure if you're using it correctly. Try doing it this way:

Try using both your method as well as this one and see if either works.
-Stu
 
Kajol Singh
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I used Vector instead of String Array, because the size of the array is determined dynamically.
String empcode = "xyz";
Vector v1 = new Vector();
for (-----){
v1.add("sadasd");
}
String query =("select emp_id from abc where code = ? and emp_id in (?)");

PreparedStatement stmt = dbConn.prepareStatement(query.toString());
stmt.setString(1, empcode);
stmt.setObject(2, v1);
ResultSet rs = stmt.executeQuery();
This does not work?
Any suggestions appreciated.
Thanks,
Kaj
 
Stu Glassman
Ranch Hand
Posts: 91
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First of all, how doesn't this work? Are you getting an exception? A compile error? Nothing at all?
Secondly, try changing the declaration of String query:
String query = "select emp_id from abc where code = ? and emp_id in ?"
If this doesn't work try converting the Vector to an array.
-Stu
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic