JConnect 7 Truncates BigDecimal Value

I have an issue where I am attempting to update a table that has a numeric column with a scale of 2 and the jdbc driver appears to be truncating the value. So '2.92' ends up as '2.00' in the database. Here is the environment I am running under:

SQL Anywhere 11 with the latest EBF applied (EBF21751)

JConnect 7.07 #5 (latest available)

Hibernate 4.1.11

Spring 3.2.4

The numeric column in my database is defined as numeric(11,2). The variable in the hibernate entity for the column is mapped as a BigDecimal. When using an older JConnect 6.0 jdbc driver this problem did not occur (had to upgrade because of a timestamp bug in old 6.0 jdbc driver).

Changing the variable type to a Double solves the problem, but is not an acceptable solution due to lack of precision for doubles.

If this is a bug in the JConnect driver, does anyone know how I would go about submitting this as a bug - if it hasn't already.

Thanks

hibernate jconnect

asked 13 Dec '13, 17:00

tripperm
16●1●1●2
accept rate: 0%

edited 15 Dec '13, 16:20

Jeff Albion
10.8k●1●71●175

Are you using the latest Hibernate Dialect for SQL Anywhere 11 and Hibernate 4.x? (See: https://github.com/sqlanywhere/hibernate/tree/master/hibernate4 )

Please see the README: https://github.com/sqlanywhere/hibernate/blob/master/hibernate4/SQLAnywhereDialect_Readme_4.txt

- Supports SQL Anywhere 10 and 11, using the Dialects (*):

org.hibernate.dialect.SQLAnywhereDialect10
org.hibernate.dialect.SQLAnywhereDialect10SnapTran
org.hibernate.dialect.SQLAnywhereDialect11
org.hibernate.dialect.SQLAnywhereDialect11SnapTran
...
Sample Hibernate.cfg.xml
========================
<!-- jConnect Definition -->
<property name="hibernate.connection.driver_class">com.sybase.jdbc4.jdbc.SybDriver</property>
<property name="hibernate.connection.url">jdbc:sybase:Tds:localhost:2638</property>
<property name="hibernate.connection.username">dba</property>
<property name="hibernate.connection.password">sql</property>
<property name="hibernate.connection.JCONNECT_VERSION">7</property>
<property name="hibernate.connection.DYNAMIC_PREPARE">true</property>
<property name="hibernate.jdbc.batch.builder">org.hibernate.dialect.sqlanywhere.JConnBatchBuilderImpl</property>

jConnect Support Notes

java.math.BigInteger (NUMERIC) and java.math.BigDecimal (DECIMAL) fields are not bound
correctly if mapped using the Hibernate 'org.hibernate.type.BigIntegerType' /
'org.hibernate.type.BigDecimalType' types.
(e.g. You may receive SQLCODE -158: 'Value %1 out of range for destination' errors).

Instead, these fields must be mapped as 'org.hibernate.type.StringType' types.

So:

<property name="decimalField1" column="decimalField1" precision="30" scale="6" type="org.hibernate.type.BigDecimalType" />

would become:

<property name="decimalField1" column="decimalField1" length="31" type="org.hibernate.type.StringType" />

(15 Dec '13, 14:00) Jeff Albion

flat view

2 Answers:

active answers oldest answers newest answers popular answers

UPDATE I can now reproduce your issue, but only if my jConnect string does not include DYNAMIC_PREPARE=true. You should ensure your hibernate.cfg.xml file includes the line:

<property name="hibernate.connection.DYNAMIC_PREPARE">true</property>

I can otherwise reproduce this behaviour with the following code:

Java

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.math.BigDecimal;
import com.sybase.jdbc4.jdbc.*;

public class jconnSelect
{
    public static void main( String args[] )
    {
        try
        {
        Connection con = DriverManager.getConnection("jdbc:sybase:Tds:localhost:2638?JCONNECT_VERSION=7", "DBA", "sql");
            Statement stmt = con.createStatement();
            stmt.executeUpdate("DROP TABLE t1");
            stmt.executeUpdate("CREATE TABLE t1 ( c1 NUMERIC(11,2) )");
            PreparedStatement pstmt = con.prepareStatement("INSERT INTO t1 (c1) VALUES (?)");
            pstmt.setBigDecimal(1, new BigDecimal(2.92));
            pstmt.executeUpdate();
            ResultSet rs = stmt.executeQuery("SELECT c1 FROM t1");
            while (rs.next())
            {
                BigDecimal bd = rs.getBigDecimal(1);
                System.out.println("Value is: " + bd.toString());
            }
            rs.close();
            stmt.close();
            con.close();
         } catch (Exeception e){
            e.printStackTrace();
         }
     }
 }

Result

Value is: 2.00

Adding &DYNAMIC_PREPARE=true to the connection string resolves the issue:

Result

Value is: 2.92

permanent link

answered 17 Dec '13, 12:56

Jeff Albion
10.8k●1●71●175
accept rate: 25%

edited 17 Dec '13, 14:15

flat view

I don't believe this is a jConnect Driver bug.
To prove this is a bug, you can write s separate java jdbc program to prove the numberic(11, 2), bind BigDecimal(2.92) to be truncated into BigDecimal(2.00).

I looks most likely this is a mapping issue in hibernate. You can open the hibernate sql trace to get more details about the sql executing.

Another point is, when you upgrade jConnect jdbc, you need execute the sp SQL in the jconnect driver on the ASA server side to get the metadata consistent with it. The sp path is about: jConnect-707spsql_asa11.sql. I think.

permanent link

answered 15 Dec '13, 00:10

Xiong He
16●2
accept rate: 0%

I did run the sql script that comes with JConnect as the documentation states. I also created a pure JDBC connection (not going through Hibernate) and binding a BigDecimal parameter still caused the truncation of the decimal portion. So I don't think this is a Hibernate issue. I will see if I am running the latest dialect just to be sure.

(16 Dec '13, 12:35) tripperm

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

jconnect ×39
hibernate ×7

question asked: 13 Dec '13, 17:00

question was seen: 5,014 times

last updated: 17 Dec '13, 14:15

SAP SQL Anywhere

JConnect 7 Truncates BigDecimal Value

jConnect Support Notes

Follow this question

Related questions