Insert blob into Firebird in block statement

Question

I have a list with many objects which holds byte[8]. I want to insert them into Firebird database as a block. Without the array I can do it like this:

var statement = "EXECUTE BLOCK AS BEGIN ";
foreach (var item in items)
{
   statement += "INSERT INTO table (id, val) VALUES ("
                + item.id + ", " + item.val + "); ";
}
statement += "END";
new FbCommand(statement, connection, transaction).ExecuteNonQuery();

When I want to insert into blob, I use something like this:

FbCommand fbc = new FbCommand("INSERT INTO table (id, blob) VALUES (@id, @blob)", connection, transaction);
fbc.Parameters.Add("@id", FbDbType.BigInt).Value = id;
fbc.Parameters.Add("@blob", FbDbType.Binary).Value = blob;
fbc.ExecuteNonQuery();

How to create parametrized statement for more than one row?

ain · Accepted Answer · 2016-01-18 15:31:19Z

3

The EXECUTE BLOCK statement can have parameters, the syntax is described in documentation:

EXECUTE BLOCK(blobVal BLOB = ?) AS BEGIN
  INSERT INTO table (id, val) VALUES (1, :blobVal);
END

The downside is that you have to "linearize" your parameters, ie if you have many rows you're going to have statement with many parameters (which you have to assign in another loop, so code could be quite hard to follow).

It seems that your blob data is actually very small, only 8 bytes, so perhaps an acceptable alternative is to use binary strings feature:

EXECUTE BLOCK AS BEGIN
  INSERT INTO table (id, val) VALUES (1, x'01234567890ABCDEF');
END

This would allow you to create the EXECUTE BLOCK statement in the loop as in your first code example (ie keep it simple).

answered Jan 18, 2016 at 15:31

ain

22.8k3 gold badges60 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Artholl Over a year ago

Yes, EXECUTE BLOCK can have parameters, but it would be really many parameters and you already wrote downsides. Binary strings looks promising. I'll give it a try. It requires conversion to string and back, but since there are no limitations (like inserting 00 in the middle of the string), it should be fine.

Artholl Over a year ago

It works for saving and I can also create select (in FlameRobin) as written in the second link you posted. But how to get those data back to C# app? When I did row select (based on some other value), it returns me some not nice string and I am not able to convert it back into the original bytes.

ain Over a year ago

Well, in your post you have byte type, not string. So do not read it back as a string but as a binary blob it is. Also, how exactly is the DB field defined (type, character set)?

Artholl Over a year ago

I tried to define the column like this: myColumn CHAR(8). Maybe I don't understand it right. I am not sure, that I am able to define, how I get the column value back from this column (bytes, not string).

ain Over a year ago

For binary data one should use BLOB SUB_TYPE 0. In your case, if your data never exceeds 8 bytes, using blob would be overkill, so use CHAR(8) CHARACTER SET OCTETS

|

Artholl · Accepted Answer · 2016-01-21 08:50:38Z

0

Since I need to save only 8 bytes and I need some solution I ended up with converting bytes into string (into hex string - each byte into two chars). I was inspired by the accepted answer.
I did this, because Firebird library for C# doesn't support conversion from column defined as myColumn CHAR(8) CHARACTER SET OCTETS, so I have to save bytes into string and then convert them back by myself.
It is not ideal solution, because I have to save bytes data into string (not nice part), but I need some working solution.

answered Jan 21, 2016 at 8:50

Artholl

1,3791 gold badge20 silver badges42 bronze badges

1 Comment

Mark Rotteveel Over a year ago

Interesting, I thought the Firebird .net provider did have support for OCTETS fields. I'll see if I can find a better solution for you.

Collectives™ on Stack Overflow

Insert blob into Firebird in block statement

2 Answers 2

10 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related