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 where you could 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. value 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 retrieving empty strings as .NULL. value makes no sense to me.

Oh, and just as a small technial detail nullable memo fields are shown as memo when empty, .NULL. when null (unless NULLDISPLAY is set differently) and Memo with content, that's all there is to this and it does not tell anything is wrong from the screenshot of your VFP result cursor. We can't look into the Memo values, though, so whatever is not working in there is yet up to investigation. You seem to have stopped before even giving that a go at all, because you don't see any of the data displayed in a Workbench result.

 

[gguerra]

Chriss..

Maybe bringing over VARCHAR(255) as memo fields is the "correct" behavior and yes the data is there inside the memo fields and yes I know capitalized memo fields contain data.

I just can't use memo fields the same way I would character fields. I cannot manipulate them the same way, I can't use them for indexing, the list goes on.

I would like the fields to be brought in as character the way they are in MySQL. Technically, all I need is to strip one character and bring them in as char(254) or even less characters, I highly doubt anybody would use more than 30 or so characters for those fields in question. That may not be a function of the ODBC connector (I'm assuming). It takes the liberty in converting them to memo fields, it could also take the liberty in truncating or trimming a character off the 255 and make it 254 to make it compatible.

The NULL values are correct, I compared them and yes, they are actually NULL in MySQL. I tried SET NULLDISPLAY to "" and that did the trick.

I will look into the CursorSchema property that dylim mentions. If I predefine the cursor, it may make a difference. I kind of doubt it but will try it anyway.

I also tried the STRCONV(resultcursor.mysqlfield,11) It did not appear to decode anything. I tried the 12 setting, similar results.

I can't paste an example here with the encoding, it automatically get's decoded and displays correctly. I could take a screen shot but there are other hurdles to get through first.

Thanks

 

[Chriss Miller]

Ok, sounds fine, then.

I cannot manipulate them the same way, I can't use them for indexing, the list goes on.

That's only half true, you can't index anything over 240 chars, if not using MACHINE collation even only 120 chars are the maximum index width limit.
You can bind a textbox to a memo field like you can bind an Editbox to a memo and display and edit that data, there's not a big hurdle to overcome, you just assume that wrongly. And your list goes on with what?

If you're interested to get these as varchar(240), you can use CA and it's cursor schema or also remote views and their field definitions, too, no big deal about that. Purely doing SQLEXEC of a query will make VFP infer the field types by its own rules, which include using memo for anything longer than 254.

By the way, you could still keep them as memo, bind them as I suggest and index on LEFT(field,240) to sort them as much as can, never risk to cut off anything and still have all you need including sorting, you even can use that for LOCATE/SEEK using the index up to 240 chars values you locate/seek.

There's one more obvious solution: Shortening the field width within MySQL to varchar(240). Besides storing data in latin1 instead of utf8mb4 or other such UTF8 collations. The ANSI nature of the driver does not cause conversion of UTF8 to ANSI, by the way. What's working most of the time is that UTF8 and ANSI are the same anyway, unless you get into specific characters that have a double byte encoding in UTF8. For example put in "¡Guerra!" into a MySQL varchar field and you'll retrieve it as "¡Guerra!" (in ANSI 1252) and STRCONV("¡Guerra!",11) will turn it back to "¡Guerra!" There's no reason to try 12, as that converts UTF8 to Unicode and in VFP Unicode is not displayed correctly, you'd see "¡ G u e r r a !", instead. Have a deep dive into character encodings, database collations, and such things, encoding also isn't encryption. And, by the way, ¡ exists as ANSI character in several ANSI codepages, not only in specifically Spanish ones. Other characters you have in UTF8 have an ANSI equivalent or not and where they haven't will be converted to ?, so if ¡ is not part of an ANSI codepage ¡Guerra! would be translated into ?Guerra!. Besides, it wouldn't show up as ¡Guerra! in the first place, but some other 2 character combination before Guerra!.

On the other hand if you edit UTF8 in a VFP frontend and make your views or CA updatable, you will need to ensure to convert any character that's having an ASC() code above 127 to a double byte UTF8 or that would arrive back wrongly in MySQL, so that's important to know and if there's a lot like that, it can become more comfortable to do whatever you want to do in Excel or any other client that's not limited to ANSI encodings.

It's obviously not viable to make changes to a database of a third party software package, it may verify whether the database structure is modified, it will very likely not like the collation to be changed to latin1, just because that's better for VFP, but you might get well along with shortening the fields, if you get into the database with the necessary priviledge of making structural changes.