Hibernate Community • View topic - Guid to RAW(16) on Oracle

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]

Guid to RAW(16) on Oracle

Page 1 of 2

[ 25 posts ]

Go to page 1, 2 Next

Previous topic | Next topic

Author

Message

alfred300p

Post subject: Guid to RAW(16) on Oracle

Posted: Wed Mar 26, 2008 11:06 am

Newbie

Joined: Wed Mar 26, 2008 10:38 am
Posts: 4

Hi,

I've been trying to map an Oracle RAW(16) primary key to a System.Guid. Gauthier's post on the subject has pointed in the right direction, but I still can't get it to work.

Before changing anything in NH's source, I got an ArgumentOutOfRangeException when OracleParameter.DbType was set to DbType.Guid, probably because Oracle doesn't support Guids natively. Implementing the SqlTypes() as

Code:

SqlType[] IType.SqlTypes(NHibernate.Engine.IMapping mapping) { return new SqlType[] { new SqlType(DbType.Binary, 16) }; } 

solved this problem, but now the CurrentSession.Get() simply returns a null value. Overriding GuidType.Set() to return the guid as an array of bytes or as its string representation also solves nothing. Can anybody shed a light on the subject?

Also, is there a way to determine the dialect being used from inside the GuidType.SqlTypes(), and return DbType.Binary for Oracle and DbType.Guid for other dialects?

Thanks

Top

tgilkison

Post subject: Oracle Raw(16) System.Guid Does Not Work

Posted: Sat Mar 29, 2008 3:24 pm

Newbie

Joined: Sat Mar 29, 2008 2:57 pm
Posts: 2

We've spent all day on this one and it is still not resolved.

Release Notes
Build 1.2.0.GA
* [NH-985] - Map DbType.Guid to CHAR(38) for Oracle

If I look in Nhibernate\Dialect\Oracle9Dialect.cs I can see that this was commented out.

We can get it working with regular SQL statements, but nobody seems to be able to give a technical answer as to how NHibernate works so we can fix it in NHibernate.

Here's how the SQL works:

Oracle
Create a RAW(16) column in Oracle.
Create a new Guid and take out the dashes with guid.ToString("N");
Insert the string into your SQL statement surrounded with single quotes
INSERT INTO MY_TABLE (ID) VALUES ('57acf9d9637c48cab14f863a369110c3')

SQL 2005
Create a UniqueIdentifier column in SQL 2005
Create a new Guid
Convert the Guid to a String, but do not change the format
INSERT INTO MY_TABLE (ID) VALUES ('57acf9d9-637c-48ca-b14f-863a369110c3')

The Guid is treated like a string in both Oracle and SQL, the difference being the format of the Guid. If you want to convert the Guid String back to a Guid, you have to convert it to a byte[] first.

Please fix this issue.

- Tom

Top

alfred300p

Post subject:

Posted: Mon Mar 31, 2008 4:05 am

Newbie

Joined: Wed Mar 26, 2008 10:38 am
Posts: 4

The problem was "solved" with 4 steps:
NHibernate\Dialect\Oracle9Dialect.cs (56)

Code:

RegisterColumnType(DbType.Guid, "RAW(16)");

NHibernate\Type\GuidType.cs (71)

Code:

SqlType[] IType.SqlTypes(NHibernate.Engine.IMapping mapping)
{
    return new SqlType[] { new SqlType(DbType.Binary, 16) };
}

NHibernate\Type\GuidType.cs (58)

Code:

public override void Set(IDbCommand cmd, object value, int index)
{
    IDataParameter parm = cmd.Parameters[index] as IDataParameter;
    bool oracle = (cmd.GetType().FullName == "Oracle.DataAccess.Client.OracleCommand");
    parm.Value = oracle ? ((Guid)value).ToByteArray() : value;
}

NHibernate\Type\GuidType.cs (26)

Code:

public override object Get(IDataReader rs, int index) { return GetFromValue(rs[index]); }
public override object Get(IDataReader rs, string name) { return GetFromValue(rs[name]); }
private object GetFromValue(object value)
{
    System.Type type = value.GetType();
    if (type == typeof(string)) return new Guid((string)value);
    if (type == typeof(Guid)) return value;
    if (type == typeof(byte[])) return new Guid((byte[])value);
    return null;
}

Except now it only works in Oracle, because on SqlType[] IType.SqlTypes() it's not possible to determine the dialect being used. And, I need a different dll for Oracle, which is extremely ugly...

tgilkison wrote:

The Guid is treated like a string in both Oracle and SQL, the difference being the format of the Guid. If you want to convert the Guid String back to a Guid, you have to convert it to a byte[] first.

It's actually not treated like a string... MsSql expects a Guid and if you pass a string it tries to parse it; Oracle expects an array of bytes, since it has no native Guid, and also tries to parse the given string. I think, though, the same string (hyphen-stripped or not) won't produce the same Guid (or byte array) on both databases (see this).

Top

mhanney

Post subject:

Posted: Sun Apr 13, 2008 12:56 am

Newbie

Joined: Fri Mar 28, 2008 2:11 pm
Posts: 7
Location: Seattle

alfred300p wrote:

tgilkison wrote:

The Guid is treated like a string in both Oracle and SQL, the difference being the format of the Guid. If you want to convert the Guid String back to a Guid, you have to convert it to a byte[] first.

See the order of bytes in an Oracle generated SYS_GUID() compared to the order of bytes in a MSSQL Guid, and Guid.ToByteArray[] in here http://michaelhanney.com/blog/2008/04/12/nhibernate-oracle-guid-as-primary-key-mapping-custom-iusertype/

Top

alfred300p

Post subject:

Posted: Mon Apr 14, 2008 5:41 am

Newbie

Joined: Wed Mar 26, 2008 10:38 am
Posts: 4

mhanney wrote:

Very useful. Thanks.

Top

Nels_P_Olsen

Post subject:

Posted: Mon Apr 14, 2008 10:59 am

Expert

Joined: Fri Oct 28, 2005 5:38 pm
Posts: 390
Location: Cedarburg, WI

Looks great! ... except ... it's still Oracle-specific. We need to use mapping types that can be used with any supported database.

Can this custom type be enhanced so that it does Oracle-specific logic if the database is Oracle, otherwise do the standard Guid type behavior? Is it even possible to detect the current value of connection.driver_class from a class implementing IUserType?

Top

tgilkison

Post subject:

Posted: Mon Apr 21, 2008 3:03 pm

Newbie

Joined: Sat Mar 29, 2008 2:57 pm
Posts: 2

We are collecting some good information in this thread, but I still don't see a response from someone developing NHibernate. There must be someone with greater knowledge of the framework that can resolve this issue appropriately. DLL swapping defeats the purpose of the ORM. It also doesn't make sense to me that this is even an issue that is difficult to resolve.

Top

alfred300p

Post subject:

Posted: Tue Apr 22, 2008 4:03 am

Newbie

Joined: Wed Mar 26, 2008 10:38 am
Posts: 4

Quote:

It also doesn't make sense to me that this is even an issue that is difficult to resolve.

Yeah, it appears pretty simple at first sight... =)

I end up answering my own question, though after a lot of trial and error. This solution corrects the issue on Oracle only, not affecting other databases.

On NHibernate\Dialect\Oracle9Dialect.cs:

Code:

RegisterColumnType(DbType.Guid, "RAW(16)");

On NHibernate\Type\GuidType.cs:

Code:

private object GetValue(object value)
{
   if (value is Guid) return value;
   else return new Guid((byte[])value);
}
 
public override object Get(IDataReader rs, int index)
{
   return GetValue(rs[index]);
}
 
public override object Get(IDataReader rs, string name)
{
   return GetValue(rs[name]);
}

On NHibernate\Driver\OracleDataClientDriver.cs:

Code:

protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlType sqlType)
{
   // convert the types not supported by ODP.NET
   switch (sqlType.DbType)
   {
      case DbType.Boolean:
         sqlType = SqlTypeFactory.Int16; 
         break;
      case DbType.Guid:
         sqlType = new SqlType(DbType.Binary,16);
         break;
   }
   base.InitializeParameter(dbParam, name, sqlType);
}

Though it works, it's still plain ugly, so NH devs: please, map Guids to raw(16) on the next version... Pretty please?

Top

jstelly

Post subject:

Posted: Tue Apr 22, 2008 3:25 pm

Beginner

Joined: Fri Jul 22, 2005 4:08 pm
Posts: 28

It would be fantastic to get an issue opened for this and have a 1.2.1 patch provided. I've got to support SQL Server and Oracle and it's a royal pain right now.

Top

bigAPE

Post subject:

Posted: Wed Jul 30, 2008 9:28 pm

Newbie

Joined: Wed Jul 30, 2008 8:47 pm
Posts: 11
Location: Merlbourne, Australia

Hi Guys,

I am after exactly the same thing, a way to select Oracle or MSSQL as the backend without having to swap out DLL's, etc, just by changing the nhibernate-configuration XML.

I am testing this with a real simple table that uses a GUID as a PK. I have MSSQL2005 and OracleXE(10g) running and I have two hibernate.cfg.xml files which I swap around, one targeting SQL2005 and one targeting Oracle

Oracle Setup & Config

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2" >
  <session-factory>
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="connection.driver_class">NHibernate.Driver.OracleDataClientDriver</property>
    <property name="connection.connection_string">Data source=localhost/XE;User Id=SYSTEM;Password=MANAGER;</property>
    <property name="show_sql">true</property>
    <property name="dialect">NHibernate.Dialect.Oracle9Dialect</property>
    <mapping assembly="TaskManager.DataTransfer"/>
  </session-factory>
</hibernate-configuration>

Code:

CREATE TABLE "SYSTEM"."TASKS_TASK" 
(  
  "TASKID" RAW(16) NOT NULL ENABLE, 
  "VERSION" NUMBER(10,0) NOT NULL ENABLE, 
  "TASKREF" NVARCHAR2(10) NOT NULL ENABLE, 
  "NAME" NVARCHAR2(100) NOT NULL ENABLE, 
  "DESCRIPTION" NVARCHAR2(255) NOT NULL ENABLE, 
  PRIMARY KEY ("TASKID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
  )
  TABLESPACE "SYSTEM"  ENABLE
) 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE
(
  INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "SYSTEM" ;

SQL2005 Setup & Config

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2" >
  <session-factory>
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
    <property name="connection.connection_string">Data Source=localhost;Initial Catalog=TaskManager;Integrated Security=True</property>
    <property name="show_sql">true</property>
    <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
    <mapping assembly="TaskManager.DataTransfer"/>
  </session-factory>
</hibernate-configuration>

Code:

CREATE TABLE [dbo].[tasks_Task](
   [TaskId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tasks_Task_TaskId]  DEFAULT (newid()),
   [TaskRef] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   [Name] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   [Description] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   [Version] [int] NOT NULL CONSTRAINT [DF_tasks_Task_Version]  DEFAULT ((1)),
PRIMARY KEY CLUSTERED 
(
   [TaskId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

HBM for Task class

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="TaskManager.DataTransfer" namespace="TaskManager.DataTransfer">
  <class name="TaskManager.DataTransfer.Task,TaskManager.DataTransfer" table="tasks_Task">
    <id name="TaskId" column="TaskId" type="Guid">
        <generator class="guid.comb"/>
    </id>
    <version name="Version" column="Version" type="integer" unsaved-value="0"/>
    <property name="TaskRef" column="TaskRef" type="string" length="10" not-null="true"/>
    <property name="Name" column="Name" type="string" length="100" not-null="true"/>
    <property name="Description" column="Description" type="string" length="255" not-null="true"/>
  </class>
</hibernate-mapping>

and the code being tested

Code:

public Guid AddTask(Task task)
{
    using (ISession session = GetSession())
    {
        using (ITransaction tx = session.BeginTransaction())
        {
            try
            {
                Guid newId = (Guid)session.Save(task);
                session.Flush();
                tx.Commit();
                return newId;
            }
            catch (NHibernate.HibernateException)
            {
                tx.Rollback();
                throw;
            }
        }
    }
}

Source Code Changes

I have implemented the changes to the NHibernate-2.1.0.Alpha1 source as shown by alfred300p on April 22nd.

Failure on Oracle

Using the SQL2005 configuration it works seemlessly. However with the Oracle configuration I am getting an "Invalid Parameter Binding" error

Code:

TestCase 'NHibernateDataProviderTest.AddTask' failed: Invalid parameter binding
Parameter name: :p4
   System.ArgumentException
   Message: Invalid parameter binding
   Parameter name: :p4
   Source: Oracle.DataAccess
   StackTrace:
   at Oracle.DataAccess.Client.OracleParameter.GetBindingSize_Raw(Int32 idx)
   at Oracle.DataAccess.Client.OracleParameter.PreBind_Raw()
   at Oracle.DataAccess.Client.OracleParameter.PreBind(OracleConnection conn, IntPtr errCtx, Int32 arraySize)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
   c:\Dev\BPII\TaskManager\Tools\nhibernate-source\src\NHibernate\AdoNet\AbstractBatcher.cs(196,0): at NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd)
   c:\Dev\BPII\TaskManager\Tools\nhibernate-source\src\NHibernate\AdoNet\NonBatchingBatcher.cs(39,0): at NHibernate.AdoNet.NonBatchingBatcher.AddToBatch(IExpectation expectation)
   c:\Dev\BPII\TaskManager\Tools\nhibernate-source\src\NHibernate\Persister\Entity\AbstractEntityPersister.cs(2588,0): at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
   c:\Dev\BPII\TaskManager\Tools\nhibernate-source\src\NHibernate\Persister\Entity\AbstractEntityPersister.cs(2935,0): at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Object obj, ISessionImplementor session)
   c:\Dev\BPII\TaskManager\Tools\nhibernate-source\src\NHibernate\Action\EntityInsertAction.cs(51,0): at NHibernate.Action.EntityInsertAction.Execute()
   c:\Dev\BPII\TaskManager\Tools\nhibernate-source\src\NHibernate\Engine\ActionQueue.cs(130,0): at NHibernate.Engine.ActionQueue.Execute(IExecutable executable)
   c:\Dev\BPII\TaskManager\Tools\nhibernate-source\src\NHibernate\Engine\ActionQueue.cs(113,0): at NHibernate.Engine.ActionQueue.ExecuteActions(IList list)
   c:\Dev\BPII\TaskManager\Tools\nhibernate-source\src\NHibernate\Engine\ActionQueue.cs(146,0): at NHibernate.Engine.ActionQueue.ExecuteActions()
   c:\Dev\BPII\TaskManager\Tools\nhibernate-source\src\NHibernate\Event\Default\AbstractFlushingEventListener.cs(241,0): at NHibernate.Event.Default.AbstractFlushingEventListener.PerformExecutions(IEventSource session)
   c:\Dev\BPII\TaskManager\Tools\nhibernate-source\src\NHibernate\Event\Default\DefaultFlushEventListener.cs(19,0): at NHibernate.Event.Default.DefaultFlushEventListener.OnFlush(FlushEvent event)
   c:\Dev\BPII\TaskManager\Tools\nhibernate-source\src\NHibernate\Impl\SessionImpl.cs(1182,0): at NHibernate.Impl.SessionImpl.Flush()
   C:\Dev\BPII\TaskManager\Proto\TaskManager\DataAccessLayer\NHibernateDataProvider.cs(43,0): at TaskManager.DataAccessLayer.NHibernateDataProvider.AddTask(Task task)
   C:\Dev\BPII\TaskManager\Proto\TaskManager\DataAccessLayerTest\NHibernateDataProviderTest.cs(31,0): at TaskManager.DataAccessLayerTest.NHibernateDataProviderTest.AddTask()

I take it you didn't get this on your tests ?

Any help or advice greatly received

Al

Top

bigAPE

Post subject:

Posted: Thu Jul 31, 2008 10:04 pm

Newbie

Joined: Wed Jul 30, 2008 8:47 pm
Posts: 11
Location: Merlbourne, Australia

Here is the Log4Net output for the test I am running

Quote:

2008-08-01 11:54:04,328 DEBUG opened session at timestamp: 633531884443
2008-08-01 11:54:04,343 DEBUG Begin (Unspecified)
2008-08-01 11:54:04,343 DEBUG Obtaining IDbConnection from Driver
2008-08-01 11:54:04,468 DEBUG saving transient instance
2008-08-01 11:54:04,468 DEBUG generated identifier: 62d96510-55a4-44c0-98be-9aeb00c4205d, using strategy: NHibernate.Id.GuidCombGenerator
2008-08-01 11:54:04,468 DEBUG saving [TaskManager.DataTransfer.Task#62d96510-55a4-44c0-98be-9aeb00c4205d]
2008-08-01 11:54:04,484 DEBUG unsaved-value: 0
2008-08-01 11:54:04,484 DEBUG Seeding: 1
2008-08-01 11:54:04,484 DEBUG flushing session
2008-08-01 11:54:04,484 DEBUG processing flush-time cascades
2008-08-01 11:54:04,500 DEBUG dirty checking collections
2008-08-01 11:54:04,500 DEBUG Flushing entities and processing referenced collections
2008-08-01 11:54:04,500 DEBUG Processing unreferenced collections
2008-08-01 11:54:04,500 DEBUG Scheduling collection removes/(re)creates/updates
2008-08-01 11:54:04,500 DEBUG Flushed: 1 insertions, 0 updates, 0 deletions to 1 objects
2008-08-01 11:54:04,500 DEBUG Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
2008-08-01 11:54:04,515 DEBUG listing entities:
2008-08-01 11:54:04,515 DEBUG TaskManager.DataTransfer.Task{Version=1, TaskRef=1.0, Name=Oracle Task, Description=Oracle Task Description, TaskId=62d96510-55a4-44c0-98be-9aeb00c4205d}
2008-08-01 11:54:04,515 DEBUG executing flush
2008-08-01 11:54:04,515 DEBUG registering flush begin
2008-08-01 11:54:04,515 DEBUG Inserting entity: [TaskManager.DataTransfer.Task#62d96510-55a4-44c0-98be-9aeb00c4205d]
2008-08-01 11:54:04,515 DEBUG Version: 1
2008-08-01 11:54:04,531 DEBUG Opened new IDbCommand, open IDbCommands: 1
2008-08-01 11:54:04,531 DEBUG Building an IDbCommand object for the SqlString: INSERT INTO tasks_Task (Version, TaskRef, Name, Description, TaskId) VALUES (?, ?, ?, ?, ?)
2008-08-01 11:54:04,531 DEBUG Dehydrating entity: [TaskManager.DataTransfer.Task#62d96510-55a4-44c0-98be-9aeb00c4205d]
2008-08-01 11:54:04,531 DEBUG binding '1' to parameter: 0
2008-08-01 11:54:04,531 DEBUG binding '1.0' to parameter: 1
2008-08-01 11:54:04,531 DEBUG binding 'Oracle Task' to parameter: 2
2008-08-01 11:54:04,531 DEBUG binding 'Oracle Task Description' to parameter: 3
2008-08-01 11:54:04,531 DEBUG binding '62d96510-55a4-44c0-98be-9aeb00c4205d' to parameter: 4
2008-08-01 11:54:04,546 DEBUG INSERT INTO tasks_Task (Version, TaskRef, Name, Description, TaskId) VALUES (:p0, :p1, :p2, :p3, :p4); :p0 = '1', :p1 = '1.0', :p2 = 'Oracle Task', :p3 = 'Oracle Task Description', :p4 = '62d96510-55a4-44c0-98be-9aeb00c4205d'
2008-08-01 11:54:04,546 DEBUG Enlist Command
2008-08-01 11:54:04,656 DEBUG Closed IDbCommand, open IDbCommands: 0
2008-08-01 11:54:04,656 DEBUG registering flush end
2008-08-01 11:54:04,656 DEBUG IDbTransaction disposed.
2008-08-01 11:54:04,656 DEBUG transaction completion
2008-08-01 11:54:04,656 DEBUG aggressively releasing database connection
2008-08-01 11:54:04,671 DEBUG Closing connection
2008-08-01 11:54:04,687 DEBUG running ISession.Dispose()
2008-08-01 11:54:04,687 DEBUG closing session
2008-08-01 11:54:04,687 DEBUG running BatcherImpl.Dispose(true)

The thing that is concerning me is this

Code:

INSERT INTO tasks_Task (Version, TaskRef, Name, Description, TaskId) VALUES (:p0, :p1, :p2, :p3, :p4); :p0 = '1', :p1 = '1.0', :p2 = 'Oracle Task', :p3 = 'Oracle Task Description', :p4 = '62d96510-55a4-44c0-98be-9aeb00c4205d'

I was under the impression that to successfully pass a .NET Guid to Oracle RAW(16) you had to convert the Guid from "62d96510-55a4-44c0-98be-9aeb00c4205d" to "62d9651055a444c098be9aeb00c4205d" by using the following

Write to Oracle: guid.ToString("N")

read from Oracle: new Guid( new SoapHexBinary( (byte[])oracle Guid).ToString() )

If so, wouldn't we have to put this into the GuidType.cs somehow ?

Al

Top

bigAPE

Post subject:

Posted: Thu Jul 31, 2008 10:35 pm

Newbie

Joined: Wed Jul 30, 2008 8:47 pm
Posts: 11
Location: Merlbourne, Australia

OK, I've hacked it until its working in Oracle with a further change to the GuidType.cs after reading some of the blogs out there about this issue

Code:

public override void Set(IDbCommand cmd, object value, int index)
{
    ((IDataParameter)cmd.Parameters[index]).Value = 
            System.Runtime.Remoting.Metadata.W3cXsd2001.SoapHexBinary.Parse( ( (Guid)value ).ToString("N") ).Value;
}

However this will only work when using it with Oracle. If you run this with MSSQL it will fail as Guid is now setting a Byte Array

Log from INSERT test

Quote:

2008-08-01 12:23:13,546 DEBUG Dehydrating entity: [TaskManager.DataTransfer.Task#b1475f7e-5548-4c2b-9b7e-9aeb00cc21f9]
2008-08-01 12:23:13,546 DEBUG binding '1' to parameter: 0
2008-08-01 12:23:13,546 DEBUG binding '1.0' to parameter: 1
2008-08-01 12:23:13,546 DEBUG binding 'Oracle Task' to parameter: 2
2008-08-01 12:23:13,546 DEBUG binding 'Oracle Task Description' to parameter: 3
2008-08-01 12:23:13,546 DEBUG binding 'b1475f7e-5548-4c2b-9b7e-9aeb00cc21f9' to parameter: 4
2008-08-01 12:23:13,546 DEBUG INSERT INTO tasks_Task (Version, TaskRef, Name, Description, TaskId) VALUES (:p0, :p1, :p2, :p3, :p4); :p0 = '1', :p1 = '1.0', :p2 = 'Oracle Task', :p3 = 'Oracle Task Description', :p4 = 'System.Byte[]'

Log from SELECT test

Quote:

2008-08-01 12:25:26,265 DEBUG loading entity: [TaskManager.DataTransfer.Task#b1475f7e-5548-4c2b-9b7e-9aeb00cc21f9]
2008-08-01 12:25:26,312 DEBUG Opened new IDbCommand, open IDbCommands: 1
2008-08-01 12:25:26,312 DEBUG Building an IDbCommand object for the SqlString: SELECT task0_.TaskId as TaskId0_0_, task0_.Version as Version0_0_, task0_.TaskRef as TaskRef0_0_, task0_.Name as Name0_0_, task0_.Description as Descript5_0_0_ FROM tasks_Task task0_ WHERE task0_.TaskId=?
2008-08-01 12:25:26,312 DEBUG binding 'b1475f7e-5548-4c2b-9b7e-9aeb00cc21f9' to parameter: 0
2008-08-01 12:25:26,312 INFO SELECT task0_.TaskId as TaskId0_0_, task0_.Version as Version0_0_, task0_.TaskRef as TaskRef0_0_, task0_.Name as Name0_0_, task0_.Description as Descript5_0_0_ FROM tasks_Task task0_ WHERE task0_.TaskId=:p0
2008-08-01 12:25:26,312 DEBUG SELECT task0_.TaskId as TaskId0_0_, task0_.Version as Version0_0_, task0_.TaskRef as TaskRef0_0_, task0_.Name as Name0_0_, task0_.Description as Descript5_0_0_ FROM tasks_Task task0_ WHERE task0_.TaskId=:p0; :p0 = 'System.Byte[]'
2008-08-01 12:25:26,312 DEBUG Obtaining IDbConnection from Driver
2008-08-01 12:25:26,453 DEBUG Opened IDataReader, open IDataReaders: 1
2008-08-01 12:25:26,453 DEBUG processing result set

Question is how do we now implement this elegantly so that the Set operation works seamlessly between Oracle and MSSQL, etc ?

Al

Top

mhanney

Post subject:

Posted: Mon Aug 04, 2008 1:40 pm

Newbie

Joined: Fri Mar 28, 2008 2:11 pm
Posts: 7
Location: Seattle

Hi Al,

Looks like you are the current Oracle Guid patch developer. Great work. I would like to help if I can. I may have some hours this week to assist with testing if that helps.

Using SoapHexBinary to convert the byte order of a .NET generated Guid is only necessary if the Guid stored in the DB needs to be the same byte order as an Oracle generated GUID, like those that Oracle's SYS_GUID() function produces.

A question to all NH Oracle users, how important is it for NH generated Guid to be compatible with Oracle's SYS_GUID() implementation?

It could be argued, not at all - because in the context of .NET, a Guid is the Microsoft implementation of UUID, and not Oracle's. Of the top of my head I am not certain, but isn't the guid.comb generator in NH producing a Microsoft Guid? If so, why not just persist it that way and keep the Microsoft byte order?

How does the Java version of Hibernate do it?

Mike.

Top

mhanney

Post subject:

Posted: Tue Aug 05, 2008 3:01 am

Newbie

Joined: Fri Mar 28, 2008 2:11 pm
Posts: 7
Location: Seattle

I can't see an elegant solution to this, but I have a solution/suggestion that might be acceptable. It uses a lot of ideas already suggested. I am no longer concerned about byte order of the GUID.

I'm using the trunk (revision 3694)

in Oracle9Dialect.cs

Code:

RegisterColumnType(DbType.Guid, "RAW(16)");

in GuidType.cs

Code:

public override object Get(IDataReader rs, int index)
{
    if (rs.GetFieldType(index) == typeof(Guid))
    {
        return rs.GetGuid(index);
    }
    
    if(rs.GetFieldType(index) == typeof(byte[]))
    {
        return new Guid((byte[])(rs[index]));
    }

    return new Guid(Convert.ToString(rs[index]));
}

public override void Set(IDbCommand cmd, object value, int index)
{
    if (value.GetType() == typeof(byte[]))
    {
        ((IDataParameter)cmd.Parameters[index]).Value =
            new Guid(value.ToString()).ToByteArray();
    }
    else
    {
        ((IDataParameter)cmd.Parameters[index]).Value = value;
    }
}

in OracleDataClientDriver.cs

Code:

protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlType sqlType)
{
    // if the parameter coming in contains a boolean then we need to convert it 
    // to another type since ODP.NET doesn't support DbType.Boolean or DbType.Guid
    switch (sqlType.DbType)
    {
        case (DbType.Boolean):
            sqlType = SqlTypeFactory.Int16;
            break;

        case (DbType.Guid):
            sqlType = SqlTypeFactory.GetBinary(16);
            break;
    }

    base.InitializeParameter(dbParam, name, sqlType);
}

The tests pass on 10gR2 on Windows Server 2003.

Top

bigAPE

Post subject:

Posted: Tue Aug 05, 2008 3:01 am

Newbie

Joined: Wed Jul 30, 2008 8:47 pm
Posts: 11
Location: Merlbourne, Australia

mhanney wrote:

Looks like you are the current Oracle Guid patch developer. Great work.

Jesus, I wouldn't go that far!

The biggest stumbling block I have is how to alter the Set operation in the GuidType.cs so that it works seamlessly with both MSSQL and Oracle ! I can't see any flags in the code to say

Code:

  IF ORACLE THEN
        ... Set it this way
  IF OTHERS THEN
        ... Set it this way

If I could then I would give the Patching thing a go.

Al

Top

Page 1 of 2

[ 25 posts ]

Go to page 1, 2 Next

Board index » Hibernate & Java Persistence » NHibernate Users

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