Hi,

I have trouble with mapping SQL type TIMESTAMP to java.sql.Timestamp. I use Microsoft SQL Server 2000 and driver I-Net software OPTA2000. On driver's pages (http://www.inetsoftware.de/English/prod ... efault.htm) in FAQ section there's question if it's possible to get a java timestamp from a timestamp column of the SQL server. The answer is no, because it is a binary number (8 byte). The only way to read it is with the methods getBytes, getString or getBinaryStream.
And here comes my problem. In plain JDBC I have found a workaround by getting not the timestamp column itself, but int value as the result of convert(int, timestampColumnName). Now I need to map it using Hibernate somehow. At the moment a use the following:

<timestamp name="appPropertyName" column="tblColName" />

I think this is just fine but this mapping in default creates java.sql.Timestamp, which generates the following exception:
java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss.fffffffff
at java.sql.Timestamp.valueOf(Timestamp.java:164)
at com.inet.tds.j.getTimestamp(Unknown Source)
at com.inet.tds.j.getTimestamp(Unknown Source)
at net.sf.hibernate.type.TimestampType.get(TimestampType.java:22)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.type.AbstractType.hydrate(AbstractType.java:66)
at net.sf.hibernate.loader.Loader.hydrate(Loader.java:611)
at net.sf.hibernate.loader.Loader.loadFromResultSet(Loader.java:552)
at net.sf.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:511)
at net.sf.hibernate.loader.Loader.getRow(Loader.java:426)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:209)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:836)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:856)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:59)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:51)
at net.sf.hibernate.persister.EntityPersister.load(EntityPersister.java:419)
at net.sf.hibernate.impl.SessionImpl.doLoad(SessionImpl.java:2113)
at net.sf.hibernate.impl.SessionImpl.doLoadByClass(SessionImpl.java:1987)
at net.sf.hibernate.impl.SessionImpl.get(SessionImpl.java:1923)
....

This is propably normal situation because as I stated in the beginning of the post, it is probably impossible to get a java timestamp from a timestamp column of the SQL server.
What I would like to know is if there is any possibility to for example map the timestamp column as long or date and say Hibernate not to read the timestamp column directly but through the result of the convert function. I need the timestamp behaviour, I can't use versioning, because I share the database with other applications.
Currently I use Hibernate 2.1.6 version with Spring 1.1 and XDoclet 1.2.2 for mappings generation.

I would very appreciate any help or ideas.

Pavel

UserType

Can you please be more specific?

It looks nice for property mapping. If I try the same for timestamp mapping I get exception:

org.xml.sax.SAXParseException: Attribute "type" is not declared for element "timestamp"

I need the property mapped as timestamp, so it's further included in update, delete statements. Unfortunatelly tag <timestamp> does not allow type attribute.

Is that correct or am I missing something?

Pavel

Could you use the <version> tag instead of the <timestamp> one as this allows a type to be specified? Then just create a custom user type ( extend UserType ) and make it implement the VersionType interface to support the versioning functionality for hibernate.

In case someone had to face the same problem, this is a way how I solved it for my purposes:

There's really no way to read the binary MSSQL Timestamp using Hibernate timestamp mapping.
If you are starting a new projects, don't use a Timestamp columns in your tables, have Int columns and map it using Hibernate version tag.
If you are in situation as me and already have an environment, where you have to share the database tables with some legacy applications that use Timestamp columns, and you will be as lucky as me and it will be possible for you to add your columns to tables, you can add an Int version column to the tables you need to write to and map it with Hibernate Version tag. To ensure concurrency access to those table, you need to use trigger, which does nothing if you save a record, and increases version in case some legacy application does the change. The detection of trigger if it's you or legacy application saving is easy - it's you if old and new record versions in trigger are different, it's a legacy application if they are the same.
This is how it works for me. However, we are still in development phase so I have no idea how much the triggers for versioning will affect performance in real life. So far, it's OK. But in my database there already were some triggers doing some magic, so I think if there's one more action for the trigger it's not such a big deal. If I will find out that I'm wrong now and trigger versioning slows it down I will post a new solution.

BR,
Pavel