problem insering date value in database

[ravindra janapreddy]

In my jsp I have retreived the date value which was passed as a http parameter from a html page
String birth_date = request.getParameter("tf5");
System.out.println("birth_date : "+birth_date);
now I am trying to insert this value into BIRTH_DATE field of my table where this field is declared with a data type as DATE.
dbQuery = "UPDATE XXX SET BIRTH_DATE = '"+to_date ('"+birth_date+"','yyyy.MM.dd')+"'";
Though I get this in output
-----------> birth_date : 1970-06-05 00:00:00.0
Beyond this nothing happens , Obviously It is getting stuck when it comes to updating the record in the database.
Now My question is how do we insert a data value which is retrieved as a string from a http parameter sent from a html page and insert it using a java program into a database ?
There seems to be some problem in converting of java date to a database DATE . Similar problem was faced by a friend of mine. One solution seems to declare the BIRTH_DATE field in database as VARCHAR. But How do we get around a problem where already the field is declared in the database as DATE datatype ?

[martin samm]

could you get the user to enter their birth date by using 3 lists, e.g. :
Day (number 1-31), Month (number, 1-12), Year(4 digit number)?
You would then know the format for the 3 strings you would receive in the URL from the page and then convert this in the query string, as you were trying to do :
dbQuery="update XXX set birth_date=to_date('"+ year + month + day + "','yyyymmdd') where BLAH BLAH BLAH);

------------------
martin samm
[e-mail]

[Peter den Haan]

Originally posted by ravindra janapareddy:
dbQuery = "UPDATE XXX SET BIRTH_DATE = '"+to_date ('"+birth_date+"','yyyy.MM.dd')+"'";
Though I get this in output
-----------> birth_date : 1970-06-05 00:00:00.0

From your information it is immediately apparent that while you specify a 'yyyy.MM.dd' format for your date, the actual string does not satisfy this format! That's not likely to be helpful. There may be more problems, but the quotes don't make sense to me... are they right?
How do you execute the statement? executeQuery (wrong) or executeUpdate (right)?
The safest way to get a string you know you can stick into a database is, I think, to use DateFormat.parse() to convert the user input it into a Date, construct a java.sql.Date from this, and convert using java.sql.Date.toString(). You shouldn't need the (database-dependent) to_date() function, I think.
But rather than this, you could consider a PreparedStatement and use PreparedStatement.setDate().
- Peter

[This message has been edited by Peter den Haan (edited February 27, 2001).]

[ravi janap]

Dear Peter / Martin,
Thanks for the response.
I have tried the code which is similar to what you have suggsted but I get ParseException .
Please note that birth_date is simply retrieved from the database and displayed in a HTML textfield which I am passing as a http parameter to my JSP page.
System.out.println("birth_date : "+birth_date);
java.sql.Date sqlDate = null;
System.out.println("xxxxxxxxxxxxx");
try
{
DateFormat dfLocal = DateFormat.getDateInstance(
DateFormat.SHORT );
java.util.Date d = dfLocal.parse(birth_date);
System.out.println("d : "+d);
sqlDate = new java.sql.Date(
System.currentTimeMillis() );
sqlDate = new java.sql.Date( d.getTime() );
System.out.println("sqlDate : "+sqlDate);

}
catch( ParseException pe )
{
System.out.println("There is a ParseException : "+pe.getMessage());
}
pstmt = con.prepareStatement("UPDATE XXXX SET BIRTH_DATE = ? ");
int updateCount = pstmt.executeUpdate();
The output, I get is ------------------->
birth_date : 1970-10-15 00:00:00.0
xxxxxxxxxxxxx
There is a ParseException : Unparseable date: "1970-10-15 00:00:00.0"
null

Ravindra

[ravi janap]

Note this further output which I get
ORA-01407: cannot update ("RJANAPAREDDY"."XXXX"."BIRTH_DATE") to NULL

[ravi janap]

Thanks Martin
I have tried the way suggested by you. It has worked. I have been able to update the date in the database.
I have made the user to enter their birth date by using 3 lists, e.g. : Day (number 1-31), Month (number, 1-12), Year(4 digit number)?
I have retrieved these parameters as :
String month = request.getParameter("Month");
String day = request.getParameter("Day");
String year = request.getParameter("Year");
dbQuery="update XXX set birth_date=to_date('"+ year + month + day + "','yyyymmdd') where BLAH BLAH BLAH);
int updateCount = stmt.executeQuery(dbQuery);
if ( updateCount > 0 )
{
S.O.P(....)
}
Yes ....... It works this way ..................
I got the output as ------------------>
One record is updated for : ABC
Thank you very much !
Ravindra

[ravi janap]

There is small mistake in my previous reply , I have wrongly typed executeQuery instead of executeUpdate. I regret the error.

[ravi janap]

Taking my earlier problem with date further which was solved but now posing a different kind of problem . Please look at the code
java.util.Date today = new java.util.Date(); // Current Date
System.out.println("today : "+today);
int day = today.getDate();
System.out.println("day : "+day);
int month = today.getMonth();
System.out.println("month : "+month);
int year = today.getYear();
System.out.println("year : "+year);
dbQuery = "INSERT INTO XXX ( varDate ) VALUES (to_date ('"+year+month+day+"','yyyymmdd'))";
prepareStatement = con.prepareStatement(dbQuery);
insertCount = statement.executeUpdate(dbQuery);

when i execute this code I get the following output
------------------------>
today : Tue Mar 06 00:26:51 GMT+00:00 2001
day : 6
month : 2
year : 101
INSERT INTO XXXX (varDate) VALUES (to_date('10126','yyyymmdd'))
ORA-01840: input value not long enough for date format
How do I get the correct date ? The same format had earlier worked in the above case. ?
Please resolve...................

[ravi janap]

dbQuery = "INSERT INTO XXX ( varDate ) VALUES (to_date '"+('"+year+month+day+"','yyyymmdd')+"')";
but it gives alltogether a different error
invalid character constant
INSERT INTO XXX ( varDate ) VALUES (to_date '"+('"+year+month+day+"','yyyymmdd')+"')
^ ^

[Peter den Haan]

You've got your single and double quotes mixed up near "+year".
And, coincidentally, again you're specifying a format ("yyyymmdd") that won't be met by the string you're building.
- Peter