Guid in firebird

[ymaka...@gmail.com]

Hello,

 

I am trying to use Guid from a c# application.

I store a Guid to fb database    using 

“INSERT INTO PROJECTS (ProjGID, PROJCODE, PROJTITLE) VALUES (char_to_uuid('7104D437-ACD5-4781-918F-3A049DB1CFCA'), 'proj', 'projtitle')”.

ProjGID is defined as char(16) character set octets

 

Following that I load the data using

"Select uuid_to_char(PROJGID) as id , Projects.ProjGID  from Projects"

 

In the DataTable I get the two values but they differ. The correct one is the one returned by uuid_to_char. The  Projects.ProjGID is a Guid DataType but the value is different.

Any ideas?

 

Yannis

[Mark Rotteveel]

Which version of the Firebird ADO.net provider are you using? Also, the
Microsoft interpretation of GUIDs applies a combination of big endian
and little endian byte order where the RFC-standard is big endian, which
may also lead to differences in presentation.

Mark
--
Mark Rotteveel

[ymaka...@gmail.com]

I am using 5.12.1.0.

For "7104D437-ACD5-4781-918F-3A049DB1CFCA" I am getting  "37d40471-d5ac-8147-918f-3a049db1cfca"

The order of the first  3 parts differ.but I don't know why.

I use the guid returned to select rows in other tables and of course I am getting the wrong results.

Any workaround?

[Norbert Saint Georges]

I had the same problem and I created this function

 

CREATE OR ALTER FUNCTION UUID_TO_GUID(

    REAL_UUID CHAR(16))

RETURNS VARCHAR(36)

AS

declare variable i integer;

declare variable c integer;

declare variable hex_uuid varchar(36);

BEGIN

hex_uuid = '';

-- 1 2 3 4- 5 6- 7 8- 910-111213141516

--b2b3a6ee-f411-434c-a807-40588ad2a63c lower(uuid_to_char)

-- 4 3 2 1- 6 5- 8 7- 910-111213141516

--eea6b3b2-11f4-4c43-a807-40588ad2a63c dotnet guid

--eea6b3b2-11f4-4c43-a807-40588ad2a63c result

i = 3;

while (i>=0) do begin

c = ascii_val(substring(real_uuid from i+1 for 1));

if (c < 0) then c = 256 + c;

hex_uuid = hex_uuid

|| substring('0123456789abcdef' from bin_shr(c, 4) + 1 for 1)

|| substring('0123456789abcdef' from bin_and(c, 15) + 1 for 1);

i = i - 1;

end

hex_uuid = hex_uuid ||'-';

 

i = 5;

while (i>=4) do begin

c = ascii_val(substring(real_uuid from i+1 for 1));

if (c < 0) then c = 256 + c;

hex_uuid = hex_uuid

|| substring('0123456789abcdef' from bin_shr(c, 4) + 1 for 1)

|| substring('0123456789abcdef' from bin_and(c, 15) + 1 for 1);

i = i - 1;

end

hex_uuid = hex_uuid ||'-';

 

i = 7;

while (i>=6) do begin

c = ascii_val(substring(real_uuid from i+1 for 1));

if (c < 0) then c = 256 + c;

hex_uuid = hex_uuid

|| substring('0123456789abcdef' from bin_shr(c, 4) + 1 for 1)

|| substring('0123456789abcdef' from bin_and(c, 15) + 1 for 1);

i = i - 1;

end

hex_uuid = hex_uuid ||'-';

 

i = 8;

while (i < 10) do

begin

c = ascii_val(substring(real_uuid from i+1 for 1));

if (c < 0) then c = 256 + c;

hex_uuid = hex_uuid

|| substring('0123456789abcdef' from bin_shr(c, 4) + 1 for 1)

|| substring('0123456789abcdef' from bin_and(c, 15) + 1 for 1);

i = i + 1;

end 

hex_uuid = hex_uuid ||'-';

i = 10;

while (i < 16) do

begin

c = ascii_val(substring(real_uuid from i+1 for 1));

if (c < 0) then c = 256 + c;

hex_uuid = hex_uuid

|| substring('0123456789abcdef' from bin_shr(c, 4) + 1 for 1)

|| substring('0123456789abcdef' from bin_and(c, 15) + 1 for 1);

i = i + 1;

end

 

return hex_uuid;

end

 

 

Select UUID_TO_GUID (PROJGID) as id , Projects.ProjGID  from Projects

 

De : firebird-n...@googlegroups.com [mailto:firebird-n...@googlegroups.com] De la part de ymaka...@gmail.com
Envoyé : mardi 8 mai 2018 18:57
À : firebird-net-provider
Objet : Re: Guid in firebird

--
You received this message because you are subscribed to the Google Groups "firebird-net-provider" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-net-pro...@googlegroups.com.
To post to this group, send email to firebird-n...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-net-provider/5c16d538-25d1-4d81-91e7-95a1c02a47f2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Mark Rotteveel]

As I said in my previous mail, Microsoft applies a different byte-ordering

From https://en.wikipedia.org/wiki/Universally_unique_identifier
"""
The binary encoding of UUIDs varies between systems. Many systems encode
the UUID entirely in a big-endian format.
...
Other systems, notably Microsoft's marshalling of UUIDs in their COM/OLE
libraries, use a mixed-endian format, whereby the first three components
of the UUID are little-endian, and the last two are big-endian.
"""

So the initial UUID (7104D437-ACD5-4781-918F-3A049DB1CFCA) used the
big-endian byte order, but when you loaded it into a Microsoft Guid, it
applied little-endian byte order for the first three segments, and
big-endian for the last two segments, leading to
37d40471-d5ac-8147-918f-3a049db1cfca

See
4.3.2.1. <> 1.2.3.4.
7104D437 <> 37d40471
etc

These are the same guids, they just have a different presentation.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

Actually, on second thought, it would have been more correct to say
these are the same bytes, because the presentation is the actual UUID...

Mark
--
Mark Rotteveel

[ymaka...@gmail.com]

 All is clear.

I was using CHAR_TO_UUID/UUID_TO_CHAR in my FbCommand SQL statements and it was confusing.

With FbParameters problem solved.

Thank you Mark

[Mark Rotteveel]

In any case, short of the Firebird .net provider introducing a breaking
change to reorder the UUID bytes to the mixed-endian format expected by
the C# Guid class on read and to the big-endian format expected by
Firebird on write, you'll either have to use string UUIDs always, or do
the byte reordering yourself.

See also a similar question for MySQL and C#:
https://stackoverflow.com/questions/48147681/get-a-guid-to-encode-using-big-endian-formatting-c-sharp

Mark
--
Mark Rotteveel

[Eric Schneider]

Are you using CHAR(38) as the data type?

[Yannis Makarounis]

I am using char(16) character set octets and it works

--

You received this message because you are subscribed to the Google Groups "firebird-net-provider" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-net-pro...@googlegroups.com.
To post to this group, send email to firebird-n...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-net-provider/e8b752f2-b089-4c44-a1c3-8008dbf3e667%40googlegroups.com.