firebird-net-provider

Re: [Firebird-net-provider] Guid Support

[<ch...@co...>]

I've tried storing this with Firebird and it seems to work.  Firebird seem to treat CHAR types as merely as an array of bytes and doesn't seem to attempt any other interpretations.  With that knowledge and if you are developing a new database project make it a guideline that only GUID entities are defined as CHAR(16).

With that, and using some helper UDFs -- GuidCreate, GuidIsEmpty, GuidString --
Guids can be "simulated" on Firebird.

The only issue is reading from Firebird into the .NET provider as it will "think" a GUID is a Firebird TEXT type.  The way for the provider to distinguish that the data coming across is a guid is for the provider to check the size.  If the data is TEXT by and size is 16 then assume a GUID and convert the byte stream to a GUID -- wal-la!

Writing is easier as the provider can do a GetType() on the object.

Right now I've modified the provider to support this (only for sprocs) but what I'm really asking is if this make sense for community wide support.

I think having some sort of GUID support would be extremely useful especially to assist and encourage SqlServer migration to Firebird.

Thanks,
Chris



> > The question is how to use a 16-byte GUID in Firebird when the system
> > itself doesn't.  In the database that I'm designing with Firebird I
> > decided to reserve CHAR(16) for GUID only.  If I have a need for a
> > 16-byte character representation then I'll use either VARCHAR(16) or
> > CHAR(17).  In the CHAR(17) case I'm anticipating the front end to limit user
> > input.
> 
> A column of "char(16) character set octets" should in theory hold
> a binary GUID. 
> 
> But it is a field of research, whether all layers involved will gladly 
> handle bytes of zero in this. It's worth a try.
> 
> A middle ground between binary and textual GUIDs would be to use base64 
> encoded binary GUIDs, requiring 22 bytes.
> 
> Regards,
> Peter Jacobi
> 
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: The Robotic Monkeys at ThinkGeek
> For a limited time only, get FREE Ground shipping on all orders of $35
> or more. Hurry up and shop folks, this offer expires April 30th!

> http://www.thinkgeek.com/freeshipping/?cpg=12297
> _______________________________________________
> Firebird-net-provider mailing list
> Fir...@li...
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

Re: [Firebird-net-provider] Guid Support

[Peter J. <pj...@wa...>]

Hi Chris,

> I've tried storing this with Firebird and it seems to work.  

Did your test include cases where the GUID did contain
bytes of value zero? On average 1/16th of all GUID of
should have this "feature"?

> Firebird seem
> to treat CHAR types as merely as an array of bytes and doesn't seem to
> attempt any other interpretations.  

Only CHARACTER SET OCTETS is designed to be a mere array of bytes,
so that I'm curious whether your test worked without specifying the
character set.

> With that knowledge and if you are
> developing a new database project make it a guideline that only GUID
> entities are defined as CHAR(16).

CHAR(16) CHARACTER SET OCTETS

> The only issue is reading from Firebird into the .NET provider as it will
> "think" a GUID is a Firebird TEXT type.  The way for the provider to
> distinguish that the data coming across is a guid is for the provider to
> check the size.  If the data is TEXT by and size is 16 then assume a GUID
> and convert the byte stream to a GUID -- wal-la!

I don't know whether the provider already includes special casing
for character set octets, perhaps Carlos should comment on this.

Also short of adding a new type, a new (pseudo) character set can
be created, which behaves mostly like OCTETS but uniquely identifies
the field as being an GUID. It's even possible to make the stringification
of the GUID a character set conversion to ASCII, if this buys anything.

Regards,
Peter Jacobi

RE: [Firebird-net-provider] Guid Support

[Chris W. <ch...@co...>]

Hi Peter,


>Only CHARACTER SET OCTETS is designed to be a mere array of bytes,
>so that I'm curious whether your test worked without specifying the
>character set.

[1] You are very much correct to point out and bring up the impact that
Firebird character set has on altering CHAR(N) definitions.  Currently
I am using the default character set for Firebird (in other words I did
not set the character set and therefore presuming ascii) that by default
treats CHAR(N) as a stream of 16 bytes. 

Since I just noticed your email I will go back and redefine my columns
to 

CHAR(16) CHARACTER SET OCTETS

I'm confident it will work and I'll let you know the results.

In addition I also tested using Guid.Empty and it works flawlessly.  I
use
Guid.Empty to indicate to Firebird to insert the record.  A valid Guid
is used to indicate update.   Also I use a UDF to generate a new Guid
during inserts.

Thanks for your observation and input.
Chris

-----Original Message-----
From: Peter Jacobi [mailto:pj...@wa...] 
Sent: Wednesday, April 28, 2004 12:39 AM
To: ch...@co...
Cc: fir...@li...
Subject: Re: [Firebird-net-provider] Guid Support

Hi Chris,

> I've tried storing this with Firebird and it seems to work.  

Did your test include cases where the GUID did contain
bytes of value zero? On average 1/16th of all GUID of
should have this "feature"?

> Firebird seem
> to treat CHAR types as merely as an array of bytes and doesn't seem to
> attempt any other interpretations.  

Only CHARACTER SET OCTETS is designed to be a mere array of bytes,
so that I'm curious whether your test worked without specifying the
character set.

> With that knowledge and if you are
> developing a new database project make it a guideline that only GUID
> entities are defined as CHAR(16).

CHAR(16) CHARACTER SET OCTETS

> The only issue is reading from Firebird into the .NET provider as it
will
> "think" a GUID is a Firebird TEXT type.  The way for the provider to
> distinguish that the data coming across is a guid is for the provider
to
> check the size.  If the data is TEXT by and size is 16 then assume a
GUID
> and convert the byte stream to a GUID -- wal-la!

I don't know whether the provider already includes special casing
for character set octets, perhaps Carlos should comment on this.

Also short of adding a new type, a new (pseudo) character set can
be created, which behaves mostly like OCTETS but uniquely identifies
the field as being an GUID. It's even possible to make the
stringification
of the GUID a character set conversion to ASCII, if this buys anything.

Regards,
Peter Jacobi

RE: [Firebird-net-provider] Guid Support

[Chris W. <ch...@co...>]

Peter and anyone else,

  I have a general Firebird question,

    I've used the domain keyword to define a name called
"uniqueidentifier" as CHAR(16) character set octet.  Since I have all my
table column defined using "uniqueidentifer" is was easy to change all
my table as I have this defined in once place.

    However I find that isql will not accept "uniqueidentifer" when used

in a stored procedure.  I was wonder is there was a way to set up alias
in Firebird such that they can be used in a store proc in the following
manner...

create procedure my_spoc( myparamGuid Guid )

Where Guid is defined as "CHAR(16) CHARACTER SET OCTETS"

Thanks,
Chris

-----Original Message-----
From: fir...@li...
[mailto:fir...@li...] On Behalf Of
Peter Jacobi
Sent: Wednesday, April 28, 2004 12:39 AM
To: ch...@co...
Cc: fir...@li...
Subject: Re: [Firebird-net-provider] Guid Support

Hi Chris,

> I've tried storing this with Firebird and it seems to work.  

Did your test include cases where the GUID did contain
bytes of value zero? On average 1/16th of all GUID of
should have this "feature"?

> Firebird seem
> to treat CHAR types as merely as an array of bytes and doesn't seem to
> attempt any other interpretations.  

Only CHARACTER SET OCTETS is designed to be a mere array of bytes,
so that I'm curious whether your test worked without specifying the
character set.

> With that knowledge and if you are
> developing a new database project make it a guideline that only GUID
> entities are defined as CHAR(16).

CHAR(16) CHARACTER SET OCTETS

> The only issue is reading from Firebird into the .NET provider as it
will
> "think" a GUID is a Firebird TEXT type.  The way for the provider to
> distinguish that the data coming across is a guid is for the provider
to
> check the size.  If the data is TEXT by and size is 16 then assume a
GUID
> and convert the byte stream to a GUID -- wal-la!

I don't know whether the provider already includes special casing
for character set octets, perhaps Carlos should comment on this.

Also short of adding a new type, a new (pseudo) character set can
be created, which behaves mostly like OCTETS but uniquely identifies
the field as being an GUID. It's even possible to make the
stringification
of the GUID a character set conversion to ASCII, if this buys anything.

Regards,
Peter Jacobi



-------------------------------------------------------
This SF.Net email is sponsored by: Oracle 10g
Get certified on the hottest thing ever to hit the market... Oracle 10g.

Take an Oracle 10g class now, and we'll give you the exam FREE.
http://ads.osdn.com/?ad_id=3149&alloc_id=8166&op=click
_______________________________________________
Firebird-net-provider mailing list
Fir...@li...
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

RE: [Firebird-net-provider] Guid Support

[Peter J. <pj...@wa...>]

Hi Chris, All,

> in a stored procedure.  I was wonder is there was a way to set up alias in
> Firebird such that they can be used in a store proc in the following
> manner...
> 
> create procedure my_spoc( myparamGuid Guid )
> 
> Where Guid is defined as "CHAR(16) CHARACTER SET OCTETS"

In my limited understanding, this is not supported, as domains are not 
types. FB lacks user definable types and domains are only a very basic
substitute for UDTs.

But to get a more substantive and authorative answer, you may want to 
put your question to the main support mailing list, as it is not .NET 
provider specific.

Regards,
Peter Jacobi

RE: [Firebird-net-provider] Guid Support

[Chris W. <ch...@co...>]

Correct.

	This is not a "real" solution but it is a "real" solution for
those who use GUID as OIDs and looking to migrate from SQL Server to
Firebird.  This is why I suggested having a *compiler flag* -- this is
open source :-).

What I like about open source is that you can construct "real" solutions
that are inconceivable with closed source giving programmers a greater
degree of flexibility.

Clearly if the application/db developer has to support CHAR(16) this
suggestion is of no use but if this is new development -- such as what I
am engaged -- or even a migration project from SqlServer to Firebird --
this "solution" can help speed adoption of Firebird.  I've seen issues
like this used as an excuse or stumbling block.

Of course the "real" solution is for Firebird to support Guids but
taking a cursory glance at their bug/feature list I don't think Guid
support is a high priority task item for them.

Chris

-----Original Message-----
From: Carlos Guzm=E1n =C1lvarez [mailto:car...@te...]=20
Sent: Wednesday, April 28, 2004 12:54 AM
To: ch...@co...
Cc: Peter Jacobi; fir...@li...
Subject: Re: [Firebird-net-provider] Guid Support

Hello:

>The only issue is reading from Firebird into the .NET provider as it
will "think" a GUID is a Firebird TEXT type.  The way for the provider
to distinguish that the data coming across is a guid is for the provider
to check the size.  If the data is TEXT by and size is 16 then assume a
GUID and convert the byte stream to a GUID -- wal-la!
>

That is what i was thinking you are doing for implement it,
but that isn't a real solution as people can have a CHAR(16)
field that isn't used for Guid ;)






--
Best regards

Carlos Guzm=E1n =C1lavrze
Vigo-Spain

Re: [Firebird-net-provider] Guid Support

[<car...@te...>]

Hello:

>	This is not a "real" solution but it is a "real" solution for
>those who use GUID as OIDs and looking to migrate from SQL Server to
>Firebird.  This is why I suggested having a *compiler flag* -- this is
>open source :-).
>
Ok, i will think on this, huummm ... opnions are wellcome ;)






--
Best regards

Carlos Guzmán Álvarez
Vigo-spain

Re: [Firebird-net-provider] Guid Support

[<car...@te...>]

Hello:

>The only issue is reading from Firebird into the .NET provider as it will "think" a GUID is a Firebird TEXT type.  The way for the provider to distinguish that the data coming across is a guid is for the provider to check the size.  If the data is TEXT by and size is 16 then assume a GUID and convert the byte stream to a GUID -- wal-la!
>

That is what i was thinking you are doing for implement it,
but that isn't a real solution as people can have a CHAR(16)
field that isn't used for Guid ;)






--
Best regards

Carlos Guzmán Álavrze
Vigo-Spain