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

asked 13 Dec '13, 17:00

tripperm's gravatar image

tripperm
16112
accept rate: 0%

edited 15 Dec '13, 16:20

Jeff%20Albion's gravatar image

Jeff Albion
10.8k171175

1

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

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%20Albion's gravatar image

Jeff Albion
10.8k171175
accept rate: 25%

edited 17 Dec '13, 14:15

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%20He's gravatar image

Xiong He
162
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
Your answer
toggle preview

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

Question tags:

×39
×7

question asked: 13 Dec '13, 17:00

question was seen: 5,126 times

last updated: 17 Dec '13, 14:15