Characterset issues

Scriptcase 8 Discussion
#1

I have an issue with character sets in a MySQL database:

  • i have set the database to utf8
  • i have set the database connection to utf 8
  • i have set the project to utf 8
  • the form I have problems with are set to utf 8

Still if I use ??? etc. these characters are not stored correctly in the database and I get odd other characters.

If I set the data directly in the database then diplaying it from the db I get small squairs, not the data. I could solve this by putting set names ‘UTF8’ in the onscriptinit.

If I set set names ‘UTF8’ in the onscriptinit of the problem applications, than a lot of data is displayed badly on the screen…

Does anybody have clues how to solve this issue? In test it all works well, I have this issue only when I deploy the application to production.

#2

I have the same inssues

#3

Evoke the following sql statement to see if everything is set to utf8.

show variables like ‘char%’;

You should get

  | character_set_client        | UTF-8                       
  | character_set_connection    | UTF-8                       
  | character_set_database      | UTF-8                       
  | character_set_filesystem    | binary                    
  | character_set_results       | UTF-8                       
  | character_set_server        | UTF-8                       
  | character_set_system        | UTF-8                       
  | character_sets_dir          | /usr/share/mysql/charsets/

Also check the php.ini for

default_charset = “utf-8”;

just to be sure.

jsb

#4

[QUOTE=jsbinca;37380]Evoke the following sql statement to see if everything is set to utf8.

show variables like ‘char%’;

You should get

  | character_set_client        | UTF-8                       
  | character_set_connection    | UTF-8                       
  | character_set_database      | UTF-8                       
  | character_set_filesystem    | binary                    
  | character_set_results       | UTF-8                       
  | character_set_server        | UTF-8                       
  | character_set_system        | UTF-8                       
  | character_sets_dir          | /usr/share/mysql/charsets/

Also check the php.ini for

default_charset = “utf-8”;

just to be sure.

jsb[/QUOTE]

Tnx jsb,

I’ve seen this post from you before, issue is that phpMyAdmin is doing things right. Thats php/mysql so it’s mysterious for me. In dev it’s working well. But characterset server is latin!
I’ll ask my provider.\

Variable_name Value
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/

#5

[QUOTE=aducom;37382]Tnx jsb,

I’ve seen this post from you before, issue is that phpMyAdmin is doing things right. Thats php/mysql so it’s mysterious for me. In dev it’s working well. But characterset server is latin!
I’ll ask my provider.\

Variable_name Value
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/[/QUOTE]

For others that might encounter this problem, it’s solvable by using sc_exec_sql(‘set names “UTF8”’).

#6

I’m pretty sure that’s the problem.

jsb

#7

[QUOTE=jsbinca;37392]I’m pretty sure that’s the problem.

jsb[/QUOTE]

I am too. Problem is that providers are useually not willing to change it as it influences all their customers. It means moving to another hoster or apply the ‘set names’ tactically.

#8

same with me…ever have in issue…

#9

I still don’t get it, I cannot change the default charset from the server. The provider changed my php.ini and what happens that if i use ???test they are presented on the form as ???test when I retrieve the data from the database.
However if I look into phpMyAdmin the data is shown as Èéèëtest. If I display the data in a grid then it’s displayed as ???test but if I export the data then two things occur. In MsWord the data is shown as ???test but the export to Excel shows Èéèëtest

I used set names but then you still have issues. the data is stored in the database in a readable form so if I go to phpMyAdmin then the data is show ok as ???test. But if I retrieve the data from the database then I see black squares on the position of the ???.

Is there a possible solution for this? Or am I forced to move over to another host?

#10

?ˆéèëtest

This will occur when trying to convert a string to UTF-8 format and it is already an UTF-8 format. So it breaks up the string.

Correct me if I am wrong, but SC’s export to MSWord works fine and SC’s export to MSExcel breaks the string, is that correct? If it is the case it has a good chance to be a bug and it needs to be checked.
However I am not sure about charset configurations on Microsoft’s softwares, I know the Windows work with ISO (and that sucks).

Windows + PhP generate some weird charset issues (wich PhP is trying to fix and I belive it will be fixed at version 5.6 - 5.7), but you might well know that we can’t migrate to the newest php version because of our encryption.

#11

[QUOTE=Cavadinha;37757]This will occur when trying to convert a string to UTF-8 format and it is already an UTF-8 format. So it breaks up the string.

Correct me if I am wrong, but SC’s export to MSWord works fine and SC’s export to MSExcel breaks the string, is that correct? If it is the case it has a good chance to be a bug and it needs to be checked.
However I am not sure about charset configurations on Microsoft’s softwares, I know the Windows work with ISO (and that sucks).

Windows + PhP generate some weird charset issues (wich PhP is trying to fix and I belive it will be fixed at version 5.6 - 5.7), but you might well know that we can’t migrate to the newest php version because of our encryption.[/QUOTE]

It is true that we develop on the windows platform but on windows it all works well. The problem exists on a linux platform. The characterset of the server is latin, all the other is utf8. I can live with the fact that the data is stored in a funny way as long as I get the data back in utf8 which seems to be the case. But yes, if you export it to excel you get the funny chars, if you export to word it goes ok.

But the best way is that the data is stored in the same way as it is used in the application. It’s nice that data is stored as ?ˆéè? but in the application shown as ???, but I would like to see ??? in the form as well as in phpMyAdmin. Using ‘set names UTF8’ on MySQL is giving uncertain results as it will store the data ok, but retrieving it goes wrong.

  • i have set the database to utf8 and all tables are utf8
  • i have set the database connection to utf 8
  • i have set the project to utf 8
  • the form I have problems with are set to utf 8

ariable_name Value
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/

#12

To show the issue I created a short movie. As the cherry on the pie I tried a small test application. Same issue, but the other way around. All applications where fully regenerated, and fresh deployed on an empty storage. So no overwriting problems etc. The results of the applications are not consistent which is very odd. What’s going on here?

http://aducomportal.nl/movies/bug.mp4

#13

[QUOTE=aducom;38148]To show the issue I created a short movie. As the cherry on the pie I tried a small test application. Same issue, but the other way around. All applications where fully regenerated, and fresh deployed on an empty storage. So no overwriting problems etc. The results of the applications are not consistent which is very odd. What’s going on here?

http://aducomportal.nl/movies/bug.mp4[/QUOTE]

Give me a couple days, I’ll take a look.

#14

That’s ok. In addition, we’re talking about the same database etc. The issue occurs on a linux server system where all is utf8 except the server itself as the settings are show in an earlyier post, it works well on a windows server system.

#15

Is issue fix?

Any solution on characterset issues

#16

Unless the issue has returned, it was solved in one of the history updates.

#17

A dedicated server, or VPS, allows you the flexibility to setup your MySQL environment as you like.

It’s good to know there is a ScriptCase macro to work around this problem.

#18

The problem in this case was that the macro’s where not the (temporary) solution. I mean, yes you could get the data correct on the screen, but in the database is was not well. But this is an old (solved) issue, so let it rest. And yes. If you have control of your server like in dedicated or virtual you have more means. But we as softwarebuilders need to deploy on the machines of the client(s). There it’s more difficult.

#19

Old issue, however this has popped up for me again in the last few months on a dedicated whm/cpanel server. SC form saving incorrectly into MySQL database. The MySQL connection was set to UTF8 in prod environment. the project was set to UTF8, app to UTF8… I couldn’t figure it out.

show variables like ‘char%’ also said that the server encoding was latin1. I changed it in the my.cnf file using:

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

And now the scriptcase form is saving into the DB correctly.

Any thoughts on anything else that may be misconfigured in SC? It is weird that the server needs this ‘hack’ to make UTF8 work.

#20

Maybe it is the fault of SC? The MySQL query log shows the following which distinctly lacks “SET NAMES ‘utf8mb4’” that sets the connection to UTF8 and fixes the issue. Setting the server character set to UTF8 is most likely just a global “SET NAMES ‘utf8mb4’”.

1107 Connect ca_prod@localhost on ca_online using Socket
1107 Query SET AUTOCOMMIT=0
1107 Query START TRANSACTION
1107 Query select count(*) AS countTest from email_templates where id = 29
1107 Query UPDATE email_templates SET…
1107 Query COMMIT