Mariadb and utf8

  1. 10 months ago

    Hello,

    There are strange characters on fields retrieved from a table of database, however retrieving records using heidi or navicat are shown correctly.
    Database is mariadb and collate is utf8_general_ci

    What am I doing wrong ?

    Thank you

    It sounds like an encoding issue, when retrieving the data, do you define the encoding as utf8 when retrieving the data. Take a look in the xojo manual at Define Encodings and try below to see if you get a better result.

    result=DefineEncoding(rs.IdxField(j).StringValue, Encodings.utf8)
  2. Emile S

    27 Nov 2018 Europe (France, Strasbourg)

    The font used to display the non ASCII texts ?

  3. Datata is retrieved using recordset

    rs.IdxField(j).StringValue

  4. Emile S

    27 Nov 2018 Europe (France, Strasbourg)

    This seems correct if the data are strings…

  5. Paul B

    27 Nov 2018 Pre-Release Testers, Xojo Pro Answer Europe (UK, Somerset)
    Edited 10 months ago

    It sounds like an encoding issue, when retrieving the data, do you define the encoding as utf8 when retrieving the data. Take a look in the xojo manual at Define Encodings and try below to see if you get a better result.

    result=DefineEncoding(rs.IdxField(j).StringValue, Encodings.utf8)
  6. The table schema is

    CREATE TABLE `test`.`test` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `NAME` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    PRIMARY KEY (`ID`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 13640 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

  7. Louis D

    27 Nov 2018 Pre-Release Testers, Xojo Pro QC, Canada

    Regardless of the table definition, please give @Paul Bs suggestion a try. You may well be happy with the results.

  8. Sascha S

    27 Nov 2018 Pre-Release Testers, Xojo Pro Germany, Lower Saxonary

    @Jaume J Datata is retrieved using recordset

    rs.IdxField(j).StringValue

    AFAIK those Strings have no defined Encoding. You have to define the Encoding like @Paul B wrote.

  9. Paul, you were right. it is working fine.
    Thank you.

  10. Xojo works using utf8, database encoding is utf8. Do you know the reason why we have to encode ? besides sqlite doesn't require to encode.

    thank you

  11. Alberto D

    5 Dec 2018 Pre-Release Testers

    Norman once told me this:

    You need to define the encoding when you retrieve it just like you would with any "external" data

  12. David C

    6 Dec 2018 Pre-Release Testers, Xojo Pro Derby, ITM
    Edited 10 months ago

    You can, and you need to, define the encoding inside a SQLite database WHEN you create it. Despite this, I set the encoding on any relevant text coming from a SQLite database, just to make sure. Same with MySQL.

  13. 8 months ago

    I'm going to be mad.

    I created a Mariadb database using utf8 collation. It works fine using encodings every time I read a record from database.

    Later, I installed prestashop (mariadb database, utf8 collation) and updated some records from the first mariadb database using a string variable

    sql ="UPDATE pso_customer SET name=" + chr(34) + rs.field("name").StringValue + chr(34) + " WHERE " ...

    After that, strange characters are shown on prestashop database.

    Any ideas ?

    Thank you

  14. Dave S

    8 Jan 2019 San Diego, California USA

    not related to your original question.... but I highly suggest the use of PREPARED STATEMENTS especially in an UPDATE like that on

or Sign Up to reply!