8
Jul
Tags: , | 13 Comments »

How to store GUID values (not only from .NET) on a Firebird database

After Binding Boolean fields from Firebird post few days ago I got some comments about GUID values in Firebird. Firebird doesn’t have some native guid-datatype alike to bool. :) But the solution is there (well, people around Firebird can do anything with FB ;) ).

The most common way is to use CHAR field with character set OCTETS. You can of course use CHAR with i.e. NONE charset, but you’re only wasting space. Creating column as CHAR(16) CHARACTER SET OCTETS will fit all your needs with GUID. Why only 16-chars length? Because when you use octets, you’re saving data in “byte” form. So 128 is 16 x 8 and therefore CHAR(16) is good.

To test this, you can use this simple peice of code:

using (FbConnection conn = new FbConnection(connectionStringBuilder.ToString()))
{
  conn.Open();

  using (FbCommand cmd = conn.CreateCommand())
  {
    // first create the table for testing
    cmd.CommandText = "recreate table GUID_test (guid char(16) character set octets)";
    cmd.ExecuteNonQuery();
  }

  using (FbCommand cmd = conn.CreateCommand())
  {
    // working with GUID
    cmd.CommandText = "insert into GUID_test values (@guid)";

    // classic way, works good
    cmd.Parameters.Add("@guid", FbDbType.Char, 16).Value = Guid.NewGuid().ToByteArray();
    // another way, maybe better readability, but same result
    cmd.Parameters.Add("@guid", FbDbType.Guid).Value = Guid.NewGuid();

    cmd.ExecuteNonQuery();
  }

  using (FbCommand cmd = conn.CreateCommand())
  {
    // drop it, it has no real application
    cmd.CommandText = "drop table GUID_test";
    cmd.ExecuteNonQuery();
  }
}

There's 13 Comments So Far

  • Anonymous
    July 10th, 2007 at 17:01

    I guess your title is quite misleading. You should title this blog entry “How to store GUID values on a Firebird database”. When I saw “GUID in Firebird” I almost jumped out of my seat in happiness, just to discover what I already know: firebird does not have a native GUID datatype. :( BTW, maybe you could post a tutorial on how to generate GUID values in firebird, using some third party function library? I’ve tried myself with some, with no luck :(

  • cincura.net
    July 10th, 2007 at 17:24

    OK, title “fixed”. :) I’ll try to find some tips how to generate GUID inside FB, good idea.

  • Anderson
    July 11th, 2007 at 00:43

    rFUNC (http://rfunc.sourceforge.net/) does have a CreateGUID function (same is true for other UDFs out there)New FB 2.1 (still beta) has a new built-in function GEN_UUID (heard that uuid is batter to store than ‘pure’ guids — something related to index optimization)Recently, on firebird-support news groups, some discussion pointed out a small code to build your custom udf (using Delphi AFAIR) to returns UUIDs (look for uuid)Regards

  • cincura.net
    July 11th, 2007 at 09:40

    AFAIK rFunc doesn’t support all features of UDF handling introduced in FB 2.0 (and seems to be dead, isn’t it?). But there’s a lot of other that has some GUID function, just to test, how good is the function. :)

  • pavel
    July 11th, 2007 at 18:04

    myslím že je smutný že nějaká databáze ještě v roce 2007 nepodporuje GUID – chápu že to bylo normální v době kdy vznikla interbase ale proč už to proboha dávno firebird neumí ? Proč tam tu podporu nedoděláte a místo toho nutíte tisíce uživatelů vymýšlet opičárny jak tento nedostatek obejít ?

  • cincura.net
    July 11th, 2007 at 18:05

    A jake vyhody by datovy typ guid poskytl? Pripadne co by mel umet vice oproti aktualnimu stavu?

  • pavel
    July 11th, 2007 at 18:37

    o tom opravdu nebudu diskutovat jestli guid je potřebný nebo ne — podívejte se na jiné databáze a poučte se

  • cincura.net
    July 11th, 2007 at 18:45

    Me by ale opravdu zajimalo, jakou vyhodu mi tento typ prinese a jake bude mit vlastnosti (pujde s nim delat nejake operace?, jake?).

  • cincura.net
    July 11th, 2007 at 18:52

    BTW kdyz vezmu klasicky set MS SQL, ORA, FB, PG, MySQL, DB2, Informix; ma jeste nekdo krom MS SQL tento datovy typ. AFAIK ORA, FB, MySQL nema. Dalsi temer vubec neznam.

  • Pavel
    July 18th, 2007 at 19:35

    PostgreSQL 8.3 ma podporu pro typ guid v contribu

  • cincura.net
    July 18th, 2007 at 19:39

    A jakou vyhodu mi to prinese (nepocitam to, ze jsem blbej a nekde jsem si neco blbe zkonvertoval)? Jake operace navic umi?

  • cincura.net
    July 28th, 2007 at 20:47

    Some days ago I wrote post about ” How to store GUID values (not only from .NET) on a Firebird database

Who Linked To This Post?

  1. Firebird News » GUID in Firebird

Share your thoughts, leave a comment!