Backups issues with accentuated characters

[swierzbicki]

Hello,

I've an issue with my backups where accentuated characters aren't correctly saved.
Here is my database definition :

Code: Select all

CREATE DATABASE myDB
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'French_France.1252'
       LC_CTYPE = 'French_France.1252'
       CONNECTION LIMIT = -1;
GRANT ALL ON DATABASE myDB TO public;
GRANT ALL ON DATABASE myDB TO postgres;

CREATE TABLE public.evenements (
  ligne VARCHAR(50) NOT NULL,
  id_multicn INTEGER DEFAULT 0 NOT NULL,
  date_debut_evenement TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  date_fin_evenement TIMESTAMP WITHOUT TIME ZONE,
  duree_evenement TIME WITHOUT TIME ZONE,
  id_statut_machine INTEGER NOT NULL,
  id_evenement_multicn INTEGER NOT NULL,
  id_evenement_pilote INTEGER,
  id_poste INTEGER NOT NULL,
  date_poste DATE NOT NULL,
  heure_debut_production TIME WITHOUT TIME ZONE DEFAULT '00:00:00'::time without time zone,
  heure_fin_production TIME WITHOUT TIME ZONE DEFAULT '00:00:00'::time without time zone,
  id_pilote_1 VARCHAR(100) NOT NULL,
  id_pilote_2 VARCHAR(100) NOT NULL,
  duree_production TIME WITHOUT TIME ZONE DEFAULT '00:00:00'::time without time zone,
  duree_piquage TIME WITHOUT TIME ZONE DEFAULT '00:00:00'::time without time zone,
  ordre_fabrication VARCHAR(6),
  compteur_produit INTEGER DEFAULT 0,
  point REAL DEFAULT 0,
  piquage VARCHAR(100),
  cadence_moyenne REAL DEFAULT 0,
  cadence_max REAL DEFAULT 0,
  longueur_deroulee REAL DEFAULT 0,
  plateau VARCHAR(50) DEFAULT ''::character varying,
  compteur_plateau INTEGER DEFAULT 0,
  arret_en_cours BOOLEAN NOT NULL,
  version VARCHAR(50) DEFAULT ''::character varying,
  date_export TIMESTAMP(0) WITHOUT TIME ZONE,
  reference_export VARCHAR(50),
  date_import TIMESTAMP(0) WITHOUT TIME ZONE,
  reference_import VARCHAR(50),
  cadence_instantanee REAL DEFAULT 0,
  point_reel REAL DEFAULT 0,
  position_x REAL DEFAULT 0,
  position_y REAL DEFAULT 0,
  longueur_piquage REAL DEFAULT 0,
  CONSTRAINT evenements_pkey PRIMARY KEY(ligne, date_debut_evenement, date_poste, id_poste, id_pilote_1, id_pilote_2, id_statut_machine, arret_en_cours, id_multicn, id_evenement_multicn)
) 
WITH (oids = false);

For example, insert script for evenements table is generated like this :

Code: Select all

INSERT INTO "evenements" VALUES ('PICPIC 3', '9', '2015-09-23 01:50:16', '2015-09-23 01:52:59', '00:02:43', '1', '-1', NULL, '3', '2015-09-23', '01:16:59', NULL, '26', '52', '00:33:16.189', '02:39:52.204', '359007', '19', '5', E'P:\\Carreaux\\CARREAUX_pav?25x28 coupe 2.7 avec z?o.mcn', '2.04', '3.21', '67.72', 'G20', '19', 'f', '1.0.0.90', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

Where piquage fields 'P:\\Carreaux\\CARREAUX_pav?25x28 coupe 2.7 avec z?o.mcn' should be 'P:\\Carreaux\\CARREAUX_pavé 25x28 coupe 2.7 avec zéro'

This is problematic, after restoring these data, I'll get "invalid byte sequence for encoding "UTF8": 0xe9 0x71 0x75 exception when fetching these malformed data !

Code: Select all

object UniConnection1: TUniConnection
  ProviderName = 'PostgreSQL'
  Database = 'myDB'
  SpecificOptions.Strings = (
    'SQLite.BusyTimeout=10000'
    'SQLite.EnableSharedCache=True'
    'SQLite.UseUnicode=True'
    'SQLite.Direct=True'
    'SQLite.ReadUncommitted=True'
    'PostgreSQL.Charset=WIN1252'
    'PostgreSQL.ApplicationName=App'
    'PostgreSQL.UseUnicode=True')
  Options.KeepDesignConnected = False
  Username = 'postgres'
  Server = 'localhost'
  LoginPrompt = False
  Left = 24
  Top = 8
  EncryptedPassword = 'xyz'
end



object udBackup: TUniDump
  Connection = UniConnection1
  Options.QuoteNames = True
  Left = 64
  Top = 304
end

It looks like backup generated files are ANSI only and not UTF8.
Is their a way to update my tables by replacing all these non readable chars ? Doing my users won't get any exceptions anymore.

Ps : Using latest version on Delphi 10 update 1

[swierzbicki]

Is anybody from Devart monitoring this ?

[AlexP]

Hello,

For correct saving/restoring of your data, you should set the PgConnection.Options.UseUnicode property to True, and the PgConnection.Options.Charset property - to WIN1252

[swierzbicki]

Hello,

UniConnection specifics options are already set to

Code: Select all

    'PostgreSQL.Charset=WIN1252'
    'PostgreSQL.UseUnicode=True'

[azyk]

We tried to reproduce the issue according to your recommendations. However the issue wasn't reproduced.

Please use the pg_dump utility to create a dump of the test table that reproduces the issue, and send it to andreyz*devart*com . In addition, let us know:
- the full version of PostgreSQL;
- Windows regional settings on the client machine. For this, open in explorer
'Control Panel\Clock, Language, and Region' , click Region, let us know the value of the Format checkbox on the Formats tab. Switch to the Administrative tab and let us know the value of the 'Current language for non-Unicode programs:' option.

[swierzbicki]

I've sent you SQL dump + screenshots + demo project reproducing this.

[azyk]

We tried to restore "pgdump\backup.sql" from the sent archive using the pg_restore utility (9.5.1), but got a message: 'pg_restore: [archiver] input file does not appear to be a valid archive'. It looks like the backup.sql file is corrupted. Please make sure this file is restored on your side with no errors and resend it to us.

[swierzbicki]

Please check your email box. You'll find a correct dump.

[azyk]

We have reproduced the described issue using your sample and we continue its investigation. We will notify you about the results as soon as any are available.

[azyk]

We have fixed this bug. This fix will be included in the next UniDAC release.