-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Oracle FIXED_CHAR read problem
PostPosted: Mon Apr 17, 2006 5:40 pm 
Newbie

Joined: Mon Mar 13, 2006 5:19 pm
Posts: 5
Hibernate version: 3.1.2

Mapping documents:
Working
Code:
<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
            "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
            "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping package="com.kewill.dashboard.db.system">
   <class name="SystemParameter" table="SYSTEM_PARAMETER" lazy="false">
      <comment>PowerHouse file location: TRM:SYSPRM.DAT</comment>

      <id name="parameterKey" type="com.kewill.base.db.OracleCHARUserType" length="20">
         <column name="PARAMETER_KEY" sql-type="character(20)" not-null="true" />
         <generator class="assigned"/>
      </id>

      <property name="parameterValue" type="com.kewill.base.db.OracleCHARUserType" length="100">
         <column name="PARAMETER_VALUE" sql-type="character(100)" not-null="true" />
      </property>

   </class>
</hibernate-mapping>

Not Working
Code:
<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
            "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
            "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping package="com.kewill.dashboard.db.system">
   <class name="UserProfile" table="USER_PROFILE" lazy="false">
      <comment>PowerHouse file location: TRM:USRPRF.DAT</comment>

      <id name="logonId" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="12">
         <column name="LOGON_ID" sql-type="character(12)" not-null="true" />
         <generator class="assigned"/>
      </id>

      <property name="userClassId" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="12">
         <column name="USER_CLASS_ID" sql-type="character(12)" not-null="true" />
      </property>
      <property name="userName" type="com.kewill.base.db.OracleCHARUserType" length="30">
         <column name="USER_NAME" sql-type="character(30)" not-null="true" />
      </property>
      <property name="userAddr" type="com.kewill.base.db.OracleCHARUserType" length="30">
         <column name="USER_ADDR" sql-type="character(30)" not-null="true" />
      </property>
      <property name="userCity" type="com.kewill.base.db.OracleCHARUserType" length="20">
         <column name="USER_CITY" sql-type="character(20)" not-null="true" />
      </property>
      <property name="userStatecode" type="com.kewill.base.db.OracleCHARUserType" length="2">
         <column name="USER_STATECODE" sql-type="character(2)" not-null="true" />
      </property>
      <property name="userZipcode" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="9">
         <column name="USER_ZIPCODE" sql-type="character(9)" not-null="true" />
      </property>
      <property name="userPhone" type="long" length="15">
         <column name="USER_PHONE" not-null="true" />
      </property>
      <property name="userPhoneExt" type="com.kewill.base.db.OracleCHARUserType" length="4">
         <column name="USER_PHONE_EXT" sql-type="character(4)" not-null="true" />
      </property>
      <property name="userPassword" type="com.kewill.base.db.OracleCHARUserType" length="8">
         <column name="USER_PASSWORD" sql-type="character(8)" not-null="true" />
      </property>
      <property name="userDept" type="com.kewill.base.db.OracleCHARUserType" length="15">
         <column name="USER_DEPT" sql-type="character(15)" not-null="true" />
      </property>
      <property name="timeUpdated" type="short" length="4">
         <column name="TIME_UPDATED" not-null="true" />
      </property>
      <property name="dateUpdated" type="com.kewill.base.db.DateUserType" length="8">
         <column name="DATE_UPDATED" not-null="true" />
      </property>
      <property name="modifiedBy" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="12">
         <column name="MODIFIED_BY" sql-type="character(12)" not-null="true" />
      </property>
      <property name="brokerTeam" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="4">
         <column name="BROKER_TEAM" sql-type="character(4)" not-null="true" />
      </property>
      <property name="userCashGroup" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="12">
         <column name="USER_CASH_GROUP" sql-type="character(12)" not-null="true" />
      </property>
      <property name="faxFrom" type="com.kewill.base.db.OracleCHARUserType" length="30">
         <column name="FAX_FROM" sql-type="character(30)" not-null="true" />
      </property>
      <property name="manualPurgeInProcess" type="com.kewill.base.db.YesNoUserType" length="1">
         <column name="MANUAL_PURGE_IN_PROCESS" not-null="true" />
      </property>
      <property name="reincarnateInProcess" type="short" length="1">
         <column name="REINCARNATE_IN_PROCESS" not-null="true" />
      </property>
      <property name="queueName" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="15">
         <column name="QUEUE_NAME" sql-type="character(15)" not-null="true" />
      </property>
      <property name="spUseDefaults" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="1">
         <column name="SP_USE_DEFAULTS" sql-type="character(1)" not-null="true" />
      </property>
      <property name="spDefCrFlag" type="com.kewill.base.db.YesNoUserType" length="1">
         <column name="SP_DEF_CR_FLAG" not-null="true" />
      </property>
      <property name="spDefEsFlag" type="com.kewill.base.db.YesNoUserType" length="1">
         <column name="SP_DEF_ES_FLAG" not-null="true" />
      </property>
      <property name="spDefBillFlag" type="com.kewill.base.db.YesNoUserType" length="1">
         <column name="SP_DEF_BILL_FLAG" not-null="true" />
      </property>
      <property name="spDefDoFlag" type="com.kewill.base.db.YesNoUserType" length="1">
         <column name="SP_DEF_DO_FLAG" not-null="true" />
      </property>
      <property name="spDefFdaFlag" type="com.kewill.base.db.YesNoUserType" length="1">
         <column name="SP_DEF_FDA_FLAG" not-null="true" />
      </property>
      <property name="spDef7512Flag" type="com.kewill.base.db.YesNoUserType" length="1">
         <column name="SP_DEF_7512_FLAG" not-null="true" />
      </property>
      <property name="spDefSebFlag" type="com.kewill.base.db.YesNoUserType" length="1">
         <column name="SP_DEF_SEB_FLAG" not-null="true" />
      </property>
      <property name="spDefBolFlag" type="com.kewill.base.db.YesNoUserType" length="1">
         <column name="SP_DEF_BOL_FLAG" not-null="true" />
      </property>
      <property name="spDefNoteFlag" type="com.kewill.base.db.YesNoUserType" length="1">
         <column name="SP_DEF_NOTE_FLAG" not-null="true" />
      </property>
      <property name="spDefRateFlag" type="com.kewill.base.db.YesNoUserType" length="1">
         <column name="SP_DEF_RATE_FLAG" not-null="true" />
      </property>
      <property name="spDefOthdocFlag" type="com.kewill.base.db.YesNoUserType" length="1">
         <column name="SP_DEF_OTHDOC_FLAG" not-null="true" />
      </property>
      <property name="spDefReprtFlag" type="com.kewill.base.db.YesNoUserType" length="1">
         <column name="SP_DEF_REPRT_FLAG" not-null="true" />
      </property>
      <property name="profileUserName" type="com.kewill.base.db.OracleCHARUserType" length="30">
         <column name="PROFILE_USER_NAME" sql-type="character(30)" not-null="true" />
      </property>
      <property name="secKey" type="com.kewill.base.db.OracleCHARUserType" length="4">
         <column name="SEC_KEY" sql-type="character(4)" not-null="true" />
      </property>
      <property name="emailAddress" type="com.kewill.base.db.OracleCHARUserType" length="60">
         <column name="EMAIL_ADDRESS" sql-type="character(60)" not-null="true" />
      </property>
      <property name="dashboardTickerFlg" type="com.kewill.base.db.YesNoUserType" length="1">
         <column name="DASHBOARD_TICKER_FLG" not-null="true" />
      </property>
      <property name="supervisorFlg" type="com.kewill.base.db.YesNoUserType" length="1">
         <column name="SUPERVISOR_FLG" not-null="true" />
      </property>
      <property name="supervisor" type="com.kewill.base.db.UpperCaseOracleCHARUserType" length="12">
         <column name="SUPERVISOR" sql-type="character(12)" not-null="true" />
      </property>
      <property name="outOfOfficeUser" type="com.kewill.base.db.OracleCHARUserType" length="12">
         <column name="OUT_OF_OFFICE_USER" sql-type="character(12)" not-null="true" />
      </property>
      <property name="outOfOfficeFlg" type="com.kewill.base.db.OracleCHARUserType" length="1">
         <column name="OUT_OF_OFFICE_FLG" sql-type="character(1)" not-null="true" />
      </property>

   </class>
</hibernate-mapping>


Full stack trace of any exception that occurs:
Quote:
Hibernate:
select
userprofil0_.LOGON_ID as LOGON1_61_0_,
userprofil0_.USER_CLASS_ID as USER2_61_0_,
userprofil0_.USER_NAME as USER3_61_0_,
userprofil0_.USER_ADDR as USER4_61_0_,
userprofil0_.USER_CITY as USER5_61_0_,
userprofil0_.USER_STATECODE as USER6_61_0_,
userprofil0_.USER_ZIPCODE as USER7_61_0_,
userprofil0_.USER_PHONE as USER8_61_0_,
userprofil0_.USER_PHONE_EXT as USER9_61_0_,
userprofil0_.USER_PASSWORD as USER10_61_0_,
userprofil0_.USER_DEPT as USER11_61_0_,
userprofil0_.TIME_UPDATED as TIME12_61_0_,
userprofil0_.DATE_UPDATED as DATE13_61_0_,
userprofil0_.MODIFIED_BY as MODIFIED14_61_0_,
userprofil0_.BROKER_TEAM as BROKER15_61_0_,
userprofil0_.USER_CASH_GROUP as USER16_61_0_,
userprofil0_.FAX_FROM as FAX17_61_0_,
userprofil0_.MANUAL_PURGE_IN_PROCESS as MANUAL18_61_0_,
userprofil0_.REINCARNATE_IN_PROCESS as REINCAR19_61_0_,
userprofil0_.QUEUE_NAME as QUEUE20_61_0_,
userprofil0_.SP_USE_DEFAULTS as SP21_61_0_,
userprofil0_.SP_DEF_CR_FLAG as SP22_61_0_,
userprofil0_.SP_DEF_ES_FLAG as SP23_61_0_,
userprofil0_.SP_DEF_BILL_FLAG as SP24_61_0_,
userprofil0_.SP_DEF_DO_FLAG as SP25_61_0_,
userprofil0_.SP_DEF_FDA_FLAG as SP26_61_0_,
userprofil0_.SP_DEF_7512_FLAG as SP27_61_0_,
userprofil0_.SP_DEF_SEB_FLAG as SP28_61_0_,
userprofil0_.SP_DEF_BOL_FLAG as SP29_61_0_,
userprofil0_.SP_DEF_NOTE_FLAG as SP30_61_0_,
userprofil0_.SP_DEF_RATE_FLAG as SP31_61_0_,
userprofil0_.SP_DEF_OTHDOC_FLAG as SP32_61_0_,
userprofil0_.SP_DEF_REPRT_FLAG as SP33_61_0_,
userprofil0_.PROFILE_USER_NAME as PROFILE34_61_0_,
userprofil0_.SEC_KEY as SEC35_61_0_,
userprofil0_.EMAIL_ADDRESS as EMAIL36_61_0_,
userprofil0_.DASHBOARD_TICKER_FLG as DASHBOARD37_61_0_,
userprofil0_.SUPERVISOR_FLG as SUPERVISOR38_61_0_,
userprofil0_.SUPERVISOR as SUPERVISOR61_0_,
userprofil0_.OUT_OF_OFFICE_USER as OUT40_61_0_,
userprofil0_.OUT_OF_OFFICE_FLG as OUT41_61_0_
from
USER_PROFILE userprofil0_
where
userprofil0_.LOGON_ID=?
20060417 04:30:38,717 WARN [] logExceptions(JDBCExceptionReporter:71) - SQL Error: 17024, SQLState: null
20060417 04:30:38,717 ERROR [] logExceptions(JDBCExceptionReporter:72) - No data read


Name and version of the database you are using: Oracle 10G 10.2.0.2.0
JDBC driver: 10.2.0.1.0

Custom User Types:
OracleCHARUserType
Code:
package com.kewill.base.db;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.OracleTypes;

import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;

public class OracleCHARUserType implements UserType {

   private static final int[] SQL_TYPES = {OracleTypes.FIXED_CHAR};

   public int[] sqlTypes() {
      return SQL_TYPES;
   }

   public Class returnedClass() {
      return String.class;
   }

   public boolean equals(Object x, Object y) throws HibernateException {
      if (x == y) return true;
      if (x == null || y == null) return false;
      return x.equals(y);
   }

   public int hashCode(Object x) throws HibernateException {
      return x.hashCode();
   }

   public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException {
      if (rs.wasNull()) return null;
      String val = StringUtils.stripEnd(rs.getString(names[0]), " ");
      if (StringUtils.isEmpty(val)) {
         return null;
      }
      return val;
   }

   public void nullSafeSet(PreparedStatement statement, Object value, int index) throws HibernateException, SQLException {
      if (value == null) {
         statement.setNull(index, OracleTypes.FIXED_CHAR);
      } else {
         String val = (String)value;
         statement.setObject(index, val, OracleTypes.FIXED_CHAR);
      }
   }

   public Object deepCopy(Object value) throws HibernateException {
      if (value == null) {
         return null;
      }
      return new String((String)value);
   }

   public boolean isMutable() {
      return false;
   }

   public Serializable disassemble(Object value) throws HibernateException {
      return (Serializable)value;
   }

   public Object assemble(Serializable cached, Object owner) throws HibernateException {
      return (String)cached;
   }

   public Object replace(Object original, Object target, Object owner) throws HibernateException {
      return null;
   }

}

YesNoUserType
Code:
package com.kewill.base.db;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;

public class YesNoUserType implements UserType {

   private static final int[] SQL_TYPES = {Types.CHAR};

   public int[] sqlTypes() {
      return SQL_TYPES;
   }

   public Class returnedClass() {
      return Boolean.class;
   }

   public boolean equals(Object x, Object y) throws HibernateException {
      if (x == y) return true;
      if (x == null || y == null) return false;
      return x.equals(y);
   }

   public int hashCode(Object x) throws HibernateException {
      return x.hashCode();
   }

   public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException {
      if (rs.wasNull()) return false;
      String val = StringUtils.stripEnd(rs.getString(names[0]), " ");
      if (StringUtils.equalsIgnoreCase("Y", val)) {
         return true;
      }
      return false;
   }

   public void nullSafeSet(PreparedStatement statement, Object value, int index) throws HibernateException, SQLException {
      boolean val = (Boolean)value;
      if (val) {
         statement.setString(index, "Y");
      } else {
         statement.setString(index, "N");
      }
   }

   public Object deepCopy(Object value) throws HibernateException {
      if (value == null) {
         return new Boolean(false);
      }
      return new Boolean((Boolean)value);
   }

   public boolean isMutable() {
      return false;
   }

   public Serializable disassemble(Object value) throws HibernateException {
      return (Serializable)value;
   }

   public Object assemble(Serializable cached, Object owner) throws HibernateException {
      return (Boolean)cached;
   }

   public Object replace(Object original, Object target, Object owner) throws HibernateException {
      return null;
   }

}


Problem description:
Ok, so here's the problem. I have to connect to a legacy database that only allows NUMERIC's and FIXED_CHAR values. To try and get around the FIXED_CHAR problem I wrote a custom user type as listed above. The Uppercase user class is exactly the same but uppercases the values in the get and set. Anyway, it appears that a call to the SYSTEM_PARAMETER table returns the values no problem, however when I try and pull a row from the USER_PROFILE table it tells me "No data read" with the SQL error of 17024. When I first implemented the FIXED_CHAR user types, which I had found in the wiki, I couldn't get the app to run because it didn't know how to map SQL type 999. I managed to fix this by creating a new Dialect which only adds the following mapping:

registerColumnType( OracleTypes.FIXED_CHAR, 4000, "char($l)" );

Does anyone have any thoughts on why this would be failing?

Thanks in advance.
Adam


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.