Mariadb and utf8

  1. 7 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 7 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 Montreal, 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

    @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. 6 months ago

    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 6 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. 5 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

    Jan 8 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!