File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

problem insering date value in database

 
ravindra janapreddy
Ranch Hand
Posts: 84
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 31
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 389
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 389
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Note this further output which I get
ORA-01407: cannot update ("RJANAPAREDDY"."XXXX"."BIRTH_DATE") to NULL
 
ravi janap
Ranch Hand
Posts: 389
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 389
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There is small mistake in my previous reply , I have wrongly typed executeQuery instead of executeUpdate. I regret the error.
 
ravi janap
Ranch Hand
Posts: 389
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 389
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3252
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Consider Paul's rocket mass heater.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic