How to map small binary objects properly in SQLite/NHibernate combo (wrong type affinity)?

Question

Trying to store property of C#/.NET type byte[] in SQLite. Here is my mapping:

<class name="MyClass" lazy="false" table="MyTable">
   <property name="MyProperty" type ="BinaryBlob" access="property" />
</class>

In SQL server it works like a charm even without the explicit type="BinaryBlob" in the mapping. In SQLite I've tried various types' combinations between SQL CREATE TABLE statements and in NHibernate data types, but each time getting either an exception that "the mapping can't be compiled" (because of type incompatibility) or an exception that a cast from the fetched datatype to the mapping type is impossible.

The value of MyProperty in insert statement looks like this: 0x7C87541FD3F3EF5016E12D411900C87A6046A8E8.

Update: continuing to debug System.Data.SQLite.SQLiteDataReader - looks like no matter what SQL type is (tried decimal, blob, unsigned big int) - the type affinity is always text.

What am I doing wrong, please (either technically or in general)? Any suggestion is welcomed.

Answer 1

Did you look at: How do i store and retrieve a blob from sqlite? There is an article on ayende.com as well here: Lazy loading BLOBS and the like in NHibernate. These links might help push you in the right direction to see what is going on.

Answer 2

The reason for text affinity was that the data was imported into a table from CSV (comma-separated values) file. Switching to the SQL file with a proper INSERT statement solved the problem.