aspose file tools*
The moose likes JDBC and the fly likes problem insering date value  in database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "problem insering date value  in database" Watch "problem insering date value  in database" New topic
Author

problem insering date value in database

ravindra janapreddy
Ranch Hand

Joined: Jan 30, 2001
Posts: 84
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 ?


SCEA, SCBCD, SCWCD, SCJD, SCJP
martin samm
Ranch Hand

Joined: Nov 09, 2000
Posts: 31
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
m_sam@rroom.net


martin samm<BR>m_sam@rroom.net
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
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
Ranch Hand

Joined: Nov 04, 2000
Posts: 389
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

SCJP, SCJD, SCWCD, SCBCD, SCEA
ravi janap
Ranch Hand

Joined: Nov 04, 2000
Posts: 389
Note this further output which I get
ORA-01407: cannot update ("RJANAPAREDDY"."XXXX"."BIRTH_DATE") to NULL
ravi janap
Ranch Hand

Joined: Nov 04, 2000
Posts: 389
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
Ranch Hand

Joined: Nov 04, 2000
Posts: 389
There is small mistake in my previous reply , I have wrongly typed executeQuery instead of executeUpdate. I regret the error.
ravi janap
Ranch Hand

Joined: Nov 04, 2000
Posts: 389
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
Ranch Hand

Joined: Nov 04, 2000
Posts: 389
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
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
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
 
 
subject: problem insering date value in database