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?