Monthly Archives: July 2007

Generating GUID in Firebird

Leave a reply

Some days ago I wrote post about “How to store GUID values (not only from .NET) on a Firebird database“. In comments there was a really good point about generating GUID directly inside Firebird database. With current latest stable version (FB 2.0.1) this isn’t possible. With 1.5 trunk version neither too. The only way how to do it, is to write UDF library with this function (or to download some UDF library, a lot of it has GUID-generate function). [I've never seen this written in stored procedure, but maybe ... ;) ] This isn’t good news. But it’s not so bad. :) The new Firebird version 2.1, which is now in beta-stage, has a lot new function built-in – all functions from UDF-libs shipped with previous FB versions are now inside and also some new. One of these is also the GEN_UUID function. It’s doing exactly what we need – returns universal unique number. Here’s result from FB 2.1 Beta 1:
SQL> select gen_uuid() from rdb$database;

GEN_UUID
================================
7211FBA89FCE9E4680DE72A8DA504B55

SQL> select gen_uuid() from rdb$database;

GEN_UUID
================================
714BEC373EBC664990BB248A26111E04

SQL> select gen_uuid() from rdb$database;

GEN_UUID
================================
5831F10169030D46AB5A33FA15648D9C

SQL> select gen_uuid() from rdb$database;

GEN_UUID
================================
CCF4A86789D04F4B8BEE808C5CFCB852

DDEX, Vista (64-bit), Firebird, Visual Studio 2005

20 Replies

Some months ago I was writing about installing DDEX provider for Firebird to Visual Studio (DDEX and Firebird .NET Data Provider). There is described the “old style” method of using it. It’s still working, but you need Visual Studio SDK, and so it’s not so direct.

But DDEX for Firebird is able to work without this SDK too (not so long like with SDK, but it’s not brand new stuff). Because I installed on my new laptop Vista 64-bit, I need to install the DDEX interface to Firebird too. Some new cool stuff was introduced with Vista, but some things stopped working, so I was little bit scary how the privovider will work. So let’s look at it – it’s not tricky, everything is in readme, but you know …. [Note: This procedure is applicable to both 32-bit and 64-bit systems as well to WinXP.]

I have VS 2005 Prof. with SP1 and Vista SP installed. The I build DDEX provider on my old laptop (but you can download binary package). Install/Copy dll files to you favourite place. Then prepare FirebirdDDEXProviderPackageLess64.reg file (or FirebirdDDEXProviderPackageLess32.reg if you have 32-bit system). You need to change %Path% “variable” to your installation path (remember backslashing backslash), so the path should look i.e. C:\something\somethingelse\FirebirdSql.VisualStudio.DataTools.dll. Then import this file into registry, double-click does this (you will need administrator’s permissions). OK. Next step. Verify that you have FirebirdSql.Data.FirebirdClient in GAC (gacutil /l FirebirdSql.Data.FirebirdClient should return you at least one item). If not, use gacutil to add it to GAC (gacutil /i <Path To Your FirebirdSql.Data.FirebirdClient assembly>). The info that you get from gacutil Copy’n'Paste to some place (or just don’t close console window). Now find machine.config files (there are two on 64-bit systems; one on 32-bit) [note: To only make Visual Studio to work with DDEX you can modify ony 32-bit version of machine.config. But i.e. factories in your 64-bit apps will not be able to use FB.]. Modify it, as described in readme. You have to substitute %Version%, %Culture%, %PublicKeyToken% “variables” to your real data – you can find it in the output from gacutil (yes, now you can close console ;) ). Save these files. Open Visual Studio, Server Explorer and try to add connection to FB server.

First you have to see FB provider in list – if not, the registry file isn’t imported right.
Second when you type something in the next dialog and it closes, the most common reason is, that the FirebirdClient assembly cannot be loaded. Verify whether it’s in GAC and whether assembly info in machine.config is same as in GAC.

Well, nothing painfull, isn’t it? DDEX, Vista (64-bit), Firebird, Visual Studio 2005; everything works fine.
To prove, I’m not cheating :) look (ok you can say, that it’s mounting, but I’m not so patient to do this):

Problém se spuštění Firebirdu 2.1 Beta 1

Leave a reply

Pokud máte problém se spuštěním čerstvé bety Firebirdu (2.1), vězte, že vám chybí manifest pro dll knihovnu (Microsoft.VC80.CRT.manifest) (na Windows samozřejmě). Pravděpodobně na to narazíte – jako já – při testování na čistém (virtuálním) stroji (normálně se dostane vše do systému s Office, Visual Studio apod.). Build nebude aktualizován, ale na stránkách Firebirdu (www.firebirdsql.org) je soubor separátně ke stažení. Stačí jej plácnout do adresáře “bin”.

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

13 Replies

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();
  }
}

Binding Boolean fields from Firebird

33 Replies

From time to time there’s a question about binding bool fields from Firebird into DataSet/DataTable. Because Firebird has no bool datatype people created a lot of workarounds. The most used way is to use CHAR(1) or SMALLINT as base type (or domain with check constraint). With integer, there’s no problem. When you have 0/1 False/True it’s trivial. But how to do this with char (or any other way you’re using). Well, the solution is easy. I like this one.

I create select, that returns true/false as 1/0 directly. Using CASE statement is really helpful. This solution has minimal overhead and can be used with any datatype you’re using as bool. For fine-tunning you can create a view or stored procedure (for select or only for returning 0/1 values from your type).

IMO this solution pretty easy and straightforward. You can use it with Fb… objects directly or with DDEX generated without any problem.