[CONJ-231] NumberFormatException for getString from BigInt Created: 2015-12-08  Updated: 2016-08-23  Resolved: 2016-08-23

Status: Closed
Project: MariaDB Connector/J
Component/s: Other
Affects Version/s: 1.3.3
Fix Version/s: 1.5.1-RC

Type: Bug Priority: Major
Reporter: sfahren Assignee: Diego Dupin
Resolution: Not a Bug Votes: 0
Labels: DOUBLE, Long.parseLong(), NumberFormatException, queryResult.getString()
Environment:

Windows 7, eclipse mars, mariaDB 10.1


 Description   

In version 1.3.2 the handling of reading DOUBLE values with ResultSet.getString() has broken in version 1.3.3. The return value from the getString()-method is now change, so the Long.parseLong() throwing a NumberFormatException.

// 1449576922917
String value = queryResult.getString("index");
Long.parseLong(value)

//"1.449576736113E12"
String value = queryResult.getString("index");
Long.parseLong(value)

 Comments   
Comment by Diego Dupin [ 2015-12-14 ]

The previous implementation, may return "standard" or scientific notation depending on value and type of query.
example : 

           stmt.execute("insert into testDouble values (1234567890123456789)");
 
            ResultSet rs = stmt.executeQuery("select * from testDouble");
 
            rs.next();
 
            System.out.println(rs.getString(1)); //return 1.2345678901234568e18

Now a getString on a element store in DOUBLE type will return the java Double.toString() value (that most of the time is displayed as scientific value). That will permit for example to use Double.parseDouble().

I understand that can break exactly the case you have, but i may be wrong, but this use case must not be use normally (correct me if not) :

  • if you want accurate data with decimal value, use the DECIMAL type.
  • if you want accurate data without decimal value, you can use the BIGINT type.
  • if the DOUBLE type is the value you want, use resultSet.getDouble() and not getLong(),

because

  • what if there some decimal value ?
  • if you want the long value of it, use resultset.getLong, but this will be an approximation ...
  • is a DOUBLE field is store long, better to change to BIGINT type

example : 

try (Connection connection = DriverManager.getConnection("jdbc:mariadb://localhost:3306/testj?user=root")) {
 
            Statement stmt = connection.createStatement();
 
            stmt.execute("drop table if exists testDouble");
 
            stmt.execute("create table testDouble (id DOUBLE)");
 
            stmt.execute("insert into testDouble values (1234567890123456789)");
 
            stmt.execute("insert into testDouble values (1234567890123456790)");
 
 
 
            ResultSet rs = stmt.executeQuery("select * from testDouble");
 
            rs.next();
 
            //retrieve 1234567890123456768 and not 1234567890123456789
 
            assertEquals(1234567890123456768L, rs.getLong(1));
 
 
 
            rs.next();
 
            //retrieve 1234567890123456768 and not 1234567890123456790
 
            //same value as previous row, even if send value was different. 
            assertEquals(1234567890123456768L, rs.getLong(1));
 
        }

Comment by Diego Dupin [ 2016-08-23 ]

close since no answer since more than 6 month

Generated at Thu Feb 08 03:14:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.