PxPlus User Forum
Main Board => Discussions => Programming => Topic started by: Thomas Bock on July 12, 2019, 07:27:26 AM
-
Our application uses ISO8859-15. A MySQL-DB uses uft8 as a default.
If the application uses a prefix file in order to open a sql-table instead of a keyed file, what options and adjustments need to be made in order to keep german umlauts and french accented characters and the euro sign of course?
I tried several things but always gain questionmarks.
-
Have you tried the options as described here:
http://www.mysqltutorial.org/mysql-collation/
It appears you may just need to change the Database or Server to a different character set.
-
I don't think it is a collation thing.
As far as I have read by now it seems to be an issue with the character set been sent by the application. A MySQL- or Maria-DB on an utf8-OS expects utf8 unless you tell him the data uses a different character set. So you need to talk to the "connection". A "set namens 'cp1250'" solves the issue, but I don't have the chance to configure it somewhere, as charset=cp1250 is ignored in the opt-string.
-
The web site we sent you to described two options --- Collate and Charset
CREATE DATABASE database_name
CHARACTER SET character_set_name;
COLLATE collation_name
What did you set the database character set to?
-
The setting for the database and the tables is:
collation: cp1250_general_ci
characterset: cp1250
Here is an example with the german word for the french language:
-}new
-}open(1,iol=*)"data/gd/sgb03"
-}?pth(1)
[MYSQL]koss;gd03_sgb
-}?opt(1)
server=srvr;user=me;pswd=Passwort;prepare=0;concurrency=OPT_
VALUE;extropt=$ for update;timeout=1;cursorclose=YES;cursor_type=DYNAMIC;isolati
on=COMMITED;autocommit=ON;nonumadj=Y;KEY=sgb_nr;REC=sgb_nr:1+sgb_bez:25+iac,iac3
,monat_l01,monat_l02,monat_l03,monat_l04,monat_l05,monat_l06,monat_l07,monat_l08
,monat_l09,monat_l10,monat_l11,monat_l12,monat_k01,monat_k02,monat_k03,monat_k04
,monat_k05,monat_k06,monat_k07,monat_k08,monat_k09,monat_k10,monat_k11,monat_k12
,tag_l1,tag_l2,tag_l3,tag_l4,tag_l5,tag_l6,tag_l7,tag_k1,tag_k2,tag_k3,tag_k4,ta
g_k5,tag_k6,tag_k7
-}dim sgb$:iol(1)
-}sgb.sgb_nr$="3"
-}sgb.sgb_bez$="Französisch"
-}write record(1)sgb$
-}read record(1,key="3")sgb$
-}?sgb.sgb_bez$
Franz?sisch
-
After doing the write you can do a KEN(chan) on the channel to see what the SQL was that PxPlus sent to MySQL. If you look at that after the write does that data being sent in the SQL have the accented characters?
If the answer to that is yes then also answer the following question:
If you look at the data in the MySQL table using the MySQL client does it have the accented characters?
-
PxPlus keeps the accented character
-}?ken(1)
UPDATE gd03_sgb SET `sgb_nr`='3', `sgb_bez`='Französisch ', `iac`=
'' , `iac3`= '' , `monat_l01`= '' , `monat_l02`= '' , `monat_l03`= '' , `monat_
l04`= '' , `monat_l05`= '' , `monat_l06`= '' , `monat_l07`= '' , `monat_l08`= ''
, `monat_l09`= '' , `monat_l10`= '' , `monat_l11`= '' , `monat_l12`= '' , `mona
t_k01`= '' , `monat_k02`= '' , `monat_k03`= '' , `monat_k04`= '' , `monat_k05`=
'' , `monat_k06`= '' , `monat_k07`= '' , `monat_k08`= '' , `monat_k09`= '' , `mo
nat_k10`= '' , `monat_k11`= '' , `monat_k12`= '' , `tag_l1`= '' , `tag_l2`= '' ,
`tag_l3`= '' , `tag_l4`= '' , `tag_l5`= '' , `tag_l6`= '' , `tag_l7`= '' , `tag
_k1`= '' , `tag_k2`= '' , `tag_k3`= '' , `tag_k4`= '' , `tag_k5`= '' , `tag_k6`=
'' , `tag_k7`= '' WHERE `sgb_nr` = '3'
In MySQL Workbench there is a question mark and that is what PxPlus returns after reading that record.