Reading and writing a BLOB from Firebird

I'ts been a long time since I've written anything so I thought I'd make it usefull.

For those of you who don't know, a BLOB is a Binary Large Object and is a data type for storing binary (and text) data in a database.

It could be used for storing images, files etc in a database for later retrieval or even to persist objects.

There are scarce resources on the Internet relating to this subject in relation to the Firebird database. Whilst researching this topic, many of the links I found led me back to the following page:

http://www.dotnetfirebird.org/blob-reading-example-csharp

I'ts a great site and many people link to it, however you would be mislead to think that this was the only method.


The code which follows assumes that a database table named BLOB_DATA already exists and contains the columns ID and DATA, a similar table could be ctreated by executing the following script:

--==============================================================
-- Create Tables
--==============================================================
CREATE TABLE BLOB_DATA (
ID INTEGER NOT NULL,
DATA BLOB NOT NULL,
PRIMARY KEY (ID) );

--==============================================================
-- Create Generators for Auto Increment Primary Keys
--==============================================================
CREATE GENERATOR GEN_BLOB_DATA;

--==============================================================
-- Create Triggers for Auto Increment Primary Keys
--==============================================================
SET TERM ^ ;
CREATE TRIGGER ID FOR BLOB_DATA
ACTIVE BEFORE INSERT
AS
BEGIN
IF(NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(GEN_BLOB_DATA, 1);
END^
SET TERM ; ^

The following code will store a BLOB from a Firebird database:

public void SaveBlob( byte[] data )
{
//instanciate a new connection string builder
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.Database = "MyDatabase";
csb.UserID = "SYSDBA";
csb.Password = "masterkey";
csb.ServerType = 1; //embedded server

//instanciate a connection object
FbConnection con = new FbConnection(csb.ToString());

//build the query
cmd = new FbCommand( "INSERT INTO BLOB_DATA VALUES ( null, @data )", con );
//add the parameters
cmd.Parameters.Add( "@data", data );
//open the connectoin
con.Open();
//execute the query
cmd.ExecuteNonQuery();
//close the connection
con.Close();
}


The following code will read BLOB data from a Firebird dataase:

public byte[] ReadBlob( int id )
{
//instanciate a new connection string builder
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.Database = "MyDatabase";
csb.UserID = "SYSDBA";
csb.Password = "masterkey";
csb.ServerType = 1; //embedded server

//instanciate a connection object
FbConnection con = new FbConnection(csb.ToString());

//build the query
cmd = new FbCommand( "SELECT * FROM BLOB_DATA WHERE ID = @id", con );
//add the parameters
cmd.Parameters.Add( "@id", id );
//open the connectoin
con.Open();
//execute the query
FbDataReader reader = cmd.ExecuteReader();

byte[] data = null;
//read the data
while ( reader.Read() )
{
data = (byte[])reader["DATA"] ;
}
//close the connection
con.Close();

return data;
}

In the real world, you wouldn't instanciate a new connection string in each method, the database routines would possibly be static methods inside a sealed class. This was just a quick'n'dirty demonstration of a simple method to achieve the required results.