-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 
Author Message
 Post subject: Hibernate MS SQL server timestamp mapping
PostPosted: Mon Sep 27, 2004 10:29 am 
Newbie

Joined: Mon Sep 27, 2004 9:40 am
Posts: 11
Location: Ostrava, Czech Republic
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


Top
 Profile  
 
 Post subject:
PostPosted: Mon Sep 27, 2004 10:44 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
UserType


Top
 Profile  
 
 Post subject:
PostPosted: Mon Sep 27, 2004 10:55 am 
Newbie

Joined: Mon Sep 27, 2004 9:40 am
Posts: 11
Location: Ostrava, Czech Republic
Can you please be more specific?


Top
 Profile  
 
 Post subject:
PostPosted: Mon Sep 27, 2004 10:56 am 
Hibernate Team
Hibernate Team

Joined: Thu Dec 18, 2003 9:55 am
Posts: 1977
Location: France
http://www.hibernate.org/100.html

_________________
Anthony,
Get value thanks to your skills: http://www.redhat.com/certification


Top
 Profile  
 
 Post subject:
PostPosted: Mon Sep 27, 2004 11:28 am 
Newbie

Joined: Mon Sep 27, 2004 9:40 am
Posts: 11
Location: Ostrava, Czech Republic
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


Top
 Profile  
 
 Post subject:
PostPosted: Mon Sep 27, 2004 5:13 pm 
Senior
Senior

Joined: Sun Jan 04, 2004 2:46 pm
Posts: 147
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.


Top
 Profile  
 
 Post subject: MSSQL Timestamp solution
PostPosted: Wed Oct 13, 2004 6:49 am 
Newbie

Joined: Mon Sep 27, 2004 9:40 am
Posts: 11
Location: Ostrava, Czech Republic
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


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.