0 Replies Latest reply on Jan 23, 2007 6:38 AM by James Callaghan

    Hibernate3.jar and <sql-insert>

    James Callaghan Newbie

      My problem is this...

      I've got a mapping that uses:

      <sql-insert>
       insert into payment_subscriber_account_fund
       (fund_id, subscriber_id, account_number,
       account_holder_name, expiry_date, is_active, full_account_number)
       values (?, ?, ?, ?, ?, ?, aes_encrypt(?, '&key;'))
      </sql-insert>



      This was working fine in hibernate 3.0 but I've upgraded my JBoss server from 4.0.3 to 4.0.4 and hibernate has upgraded with it. Now the sql produced doesn't include the aes_encrypt value. After turning on show_sql=true I can see it do this...

      insert into payment_subscriber_account_fund (fund_id, subscriber_id, account_number, account_holder_name, expiry_date, is_active, full_account_number) values (?, ?, ?, ?, ?, ?, ?)



      Mine seems to be the correct syntax according to the documentation, and I've found one mention of this in another forum that wasn't answered. I'm assuming that this is still possible. The full mapping is as follows:

      **********************************************************

      <?xml version="1.0"?>
      <!DOCTYPE hibernate-mapping PUBLIC
       "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
       "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"
       [<!ENTITY key "grGWcw$+BrC6ya($F0[CFx}GYsWdpi-Pzte-tj7rm_wr4J65v,kObyS2sS;">]
       >
      
      <hibernate-mapping>
      
       <class name="com.telrock.mpayment.vo.SubscriberAccountFundVO"
       table="payment_subscriber_account_fund" lazy="false">
      
       <id name="id" type="integer" column="id">
       <generator class="identity"/>
       </id>
      
       <property type="integer" name="fundId">
       <column name="fund_id" not-null="true"/>
       </property>
      
       <property type="integer" name="subscriberId">
       <column name="subscriber_id" not-null="true"/>
       </property>
      
       <property type="string" name="accountNumber">
       <column name="account_number" not-null="true"/>
       </property>
      
       <property type="string" name="accountHolderName">
       <column name="account_holder_name" not-null="false"/>
       </property>
      
       <property type="string" name="expiryDate">
       <column name="expiry_date" not-null="false"/>
       </property>
      
       <property type="boolean" name="active">
       <column name="is_active" not-null="true"/>
       </property>
      
       <property type="string" name="fullAccountNumber">
       <column name="full_account_number" not-null="true"/>
       </property>
      
       <loader query-ref="payment_subscriber_account_fund"/>
      
       <sql-insert>
       insert into payment_subscriber_account_fund
       (fund_id, subscriber_id, account_number, account_holder_name, expiry_date, is_active, full_account_number)
       values (?, ?, ?, ?, ?, ?, aes_encrypt(?, '&key;'))
       </sql-insert>
      
       <sql-update>
       update payment_subscriber_account_fund set fund_id=?, subscriber_id=?, account_number=?,
       account_holder_name=?, expiry_date=?, is_active=?, full_account_number=aes_encrypt(?,'&key;') where id=?
       </sql-update>
      
      
       </class>
      
      </hibernate-mapping>


      I created a unit test to reproduce this problem. I have hibernate3.jar and ehcache1.1.jar in the classpath of the unit test. When I run the test the sql that is produced is correct - i.e. it includes the aes_encrypt.

      Now I change the hibernate3.jar with the new jar that comes bundled with JBoss404 and replace ehcache1.1.jar with ehcache1.2.jar (also in that JBoss distribution). Run the test again and the sql produced is missing the aes_encrypt.

      Next I replace these jars with those that are bundled with JBoss405 and I still have the wrong SQL.

      It would seem that <sql-insert> is being ignored by applications using later versions of the hibernate3.jar than that which comes bundled with JBoss403.

      Any ideas on how I can get this to work on 404 and above?