Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

What encoding shall be applied VFP9 & MySQL & PHP in order to get right chars on the screen?Helpful Member! 

Eliott (Programmer) (OP)
21 Dec 12 17:35
Today I successfully installed MySQL 3.51.30 on local computer running Win7/32bit and codepage windows-1250, where is Apache 2, MySQL 5.5.12 and PHP 5.4 and VFP9. I created MySQL database with character set utf-8, collation utf_general_ci. After that I made small plain VFP form with update-able view using:

CODE --> VFP9

lcStringConn="Driver={MySQL ODBC 3.51 Driver};Server=&lcServer;Port=3306;Option=16384;Stmt=;Database=&lcDatabase;Uid=&lcUser;Pwd=&lcPassword" 
I wrote few records into MySQL table from this VFP's form where some fields contained national (diacritics) characters and records were inserted into table without hassle. I made few time connect/disconnect, I was flipping through the records left and right and characters showed as they was when I wrote them. Then, I made PHP script, saved it encoded as UTF-8 and I wrote also in header

CODE --> PHP

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="hr">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<meta http-equiv="Content-Language" content="hr" />
</head> 

but national characters appeared wrong, like as they looks odd in table preview of some MySQL management software (MySQL Fornt or phpMyadmin). I tried to solve it by changing encoding of PHP-script in meta-tags and/or saving as encoded file (ANSI, UTF8) but w/o success. Then I tried to add a option in connection string: charset=UTF8, but records with national chars now appeared incorrectly in VFP form, and if I try then to add even single national char into any form's field then ODBC reported error:

CODE --> message

Connectivity error:[MySQL][ODBC 3.51 Driver][mysqld-5-5-12] Incorrect string value: '\xE6 ...' for column 'lastname' at row 1 
and 4 buttons: Cancel, Suspend, Ignore and Help. What ever I push there is no way to close view nor make exit from VFP except by using Task manager mad. Now I don't know what kind of encoding to pick in order to make national characters is displayed correctly in VFP forms and in PHP scripts too under running Win7 (code page windows 1250)!? Is there some kind of conversion during update from VFP or before loading data from MySQL table? I don't have a idea what thing caused such behavior of VFP & PHP? Is here some members who had similar problems with displaying and recording national characters? Some conclusion for share? Thank you in advance.

There is no good nor evil, just decisions and consequences.

vhad (Programmer)
28 Dec 12 17:58
Have not tried this.
But why don't you try using Mysql-connector 5.11 instead of 3.51?

Regards

Vince
vhad (Programmer)
28 Dec 12 18:02
Sorry,
I meant 5.1

Regards

Vince
Eliott (Programmer) (OP)
28 Dec 12 18:54
Hi Vince, thanks you stopped by,
I tried later but no improvements. I tried to add option charset=utf-8 in connection string but then VFP crashed itself and I gave up. I think to make some custom function for two ways search 'nd replacement but looks like huge job without any promises. Funny, but thought that these new ODBC 5.* are more "error-proof" and more user friendly... I presume that people from Germany and other states with special chars have also their problems very similar like I have already... maybe someone with some idea will soon appear here, just a New year is near and less visitors on forums.

There is no good nor evil, just decisions and consequences.

OlafDoschke (Programmer)
29 Dec 12 5:56
You can't have ANSI on the VFP side, if you connect with charset=UTF8 the mysql ODBC driver does only accept valid UTF-8 chars and rejects \xE6 as the error says, which is CHR(0xe6). That's all. Either STRCONV() all your VFP texts to UTF-8 or setup all components to support codepage 1250. Since VFP is bad at UTF-8 you should setup MySQL to ANSI too, like you did with Apache.

Also, if Apache is setup to serve windows-1250 codepage, this is put into http headers and inconsistent with your HTML header specifying UTF-8. This will make browsers work in quirk mode and render your HTML differently and non standards conform.

By the way: The way you did it initally without option charset=utf-8 mysql doesn't check what you pass in, you also get out what you put in, but if you look at data in the browser via PhpMyAdmin you would see this shows up as garbage, and in a web page it would also show up as garbage.

Get everything in line, then it will work.

Bye, Olaf.
vhad (Programmer)
29 Dec 12 6:09
Hi Elliot,
I am no expert.
I usually do program by trial and error.
So, sorry, I have no idea why it is not working in your place.

But, I successfully insert data into mysql with SQL PassThrough
And show exactly 'è' in VFP and PhpMyAdmin

CODE -->

LOCAL cConnString, nHandle, cSQL

cConnString = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;PORT=3306;DATABASE=dbname;USER=root;PASSWORD=anypass;OPTION=279043"
nHandle = SQLSTRINGCONNECT(cConnString)

cSQL = "INSERT INTO test (name) VALUES ('è')"
	
SQLPREPARE(nHandle,cSQL,"")
IF SQLEXEC(nHandle) > 0
	WAIT WINDOW "OK"
ELSE
	WAIT WINDOW "ERROR"
ENDIF

SQLDISCONNECT(nHandle) 

I am using XAMPP 1.80, Apache 2.4.2, MySQL 5.5.25a, PHP 5.4.4, VFP9 SP2,
OS Windows XP Pro SP3

Hope this can gives you some direction.

Regards,

Vincent
Helpful Member!  OlafDoschke (Programmer)
29 Dec 12 7:00
vhad, you specify almost anything neede, but not what collation sequence your database has, so PHPmyAdmin also shows è. Additional to that è is not a very extensive test. It is ASCII char 2332, so it's a double byte char in UTF-8, but you should have more tests and also see what codepage the browser tells it uses. The è can also show up right, if the browser detects "what you mean" and corrects the error. That means a double conversion can lead back to the right char.

Elliot, simply try to generate a cp1250_general_ci mysql database, skip the charset option of the connection and create HTML specifying charset=ISO-8859-2 in it's header, then everything is in line.

Bye, Olaf.
Eliott (Programmer) (OP)
29 Dec 12 8:24
Olaf, Vhad... thank you for your valuable inputs. I presume that some "line of equal" shall exist but in many articles I have read that for non-English language UTF-8 coding information in database is the best way to avoid later any trouble. So I did it. I'll try to make stuff according to your ideas and I hope it will work.
Funny but I must confess, due this problem agonized I almost started to learn Lianja even VFP shall be alive next few years (hopefully). PHP is nice but hard coding all things that exists in VFP's forms is painfully, even with the help of Javascript.
Thanks again.

There is no good nor evil, just decisions and consequences.

OlafDoschke (Programmer)
29 Dec 12 8:58
It's true you can store much more languages and other special chars in UTF-8 and it's also very widespread in webapplications, but VFP can't cope with UTF-8 in controls, you always have to convert to ANSI and back to make use of VFP. UTF-8 is okay, if your main goal is to support web users and your VFP frontend only applies to some administrative users of a website, but then you also would normally do that administrative part on the website itself and VFP wouldn't play a role.

If your main goal is changing from DBF to MySQL data storage, then Apache is not needed.

The typical middle- and western european codepage is 1252, 1250 is good for middle- and east european countries, if that matters.

If using an oledb provider instead of odbc there is an easy way of autoconverting via SYS(3101,x) (see help on sys functions), but that only does autoconvert, if going to COM/OLE, not if using ODBC. There is no MySQL OLEDB Provider from Oracle or MySQL AB, there only are third party drivers, eg http://cherrycitysoftware.com/ccs/providers/ProvMy...
Then there is SYS(987,.T.), buit it merely converts to Unicode, not UTF-8. And last not least there is of course STRCONV. But as you can't bind to neither UTF-8 nor Unicode text with VFP controls choosing a codepage and using MySQLs abilities to support the same code page are giving you less effort. Otherwise you have to take VFP out of the equation to easier cope with more general charsets like UTF-8 or Unicode.

If you're sure you want to stay VFP and still unsure how exactly to combine it with MySQL, I suggest Hentzenwerke http://www.hentzenwerke.com/catalog/mysqlvfp.htm
From the TOC it's unclear, if it specifically addresses charsets, but for sure you get samples working from VFP to MySQL back and forth.

Bye, Olaf.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close