1

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

user2092020
  • 21
  • 1
  • 3
  • 1
    http://stackoverflow.com/questions/2858182/preparedstatement-and-settimestamp-in-oracle-jdbc – Jayan Feb 20 '13 at 16:22
  • 1
    Timestamp (java) usually differs from TIMESTAMP in your database. What data type did you specify in your database for the field? – mr.VVoo Feb 20 '13 at 16:23
  • 2
    As a suggestion, **never use `Statement`**, instead use `PreparedStatement` – Luiggi Mendoza Feb 20 '13 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. –  Feb 20 '13 at 16:24
  • Yeah I did try other ways, that was just trying every possibility because I was just lost. I specified Time – user2092020 Feb 20 '13 at 16:24

4 Answers4

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.

Boris the Spider
  • 59,842
  • 6
  • 106
  • 166
  • 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. – user2092020 Feb 20 '13 at 16:37
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

user_CC
  • 4,686
  • 3
  • 20
  • 15
  • Thanks, I tried this but I get this error, any idea? The statement did not return a result set. Error saving result – user2092020 Feb 20 '13 at 16:35
  • are you using executeUpdate() method to insert the record? use this statement if you are not using this one – user_CC Feb 20 '13 at 16:43
  • 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. – user2092020 Feb 20 '13 at 17:47
  • @user2092020 I am glad I solved your problem and answered your question. Please accept my answer. Many thanks – user_CC Feb 20 '13 at 22:48
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 + "'" +")";
RandomUs1r
  • 4,010
  • 1
  • 24
  • 44
0

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

Cromax
  • 1,822
  • 1
  • 23
  • 35
  • 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. – user2092020 Feb 20 '13 at 16:38