• Post Reply Bookmark Topic Watch Topic
  • New Topic

Error in posting data to sql  RSS feed

 
Rakesh Keerthi
Ranch Hand
Posts: 152
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I've the below data in my HTML.



and i'm trying to post it to Insert_Record servlet. and it is as below.



I'm getting the below error.



if i use d-MM-yyy, the output is



but i want the date in output to be.



below is how my data base is like.



please let me know how can i get this.

Thanks,
Rakesh
DB.png
[Thumbnail for DB.png]
Table
 
Ulf Dittmer
Rancher
Posts: 42970
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The issue of data storage is unrelated to the issue of data display. You will need to provide the DB with a data format it can store.

The first thing you should do is switch back to using the PreparedStatement. It will do many things for you, including the handling data types that you're currently struggling with. You would not use "setString" for date and timestamp fields, but methods that are appropriate for those data types.

Another thing PreparedStatement does is to protect you from SQL injection attacks - your current code is wide open to those attacks.
 
Rakesh Keerthi
Ranch Hand
Posts: 152
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Ulf,

Thanks for the quick response, here first of all, i'm trying to print the query and lateron, once i get the date in the format that i want, i'll change the setString to setDate or corresponding things, her the date the query i'm printing is not in the proper format that i want.


but when i get this type of date in output it is throwing the exception as mentioned above.
 
Ulf Dittmer
Rancher
Posts: 42970
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is the "output"? I don't see code that would output dates anywhere. So I'm not clear which line of code produces the exception you mention.

If you're talking about printing the query, then that calls the Date.toString method, the output of which is irrelevant to either storing the date in the DB, or displaying the date in a web page. (Date objects have no inherent format, they're just data.)

So I repeat: start by using PreparedStatement so that the storing in the DB works.
 
Rakesh Keerthi
Ranch Hand
Posts: 152
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Below screenshots shows the input and the query generated.

The red boxes represents the dates.
Input.JPG
[Thumbnail for Input.JPG]
Input
Query.JPG
[Thumbnail for Query.JPG]
Query
 
Ulf Dittmer
Rancher
Posts: 42970
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That doesn't look like valid SQL - what's the "str_" doing there? That's another example of how a PreparedStatement would help.
 
Rakesh Keerthi
Ranch Hand
Posts: 152
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry, that was a typo in my sql string. updated it, but still the same output

Query.JPG
[Thumbnail for Query.JPG]
DB Query
 
Dave Tolls
Ranch Hand
Posts: 2504
27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As Ulf has said, you are mixing displaying your dates up with how they are stored.
Your Date fields in the database do not have a format...just as java.util.Date's do not have a format.

So parse the dates supplied by the user into a Date and use a prepared statement to store that in the relevant date column in your table.
When you read it out for display then format the returned Date into the form you require.
 
Ulf Dittmer
Rancher
Posts: 42970
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So what date format are you now using? For "09-10-14", it should be something like "MM-dd-yy", assuming that the month comes first.
 
Rakesh Keerthi
Ranch Hand
Posts: 152
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes that is right when i use

the output is


when i use

the output is
 
Ulf Dittmer
Rancher
Posts: 42970
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So all is good now?
 
Rakesh Keerthi
Ranch Hand
Posts: 152
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nop. I'm getting the Exception if i change the format.
 
Ulf Dittmer
Rancher
Posts: 42970
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Now that you know what the correct format is, why would you switch back to the wrong one?
 
Rakesh Keerthi
Ranch Hand
Posts: 152
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
when i'm trying to use the correct format it doesn't show the correct output in the query, if the format is modified, then i get the correct format with an exception
 
Ulf Dittmer
Rancher
Posts: 42970
73
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'll say it again: the way the Date object is printed in that SQL string is completely irrelevant. If you think it is then you have misconceptions about how Date objects and their parsing and formatting works. As I've also said, you should be using PreparedStatement (and not care about what SQL is generated). What matters is that you are able to store it in the DB, no?

How dates are formatted in the input and output HTML pages are separate matters that have nothing to do with how the date is stored in the DB.
 
Rakesh Keerthi
Ranch Hand
Posts: 152
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Got it thank you very much. :-)
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!