-->
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.  [ 9 posts ] 
Author Message
 Post subject: INSERT|UPDATE of time column using server side funcs?
PostPosted: Fri Aug 26, 2005 1:32 am 
Regular
Regular

Joined: Sun May 08, 2005 2:48 am
Posts: 118
Location: United Kingdom
I have a bunch of code that whose database modifications (INSERT/UPDATE) don't use a generated timestamp by the database client application but use the appropiate server side macro for the current time.

I have two basic requirements:

1) Simple use of the CURRENT_TIMESTAMP to fill in one or more column values.

INSERT INTO my_table SET id=1, tstamp=CURRENT_TIMESTAMP(), date=CURRENT_TIMESTAMP();

UPDATE my_table SET tstamp=CURRENT_TIMESTAMP() WHERE id=1


2) More complex use of server side date & time functions to fill in.

INSERT INTO my_table SET id=1, date=SOME_DATE_FUNC(CURRENT_TIMESTAMP(), 1);

UPDATE my_table SET date=SOME_DATE_FUNC(CURRENT_TIMESTAMP(), 1) WHERE id=1;


It's not clear how I would construct such an SQL expression so that the Session.saveOrUpdate() operation can be merged with the a server side expression. In the case of an insert my date column may have a NOT NULL contraint.

The application code I have re-uses prepared statements, which are partial INSERT/UPDATEs (as in: they do not directly assign any value for some columns, picking up the database default value for that column).


The documentation I have found so far:

http://www.hibernate.org/hib_docs/v3/re ... rysql.html

I understand this to allow me to create a named SELECT queries, or call a stored-proc, or replace the default INSERT, UPDATE, DELETE that operates on all columns.


Top
 Profile  
 
 Post subject: Re: INSERT|UPDATE of time column using server side funcs?
PostPosted: Fri Aug 26, 2005 5:09 am 
Expert
Expert

Joined: Mon Jul 04, 2005 5:19 pm
Posts: 720
dlmiles wrote:
INSERT INTO my_table SET id=1, tstamp=CURRENT_TIMESTAMP(), date=CURRENT_TIMESTAMP();

UPDATE my_table SET tstamp=CURRENT_TIMESTAMP() WHERE id=1


Are you using Hibernate ? If so, what happens if you pass these strings as native SQL?


Top
 Profile  
 
 Post subject: Re: INSERT|UPDATE of time column using server side funcs?
PostPosted: Fri Aug 26, 2005 2:29 pm 
Regular
Regular

Joined: Sun May 08, 2005 2:48 am
Posts: 118
Location: United Kingdom
dennisbyrne wrote:
dlmiles wrote:
INSERT INTO my_table SET id=1, tstamp=CURRENT_TIMESTAMP(), date=CURRENT_TIMESTAMP();

UPDATE my_table SET tstamp=CURRENT_TIMESTAMP() WHERE id=1


Are you using Hibernate ? If so, what happens if you pass these strings as native SQL?



Porting JDBC application to Hibernate, the example SQL I quote is how JDBC prepares the data that I want Hibernate to mimic.

To clarify for me: Are you saying if I use native statements with the ":id" or "?" style substituions and pass myObject.getSomeProperty() data as the parameter, then Hibernate will always emit the value data just like it would if I had done with Session.save(myObject) ? I want to confirm there will never be any inconsitancy in the encoding of the data.


How would that work ?

My pseudo-code, I'm expecting my_col_three to subsitite via automatic property getter from the property I specify. Im expecting my_col_four to automatically find the appropiate property to fill in that column from the mapping for that column. Let hibernate do all the work for me, so I make less mistakes with the code.

Code:
Query q = session.createNativeSQL("INSERT myObject SET my_col_one = :id, my_col_two = ?, my_col_three = {myObject.myColThree}, my_col_four = {myObject}");
q.setColumn("id", myObject.getId()); // explicit value
q.setColumn(1, myObject.getMyColTwo());  // explicit value
q.setColumnObject("myObject", myObject); // This give is an instance to work from
q.executeUpdate();


Are seeing where I'm trying to be from this example.


From the 2nd paragraphic of the documentation I found:

"Hibernate3 allows you to specify handwritten SQL (including stored procedures) for all create, update, delete, and load operations."

There is no example of INSERT or UPDATE statements used with parameter passing, could I put a request out for someone to contribute an example here that could possibliy be used to improve the documentation.

Thanks


Top
 Profile  
 
 Post subject:
PostPosted: Sun Aug 28, 2005 7:26 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
with Hibernate 3.1, the easier approach is to simply use the generated timestamp values specifying that hibernate should use the db-generated values:
Code:
<timestamp name="timestamp" column="tstamp" source="db"/>

which uses the database's current timestamp as the value.

another option is to use db-side triggers to assign the values and mark the properties as 'generated':
Code:
<property type="timestamp" name="timestamp" column="tstamp" generated="true"/>

which reads the values back after INSERT/UPDATE operations.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Aug 30, 2005 2:01 am 
Regular
Regular

Joined: Sun May 08, 2005 2:48 am
Posts: 118
Location: United Kingdom
steve wrote:
with Hibernate 3.1, the easier approach is to simply use the generated timestamp values specifying that hibernate should use the db-generated values:


What if I want every possible case from my application within the same table:

* assign NULL
* assign explicitly
* SQL server generated with CURRENT_TIMESTAMP()
* SQL server generated with MY_FOO_DATE_FUNC()



I really would like to see a nuts and bolts interface for partial INSERT/UPDATE operations, this would allow me to use Hibernate for all data operations.

Call it a gloryfied statement builder if you like by based around object mappings / HSQL.

If you have the infrastructure in place to be able to do timestamps in 3.1 and make Hibernate realise that it needs to read back its value if the application touches it, then would it not be possible to extend the same mechnism to any column on a per-execution basis.

Allowing partial INSERTs to read back generated default values.

Allowing partial UPDATEs where the metadata could specify against each column if the SQL server if performing any magic on that column value on every update, or just on NULL assignment, or just on application given function litteral at the next statement execution.



Thinking about it some more, maybe another possible way to implement this API cleanly is from a new API call that could instate a detached object through a proxy. This proxy was able to mark each column as being touched or not.

This would allow the object to become attached during save() or update(), where as my fake peudo-code above would always keep the object detached, since the myObject instance is not magic, its the real concrete version with partially loaded values.

Code:
MyObject obj = hsession.instateDeatchedInstance(MyObject.class);

obj.setMyValueOne(1);
obj.setMyValueTwo(null);

hsession.save(obj);

long id = obj.getId();

// This generates a partial insert like:
// INSERT INTO my_object SET id=IDENTITY, my_value.one=1, my_value_two=NULL;
// even though the table has other columns, maybe like my_value_three

// During this property lookup hibernate would lazy re-load the generated value
System.out.println("my_value_three = " + obj.getMyValueThree());



Thanks for your input, this will sort me out for a number of cases where the value is always assigned with CURRENT_TIMESTAMP().


Top
 Profile  
 
 Post subject:
PostPosted: Tue Aug 30, 2005 2:20 am 
Regular
Regular

Joined: Sun May 08, 2005 2:48 am
Posts: 118
Location: United Kingdom
My another way to do the same thing:

http://forum.hibernate.org/viewtopic.php?t=946712&highlight=#2258043


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 06, 2007 7:56 am 
Newbie

Joined: Tue Mar 06, 2007 7:51 am
Posts: 2
steve wrote:
another option is to use db-side triggers to assign the values and mark the properties as 'generated':
Code:
<property type="timestamp" name="timestamp" column="tstamp" generated="true"/>

which reads the values back after INSERT/UPDATE operations.

I've tried this approach with hsqldb but my timestamp stays null. I do see the select that retrieves the timestamp from the DB after an insert or update but I guess it just returns null.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Mar 07, 2007 7:04 am 
Regular
Regular

Joined: Sun May 08, 2005 2:48 am
Posts: 118
Location: United Kingdom
SylKen wrote:
I've tried this approach with hsqldb but my timestamp stays null. I do see the select that retrieves the timestamp from the DB after an insert or update but I guess it just returns null.


Maybe start a new thread with your problem.

The original request in this thread still remains unsatified. As far as I know Hibernate does not support partial INSERT / UPDATE statements. It does not support a sequece of operations upon the same row:

* Perform partial INSERT (thus picking up default SQL values for those not specified programtically)
* Perform partial UPDATE (only modifying values specified, 'ts_foobar' wasn't set by hibernate in the UPDATE but the SQL server has a programmed side effect to modify the value)
* Perform partial UPDATE (setting column 'ts_foobar' to CURRENT_TIMESTAMP() or any other server side function made up on the fly programatically within hibernate)
* Perform partial UPDATE (setting column 'ts_foobar' to an explicit value from java.sql.Timestamp type, this case is implemented)


The support which Hibernate does have allows me to change the INSERT and UPDATE statment is always uses for objects of that type. The difference in the above is that in order to generate partial INSERT and UPDATE then each statement can be different, dependant upon application usage so a one size fits all would not work.


Top
 Profile  
 
 Post subject: Re: INSERT|UPDATE of time column using server side funcs?
PostPosted: Fri Dec 04, 2009 4:50 am 
Newbie

Joined: Fri Dec 04, 2009 4:42 am
Posts: 5
Hi everyone,

I am using hibernate 2.x and could not get more detailed information on the docs for "timestamp" element. At Hibernate 3.x it has a attribute as "source" and we can say that value could be as "db", however at hibernate 2.x there is not any attribute like that.
If I put a mapping like :

Code:
<timestamp name="insertedTime" column="inserted"></timestamp>

what would I see at the table. A db time or JVM's time?
here is the timestamp reference doc

Code:
<timestamp
  column="timestamp_column" (1)
  name="propertyName" (2)
  access="field|property|ClassName" (3)
  unsaved-value="null|undefined" (4)
/>

also if I could not solve that problem how can I solve with triggers in hibernate 2.x, As I know there is not any attribute "generated" at 2.x
Regards,

_________________
----
Melih
melihbirim.com


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 9 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.