Problem with mapping BinaryBlob type for SQL Server Compact Edition

[Artur]

Hi,

I'm very new to NHibernate, so I might be hitting a known problem, but
I searched for solution and couldn't find anything helpful.

I want to use NHibernate (2.0 GA) together with SQL Server Compact
Edition (3.5 SP1). I need to store some images in the database so
adequate sql data type is Image and, as I understand, on NHibernate
side it should be mapped as BinaryBlob, and type of property in my
model should be byte[].

Initially, the relevant part of my mapping was:

<property name="Data" type="BinaryBlob" not-null="true"/>

but SchemaExport (from NHibernate.Tools.hbm2ddl) produced a
varbinary(4000) column from it. That was first sign that something is
wrong.
Later I changed the mapping and forced sql Image type with this:
<property name="Data" type="BinaryBlob">
<column name="Data" sql-type="Image" not-null="true"/>
</property>

That produced correct schema, but I can't persist anything large to
that column, because I get an exception saying: "byte
array truncation to a length of 8000". That looks like NHibernate is
setting wrong type for SQL query parameter.

Am I doing something wrong or can it be some bug in NH?

Kind regards
Artur

[Ayende Rahien]

I think you may want to set the length, since NH will automatically assign that, IIRC.
Please create a test case for this if setting the length doesn't work.

[Artur]

On 11 Wrz, 04:31, "Ayende Rahien" <aye...@ayende.com> wrote:
> I think you may want to set the length, since NH will automatically assign
> that, IIRC.

I tried setting length before, but I failed and didn't look much into
it.
Now I tested it more consistently and it actually helps for producing
the schema I want (and I don't need column element with sql-type
attribute), but not for the second problem.

I've found out that BinaryBlob is mapped to VARBINARY(n) or IMAGE
depending on specified length. If length is bigger than maximum for
VARBINARY, than it switches to IMAGE. It's consistent with e.g.
MsSql2005Dialect (where it switches to VARBINARY(MAX), since IMAGE is
deprecated), but I don't understand why it can't be IMAGE type all the
time (with length ignored), especially when there is a separate Binary
type in NH.

I think I have found a small problem with maximum for VARBINARY for
MsSqlCeDialect. Apparently it is set to 4000, however SQL Server CE
docs say it is 8000.
I created this test, based on what I found NH sources:

[Test]
public void BinaryBlob_mapping_to_SqlCe_types()
{
Dialect dialect = new MsSqlCeDialect();
SimpleValue sv = new SimpleValue();
sv.TypeName = NHibernateUtil.BinaryBlob.Name;
Column column = new Column();
column.Value = sv;

// no length, should produce maximum
Assert.AreEqual("VARBINARY(8000)", column.GetSqlType(dialect, null));

// maximum varbinary length is 8000
column.Length = 8000;
Assert.AreEqual("VARBINARY(8000)", column.GetSqlType( dialect,
null));

column.Length = 8001;
Assert.AreEqual("IMAGE", column.GetSqlType( dialect, null));
}

First two asserts fail.

> Please create a test case for this if setting the length doesn't work.

I'm not sure how that test case would need to look like. I don't know
NH internals, so I really can't produce proper unit tests for this.
I can create something bigger, with separate hbm.xml mapping &
configuration file and code.
Would it be OK?

Thanks
Artur

[Ross McEwan]

Hi,
I had a similar issue - though with NText columns in SqlCE 3.5.
See my post here : http://groups.google.com/group/nhusers/browse_thread/thread/47b8d0da67da9fcc?hl=en
There's some details in there of how I attempted to fix it, as well as
proposed fixes from MS forums - but nothing that helps too much.
I hope you come right.
We're implementing a work-around for this now - taken way too long
trying to get it right.
Regards,
Ross

[Artur]

On 11 Wrz, 12:30, Ross McEwan <rossmce...@gmail.com> wrote:
> Hi,
> I had a similar issue - though with NText columns in SqlCE 3.5.

> See my post here :http://groups.google.com/group/nhusers/browse_thread/thread/47b8d0da6...

> There's some details in there of how I attempted to fix it, as well as
> proposed fixes from MS forums - but nothing that helps too much.
> I hope you come right.

I'm pretty sure I read your post when searching for some hints and I
definitely read the post on MSDN forums you link to. But, as you note,
that doesn't help much.
There is, however, reference to SQL CE readme file that states that
"SQL Server Compact 3.5 SP1 data types for parameters like SqlDbType
or DbType should be explicitly set ... This is critical in case of
BLOB data types (image and ntext)." There is some code example as
well.
Unfortunately, that doesn't tell me much.

[Ayende Rahien]

Thanks for reporting this.
Your test now passes on the trunk.

[Artur]

On 13 Wrz, 08:36, "Ayende Rahien" <aye...@ayende.com> wrote:
> Thanks for reporting this.
> Your test now passes on the trunk.

Thanks Ayende, that's nice to hear.

Can I still trouble you with the other problem (array truncation)?
I uploaded a very minimalistic, standalone VS 2008 solution to:
http://sites.google.com/a/dorochowicz.com/artur-public-files/files-1/ImageTest.zip
It's enough to build it and run it and it consistently shows the
problem.

Will be grateful for any insight from you on this, but if you don't
feel like looking into it, just ignore this e-mail.

Thanks again
Artur

[Artur]

OK, I have a workaround for this bug. It works for me.

Create a new driver and make it derive from actual SqlServerCeDriver
like so:

using System.Data;
using System.Data.SqlServerCe;
using NHibernate.SqlTypes;

namespace MyNamespace
{
public class SqlServerCeDriver : NHibernate.Driver.SqlServerCeDriver
{
protected override void InitializeParameter(IDbDataParameter
dbParam, string name, SqlType sqlType)
{
base.InitializeParameter(dbParam, name, sqlType);

if (sqlType is BinarySqlType)
{
var parameter = (SqlCeParameter) dbParam;
parameter.SqlDbType = SqlDbType.Image;
}
}
}
}

Use this driver instead of NH's one.
What I do here is I override every BinarySqlType parameter with
SqlDbType.Image type.
I can get away with it, because in my schema I have no ordinary
VARBINARY columns, only IMAGE columns. (And of course I reference
SqlServerCe assembly in my project, NHibernate avoids that with
reflection)

Problems (bugs?) on NH's side:
- sqlType is BinarySqlType and not BinaryBlobSqlType even though the
associated property is defined as BinaryBlob in hbm.xml mapping file.
- sqlType has no length defined (Length==0, LengthDefined==false)
even though it has length defined in mapping file.


Hope this will help somebody.

Regards
Artur

[Ayende Rahien]

I investigated it a bit more, and it looks like there is no way of doing this without creating a separate dialect for SQL CE that depends on SQL CE.
Since we don't want NHibernate to have a dependency on SQL CE, we need a new project for that. Would you be interested in turning this into one of our Contrib projects?

[Artur Dorochowicz]

At this point I don't really know what would need to be delivered or
how that would need to differ from current implementation in NH.
But with some guidance and instructions I can definitely give it a
try.

[Ayende Rahien]

Basically, it is taking the existing SqlCeDriver and moving it (and the dialect) into their own project, which will allow to fix this bug and create a set of tests that run against the dialect.

[Artur Dorochowicz]

I'm sorry, but you will need to be more explicit.
I just don't see how playing with dialect or driver could correct the
problem. My earlier code is not a fix for a bug, it's just a
workaround that happens to work in my case.
I also checked with SqlClientDriver and the actual problem IMHO is
that arguments that NH feeds into IDriver.GenerateCommand(...
SqlType[] parameterTypes) are too generic (BinaryBlob becomes Binary,
StringClob becomes String, no lengths are set). I'm probably missing
something, but without more specific information driver cannot
reliably set command parameter types. And frankly saying, it really
surprises me that that happens to work OK for drivers included in NH
(well, not for SqlServerCe driver).

[Ayende Rahien]

The issue is that for most drivers, length really doesn't matter for the type you are using. Those are orthogonal concepts.

The problem with the scenario you outline is that for SQL CE, it _still_ doesn't matter, but you have to set a SQL CE value as the param type to get it to work.

That is why I think that taking your code and spinning it into a full driver project would be the best thing. That driver project could take a dependency on the SQL CE assembly and use it

[Artur Dorochowicz]

OK, I get it now.

I just thought about this solution below. It would save us from
creating a new project.

using System.Reflection;
...

protected override void InitializeParameter(IDbDataParameter dbParam,
string name, SqlType sqlType)
{
base.InitializeParameter( dbParam, name, sqlType );

if( sqlType is BinarySqlType )
{

PropertyInfo property =
dbParam.GetType().GetProperty( "SqlDbType" );
property.SetValue( dbParam, SqlDbType.Image, null );
}
else if( sqlType is StringSqlType )
{
PropertyInfo property = dbParam.GetType().GetProperty("SqlDbType");
property.SetValue( dbParam, SqlDbType.NText, null );
}
}

PropertyInfo creation could of course be moved outside and done once
for the lifetime of the driver.

If that's not acceptable then how do I go about contributing to
NHContrib project?

On 27 Wrz, 21:56, "Ayende Rahien" <aye...@ayende.com> wrote:
> The issue is that for most drivers, length really doesn't matter for the

> type you are using. Those are orthogonal concepts.The problem with the

[Ayende Rahien]

I think that a cleaner solution would be a separate project, but that would work as well.
Can you create a patch for those?

[Artur Dorochowicz]

Patch is here:
http://sites.google.com/a/dorochowicz.com/artur-public-files/files-1/SqlServerCeDriver.cs.patch
That's against the trunk.

That should close this issue, I think. Thanks for your help Ayende.

[Ayende Rahien]

Please take the reflection code outside of the init method, that shouldn't be there.

[Artur Dorochowicz]

Here you go:
http://sites.google.com/a/dorochowicz.com/artur-public-files/files-1/SqlServerCeDriver.cs.patch

[Ayende Rahien]

Applied, thanks.

[Artur Dorochowicz]

Sorry Ayende, but you may just need to revoke that patch.
(I was kind of hoping that you did run the NH test suite on it, I
didn't)

I just got this nice exception when querying on what is an nvarchar
column in the database:
{"The ntext and image data types cannot be used in WHERE, HAVING,
GROUP BY, ON, or IN clauses, except when these data types are used
with the LIKE or IS NULL predicates."} System.Exception
{System.Data.SqlServerCe.SqlCeException}

That, I guess, means that a separate project won't do any good either
and eventually NH will need to feed something more specific to
GenerateCommand.


On 29 Wrz, 19:25, "Ayende Rahien" <aye...@ayende.com> wrote:
> Applied, thanks.

[Ayende Rahien]

Sigh, I run the tests, with the wrong dialect :-(
Reverted.