• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

equivalent of on_update current_timestamp in MSSQL.

 
Arun Kumarr
Ranch Hand
Posts: 661
Eclipse IDE Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hai,
We just migrated from MySQL to MS-SQL Database(2000). We couldn't find an equivalent for on_update current_timestamp of MySQL in MS-SQL. A trigger was instead written to take care of timestamp change everytime we update the tables. (I don't feel it like the right thing to do. Any alternatives, would be happy to hear that)

Now with this trigger, we are getting the infamous OptimisticLocking and StateState exceptions. I did check the values which is coming to the hibernnate layer and the DB (especially the timestamp and id of the tuple). It seems everything is fine. But I still get the exceptions, everytime I try to delete and sometimes when I try to update. The moment i remove those triggers, it works fine.

Any idea why this is happeneing. Would deeply appreciate any suggestions.
please tell me if you need more information. Should I share the trigger details.
 
Jaikiran Pai
Marshal
Pie
Posts: 10447
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The way we handled this was by letting Hibernate update a timestamp field whenever it updated a row. For this, in our table we had column named Update_Dttm of type timestamp. We mapped this column through Hibernate as a 'timestamp' as follows:



Using this 'timestamp' will let Hibernate update the field whenver it fires a update/insert on the table for a particular row. You must make sure that you never play with this field in your code(never set a value through a code), or else you will start seeing exceptions. Let Hibernate control this field for you. The Hibernate dtd might provide you more details about the timestamp element.
 
Arun Kumarr
Ranch Hand
Posts: 661
Eclipse IDE Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi jaikiran pai,
Thanks for the reply. But we too use <timestamp> mapping for our column last_update_time. It was working fine for MySQL. In MSSQL, it doesn't seem to work. Moreover we don't do any explicit setting of time values for that column when we do a insert or update. We have also tried to have default current_timestamp when creating the table. But Iam searching for an equivalent for on_update current_timestamp in MS-SQL.
[ August 28, 2006: Message edited by: Arun Kumarr ]
 
Jaikiran Pai
Marshal
Pie
Posts: 10447
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In MSSQL, it doesn't seem to work


Whats the difference that you are seeing in MSSQL? Any exceptions or any other behavior?
 
Arun Kumarr
Ranch Hand
Posts: 661
Eclipse IDE Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yupp. OptimisticLockingException and StaleStateException.
 
Arun Kumarr
Ranch Hand
Posts: 661
Eclipse IDE Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Iam using the same thread, as I find this somewhat related.
Without the triggers I mentioned, Iam able to update the database tuples now, but the problem is the last_update_time is picked up from the middle tier and not the database time. everytime i update i update an object the middle-tier's time sits in the last_update_column and not the database server's time. Any thoughts. would deeply appreciate it.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic