Changing a date property with an update query

Hi,

I'm trying to update a date property with an UPDATE DQL query, but the result is always a nulldate value.

For instance:

DQL> update dm_document objects set document_date = date('06/25/2010 00:00:00','mm/dd/yyyy hh:mm:ss') where r_object_id = '0900372380018c80'...objects_updated1

Now, when I dump the object, here's what I get;

dump,c,0900372380018c80...document_date                    : nulldate...

I've tried a number of permutations in the date format ('dd/mm/yyyy', 'dd-mon-yyyy'...), but it's always the same result. In Documentum Administrator, I can update the property without any problem.

Does anybody have a suggestion?

Thanks!

Raph

Find more posts tagged with

Documentum

Comments

JorgKrause

Hi,

you're using an invalid pattern

update dm_document objects

set document_date = date('06/25/2010 00:00:00','mm/dd/yyyy hh:mi:ss')

where r_object_id = '0900372380018c80'

should work better

Regards

Jørg

mrgou

Thanks. Same thing, though. I have tried a number of patterns, including with only the date but not the time.

Getting the SQL displayed when running the query, I noticed that this is performed by inserting a dmi_queue_item row. Is there any easy way I can retrieve the result of the queue item processing and see what went wrong, or do I have to dig into the database?

Thanks!

JorgKrause

Hmmh,

that's strange :-(

I tried following queries here

update dm_document objectsset a_last_review_date=date('06/25/2010 12:13:14','mm/dd/yyyy hh:mi:ss')where r_object_id = '09xxxxxxxxxxx'update dm_document objectsset a_last_review_date=date('06/25/2010','mm/dd/yyyy')where r_object_id = '09xxxxxxxxxxx'update dm_document objectsset a_last_review_date=date('nulldate')where r_object_id = '09xxxxxxxxxxx'

All return rows_updated 1, and all of them update the date attribute the way I want it to be.

Anyway, this isn't the query you're running, isn't it? dm_document doesn't have attribute document_date, as far as I remember.

Regards

Jørg

mrgou

Not litterally indeed. For confidentiality reasons, I'd rather not give indications on my company's data model. It's actually a custom property of a subtype of dm_document.

I've brought this up to the DBA. I've had strange behaviors caused before by the underlying DBMS. If we can track down what the content server really tries to do on the database, maybe we can figure out what's wrong.

I'll share any finding with the community.

Raph

JorgKrause

Ok, that's what I thought

Anyway, you could try to update the a_last_review_date with these quueries, just to check that syntax is correct.

I think I've seen some trouble updating custom date attributes thru DQL earlier, but I don't recall the reason and/or solution.

I'll try to find them later today.

Regards

Jørg

bacham2

My guess is that what Joerg wrote is working but your test to see if it worked is flawed. I'm not sure which tools you are using for running DQL and performing the dump, but be aware that API calls typically work off the DFC cache while DQL goes straight to the Content Server. So if you fetch your object, say in an iapi session, if you dump it repeatedly you will always see the same thing, even if you ran a DQL update against it.

What you need to do is a fetch (fetch,c,<objid>) of the object before you dump it again to see the change.

Furthermore, if any dmi_queue_item objects are created it's because some users registered for notifications (typically dm_fulltext_index_user and perhaps other users). But this is irrelevant for your problem.