ansaurus

Question

How do you get a NOW() value in C# using the MySQL Connector?

Answer 1

A:

The usual way is to set a LastUpdated field in the database, either in the stored procedure or in a trigger. Then you can read it back.

Steven Sudit 2010-08-01 23:34:57

Answer 2

+1 A:

To my knowledge, MySQL doesn't have functionality like Oracle's RETURNING or SQL Server's OUTPUT clause to be able to save a query by returning values from INSERT/UPDATE statements. So that means two statements minimum...

is there in any way possible to get the exact MySQL server time which the update query was executed?

The best I can think of is to define an audit column (they were standard approach at my previous work) for logging the timestamp when the record was updated. In MySQL, you can default the value so on update it is set to the timestamp value at that time:

ALTER TABLE your_table 
  ADD COLUMN update_timestamp TIMESTAMP NOT NULL DEFAULT ON UPDATE CURRENT_TIMESTAMP

...then this gives you a specific column value to query.

OMG Ponies 2010-08-01 23:35:18

My only concern here is that I believe it may allow specifying a value for the column, in which case its auditing function might be compromised.

Steven Sudit 2010-08-02 00:59:27

@Steven Sudit: Which is another reason why I recommend interaction via stored procedure, and restricting direct database access...

OMG Ponies 2010-08-02 01:02:17

Agreed that the easiest (and probably best) way to handle this is by blocking all but SP access, and even then, limiting it by role. If the table has to be exposed, then UPDATE and INSERT triggers would be needed.

Steven Sudit 2010-08-02 01:57:50

ansaurus

tags:

views:

answers:

How do you get a NOW() value in C# using the MySQL Connector?

related questions