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?

 

[Chriss Miller]

I don't believe you can adjust the size with the cast function in VFP?

SQLEXEC executes MySQL when connected to MySQL. This has nothing to do with VFP SQL. So you can do that. And you should know that, as it also applies to MS SQL or any other database, it's not VFP executing SQL, it's the connected database. So you always write SQL in that database dialect, not VFP.

But it's much simpler to use the cursorschema in a CA or use the view definition when using remote views:

#Define adWChar 130

Public oADODBConnection, oADOXDB, oADOXTable, oCA
oADODBConnection = CreateObject("ADODB.Connection")
oADODBConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\programming\legacy;Extended Properties=Paradox 5.x;"
oADODBConnection.Open()

oADOXTable = CreateObject("ADOX.Table")
oADOXTable.Name="demo1"
oADOXTable.Columns.Append("shorttext",adWChar,240)
oADOXTable.Columns.Append("longtext",adWChar,255)

oADOXDB = CreateObject("ADOX.Catalog")
oADOXDB.ActiveConnection = oADODBConnection
oADOXDB.Tables.Append(oADOXTable)

oCA = CreateObject("cursoradapter")
oCA.DataSourceType ="ADO"
oCA.DataSource = CreateObject("ADODB.RecordSet")
oCA.DataSource.ActiveConnection=  oADODBConnection
oCA.Alias = "demo"
oCA.SelectCmd = "Select * from demo1.db"
oCA.CursorFill()
AFields(laFields)
? laFields[1,1], laFields[1,2], laFields[1,3]
? laFields[2,1], laFields[2,2]
Use

oCA.CursorSchema="shorttext v(240), longtext v(254)"
oCA.UseCursorSchema = .T.
oCA.CursorFill()
AFields(laFields)
? laFields[1,1], laFields[1,2], laFields[1,3]
? laFields[2,1], laFields[2,2], laFields[2,3]

This is more complex as it needs to be, because at the same time of demonstrating how a CA cursorschema shortens data I also wanted to show this has nothing to do with MySQL, it also happens when the data comes from other sources, in this case a Paradox 5 table (.DB file).

When using remote views, you can open the view designer and look at the SQL of the view, which will have a section about the data types of the SQL result, there you can change from M or Memo to Varchar(240) or V(240).

Notice the SQL is simply SELECT *..., select all fields. The conversion happens because you ask for result field data types. When you aks for something completely off, you get completely off values, but if you're sensible, that will work.

 

[Chriss Miller]

Regarding the binary representation of data. This is how it should look:

This comes from a query

Select *, Cast(Name as Binary) as BinName from Animals

Where the table is created by

   Create Or Replace Table Animals (
     Id MEDIUMINT NOT NULL AUTO_INCREMENT,
     Name Char(30) NOT NULL,
     Primary Key (Id)
   );

So once the original field is not long, it doesn't turn into a blob in VFP, but just a varbinary field that directly displays.

And so 64 6F 67 are the bytes for "dog", for example. What it does to the non printable characters that may be suppressed by Workbench is it shows them in a blob. Have you double clicked on the Blob to see the content? Is it really not shown as a purely hexadecimal value? Or does it actually not react at all? If you have a blob field in VFP and put anything into it, it shows like this:


So, same thing, you can guess what I stored into this blob field.

I expect, if you do this correctly the rectangles you see in here...

...will show up with their hexadecimal representation, for example 01,02,03, somthing like that because, for example CHR(1) shows up as a rectangle, when you do ? CHR(1). The value could actually start like 0101010101333430303043... instead of 01 a rectangle could also represent 00, 02,... any non printable byte value. And also in WorkBench the full value would show up, not suppressing all the unprintable characters as anything will turn into the printable hex digits 0-F. It'll show that what you recieve in Excel and VFP is actually in the database, and the same. And it should finally convince you the driver is working and not mangling data or not decoding it. What is actually mangling data in this case is WorkBench. At least it's suppressing non-printable characters. That should be obvious. It could even be a setting of WorkBench. Or it could simply not have the rectangle in the font it uses for display and instead not even a space but an empty character, so the rest of the data displays as if its the only data.

The question I would have in your shoes is why there are additional bytes in the data you don't see in Workbench? Instead of being so darn sure of the inverse thought that this is clearly wrongly retrieved data or not fully decoded data.

And finally, if you want to suppress unprintable characters, then you have to do that. While it seems to be automatic in Excel preview, it's not in the final Excel sheet. While it's automatic in WorkBench, it's not in VFP. You could actually remove the unwanted "crap" by removing them from the data in the database itself. My guess is these extra characters have a meaning. Not to you, but to the software, so I wouldn't remove them from the data itself. But you can easily strip out all low bytes from a string with CHRTRAN in aftermath code of SQLEXEC or the view within the VFP result cursor.

Well, and if this is actually crap, then don't ask how to suppress it in display, just clean up the data, if it's not meant to contain these additional non printable bytes. And maybe ask yourself where it came from and what's wrong with the source of that data coming into the database? Also, you'll see if the software breaks when you remove them, so be warned to not clean up what isn't dirt. Keep a backup before changing this, for example. All standaard advice, nothing special, why do I need to tell you that, I'm really wondering.

 

[Chriss Miller]

Try this query in Workbench to not get a Blob field:

Select Cast(MainMark as VarBinary(50)) as HexMark From fabrication.productioncontrolitemstations

Do the same from Excel and VFP. This conversion doesn't hide anything, it just converts every character byte, printable or not, single byte character or double bytes or however many bytes into hex digits. So you look into the actual raw bytes behind what is displayed.

This way you'd see WorkBench as a base reference tool of what data is actually stored is good, but still not the underlying final truth. Every display of data that's not raw is a representation of it, not the actual bits and bytes. Everyone should know that. And with that I don't only address developers.

 

[gguerra]

When using remote views, you can open the view designer and look at the SQL of the view, which will have a section about the data types of the SQL result, there you can change from M or Memo to Varchar(240) or V(240).

I did not even attempt to do the CA, this (above) worked and brought it in as character. I even went as far as reducing the size to 40 instead of 240. I tried it one field at first, I can just go ahead and do it for all of the fields that I need that are defined as varchar(255). I made the following changes as an example.

From
DBSetProp(ThisView+".jobnumber","Field","DataType","M")
to
DBSetProp(ThisView+".jobnumber","Field","DataType","V(40)")

 

[gguerra]

Try this query in Workbench to not get a Blob field:

Select Cast(MainMark as VarBinary(50)) as HexMark From fabrication.productioncontrolitemstations

Do the same from Excel and VFP. This conversion doesn't hide anything, it just converts every character byte, printable or not, single byte character or double bytes or however many bytes into hex digits. So you look into the actual raw bytes behind what is displayed.

This gave me a Syntax Error in workbench. Image attached. Still looking into this. I did not do it in Excel or VFP.

I did open up the blob field in workbench from the previous query and yes, you I get hex. I assume I will get hex in VFP if I define a shorter field first but I get a memo which still displays the same weird characters. They will match, this really proves nothing and doesn't help me in getting rid of these characters. You mention stripping them out with CHRTRAN in the SQLEXEC. That will take time to figure out. I will have to play around with it. Obviously I will not even mess around with the data itself, I would not want to break it. If it was some kind of "protection" as you mention (I am not sure), why would it display correctly in workbench?

When you right click on the blob field in workbench, you can open it in a viewer which shows you the hex and text (image attached). Under text you can see SOH as a representation of the binary characters. Apparently that is a control character meaning "Start of Heading". I googled it and it is "used to signal the beginning of a heading or header section in data transmission or file formats" Doesn't tell me why it's there but it could help me in stripping out the characters.

I thought about this and I am thinking of emailing the company and telling them that I am trying to run some queries from Excel and I am getting weird characters in the data. We'll see what they tell me.

 

[Chriss Miller]

Don't know why this errors, then use Binary(50) instead of VarBinary, even though VarBinary is an existing data type:

MySQL :: MySQL 8.4 Reference Manual :: 13.3.3 The BINARY and VARBINARY Types

dev.mysql.com

>They will match, this really proves nothing and doesn't help me in getting rid of these characters.
It doesn't prove anything? I just have to give up on you, sorry. All attempts to see whether you can go on step yourself failed.

>If it was some kind of "protection" as you mention (I am not sure),
I said the software might actually need the data to be as is and not as you wish to see it, you're really still quite dickheaded about this.

Decimal representation of SOH:1 Hexadecimal: 01 (or also 1). What it means? It could just mean 1. Software is free to use bytes for whatever it wants to use it. Bytes are not necessary characters that should be printed, even if part if it can be seen and looks like hexadecimals. It is a series of 1. That has a meaning to the software. Look, let me just ask you this: Does the software that has this database as it's data backend work? Does it have flaws when it comes to the productioncontrolitemstations (whatever they are)? If not, the data is okay, what you need to fix is your attitude towards this. "Everythig has to be human readable, everything has to be as you expect it to be and as you saw it for the first time in the first tool you used to look at it."

No, it has to be as it has to be, if nothing is wrong in the software, there's also nothing wrong in that data.

>You mention stripping them out with CHRTRAN in the SQLEXEC.
Can you please concentrate and read what I wrote and not what you think I wrote? I said aftermath. Know what aftermath is? In the aftermath of something? Are you telling me you don't speak English? This is not a computer science term, it also has nothing to do with math, by the way.

 

[gguerra]

Don't know why this errors, then use Binary(50) instead of VarBinary, even though VarBinary is an existing data type:

MySQL :: MySQL 8.4 Reference Manual :: 13.3.3 The BINARY and VARBINARY Types

dev.mysql.com

>They will match, this really proves nothing and doesn't help me in getting rid of these characters.
It doesn't prove anything? I just have to give up on you, sorry. All attempts to see whether you can go on step yourself failed.

>If it was some kind of "protection" as you mention (I am not sure),
I said the software might actually need the data to be as is and not as you wish to see it, you're really still quite dickheaded about this.

Decimal representation of SOH:1 Hexadecimal: 01 (or also 1). What it means? It could just mean 1. Software is free to use bytes for whatever it wants to use it. Bytes are not necessary characters that should be printed, even if part if it can be seen and looks like hexadecimals. It is a series of 1. That has a meaning to the software. Look, let me just ask you this: Does the software that has this database as it's data backend work? Does it have flaws when it comes to the productioncontrolitemstations (whatever they are)? If not, the data is okay, what you need to fix is your attitude towards this. "Everythig has to be human readable, everything has to be as you expect it to be and as you saw it for the first time in the first tool you used to look at it."

No, it has to be as it has to be, if nothing is wrong in the software, there's also nothing wrong in that data.

>You mention stripping them out with CHRTRAN in the SQLEXEC.
Can you please concentrate and read what I wrote and not what you think I wrote? I said aftermath. Know what aftermath is? In the aftermath of something? Are you telling me you don't speak English? This is not a computer science term, it also has nothing to do with math, by the way.

Give up on me? Go ahead and give up. All you are telling me is to ignore it, the software uses the bytes for whatever it wants to use it. Really?

I need to use it outside of the software and yes HUMANs are reading it.

I never said the data was not ok or corrupted or whatever, that is OBVIOUS. It WORKS or I would be complaining about it to somebody. Duh.

I already came to the conclusion (a while back and I told you), the binary characters are in the data. Excel is not adding it and neither is VFP. I DO NOT need to see it in hex to tell me that. That is why it proves nothing. There is no point in seeing it in hex. It's the same.

I just want to make it USABLE and READABLE by HUMANS and yes it has to be readable. What kind of asinine question is that? Pretty straight forward and if anybody is being dickheaded about it and rambling on and on about it, it is you and yeah I speak English. No need to get insulting about it.

 

[dylim]

Give up on me? Go ahead and give up. All you are telling me is to ignore it, the software uses the bytes for whatever it wants to use it. Really?

I need to use it outside of the software and yes HUMANs are reading it.

I never said the data was not ok or corrupted or whatever, that is OBVIOUS. It WORKS or I would be complaining about it to somebody. Duh.

I already came to the conclusion (a while back and I told you), the binary characters are in the data. Excel is not adding it and neither is VFP. I DO NOT need to see it in hex to tell me that. That is why it proves nothing. There is no point in seeing it in hex. It's the same.

I just want to make it USABLE and READABLE by HUMANS and yes it has to be readable. What kind of asinine question is that? Pretty straight forward and if anybody is being dickheaded about it and rambling on and on about it, it is you and yeah I speak English. No need to get insulting about it.

Welcome to the club GGUERA! And for a while I thought, it was just me.

People who really want to help should be kind as well. Not brash, insulting, and full of hubris.

Cheers!

• Romans 12:16:
  "Be alike affected to each other: not aiming at high things, but accommodating yourselves to persons of low rank. Be not wise in your own conceit."

 

[Steve Meyerson]

dylim & gguerra,

I feel your frustration on the subject. I think you're referring to some of Chris's comments.

Please don't take it out on him. He has answered probably over a thousand posts on this forum. Had I done that, I think I (and most people) would have often lost at least some patience a LONG time ago.

He's been a super valuable contributer. In my opionion, you could be a little more tolerant. Just a thought.

Steve

 

[dylim]

Steve,

IMHO, maybe, I guess it's a difference of upbringing/culture/value sets? No judgement here okay?

I was tolerant at his hubris and inconsiderate remarks. And in your words, I simply "tolerated". When I read GGUEROS's reply to Chris's brash comments, I simply seconded the same feedback/response of GGuera sir.

Answering a thousand posts, heck even a million.. does this exempt one from being brash and condescending?

In my book, in this case, I would rather have quality over quantity. I am certain a lot more would fear posting issues and questions because of his hubris.

I am sure we are not the only ones. I saw a few threads with him having the same behavior.

 

[Steve Meyerson]

dylim,

Yes, I see your point. But even if Chris has some of that negative "hubris" you mentioned, my only point was in his case, the plusses far exceed the minuses and the value outweighs any names we might be called. I've been called worse (not by him).

Regarding this forum and the ageing of VFP, this forum is (might be?) the only one left.

Steve

 

[dylim]

if he makes replies like how he does, I am sorry, but he is definitely not contributing to the un-aging of VFP:.