[Solved] Cannot retrieve a timestamp from a MySQL table

[madods]

I've downloaded and installed FPC 2.6.0 and Lazarus 1.0.2.

I'm using the SQLdb controls to access a MySQL database OK, but I can't retrieve a timestamp field from a table.

I can do "select firstname from members where id='365'" OK, but "select updated from members where id='365'" fails with a error dialog:

"" is an invalid integer

I'm using a TMySQL51Connection control to connect to the database, and the libmysql.dll is 5.1.59. I've also tried libmysql 5.1.41

In the short term I can work around the problem by selecting the field I need rather than *, but I will eventually need to retrieve the 'updated' timestamp.

Can anyone help?

[BigChimp]

Hi madods,

Thanks for explaining your problem well; you may want to explicitly say you're on Windows (and the version) next time in case it matters. You could put that version information in a forum signature if you want to.

0. I don't like mysql as you constantly have to keep track of matching dll versions with connectors, I have the feeling the mysql platform/support/ubiquity is disintegrating and I like other dbs more  - hint: look at Firebird or PostgreSQL if you want to switch. However, some thoughts below.

1. Always try these SQL commands in a query tool such as phpmyadmin, mysql administrator or whatever to see if they are valid SQL. This way you can isolate problems with your code from sql problems.

2. Are you sure id is a varchar/char column? You're quoting it as if it is a text type column, while I would suspect an integer column. Instead of

Code: [Select]

select firstname from members where id='365'code like this may make more sense:

Code: [Select]

select firstname from members where id=365Better still, use parameterized queries, e.g. something like

Code: [Select]

select firstname from members where id=:theidparametersee sqldb tutorial3: http://wiki.lazarus.freepascal.org/SQLdb_Tutorial3#Parameterized_queries)

3. Strange error message. I would expect the first query to fail just as much as the second because the error message pertains to id in the WHERE clause, not the first columns. That said, perhaps the name updated is a reserved word in MySQL SQL and needs to be quoted/escaped - perhaps as `updated` but I'm not sure.

Good luck

[madods]

Thanks BigChimp,

Your comments on MySQL noted, but this is an existing database we want to access. In fact the data was transferred many years ago from an even older DOS based DB, which may account for the id being a varchar instead on an int.

The test queries both work in phpMyAdmin, so "updated" doesn't appear to be a reserved word.

Because the "where" clause is common to both queries, I'm inclined to suspect that the type ("timestamp") of the "updated" field is the problem. For instance, I created a new "updated_s" varchar field in the database and copied the "updated" values into it. This query then runs without error:

Code: [Select]

select updated_s from members where id='365'
In fact the contents of "updated" are "20121211102020" which is indeed an invalid integer.

Is there anyway of forcing Lazarus to treat data from a timestamp field as string or a float instead of an integer?

[madods]

Some further testing:

I created a new table called "ts_test" with two columns: "ts" (type timestamp) and "str" (type varchar) and added a row where both "ts" and "str" contain "20121211110458".

This query runs without error:

Code: [Select]

select str from ts_test
Whereas this query:

Code: [Select]

select ts from ts_test
produces a dialog box with the error:

Code: [Select]

"" is an invalid integer

Press OK to ignore and risk data corruption.
Press Cancel to kill the program.

[madods]

OK, a bit more progress - it seems that the contents of a timestamp field have changed.  MySQL 4 stores the stamp in the form yyyymmddhhmmss whereas MySQL 5 stores it as yyyy-mm-dd hh:mm:ss. I guess that the absence of colons and hyphens in the MySQL 4 timestamp means that Lazarus tries to treat it as an integer.

I'm guessing that if I use a TMySQL40Connection it will correctly interpret a timestamp from a MySQL 4 server, however I can't find a v4.0.x version of libmysql.dll to try it with.

Is there any way to force Lazarus to treat data retrieved from a timestamp field as a string rather than an integer? If not, I guess I have to convert the timestamp field to a varchar field, and update it in the "update" statements instead of allowing it to auto-update.

[BigChimp]

Thanks for the background info and nice analysis!

So if the problem is indeed with the mysql library misinterpreting timestamp columns/fields, changing things at the FPC/Lazarus level could be a bit too late.
Perhaps we can force the server to not return timestamp datatypes by something like that:

Code: [Select]

select cast(timestampfield as varchar(25)) from sometable... if that is supported. If CAST is not supported, perhaps there is some function TIMESTAMP2VARCHAR or something.

Perhaps being obvious here, but there might be other issues with a 4.x server and a 5.x driver/connection library. I'd check the release notes/change logs for warnings there to make sure.

[madods]

According to Process Explorer the diailog box containing the "is an invalid integer" error belongs to the compiled .exe. I'll attach an image of it.

I might be able to use this as leverage to force a upgrade to a newer version of MySQL on the server, or a migration to a server with a newer version.

In the mean time, this syntax works as a work-around!

Code: [Select]

select cast(ts as char) as ts from ts_test
Thanks BigChimp for the cast() hint!

[BigChimp]

Glad it works for you!

According to Process Explorer the diailog box containing the "is an invalid integer" error belongs to the compiled .exe. I'll attach an image of it.

I expected as much. Where did you think the error would originate?
1. The MySQL dll and your exe are in the same process (the exe loads the dll dynamically when running)
2. Your code (well, ok, the fpc sqldb code) calls the MySQL dll with your SQL which converts this to the MySQL on the wire protocol and passes it on to the server then gets back an error code. The DLL passes on this error code to the sqldb FPC code which passes it on to your application code. Result: your application generates an error message.
Alternatively, the dll errors out on the conversion step before sending stuff to the server... same end result though.

[madods]

I wondered if Process Explorer could tell the difference between an exe and a dll it loaded. Apparnetly not.

I guess we could determine if its the dll by loading something like the MySQL admin module and retrieving the timestamp field. If it was the dll that should fail also.

Even if it is an FPC/Lazarus problem, I can't see the code being changed to allow access to a MySQL version that's no longer supported by MySQL. So I guess I've gone as far as I can. Annoying that I can't use "select * ..." but such is life.

Thanks for your help. I'll go and work out how to mark this as "solved".

[Lacak2]

Please tellus what sqlDB component do you use and what is version of MySQL server you are connected to?
(with TMySQL51Connection and MySQL 5.1 Server it works for me)

[madods]

Yes Lacak2,

You'll see from my testing described in earlier posts that the v5.1 connector & libmysql.dll works fine with MySQL server 5 for me also. Its a v4 server and the old yyyymmddhhmmss format timestamp that's the problem.

[BigChimp]

Sorry I got back so late... have been busy.

I guess we could determine if its the dll by loading something like the MySQL admin module and retrieving the timestamp field. If it was the dll that should fail also.

Exactly. Trying something like mysql administrator with the offending dlls in its directory may indicate the problem lies with the dll/server.

Even if it is an FPC/Lazarus problem, I can't see the code being changed to allow access to a MySQL version that's no longer supported by MySQL. So I guess I've gone as far as I can. Annoying that I can't use "select * ..." but such is life.

Yep. Well, of course you're free to provide a patch if it really is an FPC problem but I'd think you'd first need to demonstrate that. That patch would end up in the trunk/dev version of FPC so you'd have to wait for a backport or recompile FPC stable your patch etc. Not a very quick process either. Doable but slow..
Edit: to clarify: it would be a patch that checked use of a 5.x client with a 4.x unsupported server, as you said. On second thoughts, the chances of such a patch ending up in FPC are quite small. Apart from that I really suspect the problem lies with the 5.x client driver<>4.x server anyway.

Thanks for your help. I'll go and work out how to mark this as "solved".

My pleasure. You can edit your first post and e.g. put [SOLVED] in the topic title.