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]

C5 is not just an error, it is a crash.

What line is reported within vfp9err.log and what's the code of it? Assuming it's SQLEXEC, the driver used crashed.

In recent threads about MySQL and VFP what turns out to be the better database is MariaDB with MariaDB drivers, but I gueess you don't have that option. The last drivers you can use in VFP9 are 32bit 8.0.x ANSI drivers. Which one are you using?

Besides the crash problem, when you have encrypte data you have to have the necessary key or certificate or whatever else necessary to decrypt this. If you can't be more specific there's nothing to tell you about that. Data could be encrypted outside MySQL and then stored and would be decrytped after retrieved, the encryption could be based on MySQL functions like asymmeric_encrypt() and asymmetric_decrypt(). If you don't knwo there's not much to tell, is there?

 

[gguerra]

I realize it's a crash. It kicks me out completely.

I am in fact using the same ANSI driver you mention. The unicode driver would not even let me run the query, it would crash. When I say run the query, I am doing it from the database designer with a remote view. Have not tried coding it.

As for the encoding, it appears to be utf8mb3. I see that from MySQL workbench. I can query it in workbench, the data is there and not encoded.

I am able to read the table/s from Excel which obviously is modern compared to VFP. When I create the connection from Excel, in the preview, you can see that data in plain text. When it reads it in, you can read it but it does have the binary characters along with the text. I am newbie when it comes to encoding. In this case I don't even see the purpose of it.

Many of the tables for this system contain no encrypted data. Some do, which is where I am having trouble. It makes no sense to encrypt this data, they are just part numbers and would be in plain text.

I work for a steel fabricator. The system is called Tekla Powerfab. I am trying to do some custom queries. My preference is Foxpro (yeah I'm old)

I do know this and this may be a clue, even when querying tables that have no encrypted data, they do not translate correctly. What should contain data is all NULL values in VFP. I think it's the ODBC driver. I am now looking at something from a 3rd party. I am about to try that now. I am downloading a free trial.

Thanks

 

[Chriss Miller]

I am in fact using the same ANSI driver you mention.

There are 44 drivers of the 8.0.x series, could you just tell the version number?

 

[gguerra]

There are 44 drivers of the 8.0.x series, could you just tell the version number?

It's 8.0.42.

Installer is mysql-connector-odbc-8.0.42-win32.msi

Which I got from

MySQL :: Download Connector/ODBC

dev.mysql.com

 

[Chriss Miller]

Click on Archive and pick version 8.0.33 mysql-connector-odbc-8.0.33-win32.msi and try that.
Tip: Unistall the 8.0.42 driver before installing the 8.0.33 driver.

Also, after reading in UTF8 texts, convert them with STRCONV(resultcursor.mysqlfield,11)

 

[gguerra]

Click on Archive and pick version 8.0.33 mysql-connector-odbc-8.0.33-win32.msi and try that.
Tip: Unistall the 8.0.42 driver before installing the 8.0.33 driver.

Also, after reading in UTF8 texts, convert them with STRCONV(resultcursor.mysqlfield,11)

OK, uninstalled the newer one, tried 8.0.33. I tried it on a simple table , no encoding.
Same thing basically. It retrieves character fields as memo fields, bigint as character. Integer is the only that looks ok.

If I get past a simple table, I will look at the STRCONV function.

I was reading online. Others have had similar issues with character fields and version 8 of MySQL. The issue didn't exist for ver 5 of MySQL. Not possible to downgrade.

I am waiting to try a driver that is not free. It's a trial for now. I had issues installing it that I wont get into. I am waiting for support. It will be a matter of time.

Should I try an even older driver for ver 8? It goes back to 8.0.11. TBH, I doubt it would make a difference.

Thanks

 

[Chriss Miller]

VFP9 is about 20 years old, as you have seen the drivers work with Excel, it's VFP9 failing. Older drivers could work, worth trying.

 

[dylim]

OK, uninstalled the newer one, tried 8.0.33. I tried it on a simple table , no encoding.
Same thing basically. It retrieves character fields as memo fields, bigint as character. Integer is the only that looks ok.

If I get past a simple table, I will look at the STRCONV function.

I was reading online. Others have had similar issues with character fields and version 8 of MySQL. The issue didn't exist for ver 5 of MySQL. Not possible to downgrade.

I am waiting to try a driver that is not free. It's a trial for now. I had issues installing it that I wont get into. I am waiting for support. It will be a matter of time.

Should I try an even older driver for ver 8? It goes back to 8.0.11. TBH, I doubt it would make a difference.

Thanks

Hi gguera,

Allow me to share my very own horrific experience. I have been using VFP with MySQL 5.7 and ODBC 3.51 for the longest time. When I tried to level up to MySQL 8 and ODBC 8, all hell broke loose. Classes which I have been using for decades suddenly exhibit erratic behaviour, and randomly crashes compiled apps (C000005). What's weird though is, the vfp error log file is always empty whenever I check its contents after each crash!

Had no choice but to go back to MySQL 5.7.41. As for the ODBC 8, the version that did not cause me any trouble 8.0.31. Any version higher is problematic.

Since I have tried out MariaDB (11.7.2) and its ODBC 3.2.5, both the latest versions todate, I have not looked back to MySQL. Aside from some minor issues, MariaDB has been such a charm, and it is way snappier at that!

 

[Chriss Miller]

the version that did not cause me any trouble 8.0.31. Any version higher is problematic.

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.

 

[moinkhan7186]

ok, I will try

 

[gguerra]

Hi gguera,

Allow me to share my very own horrific experience. I have been using VFP with MySQL 5.7 and ODBC 3.51 for the longest time. When I tried to level up to MySQL 8 and ODBC 8, all hell broke loose. Classes which I have been using for decades suddenly exhibit erratic behaviour, and randomly crashes compiled apps (C000005). What's weird though is, the vfp error log file is always empty whenever I check its contents after each crash!

Had no choice but to go back to MySQL 5.7.41. As for the ODBC 8, the version that did not cause me any trouble 8.0.31. Any version higher is problematic.

Since I have tried out MariaDB (11.7.2) and its ODBC 3.2.5, both the latest versions todate, I have not looked back to MySQL. Aside from some minor issues, MariaDB has been such a charm, and it is way snappier at that!

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

 

[Chriss Miller]

Thanks for the info but the 8.0.31 did the same thing as the 8.0.33.

MySQL Server version is 8.0.35

It may be tedious, but you can try further drivers below 8.0.31, too, one may work with VFP, I got them to work but only towards a MariaDB server. MJindrova got several of them to work, but within VFPA, not VFP9.

Anyway, I'm curious what third party driver you're trying and if that solves it. I guess you found DevArt, it's the only provider I know of, which also know the xBase world - one of their other 3rd party ODBC drivers is for DBFs, not only VFP DBFs, indeed. Might be worth it, when you can't migrate to a MariaDB server.

Then, I don't know what exactly you need to do with the data extract, you could also export data to CSV, use the official drivers with Excel, etc. etc., no need to bang your head against a wall.