Reputation: 73
When inserting a BigDecimal value into a NUMERIC column using a JDBC prepared statement with a bind variable, the decimal fraction is lost. On the other hand with a bind variable is not used, the decimal fraction is preserved.
create table DS_TEST.BIGDECIMAL_TEST (NUMERIC_COL numeric)
String connectionString = "jdbc:bigquery://${host}:${port};ProjectId=${projectId};OAuthType=${OAuthType};OAuthServiceAcctEmail=${OAuthServiceAcctEmail};OAuthPvtKeyPath=${OAuthPvtKeyPath}"
Class.forName("com.simba.googlebigquery.jdbc42.Driver").newInstance()
Connection connection = DriverManager.getConnection(connectionString)
// insert statement without bind variable
PreparedStatement preparedStatement = connection.prepareStatement
("insert into `${projectId}`.DS_TEST.BIGDECIMAL_TEST (NUMERIC_COL) values (123.45)")
preparedStatement.executeUpdate()
// insert statement using bind variable
preparedStatement = connection.prepareStatement
("insert into `${projectId}`.DS_TEST.BIGDECIMAL_TEST (NUMERIC_COL) values (?)")
preparedStatement.setBigDecimal(1, new BigDecimal("567.89"))
preparedStatement.executeUpdate()
preparedStatement.close()
I expected the output:
+-------------+
| NUMERIC_COL |
+-------------+
| 123.45 |
| 567.89 |
+-------------+
but the actual output is
+-------------+
| NUMERIC_COL |
+-------------+
| 123.45 |
| 567 |
+-------------+
Is this a bug or am I missing something?
Upvotes: 2
Views: 1029
Reputation: 2893
I was able to replicate your scenario as my BigDecimal values are being rounded too. Nevertheless, after digging into this, I found that there's an issue reported where the BigDecimal values passed to the PreparedStatement
are being rounded to the nearest integer. I suggest subscribing to it to track its updates. The workaround would be using:
PreparedStatement preparedStatement = connection.prepareStatement
("insert into `${projectId}`.DS_TEST.BIGDECIMAL_TEST (NUMERIC_COL) values (123.45)")
preparedStatement.executeUpdate()
As you already are doing.
Hope it helps.
Upvotes: 1