PxPlus User Forum

Twitter Twitter Twitter

Author Topic: character set option  (Read 1867 times)

Thomas Bock

  • Diamond Member
  • *****
  • Posts: 177
    • View Profile
character set option
« 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.

Mike King

  • Diamond Member
  • *****
  • Posts: 3811
  • Mike King
    • View Profile
    • BBSysco Consulting
Re: character set option
« Reply #1 on: July 12, 2019, 10:56:12 AM »
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.
Mike King
President - BBSysco Consulting
eMail: mike.king@bbsysco.com

Thomas Bock

  • Diamond Member
  • *****
  • Posts: 177
    • View Profile
Re: character set option
« Reply #2 on: July 15, 2019, 10:32:36 AM »
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.

Mike King

  • Diamond Member
  • *****
  • Posts: 3811
  • Mike King
    • View Profile
    • BBSysco Consulting
Re: character set option
« Reply #3 on: July 15, 2019, 11:59:12 AM »
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?
Mike King
President - BBSysco Consulting
eMail: mike.king@bbsysco.com

Thomas Bock

  • Diamond Member
  • *****
  • Posts: 177
    • View Profile
Re: character set option
« Reply #4 on: July 16, 2019, 02:28:11 AM »
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

Devon Austen

  • Administrator
  • Diamond Member
  • *****
  • Posts: 382
  • Don’t Panic
    • View Profile
    • PVX Plus Technologies
Re: character set option
« Reply #5 on: July 16, 2019, 08:52:26 AM »
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?
Principal Software Engineer for PVX Plus Technologies LTD.

Thomas Bock

  • Diamond Member
  • *****
  • Posts: 177
    • View Profile
Re: character set option
« Reply #6 on: July 17, 2019, 02:13:35 AM »
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.