Mapping Guid Id on Oracle

[Felipe Oriani]

Hi guys, 

We have a table on the database where we want to map the ID as Guid. Our project should run over two databases, Sql Server and Oracle. For Sql Server, we define the column type as uniqueidentifier and it works fine. For Oracle, we try to define it as char(36), raw(36), varchar2(36) but it does not work properly.

After the commit of the transaction, NHibernate generate an ID different what we have on the ID property of the model mapped.

We are mapping it using Fluent NHibernate:

Id(x => x.Id).Column("ID").GeneratedBy.GuidComb();

The question is, how can we map it on Oracle? What is the best column dataType to define? Should we create an IUserType to map it?

Thank you.

--

______________________________________
Felipe B Oriani

felipe...@gmail.com

[Ricardo Peres]

In Oracle, a GUID is defined as RAW(16).

RP

[Felipe Oriani]

Ricardo,

We have success implementing an IUserType but we are not sure what is the best appoach. We will try using RAW(16). the problem is that Oracle generates an GUID without "-", which is not the default for .Net.

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+unsubscribe@googlegroups.com.
To post to this group, send email to nhu...@googlegroups.com.
Visit this group at https://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.

[Fran Knebels]

Richardo is correct.  I've used RAW(16) before for Guid's in Oracle.  

Easy way to verify is to generate the schema from your mappings.  

[Felipe Oriani]

Fran, how have you mapped it?

We are using fluent nhibernate and we try this:

Id(x => x.Id).Column("ID").GeneratedBy.GuidComb();

it is not working. In the Oracle it is generating a key like this: "D3CCAC3449579D4EB7FAA6E500ECC5A0"

[Fran Knebels]

yes.  that is a guid in Oracle.

Here's a bunch of info about Guid formatting between Oracle and .net 

http://stackoverflow.com/questions/7289734/convert-from-oracles-raw16-to-nets-guid

I guess my bigger question is why does it matter.  if this is a PK and FK in the database, why does the formatting matter?  are you showing this to users?

I'm using mapping by code.  this code is equivalent to yours

            Id(x => x.Id, map =>
            {
                map.Column("Id");
                map.Generator(Generators.GuidComb);
            });

[Felipe Oriani]

Hi Fran,

On our Entity we define the ID (PK) as Guid and we have FKs for this table. The problem is that after the insert, the Guid that is on the ID property is not the same as the one saved on the database. See the image bello (code and database saved):

The ID column is the PK and datatype is RAW(16) mapped with GeneratedBy.GuidComb().

We understand your point Fran, but the same problem here :(

[Fran Knebels]

Yeah.  It's just a different format.  See that stackoverflow thread.  There's a coversion routine to take it from the raw format to the .net 

[Felipe Oriani]

Yes, we could do that, but when the project runs over Sql Server it works so naturally with uniqueidentifier dataType, we don't want work-arounds on it :(