?
Solved

Java + MySQL and DateTime column exception

Posted on 2009-02-20
20
Medium Priority
?
1,236 Views
Last Modified: 2012-05-06
Hi,
I am running following sql and SOMETIMES I get this exception

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '20090220210660.000000' for column 'bidding_time' at row 1
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:734)
        at com.xxxxxx.auction.AuctionManager.globalTick(AuctionManager.java:105)
        at globaltick.Main.main(Main.java:24)


Can some one help me to optimize this query?

Thanks
s2.execute("INSERT INTO auction_bidding_history (auction_id,bidder,bidding_time,bid_type,new_bid_count) VALUES (" +
                                        rs3.getString("auction_id")+","+
                                        rs3.getString("user_id")+","+
                                        "now()+"+row_count+","+
                                        "1,"+(rs.getInt("bid_count")+1)+")");

Open in new window

0
Comment
Question by:bilgehanyildirim
  • 9
  • 6
  • 5
20 Comments
 
LVL 92

Expert Comment

by:objects
ID: 23696323
better off using a PreparedStaement, it will handle formatting for you.

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23696332
I would use a PreparedStatement and do


ps.setTimestamp
0
 

Author Comment

by:bilgehanyildirim
ID: 23696353
Ok,
Can you give me an example how to use preparedStatement. But please bear in mind, I need to update the timestamp column with current_timestamp+1
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 92

Expert Comment

by:objects
ID: 23696358
removing the plus one should fix it.
why do u need to add 1?

0
 
LVL 92

Expert Comment

by:objects
ID: 23696372
use DATE_ADD() to add to the date, +1 doesn't really do anything.

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23696373
A kludgy way of getting a String timestamp would be
String ts = String.format("'%s'", new java.sql.Timestamp(System.currentTimeMillis()).toString());

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23696401
Sorry - missed the +1. So, you could do
String ts = String.format("'%s'", new java.sql.Timestamp(System.currentTimeMillis() + (1000*60*60*24)).toString());

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23696412
>>I need to update the timestamp column with current_timestamp+1

+1 what btw? One day?
0
 
LVL 92

Expert Comment

by:objects
ID: 23696415
try something like this:

s2.execute("INSERT INTO auction_bidding_history (auction_id,bidder,bidding_time,bid_type,new_bid_count) VALUES (" +
                                        rs3.getString("auction_id")+","+
                                        rs3.getString("user_id")+","+
                                        "date_add(now(), "+row_count+"),"+
                                        "1,"+(rs.getInt("bid_count")+1)+")");

0
 

Author Comment

by:bilgehanyildirim
ID: 23696421
Savant,
I am inserting series of row which need to have 1 second in between them, that's why I am adding 1.
Cehj,
Is there a better way to get the current timestamp and 1 second to it?


Thanks
0
 

Author Comment

by:bilgehanyildirim
ID: 23696433
+1 is 1 second
0
 
LVL 92

Expert Comment

by:objects
ID: 23696439
> Is there a better way to get the current timestamp and 1 second to it?

yes, see the code I posted. Don't do it with java its unnecessary.

0
 
LVL 92

Accepted Solution

by:
objects earned 1600 total points
ID: 23696445
s2.execute("INSERT INTO auction_bidding_history (auction_id,bidder,bidding_time,bid_type,new_bid_count) VALUES (" +
                                        rs3.getString("auction_id")+","+
                                        rs3.getString("user_id")+","+
                                        "date_add(now(), "+row_count+" second),"+
                                        "1,"+(rs.getInt("bid_count")+1)+")");

0
 
LVL 92

Expert Comment

by:objects
ID: 23696454
let me know how that goes :)

0
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 400 total points
ID: 23696471
In that case:
String ts = String.format("'%s'", new java.sql.Timestamp(System.currentTimeMillis() + 1000).toString());

Open in new window

0
 
LVL 92

Expert Comment

by:objects
ID: 23696487
better off letting the database handle the formatting, safer and in your case more accurate.
Not to mention simpler :)

0
 

Author Comment

by:bilgehanyildirim
ID: 23696557
Savant: Thanks! It solved my problem. Fantastic tip.
By the way did you mean
"DATE_ADD(now(), INTERVAL "+row_count+" SECOND),"+

CEHJ,
Thanks for the tip about the timestamp!!


0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23696596
:-)

Bear in mind that code is not portable
0
 

Author Comment

by:bilgehanyildirim
ID: 23696678
MySQL is the only DB I know :)))) So I don't think it's going to be any problem.
0
 
LVL 92

Expert Comment

by:objects
ID: 23697623
> String ts = String.format("'%s'", new java.sql.Timestamp(System.currentTimeMillis() + 1000).toString());


thats actually non-portable, and error prone.
Better to use a PreparedStatement when using timestamps as I suggested earlier
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
The viewer will learn how to implement Singleton Design Pattern in Java.
Suggested Courses
Course of the Month14 days, 4 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question