Mysql DOUBLE column data truncated exception using Java Double

Question

I have a MySQL table with a Double datatype column, when I insert the value contained in a Java Double I always get a truncation error.

Mrz 19, 2015 11:26:00 AM databaseimport.MyImport executeQuery SCHWERWIEGEND: null java.sql.SQLException: Data truncated for column 'SHARE' at row 1

I tried changing column to a higher precision with DOUBLE PRECISION(30,30) which seems the maximum for the DOUBLE but the error remains.

Here sample snippet:

Double share = (Double) shareRow.get("share");

sql = ("INSERT INTO `SHARE_VALUES` (`ID`, `SHARE`)" +
       "VALUES (NULL, "+share+");");
try
        {
            // Create a Preparedstatement
            PreparedStatement ps;
            ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.execute();
            ResultSet generatedKeys = ps.getGeneratedKeys();

        }
        catch (SQLException ex)
        {
            java.util.logging.Logger.getLogger(Shares.class
                    .getName()).log(Level.SEVERE, null, ex);

            return -1;
        }

please provide a snipped of the java code of the insert/update statement.... — Marcx, Mar 19 '15 at 10:53
Would expect `DOUBLE(30, 15)` to work for say `1.5`. Try things out in a mysql console, workbench, Toad, or IDE SQL console. I think the problem was for the space of integer digits, the mantissa. — Joop Eggen, Mar 19 '15 at 13:14
Tried DOUBLE(30, 30) which is the maximum precision and still data is truncated. — dendini, Mar 19 '15 at 15:49

score 0 · Accepted Answer · edited Mar 19 '15 at 16:52

Try doing this instead:

sql = String.format(Locale.US, "INSERT INTO `SHARE_VALUES` (`ID`, `SHARE`)" +
       "VALUES (NULL, %.3f);", share);

When concatenating strings java might use your locale to turn, for example 6.80 into "6,80" <- the comma (',') sign has different meaning for MySQL and that may certainly cause a lot of problems.

If you intend to use prepared statement, then this would be more like it:

NumberFormat nf = NumberFormat.getInstance(Locale.getDefault());    
Double share = nf.parse(shareRow.get("share")).doubleValue();

sql = ("INSERT INTO `SHARE_VALUES` (`ID`, `SHARE`)" +
       "VALUES (NULL, ?);");
try
{
    // Create a Preparedstatement
    PreparedStatement ps;
    ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    ps.setDouble(1, share);
    ps.execute();
    ResultSet generatedKeys = ps.getGeneratedKeys();

}
catch (SQLException ex)
{
    java.util.logging.Logger.getLogger(Shares.class
            .getName()).log(Level.SEVERE, null, ex);

    return -1;
}

Note that the query string comes with "?" signs, these indicate parameters. When you call:

ps.setDouble(1, share);

that will specify that, parameter 1 value is share, thus the first "?" sign is replaced by the value of share (totally aware of java to mysql conversions and no issues with locale).

Mysql DOUBLE column data truncated exception using Java Double

1 Answers1