This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Prepared statement's 'setDate' method Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Prepared statement Watch "Prepared statement New topic
Author

Prepared statement's 'setDate' method

Varnam Aayiram
Ranch Hand

Joined: Dec 23, 2008
Posts: 88
Hi guys...

I am having an issue with preparedstatement's 'setDate' method. This is what I did.

My prepared statement import



I have an sql statement this way:


My prepared statement:


and this is my code to actually do the insert


I am getting an error that says 'unclosed character literal'. I tried replacing the single quotes around 2009.06.29, with double quotes and the problem still persists. Any idea why I am getting this error? Thanks.
Pushkar Choudhary
Rancher

Joined: May 21, 2006
Posts: 425

Varnam Aayiram wrote:

Not sure if this is the thing that's causing the problem, but the second argument here should be a java.sql.Date object.
Varnam Aayiram
Ranch Hand

Joined: Dec 23, 2008
Posts: 88
Hi Pushakar..

Thanks for the reply. After reading your message, I did this:

An import statement:


and a declaration cum assignment:

in the above mentioned line itself, I am getting the unclosed character literal....I am not sure what else to do...
Pushkar Choudhary
Rancher

Joined: May 21, 2006
Posts: 425

Try creating a new Date object by using the new operator and see if it makes any difference.

By the way,
Varnam Aayiram wrote:Hi Pushakar..

My name is Pushkar.
Pushkar Choudhary
Rancher

Joined: May 21, 2006
Posts: 425

Also, please carefully choose one forum for asking questions. Your question has nothing to do with JSPs.

Moderators, I think this question belongs to the JDBC forum and should be moved there.
Varnam Aayiram
Ranch Hand

Joined: Dec 23, 2008
Posts: 88
Hi Pushkar

Ops....sorry to have spelled your name wrongly earlier. In my haste I have posted my question in the JSP forum. I agree that JDBC would have been the more appropriate forum. I dont think I can move the to the JDBC forum myself, I hope moderators will be kind enough to do it. Thanks and sorry for the trouble.

I will continue to post here for this problem as I don't want to start another fresh thread in JDBC, while I have already created a history here (hopefully this will be 'moved' to JDBC soon).

Yes the below mentioned works perfectly well for me:


However when I run this servlet, I am getting the following error



I am listing down the full source code here:


I believe I understand what the error message is saying, that I can't enter a value in the FK_iddate column that dosen't exist in the iddate column of the calendar table. I find this puzzling as the value I am trying to add for FK_iddate column already exists in the calendar table and the relationship between the calendar and booking table is '1(calendar) to many(booking)' However when I run the SQL query directly in MYSQL itself, I am not getting any error and a new row is sucessfully added in the booking table. The MySQL query is as follows:


Any idea what is wrong, I am very puzzled by this.



Varnam Aayiram
Ranch Hand

Joined: Dec 23, 2008
Posts: 88
Hi guys...

Is one supposed to declare upfront if one has posted the same question/s in other forums? Well, the questions I have raised here I have also raised in a couple of other forums and someone from one of the other forums was not too happy about it. Anyway the reason I am doing this is to open up the questions to more people so that there will be a higher chance of getting an answer and others caught in a simillar situation as mine will also benefit. Once the problem has been finally solved on my end, I will share the solution with one and all. Thanks and sorry if I had caused any trouble.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

Hi,

the error message you get is very descriptive. It explains your problem:
(you) Cannot add or update a child row: (because) a foreign key constraint fails .

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html


OCUP UML fundamental and ITIL foundation
youtube channel
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

Varnam Aayiram wrote:Hi guys...

Is one supposed to declare upfront if one has posted the same question/s in other forums? Well, the questions I have raised here I have also raised in a couple of other forums and someone from one of the other forums was not too happy about it. Anyway the reason I am doing this is to open up the questions to more people so that there will be a higher chance of getting an answer and others caught in a simillar situation as mine will also benefit. Once the problem has been finally solved on my end, I will share the solution with one and all. Thanks and sorry if I had caused any trouble.
Hi,
you are asked to post in one forum. (just like Pushkar pointed out). Choose the forum that is the most relevant.
We are all volunteers. It is disappointing if a volunteer has spent effort to give you a good reply, only to find out that your issue has already been resolved in another forum. You will find that people stop to help you if you make a habit of posting on multiple fora.
Regards, Jan
Pushkar Choudhary
Rancher

Joined: May 21, 2006
Posts: 425

Varnam Aayiram wrote:Is one supposed to declare upfront if one has posted the same question/s in other forums?

Yes. Read this for more details.

Jan,
I think by forum, he means other websites and not forums on Javaranch, since I don't see the same question here on other Javaranch forums.
Varnam Aayiram
Ranch Hand

Joined: Dec 23, 2008
Posts: 88
Hi Jan..

As I mentioned in my posting:

I believe I understand what the error message is saying, that I can't enter a value in the FK_iddate column that dosen't exist in the iddate column of the calendar table. I find this puzzling as the value I am trying to add for FK_iddate column already exists in the calendar table and the relationship between the calendar and booking table is '1(calendar) to many(booking)' However when I run the SQL query directly in MYSQL itself, I am not getting any error and a new row is sucessfully added in the booking table.

.....I am aware of what the error message meant.

Yes as Pushkar mentioned, I posted the same question in forums of other websites. The problem has now been solved and the working code is as below:



I had initialized 'Date' this way: Date d1 = new Date(20090630); and when I did a

I got this: 1970-01-01 Definately this is not what I wanted. It was pointed out to me from someone from another forum that
Date.valueOf() is the correct way of acheiving what I wanted to do, with that the problem was solved.
Thanks Pushkar to have first pointed me in the right direction and thanks to the kind souls from the
other forums who had helped me further when I was a little lost when dealing with 'Date'.


Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

The Date(String) constructor is deprecated since java 1.1.
Maybe java.text.SimpleDateFormat.parse() can help?
Jinglong Wang
Greenhorn

Joined: Mar 05, 2010
Posts: 12
The Data Object create maybe like this:


this date object is NOW..

hope to help you!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Prepared statement's 'setDate' method
 
Similar Threads
Enhanced PreparedStatement
SQL query
JDBC and smalldatetime - ms sql server 2008
inserting single quotes into database
preparestatement