Hi,

The TMySQLConnection.LoadField method from sqldb/mysql does not fetch the field lengths properly. It uses mysql_fetch_field_direct, but this only returns data about an entire column in your result set. It does not give valid field lengths for fields of variable length. Is should use mysql_fetch_lengths I think (http://dev.mysql.com/doc/mysql/en/mysql-fetch-lengths.html).

Example query causing an access violation:

SELECT FORMAT(myDouble,2) FROM myTable

Actually, the field length reported by mysql_fetch_field_drect is 5592424 in my program.

The MySQLDataType method also uses length information from mysql_fetch_field_direct and pumps this into TFieldDef.Create. I'm not sure about the meaning of the ASize parameter for TFieldDef.Create. What if a string can have arbitrary length?

I need answers to these questions if I want to make a (good) patch I think.

Greetings,

Bram

The TMySQLConnection.LoadField method from sqldb/mysql does not fetch the field lengths properly. It uses mysql_fetch_field_direct, but this only returns data about an entire column in your result set. It does not give valid field lengths for fields of variable length. Is should use mysql_fetch_lengths I think (http://dev.mysql.com/doc/mysql/en/mysql-fetch-lengths.html).

I don't know how the MySQLConnection works internally. But the LoadField method should load the data from the underlying DB in it's native format, and convert that to the format expected by the fieldtype, and place that value in the buffer.

So I think this should be adjusted. But take care for the convertion to the right data-type.

The MySQLDataType method also uses length information from mysql_fetch_field_direct and pumps this into TFieldDef.Create. I'm not sure about the meaning of the ASize parameter for TFieldDef.Create. What if a string can have arbitrary length?

Look in the Delphi-help for the meaning of TField.Size. It's different for all sort of fields. For string-fields, it's the maximum amount of characters in a string. For ftBCD it's the amount of digits after the decimal place...

The size of the buffer associated with the field, is given in field.datasize.

If a string-field is created, aSize is used to set the datasize. But a ftInteger-field doesn't do anything with it. It's datasize is always set to 4.

I need answers to these questions if I want to make a (good) patch I think.

If you need more help, let me know. (Or else, send in the patch!)

Joost van der Sluis wrote

If you need more help, let me know. (Or else, send in the patch!)

I've tried several things with the mysql connection, with the following results so far:

After understanding what a VARCHAR is (namely some sort of shortstring with a fixed maximum length), I wanted first to know whether a newer MySQL server reports proper field lengths for fields like 'FORMAT(mydoublefield,3) AS myalias', so I switched to a 4.1 server.

However this didn't work, because I needed a newer client also, because the older client didn't support the newer authentication protocol requested by the server (according to the error message I got).

So I used a newer libMySQL.dll file and adjusted mysql4dyn to accepting this version. But now I got a authentication error for user However I didn't connect as user ODBC?!?

The I figured out the TMySQLConnection object connected twice to the database, once in ConnectToServer and once in PrepareStatement. The second time it tries to fetch the Host, UserName, Password from the FMySQL:PMySQL field. But there seem to be nil values in there... and I guess MySQL somehow tries user ODBC because no username is given.

I also tried loading the newer dll statically, but then I had to comment out a lot more functions in mysql4 and mysql4_com than before, in particular some slave, master and the hash_password functions.

So my findings are:
- the fpc mysql headers contain way too much function headers, which causes 'entry point of function blablabla not found' error when statically loading the library. I think it's better only to try to import the functions which are also documented as part of the C API at http://dev.mysql.com/doc/mysql/en/c.html.
- the fpc headers contain declarations of structs (records) that are not in the MySQL documentation. The only struct described there is the MYSQL_FIELD struct (or: TMySQL record), besides the other types of which no members are listed. Perhaps using the other structs isn't very safe and prone to change.
- mysql4dyn only accepts a 4.0 library and no a 4.1 library; I hope this can be made more flexible...

I understand the fpc team has become tired of mysql header changes and incompatibilities, but this problem does not seem to exist with e.g. the php mysql interface. Maybe the problems can be solved by only importing and functions that are also in the documentation. (This would require manual action instead of simply applying h2pas to mysql.h)

Do you think this is an approach with a chance to success?

MySQL is widely used and therefore it would be nice if it can be supported by fpc. (In fact, I've seen people asking things about mysql on the Lazarus mailing list several times)

After getting the mysql headers right and 'compatible', I could try to create a better TMySQLConnection.

Regards,

Bram

Sorry for the late reply..

Bram Kuijvenhoven wrote

After understanding what a VARCHAR is (namely some sort of shortstring with a fixed maximum length), I wanted first to know whether a newer MySQL server reports proper field lengths for fields like 'FORMAT (mydoublefield,3) AS myalias', so I switched to a 4.1 server.

If you store the string 'hello' in a varchar(1000) it only needs 9 bytes of storage-space, while using char(1000) you would need 1000 bytes. In memory, however, fpc indeed uses a shortstring.

That check isn't there for nothing.

That's because they've changed the PMySQL-type. (Like they change everything, always - just like they want)

No. The idea is good though. I think I can use it with the problem of what functions/procedures need to be put into the headers.

But, the largest problem is all those things which aren't documented. Example, in http://dev.mysql.com/doc/mysql/en/c-api-datatypes.html you can find that 'field' can be one of the following: FIELD_TYPE_TINY, FIELD_TYPE_SHORT etc.etc.

But it could be that for MySQL 4.0 FIELD_TYPE_TINY is defined as 0, while in 4.1 it's defined as 1.

So for 4.0 it should be:

const FIELD_TYPE_TINY  : 0;
      FIELD_TYPE_SHORT : 1;

while for 4.1 it is:

const FIELD_TYPE_TINY  : 1;
      FIELD_TYPE_SHORT : 0;

And why they keep changing that sort of things, I really don't get. And these differences you can only see in the .h's.

(I made this example up, but those differences do exist)

MySQL is widely used and therefore it would be nice if it can be supported by fpc. (In fact, I've seen people asking things about mysql on the Lazarus mailing list several times)

Me too.

After getting the mysql headers right and 'compatible', I could try to create a better TMySQLConnection.

I'm gonna throw out all non-documented procedures. (Well, they are documented mostly, but elsewhere) And use diff on the .h's