Hi,

I've got a small problem with a mapping file containing a column of type binary. I'm using an application called XPlanner which uses Hibernate 1.2 (AFAIK). I'm trying to get XPlanner/Hibernate to work with MS-SqlServer2000 and SqlServer2000 only supports binary-columns with a max size of 8000 while XPlanner calls for 1GB for this specific column.

Large columns are supported in SqlServer2000 using one of the following column types: ntext (unicode), text and image. ntext and text maps to the following JDBC-datatype: LONGVARCHAR and image maps to LONGVARBINARY.

So I've tried changing the column type in the mapping file from binary to text and also tried changing it from binary to longvarbinary. When I run an ant build script called install.db.schema provided by XPlanner I get the following error (both when using text and longvarbinary):

[java] 546 [main] INFO cirrus.hibernate.impl.DatastoreImpl - Mapping file: C:\javaapps\xplanner_0.5.2\xplanner\resources\mappings\Note.xml
[java] 593 [main] ERROR cirrus.hibernate.impl.DatastoreImpl - Could not compile the mapping document
[java] cirrus.hibernate.MappingException: Could not interpret type: longvarbinary
[java] at cirrus.hibernate.map.Value.getTypeFromXML(Value.java:174)
[java] at cirrus.hibernate.map.Value.typeFromXML(Value.java:154)
[java] at cirrus.hibernate.map.Value.<init>(Value.java:71)
[java] at cirrus.hibernate.map.Value.<init>(Value.java:128)
[java] at cirrus.hibernate.map.PersistentClass.propertiesFromXML(PersistentClass.java:82)
[java] at cirrus.hibernate.map.RootClass.<init>(RootClass.java:239)
[java] at cirrus.hibernate.map.Root.<init>(Root.java:145)
[java] at cirrus.hibernate.impl.DatastoreImpl.store(DatastoreImpl.java:103)
[java] at cirrus.hibernate.impl.DatastoreImpl.storeFile(DatastoreImpl.java:68)
[java] Error creating schema Could not interpret type: longvarbinary
[java] cirrus.hibernate.MappingException: Could not interpret type: longvarbinary
[java] at cirrus.hibernate.map.Value.getTypeFromXML(Value.java:174)
[java] at cirrus.hibernate.map.Value.typeFromXML(Value.java:154)
[java] at cirrus.hibernate.map.Value.<init>(Value.java:71)
[java] at cirrus.hibernate.map.Value.<init>(Value.java:128)
[java] at cirrus.hibernate.tools.SchemaExport.main(SchemaExport.java:259)

Any suggestions how to solve this would be highly appreciated as things have sort of ground to a halt due to the problem above.

--norgie

Don't change the mapping type, but the "sqltype" of the column?

I'm completely and utterly a newbie regarding Hibernate so please have patience with me.

This is the original line in the mapping file

<property name="file" type="binary" length="1000000000"/>

What I've tried to do is to change 'type=binary' to e.g. 'type=text', 'type=image' or 'type=longvarbinary' to no avail.

When you say change the 'slqtype' of the column, do you mean in the physical database or are you referring to an argument to be added to the <property .../> line above? Or something else?

-- norgie

Try:

<property name="file" type="binary" sql-type="IMAGE" length="1000000000"/>

or ask the author of xplanner which SQL type you should use for MS SQL server. The default SQL type for the specific database dialect can be found in the Hibernate dialect source, but I'd need the exact Hibernate version to look that up.

>> <property name="file" type="binary" sql-type="IMAGE" length="1000000000"/>

This is great. I'll try this and see if it works.

--norgie

Actually it should be:




(or perhaps sql-type="IMAGE(1000000000)")

Thanks Gavin. It worked like a charm!"

Only problem now is that 'file' is a reserved word in SqlServer, so the create table script for the table containing this column is rejected.

Would it be possible to do something like this?

<property name="file" type="binary" length="1000000000">
<column name="thefile" sql-type="IMAGE" />
</property>

I.e. define a mapping between the property 'file' and the column it refers to? If so, that would solve problems pertaining to other reserved words in SqlServer, e.g. 'name'.

--norgie

Sure, just have a look at the DTD and see what is allowed for <property> and <column>. It's pretty much self explanatory.