Hibernate & Sybase integration

    This wiki is for recording the issues we found when using hibernate on Sybase.

     

    hibernate source:

     

    testing environment info:

    • OS: RHEL 5 (if you're using windows, then you should increase the java stacy by -Xss to at least 1024k, see HHH-2166 for more details)
    • JDK6
    • jConnect driver version : 7.0.0_26502 (ASE 15.5); 7.0.7_26602 (ASE 15.7)
    • driver class: com.sybase.jdbc4.jdbc.SybDriver
    • hibernate dialect : org.hibernate.dialect.SybaseASE15Dialect & org.hibernate.dialect.SybaseASE157Dialect

     

    change should made in hibernate:

    • change log4j.logger.org.hibernate.hql.ast.HqlSqlWalker=trace to error level (core/testsuite/src/test/resources/log4j.proeprties),  see HHH-2166 for more details
    • set hibernate.jdbc.use_streams_for_binary to false.
    • set quoted_identifier on
    • set arithabort numeric_truncation off
    • set ansinull on  HHH-4580
    • DYNAMIC_PREPARE=true (for jConnect 3)  HHH-2388

     

    Sybase DB configuration:

    • enable "ddl in trans" (include tempdb)
    • enable "allow nulls by default" (include tempdb)
    • page size : 8k
    • sp_configure ‘disable varbinary truncation’, 1  HHH-4452
    • sp_configure 'enable functionality group', 1 (ASE 15.7 only)
    • sp_configure 'default sortorder id', 52 - (Sybase database is 'case sensitive' by default. Make it insensitive by running following command for utf8 charset and then restarting the server.)
    • Make sure to read documentation before changing case sensitivity as the option is configured for all databases in a sybase instance.
    • sp_configure "lock scheme", 0, datarows  - ( 'Select for update' requires row level locking enanbled)


    known issues (see issue description for more details): HHH-6773

    • HHH-3716/HHH-5413  null values for columns mapped as "boolean" are persisted as 0 (zero) instead of NULL

        workaround:

         There is a class in the unit tests, org.hibernate.test.where.NumericTrueFalseType, that could be used as a workaround for mapping boolean columns.

         To use the workaround, instead of mapping:
            type="boolean"
         use:
            type="org.hibernate.test.where.NumericTrueFalseType" (fixed by HHH-6795)


    • HHH-3690  Sybase ASE 15.5 does not support multiple columns in the exists clause, fixed in ASE 15.7 -

        workaround:

         Using native sql and avoid using element method in HQL

    • HHH-3032  Sybase ASE 15.5 restriction of using alias in sub-query, fixed in ASE 15.7

        workaround:

         Using native sql maybe a better choice in this situation.

    • HHH-6424  avg() function returns a value in the type of the parameters

        workaround:

         This is ASE's behavior, not a bug.

    • HHH-6426  Sybase removes all trailing spaces when inserting data to varchar - Test skipped
    • HHH-3637  Sybase ASE 15.5 does not support union inside where in clause -  - Test skipped
    • HHH-5229  Error while scrolling on result set
    • HHH-6425  Sybase ASE stores it as single space ' ' while inserting a empty string '' -  - Test skipped

        workaround:

         This is ASE's behavior, not a bug.

    • HHH-6788  Test fail while setting DYNAMIC_PREPARE true, but woks fine as setting false
    • HHH-6807 org.hibernate.test.lob.LobMergeTest fail on Sybase ASE  - Test skipped
    • HHH-6820 Skip test org.hibernate.ejb.test.lock.LockTest.testContendedPessimisticLock for Sybase ASE15.5 - Fixed in 15.7