I really need devart help on this !
Method Archiver_Plateau is raising "invalid byte sequence for encoding "UTF8": 0xe9 0x71 0x75." (while Sauver_PlateauEnCours is always working).
Software is running on 6 Windows 7 SP1 workstation. All these workstation are running 32bit PostgreSQL 9.4.4 server. Software is failling on two workstation only (one running a x68 os, the other an x64 os)
Here is th DML for table public.productions_plateaux (working) and public.productions_plateaux_details (exception)
Code: Select all
CREATE TABLE public.productions_plateaux_details (
id_production_plateau_detail SERIAL,
ordre_fabrication VARCHAR(6) DEFAULT ''::character varying,
ligne VARCHAR(10) DEFAULT ''::character varying,
date_poste DATE,
plateau VARCHAR(50) DEFAULT ''::character varying,
date_entree_plateau TIMESTAMP WITHOUT TIME ZONE,
date_sortie_plateau TIMESTAMP WITHOUT TIME ZONE,
compteur_plateau INTEGER DEFAULT 0,
compteur_produit INTEGER DEFAULT 0,
id_poste INTEGER,
id_pilote_1 VARCHAR(10) DEFAULT ''::character varying,
id_pilote_2 VARCHAR(10) DEFAULT ''::character varying,
CONSTRAINT productions_plateaux_details_pkey PRIMARY KEY(id_production_plateau_detail)
)
WITH (oids = false);
CREATE INDEX productions_plateaux_details_date_sortie_plateau ON public.productions_plateaux_details
USING btree (date_sortie_plateau);
CREATE INDEX productions_plateaux_details_ligne ON public.productions_plateaux_details
USING btree (ligne COLLATE pg_catalog."default");
CREATE INDEX productions_plateaux_details_ordre_fabrication ON public.productions_plateaux_details
USING btree (ordre_fabrication COLLATE pg_catalog."default");
CREATE INDEX productions_plateaux_details_poste_pilote ON public.productions_plateaux_details
USING btree (id_poste, id_pilote_1 COLLATE pg_catalog."default", id_pilote_2 COLLATE pg_catalog."default");
CREATE TABLE public.productions_plateaux (
ordre_fabrication VARCHAR(6) DEFAULT ''::character varying NOT NULL,
ligne VARCHAR(10) DEFAULT ''::character varying NOT NULL,
date_poste DATE,
plateau VARCHAR(50) DEFAULT ''::character varying NOT NULL,
date_entree_plateau TIMESTAMP WITHOUT TIME ZONE,
compteur_plateau INTEGER DEFAULT 0,
compteur_produit INTEGER DEFAULT 0,
id_poste INTEGER,
id_pilote_1 VARCHAR(10) DEFAULT ''::character varying,
id_pilote_2 VARCHAR(10) DEFAULT ''::character varying,
CONSTRAINT productions_plateaux_pkey PRIMARY KEY(ordre_fabrication, ligne)
)
WITH (oids = false);
CREATE INDEX productions_plateaux_ligne ON public.productions_plateaux
USING btree (ligne COLLATE pg_catalog."default");
CREATE INDEX productions_plateaux_plateau ON public.productions_plateaux
USING btree (plateau COLLATE pg_catalog."default");
Here is code compiled with Delphi XE8 Update 1
Code: Select all
Const
Plateau_En_Cours =
'SELECT Ordre_Fabrication,Ligne,Plateau,Ligne,date_poste,Date_Entree_Plateau,Compteur_Plateau,Compteur_Produit FROM Productions_Plateaux WHERE Ligne = :Ligne LIMIT 1;';
Plateau_Insert =
'INSERT {if SQLITE} OR IGNORE {endif} INTO Productions_Plateaux (Ordre_Fabrication,Ligne,date_poste,Date_Entree_Plateau,Plateau,Compteur_Plateau,Compteur_Produit,Id_Poste,Id_Pilote_1,Id_Pilote_2) '
+ ' VALUES (:Ordre_Fabrication,:Ligne,:date_poste,:Date_Entree_Plateau,:Plateau,:Compteur_Plateau,:Compteur_Produit,:Id_Poste,:Id_Pilote_1,:Id_Pilote_2);';
Plateau_Update =
'Update Productions_Plateaux set Ordre_Fabrication=:Ordre_Fabrication,' +
'date_poste=:date_poste,Plateau=:Plateau,Compteur_Plateau=:Compteur_Plateau,Compteur_Produit=:Compteur_Produit,Id_Poste=:Id_Poste,Id_Pilote_1=:Id_Pilote_1,Id_Pilote_2=:Id_Pilote_2 where Ligne=:Ligne;';
Plateau_Archive = 'INSERT INTO Productions_Plateaux_Details' + #13#10 +
' (Ordre_Fabrication,Ligne,date_poste,Plateau,Date_Entree_Plateau,Date_Sortie_Plateau,Compteur_Plateau,Compteur_Produit,Id_Poste,Id_Pilote_1,Id_Pilote_2)'
+ #13#10 + 'SELECT' + #13#10 +
' Ordre_Fabrication,Ligne,:date_poste,Plateau,Date_Entree_Plateau,:Date_Sortie_Plateau,Compteur_Plateau,Compteur_Produit,:Id_Poste,:Id_Pilote_1,:Id_Pilote_2'
+ #13#10 + 'FROM' + #13#10 + 'Productions_Plateaux' + #13#10 +
'Where Ligne = :Ligne;';
Plateau_Delete = 'DELETE' + #13#10 + 'FROM' + #13#10 + 'Productions_Plateaux'
+ #13#10 + 'Where Ligne = :Ligne ;';
...
constructor TMultiCN.Create(aOwner: TComponent);
var
begin
inherited;
//....
FInternalConnection := Tuniconnection.Create(nil);
end;
destructor TMultiCN.Destroy;
var
i: Integer;
begin
inherited;
FInternalConnection.Free;
end;
//Before running our thread, we set DB connection
procedure TMultiCN.SetConnection(const Value: Tuniconnection);
begin
if Value.ProviderName = 'SQLite' then
Begin
//We keep the same connection object or we will get "Database is locked errors"
FConnection := Value;
End
else
Begin
//Using our Connection created into our thread instead...
FConnection := FInternalConnection;
FConnection.Disconnect;
//Setting Connection properties
FConnection.ProviderName := Value.ProviderName;
FConnection.Server := Value.Server;
FConnection.Port := Value.Port;
FConnection.Username := Value.Username;
FConnection.Password := Value.Password;
FConnection.LoginPrompt := Value.LoginPrompt;
FConnection.Database := Value.Database;
End;
FConnection.SpecificOptions.Values['SQLite.BusyTimeout=5000'];
FConnection.SpecificOptions.Values['SQLite.EnableSharedCache=True'];
FConnection.SpecificOptions.Values['SQLite.UseUnicode=True'];
FConnection.SpecificOptions.Values['SQLite.Direct=True'];
FConnection.SpecificOptions.Values['SQLite.ReadUncommitted=True'];
FConnection.SpecificOptions.Values['PostgreSQL.ConnectionTimeout=5'];
FConnection.SpecificOptions.Values['PostgreSQL.UseUnicode=True'];
FConnection.SpecificOptions.Values['PostgreSQL.Charset=WIN1252'];
end;
procedure TMultiCN.Archiver_Plateau(Delete: Boolean);
var
FUniQuery: TuniQuery;
begin
FUniQuery := TuniQuery.Create(nil);
FUniQuery.Connection := FConnection;
try
FUniQuery.SpecificOptions.Values['PostgreSQL.UseParamTypes'] := 'True';
FUniQuery.SQL.Text := Plateau_Archive;
FUniQuery.ParamByName('Ligne').DataType := ftString;
FUniQuery.ParamByName('Date_Sortie_Plateau').DataType := ftDateTime;
FUniQuery.ParamByName('Id_Poste').DataType := ftinteger;
FUniQuery.ParamByName('Date_Poste').DataType := ftDate;
FUniQuery.ParamByName('Id_Pilote_1').DataType := ftString;
FUniQuery.ParamByName('Id_Pilote_2').DataType := ftString;
FUniQuery.ParamByName('Ligne').AsString := FLigne.Id_ligne;
FUniQuery.ParamByName('Date_Sortie_Plateau').AsDateTime :=
FPlateau.Date_Sortie_Plateau;
FUniQuery.ParamByName('Id_Poste').AsInteger := FPoste.Id_Poste;
FUniQuery.ParamByName('Date_Poste').AsDate := FPoste.Date_Poste;
FUniQuery.ParamByName('Id_Pilote_1').AsString := FPoste.Id_Pilote_1;
FUniQuery.ParamByName('Id_Pilote_2').AsString := FPoste.Id_Pilote_2;
if not FConnection.InTransaction then
FConnection.StartTransaction;
try
FUniQuery.Prepare;
// Do some actions with database. For example:
FUniQuery.Execute;
// Commit the current transaction to reflect changes in database if no errors were raised
FConnection.Commit;
except
// Rollback all changes in database made after StartTransaction if an error was raised
FConnection.Rollback;
HandleException;
end;
if Delete then
begin
FUniQuery.SQL.Text := Plateau_Delete;
FUniQuery.ParamByName('Ligne').DataType := ftString;
FUniQuery.ParamByName('Ligne').AsString := FLigne.Id_ligne;
if not FConnection.InTransaction then
FConnection.StartTransaction;
try
FUniQuery.Prepare;
// Do some actions with database. For example:
FUniQuery.Execute;
// Commit the current transaction to reflect changes in database if no errors were raised
FConnection.Commit;
except
// Rollback all changes in database made after StartTransaction if an error was raised
FConnection.Rollback;
HandleException;
end;
end;
finally
FUniQuery.Free;
end;
Archiver_Plateau_CSV;
end;
procedure TMultiCN.Sauver_PlateauEnCours(const Value: TPlateau);
var
FUniQuery: TuniQuery;
begin
FUniQuery := TuniQuery.Create(nil);
FUniQuery.Connection := FConnection;
try
FUniQuery.SpecificOptions.Values['PostgreSQL.UseParamTypes'] := 'True';
FUniQuery.SQL.Text := Plateau_Update;
FUniQuery.ParamByName('Ligne').DataType := ftString;
FUniQuery.ParamByName('Ordre_Fabrication').DataType := ftString;
FUniQuery.ParamByName('Compteur_Produit').DataType := ftinteger;
FUniQuery.ParamByName('Plateau').DataType := ftString;
FUniQuery.ParamByName('Id_Poste').DataType := ftinteger;
FUniQuery.ParamByName('Date_Poste').DataType := ftDate;
FUniQuery.ParamByName('Id_Pilote_1').DataType := ftString;
FUniQuery.ParamByName('Id_Pilote_2').DataType := ftString;
FUniQuery.ParamByName('Compteur_Plateau').DataType := ftinteger;
FUniQuery.ParamByName('Ligne').AsString := FLigne.Id_ligne;
FUniQuery.ParamByName('Ordre_Fabrication').AsString :=
FOrdre_Fabrication_En_Cours.Ordre_Fabrication;
FUniQuery.ParamByName('Compteur_Produit').AsInteger :=
FOrdre_Fabrication_En_Cours.Compteur_Produit;
FUniQuery.ParamByName('Plateau').AsString := Value.Nom_Plateau;
FUniQuery.ParamByName('Id_Poste').AsInteger := FPoste.Id_Poste;
FUniQuery.ParamByName('Date_Poste').AsDate := FPoste.Date_Poste;
FUniQuery.ParamByName('Id_Pilote_1').AsString := FPoste.Id_Pilote_1;
FUniQuery.ParamByName('Id_Pilote_2').AsString := FPoste.Id_Pilote_2;
FUniQuery.ParamByName('Compteur_Plateau').AsInteger :=
Value.Compteur_Plateau;
if not FConnection.InTransaction then
FConnection.StartTransaction;
try
FUniQuery.Prepare;
// Do some actions with database. For example:
FUniQuery.Execute;
// Commit the current transaction to reflect changes in database if no errors were raised
FConnection.Commit;
except
// Rollback all changes in database made after StartTransaction if an error was raised
FConnection.Rollback;
HandleException;
end;
if FUniQuery.RowsAffected = 0 then
begin
FUniQuery.SQL.Text := Plateau_Insert;
FUniQuery.ParamByName('Date_Entree_Plateau').DataType := ftDateTime;
FUniQuery.ParamByName('Ligne').DataType := ftString;
FUniQuery.ParamByName('Ordre_Fabrication').DataType := ftString;
FUniQuery.ParamByName('Compteur_Produit').DataType := ftinteger;
FUniQuery.ParamByName('Plateau').DataType := ftString;
FUniQuery.ParamByName('Id_Poste').DataType := ftinteger;
FUniQuery.ParamByName('Date_Poste').DataType := ftDate;
FUniQuery.ParamByName('Id_Pilote_1').DataType := ftString;
FUniQuery.ParamByName('Id_Pilote_2').DataType := ftString;
FUniQuery.ParamByName('Compteur_Plateau').DataType := ftinteger;
FUniQuery.ParamByName('Date_Entree_Plateau').AsDateTime :=
Value.Date_Entree_Plateau;
FUniQuery.ParamByName('Ligne').AsString := FLigne.Id_ligne;
FUniQuery.ParamByName('Ordre_Fabrication').AsString :=
FOrdre_Fabrication_En_Cours.Ordre_Fabrication;
FUniQuery.ParamByName('Compteur_Produit').AsInteger :=
FOrdre_Fabrication_En_Cours.Compteur_Produit;
FUniQuery.ParamByName('Plateau').AsString := Value.Nom_Plateau;
FUniQuery.ParamByName('Id_Poste').AsInteger := FPoste.Id_Poste;
FUniQuery.ParamByName('Date_Poste').AsDate := FPoste.Date_Poste;
FUniQuery.ParamByName('Id_Pilote_1').AsString := FPoste.Id_Pilote_1;
FUniQuery.ParamByName('Id_Pilote_2').AsString := FPoste.Id_Pilote_2;
FUniQuery.ParamByName('Compteur_Plateau').AsInteger :=
Value.Compteur_Plateau;
if not FConnection.InTransaction then
FConnection.StartTransaction;
try
FUniQuery.Prepare;
// Do some actions with database. For example:
FUniQuery.Execute;
// Commit the current transaction to reflect changes in database if no errors were raised
FConnection.Commit;
except
// Rollback all changes in database made after StartTransaction if an error was raised
FConnection.Rollback;
HandleException;
end;
end;
finally
FUniQuery.Free;
end;
end;
Here is DBMonitor output :
Code: Select all
SQL Tab
INSERT INTO Productions_Plateaux_Details
(Ordre_Fabrication,Ligne,date_poste,Plateau,Date_Entree_Plateau,Date_Sortie_Plateau,Compteur_Plateau,Compteur_Produit,Id_Poste,Id_Pilote_1,Id_Pilote_2)
SELECT
Ordre_Fabrication,Ligne,:date_poste,Plateau,Date_Entree_Plateau,:Date_Sortie_Plateau,Compteur_Plateau,Compteur_Produit,:Id_Poste,:Id_Pilote_1,:Id_Pilote_2
FROM
Productions_Plateaux
Where Ligne = :Ligne;
Parameter Tab
date_poste date 09/09/2015
Date_Sortie_Plateau datetime 09/09/2015 17:45:58
Id_Poste integer 2
Id_Pilote_1 widestring[2] '26'
Id_Pilote_2 widestring[2] '52'
Ligne widestring[8] 'PIC PIC 3'
Error Tab
invalid byte sequence for encoding "UTF8": 0xe9 0x71 0x75