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.

 

[gguerra]

I deleted my previous reply by mistake but here it is in a nutshell.

I ran the queries to convert to binary.

I attached the images of the results in VFP, Workbench (BLOB) and Workbench with the option checked ON to show binary as nonbinary which still shows binary data.

Still confused, it shows binary data even when it's supposed to be non-binary. I do believe you that it's coming from the data, question is why? Still a mystery.

But I thought of something, not related to the binary data but related to the other issue with the varchar(255) to memo issue.

I tried using the cast in workbench as follows. Jobnumber (below) was getting converted to memo.

SELECT cast(jobnumber as char(40)) FROM fabrication.projects;

It does work in reducing the size of the column in workbench. See Image 4

This would solve my problem if I could only run this with from SQLEXEC(). I don't believe you can adjust the size with the cast function in VFP?