Support of server date in NHibernate

[pdesai84]

Currently, there is no direct way to get server date (in Oracle, SYSDATE) using NHibernate in a single query. We have to first execute HQL query to get current server date and then use that date for audit like InsertDate or ChangeDate column. We must optimized it to generate query like:

Insert Into TEST_TABLE (TT_KEY, TT_CODE, TT_ChangeDate, TT_ChangedBy) Values (SEQ_TABLE.NEXTVAL, 'CODE1', SYSDATE, 'X0SA2');

Update TEST_TABLE
Set TT_ChangeDate = Sysdate
Where TT_Key = 123;

Currently, we cannot generate SQL statement like this to use SYSDATE within the Insert or Update SQL statement generated by NHibernate.

[hazzik]

Yes, you can.

[fredericDelaporte]

I would not use the ORM for this, but a db-side default value for the insert date (configurable through NHibernate mappings if you use NHibernate ddl), and a trigger in the database for the update date.

I do not know of a way to do what you want. The nearest I know is the timestamp property configured with its source as db, but that would just delegate to NHibernate the additional select in database for getting the current timestamp, while, at the same time, enabling optimistic updates.

[pdesai84]

Hi Frederic,

Thank you for your prompt response and for taking the time to clarify my questions. I truly appreciate your insights.

I've been researching this for the past month without finding a satisfactory solution, which is why I decided to reach out to your team. Given your deep knowledge and expertise of NHibernate, I felt your guidance would be more valuable than what I’ve encountered so far

Most sources suggest a similar approach. However, while it would be manageable for a small number of tables, our database contains over 1,500 tables—specifically, 1,877. Given this, neither my DBA nor I are comfortable with the idea of creating such a large number of triggers.

We use FluentNHibernate for mapping, and while I could implement the solution, I cannot maintain two separate approaches—one for InsertDate and another for ChangeDate. Both need to consistently use either the server's datetime or the client's datetime.

If you're suggesting that this is not possible, then I will continue using the client’s machine datetime for now. However, I would greatly appreciate it if NHibernate could support server-side datetime through mapping in the future.

Thank you.

[fredericDelaporte]

You may also define custom sql inserts and updates in your mappings. But defining them for all of your entities is likely to be quite a burden.

I think you would need a non-existent feature: something like update-formula and insert-formula on properties. formula allows to define custom SQL for reading the property. update-formula and insert-formula would allow to define custom SQL for updating/inserting the value. Not sure it would get implemented.

[pdesai84]

I have tried with the formula. But when I set the formula on the property, we cannot specify the column name, it throws an exception. Without defining the column name, the property becomes read-only and that too only gives current datetime, and won't read the saved dates from the database.

I thought of defining a custom insert and update, but it is too much. Given the size of our database, and some of the tables even have more than 50 columns, it will be a nightmare to maintain it.

It would have been better if NHibernate had supported allowing users to define server date formula and use it for insert and update commands just like how Sequence is implemented.

[pdesai84]

Also, I would suggest opening this case as part of the idea and discussion so that in the future this can be implemented. Like us, many other users would want to use this feature in a centralized architectural design.