I want to have a binary field (SHA1 Hash value). I could use a BLOB
for it. However, I also want to use this field as a primary key.
BLOBS can not be used as either indexes or primary keys.

From what I've read, it appears that I could represent a binary field
as a CHAR with character set assigned "OCTETS". It appears that the
1.7 Firebird .Net data provider allows GUIDS to be represented that
way (CHAR(16) CHARACTER SET OCTETS).

From what testing I've done, I CAN NOT move binary data into a CHAR
(20) CHARACTER SET OCTETS. It appears that the .Net data provider
does not handle this correctly.

If I try to set the command parameter to FbDbType.Char, the .Net data
provide calls the ToString method on the object passed in as the
value. For a byte[] object, that always returned the system type
string "System.byte[]". If I set the data type to FbDbType.Binary I
get an internal SQL error of -303 (an undocumented error).


SHA1 sha = new SHA1CryptoServiceProvider();

string cmdtext = "INSERT INTO CONTENT (SHA1_HASH,
PART_SIZE, CONTENTX) VALUES (@sha, @size, @content)";

FbConnection fbconn = new FbConnection(connectionString);
FbCommand fbcommand = new FbCommand(cmdtext, fbconn);
FbTransaction fbtrans = null;
FbParameter fbparam;


/* create some random content */
byte[] data = new byte[33];
Random rn = new Random();

for (int i = 0; i < data.Length; i++)
data[i] = (byte) rn.Next(0, 255);


fbparam = new FbParameter("@sha", FbDbType. Binary); <-
PROBLEM LINE
fbparam.Value = sha.ComputeHash(data);
fbcommand.Parameters.Add(fbparam);

fbparam = new FbParameter("@size", FbDbType.BigInt);
fbparam.Value = data.Length;
fbcommand.Parameters.Add(fbparam);

fbparam = new FbParameter("@content", FbDbType.Binary);
fbparam.Value = data;
fbcommand.Parameters.Add(fbparam);

try
{
fbconn.Open();
fbtrans = fbconn.BeginTransaction();
fbcommand.Transaction = fbtrans;
fbcommand.ExecuteNonQuery();
fbtrans.Commit();
}

catch (Exception ex)
{
if (fbtrans != null)
fbtrans.Rollback();

MessageBox.Show(ex.Message);
}

finally
{
fbconn.Close();
}


Am I missing something? If I can not directly store a binary field,
I'll BASE64 the SHA1 value to get a character string and store the
SHA1 as character data. I would however prefer to keep the more
compact binary representation.