1

Delphi XE2 and MySql.

My previous question led to the recommendation that I should be using MySql's native TIMESTAMP datatype to store date/time.

Unfornately, I can't seem to find any coding examples, and I am getting weird results.

Given this table:

mysql> describe test_runs;
+------------------+-------------+------+-----+---------------------+-------+
| Field            | Type        | Null | Key | Default             | Extra |
+------------------+-------------+------+-----+---------------------+-------+
| start_time_stamp | timestamp   | NO   | PRI | 0000-00-00 00:00:00 |       |
| end_time_stamp   | timestamp   | NO   |     | 0000-00-00 00:00:00 |       |
| description      | varchar(64) | NO   |     | NULL                |       |
+------------------+-------------+------+-----+---------------------+-------+
3 rows in set (0.02 sec)

I woudl like to :

  • declare a variable into which I can store the result of SELECT CURRENT_TIMESTAMP - what type should it be? TSQLTimeStamp?
  • insert a row at test start which has start_time_stamp = the variable above
  • and end_time_stamp = some "NULL" value ... "0000-00-00 00:00:00"? Can I use that directly, or do I need to declare a TSQLTimeStamp and set each field to zero? (there doesn't seem to be a TSQLTimeStamp.Clear; - it's a structure, not a class
  • upadte the end_time_stamp when the test completes
  • calcuate the test duration

Can somene please point me at a URL with some Delphi code whcich I can study to see how to do this sort of thing? GINMF.

Community
  • 1
  • 1
Mawg says reinstate Monica
  • 38,334
  • 103
  • 306
  • 551
  • 1
    MySQL has no TSQLTimeStamp type. – Jim Garrison Nov 21 '12 at 07:00
  • 5
    Your schema is flawed, never use a timestamp as primary key... – whosrdaddy Nov 21 '12 at 07:33
  • Can the close vote please xplain why? It is a valid question, with a definite answer, and I can't find anything by googling – Mawg says reinstate Monica Nov 21 '12 at 07:37
  • @JimGarrison +1. But if I declare a column of MySql type "TimeStamp" and retrieve it with "SELECT", which Delphi data type should I store it in? – Mawg says reinstate Monica Nov 21 '12 at 07:39
  • @whosrdaddy +1. In this case, I am sampling data from an external device every 5 seconds. The timestamp per row of measurements is guraanteed to be unique. Or should I just do without a primary key for this table (if so, why)? – Mawg says reinstate Monica Nov 21 '12 at 07:40
  • 2
    there are several reasons not to do this, one could be if you want to link data from this table (normalization), also query performance comes to mind... – whosrdaddy Nov 21 '12 at 08:11
  • 3
    @Mawg it is only guaranteed to be unique with some ifs. if there is no time adjust on sql server machine, if there is no daylight saving switch. Beyond this absolute time is just an abstract depending on an aspect. Why not measure the timespan (in seconds or microseconds) and just store the results. The timestamp will only be the time, the server has stored this result. So your timespan will be as accurate as the test machine can and not depending on something else – Sir Rufo Nov 21 '12 at 11:51

3 Answers3

6

I don't know why you want to hassle around with that TIMESTAMP and why you want to retrieve the CURRENT_TIMESTAMP just to put it back.

And as already stated, it is not a good advice to use a TIMESTAMP field as PRIMARY KEY.

So my suggestion is to use this TABLE SCHEMA

CREATE TABLE `test_runs` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `start_time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `end_time_stamp` timestamp NULL DEFAULT NULL,
    `description` varchar(64) NOT NULL,
    PRIMARY KEY (`id`)
);

Starting a test run is handled by

INSERT INTO test_runs ( description ) VALUES ( :description );
SELECT LAST_INSERT_ID() AS id;

and to finalize the record you simply call

UPDATE test_runs SET end_time_stamp = CURRENT_TIMESTAMP WHERE id = :id
Sir Rufo
  • 18,395
  • 2
  • 39
  • 73
4

just declare a TSQLQuery (or the correct component for the data access layer of your choice), attach it to a valid connection and populate it's SQL property with:

select * from test_runs;

double click on the query to launch it's fields editor and select add all fields from the contextual menu of that editor.

It will create the correct field type, according to the data access layer and driver you're using to access your data.

Once that's done, if you need to use the value in code, usually you do it by using the AsDateTime property of the field, so you just use a plain TDateTime Delphi type and let the database access layer deal with the specific database details to store that field.

For example, if your query object is named qTest and the table field is named start_time_stamp, your Delhi variable associated with that persistent field will be named qTeststart_time_stamp, so you can do something like this:

var
  StartTS: TDateTime;
begin
  qTest.Open;
  StartTS := qTeststart_time_stamp.AsDateTime;
  ShowMessage('start date is ' + DateTimeToStr(StartTS));
end;

If you use dbExpress and are new to it, read A Guide to Using dbExpress in Delphi database applications

jachguate
  • 16,976
  • 3
  • 57
  • 98
  • 1
    Execellent description of a fundamental research method. +1 – alzaimar Nov 21 '12 at 07:33
  • +1 @alzaimar, BUT - I need to be able to retrieve the value, not as `TDateTime` but as something reflecting the MySql datatype `TIMESTAMP` can you confirm that that is `TSQLTimeStamp`? The reason being that I need to 1) at test start get the current MySql timestamp and store it in a Delphi variable then insert a new row with that time for start and NUll/zero for end (how do I do that)? and 2) when the test ends I need to "UPDATE test_runs SET end_time_stamp= WHERE start_time_stamp=". Thanks for trying to help, but your answer does not address this – Mawg says reinstate Monica Nov 21 '12 at 07:55
  • 1
    @Mawg my answer does not address this as this is new to me. Which class is Delphi using for your column after you follow the steps of my question? – jachguate Nov 21 '12 at 08:22
  • @jachguate +1 and my apologies!! Delphi shows it as a `TSQLTimeStamp`. Sorry, I misunderstood; your answer is clear (guess I was loking for code; so, now I will go away & try to code it) – Mawg says reinstate Monica Nov 21 '12 at 08:45
  • 2
    @Mawg - Use something like `var StartTS: TSQLTimeStamp; begin StartTS := qTeststart_time_stamp.AsSQLTimeStamp;` – Gerry Coll Nov 21 '12 at 10:11
  • 1
    @Mawg it have to be [TSQLTimeStampField](http://docwiki.embarcadero.com/Libraries/XE3/en/Data.DB.TSQLTimeStampField). As advised, you can access this data using a plain TDateTime variable, but as Gerry said, you can do it also with a TSQLTimeStamp variable, which is a record as you can see in the documentation. Use the flavor that best suits your style. – jachguate Nov 21 '12 at 14:25
1

I don't know about MySQL, but if the TField subclass generated is a TSQLTimeStampField, you will need to use the type and functions in the SqlTimSt unit (Data.SqlTimSt for XE2+).

You want to declare the local variables as TSQLTimeStamp

uses Data.SQLTmSt....;
....

var
  StartTS: TSQLTimeStamp;
  EndTS: TSQLTimeStamp;
begin
  StartTS := qTeststart_time_stamp.AsSQLTimeStamp;

SQLTmSt also includes functions to convert to and from TSQLTimeStamp, e.g. SQLTimeStampToDateTime and DateTimeToSQLTimeStamp.

P.S. I tend to agree that using a timestamp as a primary key is likely to cause problems. I would tend to use a auto incrementing surrogate key as Sir Rufo suggests.

Gerry Coll
  • 5,867
  • 1
  • 27
  • 36