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

 

[moinkhan7186]

Thanks for the related information

 

[gguerra]

what is there to complain about, actually?

Fairly obvious I think. I would like to be able to read what is there in plain English. Why does Excel decode it in the preview and not in the result? Off topic or maybe not? I'm still at a loss as to why it's encoded at all, anywhere. No encoding appears in Workbench at all, only on the client side.

The type for Mainmark is varchar(25), nothing close to binary. No reason to "protect" it either, it's something that identifies an assembly in steel production. Purely informational and arbitrary. Image attached of how it appears in Workbench.

I really do not want to work with memo fields as they are just not appropriate for this use case even though I can bind them to editboxes or whatever. Maybe there is a way to map them correctly into VFP as character fields. As I stated, rarely if ever do they need to be 255 (or 254) characters long. I could reduce their size way down to something realistic like 30 character if there was a way to map it.

This whole process is exploratory by the way, I just want to know what is possible.

 

[Chriss Miller]

Why does Excel decode it in the preview and not in the result? Off topic or maybe not?

I would judge it that way: Preview and also workbench, it seems, are suppressing non printable characters. Therefore you see none of the rectangles there.

You get longer values in Excel and VFP. Why would a driver create additional characters, if they are not stored in the columns, actually.

I already told you what you can do to show the objective truth: Convert it to binary. You can already do so while querying.

CursorSetProp("MapBinary",.t.,0)
h = SQLStringConnect("...")
SQLExec(h,"Select Cast(MainMark as Binary) as hexmark from productioncontrolitemstations","BinaryResult")
SQLDisconnect(h)
Browse

Do the same query in Workbench, compare the hex values.

 

[Chriss Miller]

The type for Mainmark is varchar(25), nothing close to binary

Well, that's what I expected. I said:

it shows as a text field of some kind, while it would be more appropriate to be a binary type.

It indeed is more appropriate to store data like that in binary column, because that's what the core of it is. Not your fault, that's the database design of the software. But to get to the bottom of it, you should just not just look at visual representations in different tools, just look at the raw, core data. And that's done when converting it to binary.

The final question then is, how do you display it in Excel and a VFP browse the same way as the Excel Preview and Workbench do? Well, before I give my advice on that, what would be your idea? Especially - as I assume - after it turns out those extra characters are actually coming from the data and are not an error, once more.