CODEWITHSUNDEEP
javasql-server
user2092020
user2092020

Reputation: 21

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

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

Upvotes: 1

Views: 2947

Answers (4)

user_CC
user_CC

Reputation: 4776

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

Upvotes: 0

Boris the Spider
Boris the Spider

Reputation: 61128

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.

Upvotes: 2

Cromax
Cromax

Reputation: 2042

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

Upvotes: 0

RandomUs1r
RandomUs1r

Reputation: 4190

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 + "'" +")";

Upvotes: 0

Related Questions