Reading MySQL 8 encoded data into VFP9

[gguerra]

Trying to read data from a MySQL ver 8 DB into VFP9Sp2.

I am now using an ANSI ODBC driver so no problem there, it doesn't crash but it will still not read the data correctly. Some tables have fields/columns that are encoded. It will read data from any table that does not contain any UTF8 data but not correctly. I have attached the an image of the error message.

The ODBC driver from MySQL is not working correctly.

Anybody use VFP9 with MySQL (version 8)

Thanks

 

[Chriss Miller]

I see files related to MariaDB on our server. Could it be that the company I am dealing with (the vendor) uses MariaDB somehow? One example I see is a file named "mariadb-connector-c-3.3.11-win64.msi". which is an installer for odbc apparently. Not sure how it is related.

Could be their software has problems with MySQLs original ODBC connectors. I already mentioned you could also try mariadb ODBC drivers, if they use it, that's a good reference, isn't. It doesn't necessarily mean the server is MariaDB instead of MySQL, but you should know what server runs. It's listed under Windows services. Both MySQL Server and MariaDB server are a mysqld.exe process, but they run as MariaDB vs MySQL service and under different service accounts.

How do you get the idea you're trying MySQL drivers, if you don't actually know what server runs? That's the first thing you should find out.

 

[gguerra]

Could be their software has problems with MySQLs original ODBC connectors. I already mentioned you could also try mariadb ODBC drivers, if they use it, that's a good reference, isn't. It doesn't necessarily mean the server is MariaDB instead of MySQL, but you should know what server runs. It's listed under Windows services. Both MySQL Server and MariaDB server are a mysqld.exe process, but they run as MariaDB vs MySQL service and under different service accounts.

How do you get the idea you're trying MySQL drivers, if you don't actually know what server runs? That's the first thing you should find out.

I guess I missed the part of ME using the MariaDB connectors. I tried it and unfortunately, the exact same results. I suspect this system does use the MariaDB connectors as there was already a DSN entry for MariaDB which I did not install. It's part of their install. I have no idea why they would use it though. The system is modern and 64 bit. VFP is ancient and 32 bit. I will keep digging.

Anyway, as I already mentioned, I'm old. I started using dBASE in 1984 more or less, moved on to Foxbase, then Foxpro DOS, then VFP through several versions but I did come to discover the database designer in VFP.

From the command prompt, type CREATE DATABASE whatever, then MODIFY DATABASE whatever. It's an interactive tool to create actual relational databases between tables, local and remote views and connections to outside sources with ODBC. You can right click in the empty window and go from there. You may already be familiar with it and maybe not know it by the name "Database Designer" but that is the title for the main window. It's in the help by the way, I've attached an image.

Thanks for the help.

 

[gguerra]

Could be their software has problems with MySQLs original ODBC connectors. I already mentioned you could also try mariadb ODBC drivers, if they use it, that's a good reference, isn't. It doesn't necessarily mean the server is MariaDB instead of MySQL, but you should know what server runs. It's listed under Windows services. Both MySQL Server and MariaDB server are a mysqld.exe process, but they run as MariaDB vs MySQL service and under different service accounts.

How do you get the idea you're trying MySQL drivers, if you don't actually know what server runs? That's the first thing you should find out.

It says MySQL 8.0.35 on the main screen of Workbench, I ran a query to check.

 

[gguerra]

Chriss..

There is a table in the DB called 'projects'. Here are the columns straight from Workbench. I've attached some images. The first is the structure from MySQL, the second is the query results on that table from MySQL and the third is the table as it comes into VFP. You can see exactly what happens.

 

[Chriss Miller]

Well, it's no wonder varchar(255) come over as Memo, VFP only supports up to (var)char(254).
Where is the VFP result wrong, actually?

 

[gguerra]

Well, it's no wonder varchar(255) come over as Memo, VFP only supports up to (var)char(254).
Where is the VFP result wrong, actually?

I had the same thought myself but...

Did MySQL ver 5 not have the same formats? And even though I cannot confirm it, it is reported to work correctly.
I have no experience with variable length character fields, so I googled it.

This is what AI came back with.

In MySQL versions 5.0.3 and later, a VARCHAR column can store a string of variable length up to a maximum of 65,535 characters. Before this version, the maximum length was 255 characters. However, the overall row size in a table, including all columns, has a limit of 65,535 bytes.

You would also think that the ODBC driver would handle this and make the adjustments?

So far I tried multiple different drivers from difference companies and they all do the same thing. I'll be speaking with someone from one of these companies that claims their driver works with VFP. I'll get back to you on that one.

 

[Chriss Miller]

I talk about a VFP limit, not a MySQL (or MariaDB) limit, what you bring up is totally unimportant. When a field could be longer than 254 VFP chooses memo, that can hold up to 2GB, theoretically. It would break the file size limit, but you could have 1 record with 1 memo of 2GB fpt header size. And, well, lots of memos holding up to 255 chars.

The result is a VFP cursor, not a MySQL cursor, isn't it? The same goes for MSSQL, when fileds in there are longer than 254, VFP retrieves them as memo fields, you should o´r could already know that. That's not an error, that's just sane.

 

[Chriss Miller]

I'm not sure whether I should ask this question or not, but do you know what it means when a browse window shows capital M Memo vs lower case memo?

 

[dylim]

Interesting. AzNative said 8.0.33 is the last one that works, here:

Running SQLEXEC on MySQL Database -- Continued... - Microsoft: FoxPro

Hello All, In reviewing a prior thread (https://www.tek-tips.com/viewthread.cfm?qid=1829136), it seems as though I have a very similar dilemma. The primary difference is that I'd prefer not to utilize outdated ODBC connectors due to vulnerability concerns. I'll quickly recap below: MySQL...

www.tek-tips.com

So experiences differ.

By the way, a link to a previous thread doesn't work, that's due to tek-tips failing in forum migration about links to threds. I found this was referring to https://www.tek-tips.com/threads/running-sqlexec-on-mysql-database.1829136/
Note how the id 1829136 is integrated here, you can get there with https://www.tek-tips.com/threads/anything.1829136, too. Anyway, the result there was using a much older driver 3.51, the server version turns out to be 5.7.44-48, though, so that's just needing an older driver for an older server.

MySQL isn't like VFP in downward compatibility. You can access legacy DBFs with VFP9, no problem, but to connect to an older server you also need an older driver. If your version 8 server isn't the newest, an older 8.0x driver can be an obvious, normal solution, the last 8.0.42 driver is likely not supporting all version 8 servers.

You might get good results using a MariaDB driver, too. Or other third party drivers, as you're about to try.

So far, and I am glad I am done with this, I have used the following combination as my safe, conservative setup:

MySQL Server 5.7.40
MySQL ODBC 8.0.31

Good riddance though. I backed up all my stuff and just did a clean install of Windows 11 on my laptop and installed:

MariaDb Server 11.7.2
MariaDb ODBC 3.2.5

So far, it has been seamless, snappy and more importantly, I found my joy back!

 

[dylim]

Thanks for the info but the 8.0.31 did the same thing as the 8.0.33. The only types of fields it brings over correctly are integer and date. Populated character fields are brought over as memo and for blank character fields it shows a NULL.

MySQL Server version is 8.0.35

How do you fetch your data from MySQL? SQLEXEC()? Do you use CursorAdapters (CA)? In CAs, there is a property called CursorSchema, wherein you tell VFP which columns/fields will have what field data type.

 

[Chriss Miller]

My conclusion is you're not having a technical problem, you just don't realize your data is there in front of you and you just would need to display the memo field content by double clicking on a "Memo" or just by ? fieldname. And then you might still have the issue the data is UTF8, which has an overlap with ANSI in letters and digits, but will have double byte codepoints for things like accents, umlauts, etc and therefore needs STRCONV(fieldname,11) to cater for that.

Not seeing the woods for the trees, I just can't believe it.

 

[Chriss Miller]

To explain how VFP derives the result cursor structure: For SQLExec the situation differs a bit from how that works in native queres (SQL of DBFs), but one principle is the same - VFP needs to define the cursor structure and does so from the first result record. In case of native queries it can mean the first value being short causes a short char field and further data is cut off.

In case of determinning the result structure of an ODBC result you have to take into account that the ODBC driver is the messenger not only providing the data values in ODBC data type form that VFP translates to VFP data types, ODBC also specifies the data types it got from the datbase, like a varchar(255) field, which VFP isn't capable to produce. So when the first record only contains a 20 char string in the varchar(255) field it does not lead VFP to create a char(20) or varchar(20) field, it will try to do the field as varchar(255), and finds the best fit for that is memo,instead, and will pick that. It would also be the case for char(255), it's not the variable length nature of the field that makes VFP decide for memo.

So that would also have happened in older MySQL ODBC drivers and older servers, there's just nobody complaining about it as they understand why that's the only way to make sense of it and let it work. VFP does not make a varchar(254) field and pushes it's luck that this field has no record using the 255 chars limit fully in further records, just so you can directly look at the text in a browse window. I'd also not recommend to make use of predfininig the result cursor that way, as you simply risk cutting off one char, no matter how small that risk is and how unimportant it would be, even when it happens.

 

[Chriss Miller]

Thanks for the info but the 8.0.31 did the same thing as the 8.0.33. The only types of fields it brings over correctly are integer and date. Populated character fields are brought over as memo and for blank character fields it shows a NULL.

To not overlook this:
Are you sure blank fields in MySQL are just empty and not NULL. Just like in VFP you ould SET NULLDISPLAY TO '' to diplay NULLs as blank, Workbench could do so. If you really have empty varchar(255) field coming over as VFP .NULL. that's something you could perhaps control by an ODBC connection string option. I guess they are .NULL. in MySQL data already, too, because a default behavior of retrieiving empty strings as NULL value makes no sense to me.