BreakPhreak BreakPhreak - 4 months ago 11
SQL Question

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

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

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.