VFP 9 Select from MySQL Binary
Hi all. I have been trying unsuccessfully for around 12 hours to select data from a mysql database into my fp program. This is something I do all the time and it has always worked great.
The problem here is that I've never dealt with a db that stores all the data as binary.
No matter what method of select I use my result shows all fields as empty Memo.
I've tried everything from Convert on the mysql side to Cast on the FP side and each and everytime the same result - empty memo fields.
If I run the exact same queries and simply change the db name to one that is not stored in binary the results are perfect.
I'm assuming that I'm not the first person that's ever needed to grab some mysql data stored in binary format so there must be a way.
Can anybody here give me some pointers? I'm about ready to start pulling my hair out and I've got very little left as it is...
Some things I've tried:
CONVERT( on the mysql side
CAST on the FP side
I've even run an sql script to change all the table properties from utf8 / collate utf8_bin to latin1 / latin1_swedish_ci
that didn't work, same empty memo results
Then I created a brand new db and copied the data (only) from the binary into my latin1 db. Still didn't work!
augh!!!!
The problem here is that I've never dealt with a db that stores all the data as binary.
No matter what method of select I use my result shows all fields as empty Memo.
I've tried everything from Convert on the mysql side to Cast on the FP side and each and everytime the same result - empty memo fields.
If I run the exact same queries and simply change the db name to one that is not stored in binary the results are perfect.
I'm assuming that I'm not the first person that's ever needed to grab some mysql data stored in binary format so there must be a way.
Can anybody here give me some pointers? I'm about ready to start pulling my hair out and I've got very little left as it is...
Some things I've tried:
CONVERT( on the mysql side
CAST on the FP side
I've even run an sql script to change all the table properties from utf8 / collate utf8_bin to latin1 / latin1_swedish_ci
that didn't work, same empty memo results
Then I created a brand new db and copied the data (only) from the binary into my latin1 db. Still didn't work!
augh!!!!
Dave Baldwin
I've never heard of MySQL "stored in binary". As far as I know, a SELECT statement returns almost all datatypes in text format. Are you talking about 'blob' datatypes? Can you show us the structure of the table?
What is your MySQL version?
What is exact column data type? Could you provide a script to create the table?
What is your ODBC connector version?
What character set did you enter into ODBC DSN configuration?
I've just installed MySQL and I don't have such problems with MySQL 5.1 driver and MySQL 5.5 database. Of course, some settings were necessary to change because not all CP 1250 characters were displayed/retrieved correctly... Memo field is created for columns exceeding 254 characters.
The utf8_bin is a standard UTF8 character set with binary collation order. If your UTF8 data contain characters supported in VFP then it should be fetched correctly.
You could also try different SYS(987) setting and also try to change some properties in CURSORSETPROP - MapVarChar, MapBinary, FetchMemo etc.
Available properties also depend on the data retrieval method you are using.
What is exact column data type? Could you provide a script to create the table?
What is your ODBC connector version?
What character set did you enter into ODBC DSN configuration?
I've just installed MySQL and I don't have such problems with MySQL 5.1 driver and MySQL 5.5 database. Of course, some settings were necessary to change because not all CP 1250 characters were displayed/retrieved correctly... Memo field is created for columns exceeding 254 characters.
The utf8_bin is a standard UTF8 character set with binary collation order. If your UTF8 data contain characters supported in VFP then it should be fetched correctly.
You could also try different SYS(987) setting and also try to change some properties in CURSORSETPROP - MapVarChar, MapBinary, FetchMemo etc.
Available properties also depend on the data retrieval method you are using.
Pavel already mentioned CursorSetProp in your other old thread about MySQL data retrieval, it works.
CursorSetProp("MapBinary",
Bye, Olaf.
CursorSetProp("MapBinary",
Bye, Olaf.
ASKER
Thanks all.
Sorry I wasn't clear. The data is utf8 collated as utf8_bin. MySQL ODBC driver 3.51.
Evidently the queries were pulling in the data all along - there was an error elsewhere that was making me believe that they were not.
I do still have a couple of issues related to this though.
As suggested I am using CursorSetProp("MapBinary",
However data selected from MySQL 'Text' fields are showing as 'blob' in VFP, and either the data is not there or I can't seem to access it.
I'm assuming I need to further convert this data?
Sorry I wasn't clear. The data is utf8 collated as utf8_bin. MySQL ODBC driver 3.51.
Evidently the queries were pulling in the data all along - there was an error elsewhere that was making me believe that they were not.
I do still have a couple of issues related to this though.
As suggested I am using CursorSetProp("MapBinary",
However data selected from MySQL 'Text' fields are showing as 'blob' in VFP, and either the data is not there or I can't seem to access it.
I'm assuming I need to further convert this data?
ASKER
Let me be more clear. Here's a sample select:
lcTableName1 = "table1"
lcTableName2 = "table2"
CURSORSETPROP('MapBinary',
lcQuery="SELECT A.*, B.* FROM " + lcTableName1 + " A LEFT JOIN " + lcTableName2 + " B ON " + ;
"(A.category_id=B.category
lnResult = SQLEXEC(pnconnection,lcQue
When the cursor is browsed most fields appear to have populated ok. However the fields stored in the source MySQL table as 'text' appear in the cursor as 'blob', and I cannot view or seem to access the data.
I'm guessing I need to convert this to 'Memo', but how can I do so in the select above? This select results in about two dozen fields being combined into the cursor, and only two of these fields are the problem 'text' format.
lcTableName1 = "table1"
lcTableName2 = "table2"
CURSORSETPROP('MapBinary',
lcQuery="SELECT A.*, B.* FROM " + lcTableName1 + " A LEFT JOIN " + lcTableName2 + " B ON " + ;
"(A.category_id=B.category
lnResult = SQLEXEC(pnconnection,lcQue
When the cursor is browsed most fields appear to have populated ok. However the fields stored in the source MySQL table as 'text' appear in the cursor as 'blob', and I cannot view or seem to access the data.
I'm guessing I need to convert this to 'Memo', but how can I do so in the select above? This select results in about two dozen fields being combined into the cursor, and only two of these fields are the problem 'text' format.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you put Text into blob fields, it arrives as what seems unreadable, but it's still the same bytes.
In that respect your choice of collation is unneeded. You request binary utf8, you get bionary utf8. There are other collations. But you can live with that, just display ''+Blobfield
This has the same effect as in:
? createbinary("ABC")
? ''+createbinary("ABC")
So this kind of "decoding" doesn't need STRCONV at all. You just will need it to make the conversion from utf8 to ANSI codepage 1252.
The values you see without prepending '' are simply the ASC() values of A, B and C in hexadecimal notation. That's all about binary fields, not only Blob, but also Q(N) varbinary fields.
Bye, Olaf.
In that respect your choice of collation is unneeded. You request binary utf8, you get bionary utf8. There are other collations. But you can live with that, just display ''+Blobfield
This has the same effect as in:
? createbinary("ABC")
? ''+createbinary("ABC")
So this kind of "decoding" doesn't need STRCONV at all. You just will need it to make the conversion from utf8 to ANSI codepage 1252.
The values you see without prepending '' are simply the ASC() values of A, B and C in hexadecimal notation. That's all about binary fields, not only Blob, but also Q(N) varbinary fields.
Bye, Olaf.
It is not so easy in all cases... E.g. my tests resulted in a Memo containing hexadecimal representation of the MySQL varchar column...
MySQL offers plenty settings and to know everything is simply impossible for beginners like me...
MySQL offers plenty settings and to know everything is simply impossible for beginners like me...
Hexadecimal representation? Like you see in varbinary/blob? Really in a Memo?
I agree the MySQL storage is confusing and the ODBC adds to it. Not just only because there are many storage engines with differing feature sets.
The main problem is, utf8_bin is not a collation sequence to use, if you want to use data with VFP.
I used utf8_general_ci for use with a website. Utf8 is good for websites and html forms, but not VFP. Use, what foxpro supports best: ANSI. That means latin1 (cp1252). You can use latin1_general_ci.
The suffix in MySQL collations has the meaning of FoxPros collation sequences. _bin is the same as "MACHINE" in vfp, but unlinke the rushmore optimization engine, MySQL can also optimize queries of data in GENERAL or language specific collation sequences. _bin doesn't mean at all you store data binary, that's the nature of blob or other binary fields.
Define your Text fields as TEXT, not as TEXT BINARY, because such a field is even closer to a Blob as a VFP Memo (Binary) is to a VFP Blob. If you want Text in Text fields, and not binaries like files, then make it TEXT.
Bye, Olaf.
I agree the MySQL storage is confusing and the ODBC adds to it. Not just only because there are many storage engines with differing feature sets.
The main problem is, utf8_bin is not a collation sequence to use, if you want to use data with VFP.
I used utf8_general_ci for use with a website. Utf8 is good for websites and html forms, but not VFP. Use, what foxpro supports best: ANSI. That means latin1 (cp1252). You can use latin1_general_ci.
The suffix in MySQL collations has the meaning of FoxPros collation sequences. _bin is the same as "MACHINE" in vfp, but unlinke the rushmore optimization engine, MySQL can also optimize queries of data in GENERAL or language specific collation sequences. _bin doesn't mean at all you store data binary, that's the nature of blob or other binary fields.
Define your Text fields as TEXT, not as TEXT BINARY, because such a field is even closer to a Blob as a VFP Memo (Binary) is to a VFP Blob. If you want Text in Text fields, and not binaries like files, then make it TEXT.
Bye, Olaf.
Really, in the Memo...
The UTF8 - ANSI conversion can do ODBC driver for you.
The UTF8 - ANSI conversion can do ODBC driver for you.
conversion by the driver? Hm, you don't mean sys functions, do you?
You can and need to specify a collation for the connectiond. And in my experience this has to match the database/table collation. If you mix collations you get very unpredictable results, not conversion from one to the other.
You can do COLLATE in Select queries, to convert from the table collation to the collation of the connection, and if you don't you rather not get converted texts. That's needed, when you can't choose the collation of data in MySQL. But if you can choose, choose matching collations overall and you don't need to convert anything.
Actually what you set in the connection string is the charset. the collation in the sense of sorting order is independant of that anyway.
Bye, Olaf.
You can and need to specify a collation for the connectiond. And in my experience this has to match the database/table collation. If you mix collations you get very unpredictable results, not conversion from one to the other.
You can do COLLATE in Select queries, to convert from the table collation to the collation of the connection, and if you don't you rather not get converted texts. That's needed, when you can't choose the collation of data in MySQL. But if you can choose, choose matching collations overall and you don't need to convert anything.
Actually what you set in the connection string is the charset. the collation in the sense of sorting order is independant of that anyway.
Bye, Olaf.
Yes, my ODBC driver allows to set the output character set probably as the equivalent to the charset in connection string.
ASKER
Thanks guys - I made use of what pcelba suggested and it suited my needs.
Just a fyi - I've been using a couple different VFP programs that connect to mysql for over a year now. I've never encountered any kind of problem like this before.
The other mysql dbs all use latin1, where this one is utf8 (not a problem) that is collated as utf8_bin (the problem).
Unfortunately I don't have the option to change this, and have to work with it as is.
This bin collation stuff has caused me many hours of extra work over the last week and a lot of extra stress.
All I can say is I am really glad you guys are here - I don't know what I'd do without your help!!
Just a fyi - I've been using a couple different VFP programs that connect to mysql for over a year now. I've never encountered any kind of problem like this before.
The other mysql dbs all use latin1, where this one is utf8 (not a problem) that is collated as utf8_bin (the problem).
Unfortunately I don't have the option to change this, and have to work with it as is.
This bin collation stuff has caused me many hours of extra work over the last week and a lot of extra stress.
All I can say is I am really glad you guys are here - I don't know what I'd do without your help!!
You are welcome.
MySQL is definitely a good option because we may expect better support from MySQL development team and community than Microsoft offers for MS SQL Server. The bug SQL Native client which causes zero column length retrieval in VFP and other tools is ignored for years from Microsoft side...
MySQL is definitely a good option because we may expect better support from MySQL development team and community than Microsoft offers for MS SQL Server. The bug SQL Native client which causes zero column length retrieval in VFP and other tools is ignored for years from Microsoft side...
I really can't imagine the _bin is the problem, that is really just about the sort order. UTF8 is UTF8. In your case it must be the specifics of single tables or even single fields, that caused your problems.
Also, for english text utf8 is mostly equal to latin1, the encoding differs for example in german umlauts and many other characters, the lower 128 chars including some control characters are equal to ascii/latin1/cp1252 and some other code pages.
I suggest you take a look at what SHOW TABLES (send that as mySQL command) tells you and what SHOW COLUMNS (again mysql) tells you about the fields. Any one can differ from the datbase default and anyone can be defined as BINARY, which differs from the _bin suffix of the collation. Seems you haven't llistened about what I said about that already: The _bin in the collation name just means sort order compares to VFPs "MACHINE". That's all. It doesn't make fields binary.
Bye, Olaf.
Also, for english text utf8 is mostly equal to latin1, the encoding differs for example in german umlauts and many other characters, the lower 128 chars including some control characters are equal to ascii/latin1/cp1252 and some other code pages.
I suggest you take a look at what SHOW TABLES (send that as mySQL command) tells you and what SHOW COLUMNS (again mysql) tells you about the fields. Any one can differ from the datbase default and anyone can be defined as BINARY, which differs from the _bin suffix of the collation. Seems you haven't llistened about what I said about that already: The _bin in the collation name just means sort order compares to VFPs "MACHINE". That's all. It doesn't make fields binary.
Bye, Olaf.