Map GUID with Oracle 10g

[Magdas Adrian]

Hi, does anyone know how to map a GUID as primary key with Oracle 10g?

Thanks,
Adrian

[hoghweed]

Hi,
What you mean with "mapping a guid with oracle 10g"?? what kind of
column you have?
I've found a solution with Oracle RAW type mapped as primary key, but
I have to know what kind of problem you have.
Looking outside in the Net, i haven't found any clear solution with
this problem, but only 2:
- writing a custom type to handle the column
- modify the oracle dialect to "natively" handle the column type.

While the second solution is not easy and the best one, may it could
be better to create a custom dialect which inherits from teh Oracle
ones, the first solution for me was the better because:
- I've implemented a better solution based on my app
- I haven't broken NHibernate code with the official distribution

If you want to view my code please send me and email, or I think I'll
post on my blog my poor solution.
Ciao
Manuel

[Magdas Adrian]

Hi, sorry for being so vague...
I tried to follow this example http://blogs.hibernatingrhinos.com/nhibernate/archive/2008/04/01/your-first-nhibernate-based-application.aspx
and in it it uses a System.Guid as  primary key. As a have installed  Oracle  10g  server, I modified the hibernate configuration file, and run the tests but it failed because it could not map Guid.
As this is just for studying I changed the Id to Int32 type, but I would like to hear a solution to map a Guid as primary key through NHibernate using Oracle 10g server :)

Thanks for the reply :)
Adrian.

[Fabio Maulo]

Do ORACLE, or it's DataProvider, support GUID ?
If yes please create a JIRA with the ORACLE type.

If no:
    public class MyOracleDialect : OracleDialect
    {
        /// <summary></summary>
        public MyOracleDialect () : base()
        {
           RegisterColumnType(DbType.Guid, "CHAR(38)");
        }
    }
Then configure the session-factory according.

Bye.
Fabio Maulo

[Fabio Maulo]

I forgot something....
I'm not sure that the work-around solution work correctly because it is dependent on how the Oracle .NET2.0 DataProvider interpret the GUID when you use it like parameter.

Bye.
Fabio Maulo.

[hoghweed]

these solutions are optimal but you have to see what kind of GUID type
you want to handle,
in general, it can be one of the followings:
- Guid type
- RAW type
- string represented Guid type

In my situation, the RAW type was the data type used as primary key in
Oracle 10g Server.
You probably know that oracle can use the RAW Type to map a Guid in a
Binary manner,
usually this kind of data type is used in .NET natively as a Binary
array, it's clear that it depends by the
used provider. Oracle ADO.NET provider retrieve this data type as
binary array, but if it's needed to be used as a string,
it's necessary to convert it from and back to RAW with the built-in
functions HEXTORAW and RAWTOHEX.

tell me if this kind of solution could help yo, so if yes I will post
my code to map a RAW data type with a custom NH type!
Manuel

On Apr 7, 3:14 pm, "Magdas Adrian" <magdasadr...@gmail.com> wrote:
> Hi, sorry for being so vague...

> I tried to follow this examplehttp://blogs.hibernatingrhinos.com/nhibernate/archive/2008/04/01/your...

> > > Adrian- Hide quoted text -
>
> - Show quoted text -

[Magdas Adrian]

Hi,
This seems to be what I search, so can you post your solution?

Thanks,
Adrian.

[hoghweed]

Hi,
I'm sorry I cannot yet post my solution, I'm going at home, this
evening or tomorrow morning I will post my solution to my blog
Bye

On Apr 7, 5:26 pm, "Magdas Adrian" <magdasadr...@gmail.com> wrote:
> Hi,

> > > - Show quoted text -- Hide quoted text -

[mhanney]

Hello,

I am interested in this thread and would like to offer the following
description of how I am using Guid with Oracle 10g, .Net 2.0,
NHibernate 1.2.1 GA in case it helps you.

I have a RAW(32) column mapped using type="Byte[]" length="32". I
have been told I could be using type="Byte[]" length="16", RAW(16)
instead, but length 32 is working for me and I have not had time to
try length 16 yet. I have tested this on Oracle 10g on 32 and 64 bit
windows servers and Oracle 10g on Linux (CentOS 4.4 32 bit).

I often need the byte array as a string so I use
System.Runtime.Remoting.Metadata.W3cXsd2001 to do the conversions in
C#. (I have not tried using Oracle's HEXTORAW and RAWTOHEX.)

In C# I have:

// to convert byte array to string using .Net native methods of
SoapHexBinary class
using System.Runtime.Remoting.Metadata.W3cXsd2001;

private byte[] _guid = null;
private string _guidString = String.Empty;

public byte[] GUID
{
get
{
if (_guid == null)
{
_guid = Guid.NewGuid().ToByteArray();
}
return _guid;
}
set { _guid = value; }
}


public string GUIDString
{
get
{
if (String.IsNullOrEmpty(_guidString))
{
_guidString = new SoapHexBinary(_guid).ToString();
}

return _guidString;
}
}

To convert from string to byte array I use

SoapHexBinary.Parse(_guidString).Value;

I must point out I am not using _guid as primary key. If you use _guid
as primary key I think you will need to override Equals and implement
a way to compare byte[] arrays, perhaps by converting to string first.

I always generate the GUID in the C# application because the
implementation of Oracle's SYS_GUID on 10g on some AIX and Linux
systems does not work as expected - it returns a constant! See
http://feuerthoughts.blogspot.com/2006/02/watch-out-for-sequential-oracle-guids.html.

Hope this helps.

Michael Hanney.

On Apr 7, 8:26 am, "Magdas Adrian" <magdasadr...@gmail.com> wrote:
> Hi,

> This seems to be what I search, so can you post your solution?
>
> Thanks,
> Adrian.
>

[hoghweed]

Hi, I'm really sorry, I can't post before on my blog, I found some
troubles in posting code examples,
so I decided to change my blog engine from Blogger to Wordpress.
Now, I posted my solution to this post, I hope this could help, I
found it helpful in my project, please I hope in some feedback.
This new blog is developing so be patient!
Ciao
Manuel

http://xmlguy.wordpress.com/2008/04/10/nhibernate-tales-n-mapping-oracle-raw/

> systems does not work as expected - it returns a constant! Seehttp://feuerthoughts.blogspot.com/2006/02/watch-out-for-sequential-or....

> > > > - Show quoted text -- Hide quoted text -

[Gabriel Schenker]

would you be interested to cross post it to the NHibernate FAQ blog?
http://blogs.hibernatingrhinos.com/nhibernate/Default.aspx

[Magdas Adrian]

Thanks a lot for your solution.
It's greatly appreciated.

Bye,
Adrian.

[Fabio Maulo]

Hi Manuel.
Do you can create a patch for NHibernate.UserTypes.Oracle ?
The project is in http://sourceforge.net/projects/nhcontrib, appreciate your contribution.
Thanks.
Bye.
Fabio Maulo.

P.S. If you need help to create the patch let me know in private mail.

2008/4/11, hoghweed <manuel....@gmail.com>:

[Gauthier Segay]

Oracle support UUID/GUID, see http://www.oracle-base.com/articles/9i/UUID9i.php

the problem is that it must be stored in RAW(16) datatype, there is no
UUID datatype like there is in sybase or sqlserver.

As for the ADO dataprovider (either MS or ODP), they will both return
such field as byte[] so a conversion must occur both way when the
mapped property/column is of Guid type.

Having little know-how of the NHibernate usertype extension mechanism,
I hacked a solution for oracle specific usage:

http://forum.hibernate.org/viewtopic.php?t=978995

there is also this issue where I leaved a comment:

http://jira.nhibernate.org:8080/jira/browse/NH-985

It would be cool if a definitive support for GUID <-> RAW(16) (without
string serialization) or such hack being supported in NH

[Gauthier Segay]

Oracle support UUID/GUID, see http://www.oracle-base.com/articles/9i/UUID9i.php

the problem is that it must be stored in RAW(16) datatype, there is no
UUID datatype like there is in sybase or sqlserver.

As for the ADO dataprovider (either MS or ODP), they will both return
such field as byte[] so a conversion must occur both way when the
mapped property/column is of Guid type.

Having little know-how of the NHibernate usertype extension mechanism,
I hacked a solution for oracle specific usage:

http://forum.hibernate.org/viewtopic.php?t=978995

there is also this issue where I leaved a comment:

http://jira.nhibernate.org:8080/jira/browse/NH-985

It would be cool if a definitive support for GUID <-> RAW(16) (without
string serialization) or such hack being supported in NH

On Apr 7, 3:31 pm, "Fabio Maulo" <fabioma...@gmail.com> wrote:

[hoghweed]

Hi Fabio, I don't know may be you're Italian, your name let me think
about it, if yes let me know, I'm Italian too.
but, tomorrow or at late on monday I will contact you by private mail,
I need just some details on how update the project in it's source
control system so my update could be a part of a distribution.
I didn't create a patch because at now, with no time to spend, my
project was too "time expensive..", I had not, but I will happy to do
that.
Bye

On Apr 11, 3:32 pm, "Fabio Maulo" <fabioma...@gmail.com> wrote:
> Hi Manuel.
> Do you can create a patch for NHibernate.UserTypes.Oracle ?

> The project is inhttp://sourceforge.net/projects/nhcontrib, appreciate your

> contribution.
> Thanks.
> Bye.
> Fabio Maulo.
>
> P.S. If you need help to create the patch let me know in private mail.
>

> 2008/4/11, hoghweed <manuel.mart...@gmail.com>:

>
>
>
> > Hi, I'm really sorry, I can't post before on my blog, I found some
> > troubles in posting code examples,
> > so I decided to change my blog engine from Blogger to Wordpress.
> > Now, I posted my solution to this post, I hope this could help, I
> > found it helpful in my project, please I hope in some feedback.
> > This new blog is developing so be patient!
> > Ciao
> > Manuel
>

> >http://xmlguy.wordpress.com/2008/04/10/nhibernate-tales-n-mapping-ora...

[hoghweed]

Hi!
..yes I'm interested, please let me what I have to do.
Bye

On Apr 11, 11:58 am, "Gabriel Schenker" <gnschen...@gmail.com> wrote:
> would you be interested to cross post it to the NHibernate FAQ blog?http://blogs.hibernatingrhinos.com/nhibernate/Default.aspx

>
> On Fri, Apr 11, 2008 at 9:52 AM, hoghweed <manuel.mart...@gmail.com> wrote:
>
> > Hi, I'm really sorry, I can't post before on my blog, I found some
> > troubles in posting code examples,
> > so I decided to change my blog engine from Blogger to Wordpress.
> > Now, I posted my solution to this post, I hope this could help, I
> > found it helpful in my project, please I hope in some feedback.
> > This new blog is developing so be patient!
> > Ciao
> > Manuel
>

> >http://xmlguy.wordpress.com/2008/04/10/nhibernate-tales-n-mapping-ora...

[Fabio Maulo]

2008/4/11, hoghweed <manuel....@gmail.com>:

Hi Fabio, I don't know may be you're Italian, your name let me think
about it, if yes let me know, I'm Italian too.

 

Si vede che non sei iscritto a http://groups.google.com/group/nh-it ;)

--
Fabio Maulo

[mhanney]

Hi Manuel,

Thank you for posting your solution. It helped me a great deal. I have
done some tests with Oracle generated RAW[16] Guid generated using
Oracle's SYS_GUID() function and I think there may be a problem
regarding the order of the bytes when using Guid.ToByteArray() with
Oracle.

The byte order of Guid.ToByteArray() in C# and Microsoft SQL Server
Guid is { 3, 2, 1, 0, 5, 4, 7, 6, 8, 9, 10, 11, 12, 13, 14, 15 };

An Oracle GUID created using SYS_GUID() and stored as RAW[16] is
ordered { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 };

The problem only exists if the application has GUIDs created by
Oracle, NOT by the C# application. I wanted to create a solution that
works with both.

I have posted my findings, and an alternative implementation on my
blog http://michaelhanney.com/blog/2008/04/12/nhibernate-oracle-guid-as-primary-key-mapping-custom-iusertype/

Please ignore my previous post, I now see what Steven Feuerstein is
talking about here http://feuerthoughts.blogspot.com/2006/02/watch-out-for-sequential-oracle-guids.html.
Oracle's SYS_GUID() function does NOT return a constant on some AIX
and Linux systems, it just returns sequential Guids, not randoms.

Thanks,
Michael.