I have a MySQL database application thats causing me an unusual problem.
I fill in various text fields with a persons data (name, address, etc). I also have a ListBox with two columns, the first being CheckBoxes, the second being category names. The program loops through the items in the ListBox and adds the category names for any checked items to the data to be saved. Just before saving, I use ConvertEncoding on the entire SQL statement to ensure that foreign names with accented charactrers are saved properly.
When I fill in fields for, say, Peter Rodwell and check one or more CheckBoxes in the ListBox, everything gets saved just fine.
When I fill in the fields for José Muñoz WITHOUT checking any boxes, this too is saved properly.
[quote=69452:@Norman Palardy]If you set up mysql so it could accept UTF-8 data then make sure
when you connect make sure you do sqlexecute(“set names utf8”)
when you read data back from the database make sure you call DEFINE ENCODING on the string values
how are you checking what “gets saved” as some database tools do NOT show encoded data properly[/quote]
Did that - and it worked!
This was already done.
I’m using MySQL-Front (http://www.mysqlfront.de/). It’s for Windows only but it’s quite powerful and as far as I can tell, does indeed show encoded data correctly, perhaps because its authors are German and understand accented characters.
Thanks for your help!
It’s so complex right now that I wouldn’t dare show it in this august forum. Having now gotten it working, maybe I’ll do a simplified version – I still don’t understand how checking a CheckBox can alter encoding…
[quote]Checking a checkbox doesn’t alter the encoding
Without seeing how you add data to your database its hard to know why you would get the effect you did[/quote]
OK: Each person in the database can belong to one or more groups (Administration, Sales, etc). When entering data for a new person or editing data for someone already in the database, I display a set of fields (39 of them!) which can be filled in and/or edited. If the person is already in the database, his or her data are shown, otherwise the fields are all blank. One field is a list of the groups to which the person belongs, shown in a ListBox with two columns, the first being CheckBoxes, the second containing the group names. The group names come from another database with the same encoding as main one.
When saving the new or edited data, the program loops through the ListBox and copies the group name from the second column wherever it finds a checked CheckBox in the first column. If there is more than one checked group, the group names are concatenated with “$” as a separator. All of that has worked just fine.
The problem was that if the person’s first name or surname included accented characters, it was scrambled IF AND ONLY IF one or more CheckBoxes were checked. Saving an accented name WITHOUT checking, or saving a name WITHOUT accented characters and WITH or WITHOUT checking, worked correctly.
To save, the data are copied into an array, “contents”. I get the FieldSchema from the database into another array, “fields”. For a new user, I start the SQL statement with “INSERT INTO people VALUES (” and then loop through “contents” adding each one to the statement, with the relevant punctuation. For an existing user, I start the SQL statement with “UPDATE people SET " and loop through both the “fields” and “contents” arrays to form the “field=value” pairs, again with the proper punctuation, and then ending with " WHERE user=” and the user’s name. This also works correctly except in the circumstance described above.
I’d bet reading it from the DB then trying to shove it back in probably did because I’d bet you didn’t read it & define Encoding on the data when you read it and then wrote it again
But without code that’s just my educated guess as to what transpired
JUST FYI that concatenating vales together to create a field you can treat as an array is a VERY sure sign your db model is not normalized very well & will clause you grief (just FYI)
I assume a person has some unique ID ?
And there’s a table of “Groups” and those groups have ID’s ?
Then a person is a member of a group if you create a table PersonGroups with two columns - PersonID, GroupID
To check who is in the groups you grab all the rows for a specific group ID
To see what groups a person is a member of you grab all the rows for that persons id
And you don’t have to do concatenating of values using $ and have a nicely normalized model
And your groups table could support “group made up of groups” and yeah
With what you gave now how do you find everyone who use in the Adminstration group ?
You probably have to do a “like” query on the field that holds those group memberships ?
Oh yes I did! I’ve been using encoding conversions with databases for years and never come across this particular problem before. I live in Spain and all my programs are at least bilingual (English + Spanish) and some have 6 language options (English, Spanish, French, German, Italian and Portuguese).
[quote]I assume a person has some unique ID ?
And there’s a table of “Groups” and those groups have ID’s ?
Then a person is a member of a group if you create a table PersonGroups with two columns - PersonID, GroupID
To check who is in the groups you grab all the rows for a specific group ID
To see what groups a person is a member of you grab all the rows for that persons id
And you don’t have to do concatenating of values using $ and have a nicely normalized model
And your groups table could support “group made up of groups” and yeah[/quote]
While you’re undoubtedly right, unfortunately this would affect a whole set of legacy programs (not written by me) that use the same data. I’m stuck with the databases as they are, at least for now (see below).
[quote]With what you gave now how do you find everyone who use in the Adminstration group ?
You probably have to do a “like” query on the field that holds those group memberships ?[/quote]
I do a LIKE right now and it works OK. It differentiates perfectly between “Domestic Sales” and “Foreign Sales”, for example.
At some point I’ll probably be asked to re-write the old programs (a prospect that fills me with dread). That will be the time to re-think the databases.
If you’ve not added or not had the “set names utf8” before then I’m not sure how you did it correctly
The data should have been getting mangled all along
What you described in the first post is precisely the problems i would have expected from the outset since thats not how to handle encodings properly with a DB. But since I can’t see the code I’m just supposing
You don’t need to convert the data on the way IN to the DB.
The plugin sends UTF8 data TO Mysql and if you’ve told it, via set name UTF8", to expect UTF8 data it just works
You do need to DEFINE the encoding when you read the data - and with mysql you should call “set names utf8” (or whatever encoding you ARE expecting data to be delivered in) then in your code where you read the data do DEFINE ENCODING
Correct but you could not just have a group called “Sales” as it would find both and that may not be what you want
Nicely normalized data makes your life easier in the long run - but rewrites can be exasperating
