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
  • HHH-3637  Sybase ASE 15.5 does not support union inside where in clause
  • HHH-5229  Error while scrolling on result set
  • HHH-6425  Sybase ASE stores it as single space ' ' while inserting a empty string ''

    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
  • HHH-6820 Skip test org.hibernate.ejb.test.lock.LockTest.testContendedPessimisticLock for Sybase ASE15.5 - Fixed in 15.7