ODBC updating field in MySQL Linux hosted DB

[diarmaid]

I have a problem using an ODBC connection & a VFP remote view to insert records into a table.

If I update a field (VARCHAR(100)) using the SQLyog utility I can write a value, e.g. "fixed text", and it is stored fine. If I use my Remote View to make the same change it appears ok when viewed in either SQLyog or in a VFP browse window.

However, it must be different because when it gets processed by an application (PHP based Interspire SC) the value updated via SQLyog works but not the one updated via ODBC/VFP Remote View.

SQLyog is an SQL management utility,

http://www.webyog.com/en/

Is this some character translation issue or should I be padding the values with some special characters when updating?
Any advice greatly appreciated ...

Thanks,
Diarmaid

 

[Olaf Doschke]

What codepage/charset is the mysql database set to? Nowadays the typical default is utf-8. You'll need to bear in mind, when querying into a foxpro cursor, that will have ansi codepage 1252 typically, which goes well with Latin1 or ISO-8859-1, even though there are slight differences to MS codepage 1252, but if the database is unicode or utf-8 this will display fine in VFP as you entered it, but just because vfp does no conversion, so the error only displays via PHP in webpages designed with charset utf-8.

SYS(987) might help here or use STRCONV() both after reading in and before writing back.

Bye, Olaf.

 

[diarmaid]

Olaf,

Thanks for you earlier reply. I am still struggling for a solution.

I know that using ODBC as such isn't the problem, as all works fine in an Access view of the remote data.

I know the remote data is utf-8, but I don't know what my Windows 7 PC is ... I have tried using all 16 values of STRCONV() to write the data but none work, e.g.

REPLACE VARCHARfieldname WITH STRCONV("fixed",11)

where '11' varies.

It is just the one field that is a problem, are VARCHAR fields special in some way? It seems to be all down to the remote view in VFP, should I look to editing the SQL behind that remote view in my VFP database?

I am also experimenting with SPT but it is proving very sensitive in syntax and a nightmare to figure out what should be and what is happening.

Any direction on this would be appreciated ...

Diarmaid

 

[Olaf Doschke]

A Database has a codepage, an OS not so much. You could say Windows is Unicode based, still VFP is ANSI.

When writing BACK to MySQL you need he STRCONV to convert TO utf-8, not FROM utf-8, so you'll need STRCONV(ansistring,9) and your cursor field should be binary, so eg you'd need a varbinary instead of varchar field on the vfp side, or at least a varchar (binary) field. Other fields do autoconversions according to the dbf codepage with the values put into them you won't want. This is a concept you should know, dbf are having a codepage, for most european and us countries that will be Ansi 1252 codepage.

Ideally the MySQL database you use with VFP is NOT utf-8, but if you have no influence on that, that's what you need to deal with.

Bye, Olaf.

 

[diarmaid]

Olaf,

Many thanks for your direction in this, my issue is resolved with success!

I turned out fairly simple in the end, all I had to do was edit the SQL behind the VFP Remote View of the MySQL table, don't need to use SPT direct at all.

For the VARCHAR field I edited the following, note the "V(10) NOCPTRANS" in place of what was "C(100)"

DBSetProp(ThisView+".discountmethod","Field","DataType","V(10) NOCPTRANS")

I didn't need to refer to STRTRAN() at all.

I can appreciate the codepage issue better now, though I'm not sure I fully understand what happened, but it works now.

Thanks again,

Diarmaid

 

[Olaf Doschke]

good,

both remote views and cursoradapters give you control about the field type of the result. ODBC driver options also influence what VFP receives, but indeed SPT gives less control. If, then rather through using CAST() or CONVERT() within the SQL command. eg SELECT CONVERT(field USING latin1).

Bye, Olaf.