Curosor adapter right padding with spaces

[D-Ward]

Hi all,

Am updating a MySQL database with a CA, when I open the VFP table the string fields show as being trimmed, when I call the CA and insert the data remote cursor shows the data as trimmed, but when I do the update the MySQL data on the server shows the string fields as being right padded with spaces.

I have checked the MySQL setup, have inserted records manually into the database and they are trimmed with no spaces, so it looks like the CA might be doing it, anyone seen this before or have any ideas where to look other than putting some conditions in the MySQL database?

 

[Chris Miller]

I try to understand the problem. I think it is varchar vs char, is it? VFP supports varchar (which means no automatic padding with spaces in values shorter than the char field. On the other hand, a char field, also in MySQL is not a varchar and is constant width, so it will pad with spaces, too, that's not VFP specific. So if you insert into a char(6) field with a statement having an "abc" value, the char(6) field will still end up as "abc ".

For varchar vs char, there are also some ODBC options to set with MySQL, look into that:https://dev.mysql.com/doc/connector...ection-parameters.html#codbc-dsn-option-flags

Are you useing a parameterized query with the CA, and in general, how is it set up in detail.

I'm sure it's not CA that adds spaces or something along that, the CA does only forward the query and values (if parameterized also parameters) to the MySQL server, which then executes it. Or are you using VFP7 or lower, then there is no VFP side varchar support, that was introduced in VFP8, improved and bugfixed in VFP9. Silly question, CA is VFP8 or later, no question.

 

[D-Ward]

Hi Chris,

Looks like it was me mixing VARCHAR on the MySQL end and the legacy database in VFP using CHAR and expecting MySQL to look after the VARCHAR at the back end. Found this from Mr Lewis http://www.ml-consult.co.uk/foxst-36.htm that describes the issue.

Thank you for your help and input, as always.

Darren