OleDbDriver problem with Oracle

Jumpin'Jack Flash · **Joined:** Mon Jun 06, 2005 3:36 pm **Posts:** 5

I had the following code and mapping working with the OracleClientDriver. But for availability reasons, I had to fall back to the OleDbDriver.

Class definition:

Code:

Public Class TransactionErrorNumber
    Private _id As System.String
    Private _severityId As System.Int16

(... usefull method for the class, since access is field it is useless to reprint ...)

End Class

NHibernate Mapping:

Code:

<class name="TransactionErrorNumber, HubErrorManagement" table="TRANSACTION_ERROR_NUMBER">
   <id name="_id" access="field" column="TRANS_ERROR_NUMBER" type="AnsiString">
      <generator class="assigned" />
   </id>
   <property name="_severityId" access="field" column="TRANS_ERROR_NUMBER_SEV_ID" />
</class>

Since I made that switch, the application throw out an error when it try to load the data.

From the log, I can see that there is other select that do work, so DB conectivity is fine.

My guess is with the fact that the PK is a VARCHAR2. Early I found searching this forum that I had to specify the type with type="AnsiString" in the NHibernate mapping so that the proper "string translation" would be use with Oracle.

I tried adding length="10" to the id definition (it is the length of the VARCHAR2 in the database), since the error log was refering to some missing lenght identifier (i.e. Message: OleDbCommand.Prepare method requires all variable length parameters to have an explicitly set non-zero Size.).

Part of the log:

Code:

(... truncated ...)

223796 [1744] DEBUG NHibernate.Type.NullableType - binding 'INV-0237' to parameter: 0
223796 [1744] INFO  NHibernate.Loader.Loader - SELECT transact0_.TRANS_ERROR_NUMBER as TRANS_ER1_0_, transact0_.TRANS_ERROR_NUMBER_SEV_ID as TRANS_ER2_0_ FROM TRANSACTION_ERROR_NUMBER transact0_ WHERE transact0_.TRANS_ERROR_NUMBER = ?
224218 [1744] INFO  NHibernate.Impl.BatcherImpl - Preparing SELECT transact0_.TRANS_ERROR_NUMBER as TRANS_ER1_0_, transact0_.TRANS_ERROR_NUMBER_SEV_ID as TRANS_ER2_0_ FROM TRANSACTION_ERROR_NUMBER transact0_ WHERE transact0_.TRANS_ERROR_NUMBER = ?
227515 [1744] ERROR NHibernate.ADOException - While preparing SELECT transact0_.TRANS_ERROR_NUMBER as TRANS_ER1_0_, transact0_.TRANS_ERROR_NUMBER_SEV_ID as TRANS_ER2_0_ FROM TRANSACTION_ERROR_NUMBER transact0_ WHERE transact0_.TRANS_ERROR_NUMBER = ? an error occurred
Exception: System.InvalidOperationException
Message: OleDbCommand.Prepare method requires all variable length parameters to have an explicitly set non-zero Size.
Source: System.Data
   at System.Data.OleDb.OleDbParameter.Prepare(OleDbCommand cmd)
   at System.Data.OleDb.OleDbCommand.PrepareCommandText(Int32 expectedExecutionCount)
   at System.Data.OleDb.OleDbCommand.Prepare()
   at NHibernate.Impl.BatcherImpl.Prepare(IDbCommand command)

227562 [1744] DEBUG NHibernate.Impl.BatcherImpl - done closing: 0 open IDbCommands, 0 open DataReaders
227578 [1744] ERROR NHibernate.ADOException - could not load object
Exception: NHibernate.ADOException
Message: While preparing SELECT transact0_.TRANS_ERROR_NUMBER as TRANS_ER1_0_, transact0_.TRANS_ERROR_NUMBER_SEV_ID as TRANS_ER2_0_ FROM TRANSACTION_ERROR_NUMBER transact0_ WHERE transact0_.TRANS_ERROR_NUMBER = ? an error occurred
Source: NHibernate
   at NHibernate.Impl.BatcherImpl.Prepare(IDbCommand command)
   at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
   at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
   at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
   at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
   at NHibernate.Loader.Loader.LoadEntity(ISessionImplementor session, Object[] values, IType[] types, Object optionalObject, Object optionalID)
   at NHibernate.Loader.Loader.LoadEntity(ISessionImplementor session, Object id, IType identifierType, Object optionalObject, Object optionalIdentifier)
   at NHibernate.Loader.EntityLoader.Load(ISessionImplementor session, Object id, Object optionalObject, Object optionalId)
   at NHibernate.Loader.EntityLoader.Load(ISessionImplementor session, Object id, Object optionalObject)
   at NHibernate.Persister.EntityPersister.Load(Object id, Object optionalObject, LockMode lockMode, ISessionImplementor session)
   at NHibernate.Impl.SessionImpl.DoLoad(Type theClass, Object id, Object optionalObject, LockMode lockMode, Boolean checkDeleted)

Nested Exception

(... truncated ...)

Yet, I could not find any combination of setting that would make this work...

Any ideas on why this happen and how to fix it would be appreciated.

Thanks in advance...

Jumpin'Jack Flash · **Joined:** Mon Jun 06, 2005 3:36 pm **Posts:** 5

I took the time to toy around with this problem. I had another similar one in another part of my application...

The TransactionErrorNumber class is a parent class that is fetched when I do a session.CreateQuery from a child that access it as a look-up value.

As I told, maybe it's my understanding that's not quite up to par, but when I fetch all the parents before doing my session.CreateQuery for the children, all work fine...

Since I switched the driver, I thougt it might be the hibernate.use_outer_join that might be disabled, for some reasons. But after enabling it it still produce the error when I don't fetch all the parents first.

Do I need to specify outer join when I perform the session.CreateQuery on the children side?

CZahrobsky · **Posted:** Wed Sep 24, 2008 1:36 am

FYI,

I'm not sure if this is related to the OleDb Oracle issue, but I got the same error. I had problems saving to my Microsoft Access (Jet OleDB) database until I set the prepare_sql property to false:

Quote:

<property name="hibernate.prepare_sql">false</property>

It had problems on the transaction.Commit(); command. The error I received was:

2008-09-23 09:57:49,951 [13] NHibernate.Impl.SessionImpl ERROR - could not synchronize database state with session

NHibernate.ADOException: While preparing INSERT INTO ...
at System.Data.OleDb.OleDbParameter.Prepare(OleDbCommand cmd)
at System.Data.OleDb.OleDbCommand.PrepareCommandText(Int32 expectedExecutionCount)
at System.Data.OleDb.OleDbCommand.Prepare()
at NHibernate.JetDriver.JetDbCommand.Prepare()
at NHibernate.Driver.DriverBase.PrepareCommand(IDbCommand command)
at NHibernate.Impl.BatcherImpl.Prepare(IDbCommand cmd)
--- End of inner exception stack trace ---
at NHibernate.Impl.BatcherImpl.Prepare(IDbCommand cmd)
at NHibernate.Impl.BatcherImpl.ExecuteNonQuery(IDbCommand cmd)
at NHibernate.Impl.NonBatchingBatcher.AddToBatch(IExpectation expectation)
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Object obj, ISessionImplementor session)
at NHibernate.Impl.ScheduledInsertion.Execute()
at NHibernate.Impl.SessionImpl.Execute(IExecutable executable)
at NHibernate.Impl.SessionImpl.ExecuteAll(IList list)
at NHibernate.Impl.SessionImpl.Execute()

+ InnerException {"OleDbCommand.Prepare method requires all variable length parameters to have an explicitly set non-zero Size."} System.Exception {System.InvalidOperationException}

Corrected, the full data configuration I now use is:

Code:

<session-factory>
  <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property> 
  <property name="hibernate.dialect">NHibernate.JetDriver.JetDialect, NHibernate.JetDriver</property> 
  <property name="connection.driver_class">NHibernate.JetDriver.JetDriver, NHibernate.JetDriver</property> 
  <property name="connection.connection_string">
     Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Local\DB\MyData.mdb;
   </property> 
  <property name="hibernate.use_reflection_optimizer">true</property> 
  <property name="hibernate.prepare_sql">false</property> 
  <property name="show_sql">true</property> 
</session-factory>

This may be useful to other Hibernate / NHibernate users having trouble saving to an OleDb data source.