Announcement Announcement Module
No announcement yet.
Hibernate with Sybase SQL Anywhere dialect Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Hibernate with Sybase SQL Anywhere dialect

    We are using hibernate with a Sybase SQLAnywhere dialect.

    We are using the hibernate mappings for two objects:
    CaseInfoReq (parent) and CaseInfoReqPh (child) (See below)

    The child CaseInfoReqPh table has a foreign key column that contains the key id of its parent CaseInfoReq.
    Because we are running the application on many remote machines that sync up their database tables to
    one central database, we need to manage the key ids in the parent CaseInfoReq table so they are unique.
    Therefore, when an insert is done on the parent table on the remote database, we have a trigger that will
    then get called which gets a unique key and sets it in the id column. We cannot make the id column an
    autoincrement or identity column because we want the trigger to assign the key id only to inserts that
    have an id column of NULL or 0. (This is because we can also have some inserts that will have ids
    and we do not want to change the id for them.)

    We are using Sybase SQL Anywhere for our remote databases.
    On our remote SQL Anywhere database, when we do an insert with hibernate session.saveOrUpdate on our
    CaseInfoReq instance (parent) that has a CaseInfoReqPh (child), the unique key id is assigned for the
    CaseInfoReq (parent) insert, and then hibernate uses @@identity to get the last inserted key id so it can use it as the
    foreign key in the CaseInfoReqPh (child) insert.

    Since @@identity will only work with autoincrement or identity columns and our id column cannot be
    autoincrement or identity, the at at identity returns 0 for the foreign key and an exception is thrown.
    (See debug below)

    Is it possible to alter the behavior of hibernate for a table (in a configuration file) so that we can tell
    it not to use @@identity to get the key id to use with the child insert and instead get or use a
    value that we specify? (The @@identity is used by hibernate with Sybase to get the last inserted key).

    In other words, how can hibernate insert a parent and child when the parent table does not have autoincrement on the primary key?

    <class name="CaseInfoReq" table="case_info_req">
    <id name="id" type="int">
    <column name="id" />
    <generator class="native" />
    <property name="idCase" type="java.lang.Integer">
    <column name="id_case" />

    <list name="phones" inverse="true"
    cascade="all,delete,delete-orphan" lazy="false">
    <column name="id_case_info_req" not-null="true" />
    <list-index base="1" column="phone_sequence" />
    <one-to-many class="CaseInfoReqPh" />


    <class name="CaseInfoReqPh" table="case_info_req_ph" >
    <id name="id" type="int">
    <column name="id" />
    <generator class="native" />
    <many-to-one name="caseInfoReq" column="id_case_info_req" not-null="true"/>

    <property name="phoneTypeCd" type="java.lang.Character">
    <column name="phone_type_cd" length="1" />

    2008-12-09 08:54:19,161 DEBUG [org.hibernate.SQL] -
    /* insert CaseInfoReq
    */ insert
    (id_case, begin_date, end_date, source_name, address1, address2, city_name, state_abbr, zip_code, zip_sfx,

    county_name, comments, created_dttm, updated_dttm, created_by, updated_by, created_last_name, created_first_name,

    updated_last_name, updated_first_name)
    (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    2008-12-09 08:54:19,161 TRACE [org.hibernate.type.IntegerType] - binding '6' to parameter: 1
    2008-12-09 08:54:19,161 TRACE [org.hibernate.type.DateType] - binding '08 December 2008' to parameter: 2
    2008-12-09 08:54:19,161 TRACE [org.hibernate.type.DateType] - binding '20 December 2008' to parameter: 3
    2008-12-09 08:54:19,161 TRACE [org.hibernate.type.TimestampType] - binding '2008-12-09 08:54:19' to parameter: 13
    2008-12-09 08:54:19,161 TRACE [org.hibernate.type.TimestampType] - binding null to parameter: 14
    2008-12-09 08:54:19,161 TRACE [org.hibernate.type.IntegerType] - binding '100054' to parameter: 15
    2008-12-09 08:54:19,161 TRACE [org.hibernate.type.IntegerType] - binding null to parameter: 16
    2008-12-09 08:54:19,177 DEBUG [org.hibernate.SQL] -
    2008-12-09 08:54:19,177 DEBUG [org.hibernate.SQL] -
    /* insert CaseInfoReqPh
    */ insert
    (id_case_info_req, phone_type_cd, phone, phone_extension, phone_sequence)
    (?, ?, ?, ?, ?)
    2008-12-09 08:54:19,177 TRACE [org.hibernate.type.IntegerType] - binding '0' to parameter: 1
    2008-12-09 08:54:19,177 TRACE [org.hibernate.type.CharacterType] - binding '3' to parameter: 2
    2008-12-09 08:54:19,177 TRACE [org.hibernate.type.IntegerType] - binding '1' to parameter: 5
    2008-12-09 08:54:19,177 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: -194, SQLState: 23000
    2008-12-09 08:54:19,177 ERROR [org.hibernate.util.JDBCExceptionReporter] - [Sybase][ODBC Driver][SQL Anywhere]No primary key value

    for foreign key 'case_info_req' in table 'case_info_req_ph'
    Last edited by jes11; Dec 11th, 2008, 09:53 AM. Reason: Need to use @@identity

  • #2
    This is really a question for the hibernate forum, but it sounds like you have to create your own customised dialect. Set up a simple test application and create a dialect that extends the SybaseDialect and modify appropriate method(s) with you logic. If you Google this you will not find many examples, but there are a few. Interface for Dialect:

    Looking at the interface it doesn't seem to be a trivial task.