Java - saving the current date into an sql database (time field)

Question

I'd much appreciate any help on this I have tried all sorts with this.

Basically I have an SQL database, which I want to save some data to, I know how to do this with all the other fields I have, but I also want to save the current time with this data.

str = "INSERT INTO tblResults (username, curTime, outcome) " +
"VALUES(" + "'" + user + "'" +", " + (new Timestamp(date.getTime())) + ", " + "'" +     outcome + "'" +")";

and then I execute the string, this was an example of one of my many attempts, this attempt compiles, but I get an error:

Incorrect syntax near '16'. Error saving result

16 being the hour right now... I think its because a time stamp puts the time before the date and an sql does the opposite, but I have no idea on how to fix this, so I'd appreciate any help. Thanks

Timestamp (java) usually differs from TIMESTAMP in your database. What data type did you specify in your database for the field? — mr.VVoo
– mr.VVoo, Commented Feb 20, 2013 at 16:23
As a suggestion, never use Statement, instead use PreparedStatement — Luiggi Mendoza
– Luiggi Mendoza, Commented Feb 20, 2013 at 16:23
How is the date formatted? Have you considered parameterizing the statement? That would save you all the trouble of converting the date/time to a string and back. — user806549
– user806549, Commented Feb 20, 2013 at 16:24
Yeah I did try other ways, that was just trying every possibility because I was just lost. I specified Time — user2092020
– user2092020, Commented Feb 20, 2013 at 16:24

Boris the Spider · Accepted Answer · 2013-02-20 16:29:22Z

2

Use a PreparedStatement and it will sort it out for you:

    final String str = "INSERT INTO tblResults (username, curTime, outcome)  VALUES( ?, ?, ?)";
    final PreparedStatement preparedStatement = connection.prepareStatement(str);
    preparedStatement.setString(1, user);
    preparedStatement.setTimestamp(2, new Timestamp(date.getTime()));
    preparedStatement.setString(3, outcome);

I guessed at your other data types...
As others have said, use of Statement is not recommended.

answered Feb 20, 2013 at 16:29

Boris the Spider

61.4k6 gold badges111 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2092020 Over a year ago

Thanks I did try a prepared statement the other day, I can't remember what problem I had, but I will definitely try it again.

RandomUs1r · Accepted Answer · 2013-02-20 16:25:53Z

0

DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
Date date = new Date();
str = "INSERT INTO tblResults (username, curTime, outcome) " +
"VALUES(" + "'" + user + "'" +", " + dateFormat.format(date) + ", " + "'"  +     outcome + "'" +")";

answered Feb 20, 2013 at 16:25

RandomUs1r

4,2182 gold badges27 silver badges47 bronze badges

Comments

Cromax · Accepted Answer · 2013-02-20 16:27:16Z

0

You miss apostrophes around new Timestamp() in your SQL query, but you really should use PreparedStatement instead for this case.

answered Feb 20, 2013 at 16:27

Cromax

2,0821 gold badge27 silver badges39 bronze badges

1 Comment

user2092020 Over a year ago

I have no idea how I missed the apostrophes, probably all the deleting and retyping, thanks, I did try a prepared statement the other day, I don't know why I gave up on it, thanks I will try again.

user_CC · Accepted Answer · 2013-02-20 16:31:25Z

0

The error is coming up because the reference value of new Timestamp(date.getTime()) is going into the string instead of correct timestamp formated string.

replace with this and then execute : str = "INSERT INTO tblResults (username, curTime, outcome) " + "VALUES(" + "'" + user + "'" +", " + (new Timestamp(date.getTime()).toString() + ", " + "'" + outcome + "'" +")";

if you do not put the toString() method then the reference address of the object will go into the string.

You can also print the whole string in the Logger to see what is actually going into str

edited Feb 20, 2013 at 16:31

answered Feb 20, 2013 at 16:24

user_CC

4,7863 gold badges22 silver badges16 bronze badges

4 Comments

user2092020 Over a year ago

Thanks, I tried this but I get this error, any idea? The statement did not return a result set. Error saving result

user_CC Over a year ago

are you using executeUpdate() method to insert the record? use this statement if you are not using this one

user2092020 Over a year ago

thank you very much, I was using executeQuery(); executeUpdate() has fixed it, looks like it has at least saved correctly, so at least now I have something that works before I can make it a little better. thanks again.

user_CC Over a year ago

@user2092020 I am glad I solved your problem and answered your question. Please accept my answer. Many thanks

Collectives™ on Stack Overflow

Java - saving the current date into an sql database (time field)

4 Answers 4

1 Comment

Comments

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related