Devart Forums

Discussion forums for open issues and questions concerning database tools, data access components and developer tools from Devart
https://forums.devart.com/

PostgreSQLUniProvider : unable to get the master key value

https://forums.devart.com/viewtopic.php?f=28&t=23249

Page 1 of 1

PostgreSQLUniProvider : unable to get the master key value

Posted: Fri 27 Jan 2012 08:20
by swierzbicki
Hello,

I'm struggling to get those Master / Details stuff working.
I have issue where the master field value isn't retreived after inserting a new record in it's detail table. That's really strange since the detail table is correctly opened (I can see via DBMonitor that the master key is correctly set in the SQL script).

I have already set the RefreshPAramsOnInsert property to true :

Code: Select all

initialization
  RefreshParamsOnInsert := True;
The master table is placed on the Client Datamodule form.
The detail table is placed on the Affaire Datamodule form.

Here is the Master component configuration

Code: Select all

object qrclients_Agenda: TUniQuery
  Connection = dmMain.mainconnection
  SQL.Strings = (
    'select agenda.*'
    'from'
    'agenda')
  MasterSource = dsClients
  MasterFields = 'Id_Client'
  DetailFields = 'agenda."Id_Client"'
  Options.DefaultValues = True
  BeforeOpen = DatasetBeforeOpen
  AfterOpen = DatasetAfterOpen
  Left = 272
  Top = 8
  ParamData = 
end

Here is the Detail component configuration

Code: Select all

object qrAffaires: TUniQuery
  Connection = dmMain.mainconnection
  SQL.Strings = (
    'select *'
    'from'
    'affaires')
  MasterSource = dmClient.dsClients
  MasterFields = 'Id_Client'
  DetailFields = 'affaires."Id_Client"'
  Options.DefaultValues = True
  BeforeOpen = DatasetBeforeOpen
  AfterOpen = DatasetAfterOpen
  Left = 280
  Top = 176
  ParamData = 
end
I'm setting the TQuery options (setting DefaultValue to true) on the OnBeforeOpen event.
I'm setting the Fieldsname's caption on the OnAfterOpen event.


Here is the Master table definition (Clients table)

Code: Select all

CREATE TABLE public.clients (
  "Id_Client" SERIAL, 
  "Id_Etablissement" VARCHAR(10) NOT NULL, 
  "Id_Categorie_Client" CHAR(3) DEFAULT ''::bpchar NOT NULL, 
  "Actif" BOOLEAN DEFAULT true, 
  "Date_Creation" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT now(), 
  "Date_Modification" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT now(), 
  "Date_Export" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "Utilisateur" VARCHAR(50) DEFAULT "current_user"(), 
  "Societe" VARCHAR(100) DEFAULT ''::character varying, 
  "Civilite" VARCHAR(100) DEFAULT ''::character varying, 
  "Nom" VARCHAR(100) DEFAULT ''::character varying, 
  "Prenom" VARCHAR(100) DEFAULT ''::character varying, 
  "Voie" VARCHAR(10) DEFAULT ''::character varying, 
  "Nom_Voie" VARCHAR(100) DEFAULT ''::character varying, 
  "Etage" VARCHAR(3) DEFAULT ''::character varying, 
  "Lieu_Dit" VARCHAR(100) DEFAULT ''::character varying, 
  "Localite" VARCHAR(100) DEFAULT ''::character varying, 
  "Code_Postal" VARCHAR(10) DEFAULT ''::character varying, 
  "Ville" VARCHAR(100) DEFAULT ''::character varying, 
  "Id_Pays" VARCHAR(2) DEFAULT ''::character varying, 
  "Id_Matricule" VARCHAR(10) DEFAULT ''::character varying NOT NULL, 
  "Remise" DOUBLE PRECISION DEFAULT 0, 
  "Taux_Tva" DOUBLE PRECISION, 
  "Telephone" VARCHAR(25) DEFAULT ''::character varying, 
  "Telecopieur" VARCHAR(25) DEFAULT ''::character varying, 
  "Telephone_Professionel" VARCHAR(25) DEFAULT ''::character varying, 
  "Portable" VARCHAR(25) DEFAULT ''::character varying, 
  "Adresse_EMail" VARCHAR(100) DEFAULT ''::character varying, 
  "Adresse_Web" VARCHAR(100) DEFAULT ''::character varying, 
  "Id_Condition_Paiement" VARCHAR(3) NOT NULL, 
  "Id_Condition_Livraison" VARCHAR(3) NOT NULL, 
  "Id_Nature_Client" CHAR(3) NOT NULL, 
  "Id_Nature_Construction" CHAR(3) NOT NULL, 
  "Id_Nature_Habitation" CHAR(3) NOT NULL, 
  "Id_Origine_Source" CHAR(3) NOT NULL, 
  "Id_Tranche_Age" VARCHAR(4) DEFAULT ''::character varying, 
  "Id_Parrain" INTEGER DEFAULT 0, 
  "Date_Parrainage" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "TVA_Intra_Communautaire" VARCHAR(15) DEFAULT ''::character varying, 
  "NAF" VARCHAR(5) DEFAULT ''::character varying, 
  "Siren" VARCHAR(9) DEFAULT ''::character varying, 
  "Siret" VARCHAR(14) DEFAULT ''::character varying, 
  "Remarque" TEXT DEFAULT ''::text, 
  "Liste_de_diffusion" BOOLEAN DEFAULT true, 
  CONSTRAINT clients_new_pkey PRIMARY KEY("Id_Client"), 
  CONSTRAINT clients_new_fk FOREIGN KEY ("Id_Condition_Livraison")
    REFERENCES public.ref_condition_livraisons("Id_Condition_Livraison")
    ON DELETE NO ACTION
    ON UPDATE CASCADE
    NOT DEFERRABLE, 
  CONSTRAINT clients_new_fk1 FOREIGN KEY ("Id_Condition_Paiement")
    REFERENCES public.ref_condition_paiements("Id_Condition_Paiement")
    ON DELETE NO ACTION
    ON UPDATE CASCADE
    NOT DEFERRABLE, 
  CONSTRAINT clients_new_fk2 FOREIGN KEY ("Id_Nature_Client")
    REFERENCES public.ref_nature_clients("Id_Nature_Client")
    ON DELETE NO ACTION
    ON UPDATE CASCADE
    NOT DEFERRABLE, 
  CONSTRAINT clients_new_fk3 FOREIGN KEY ("Id_Nature_Construction")
    REFERENCES public.ref_nature_constructions("Id_Nature_Construction")
    ON DELETE NO ACTION
    ON UPDATE CASCADE
    NOT DEFERRABLE, 
  CONSTRAINT clients_new_fk4 FOREIGN KEY ("Id_Nature_Habitation")
    REFERENCES public.ref_nature_habitations("Id_Nature_Habitation")
    ON DELETE NO ACTION
    ON UPDATE CASCADE
    NOT DEFERRABLE, 
  CONSTRAINT clients_new_fk5 FOREIGN KEY ("Id_Origine_Source")
    REFERENCES public.ref_origine_sources("Id_Origine_Source")
    ON DELETE NO ACTION
    ON UPDATE CASCADE
    NOT DEFERRABLE
) WITHOUT OIDS;

COMMENT ON COLUMN public.clients."Id_Client"
IS 'N° Client';

CREATE TRIGGER clients_aiud_tr
  AFTER INSERT OR UPDATE OR DELETE 
  ON public.clients FOR EACH ROW 
  EXECUTE PROCEDURE public."MAJ_historiques"();

CREATE TRIGGER clients_bu_tr
  BEFORE UPDATE 
  ON public.clients FOR EACH ROW 
  EXECUTE PROCEDURE public."MAJ_Date_Modification"();
Here is the detail table definition (table Affaires)

Code: Select all

CREATE TABLE public.affaires (
  "Id_Affaire" SERIAL, 
  "Id_Client" INTEGER NOT NULL, 
  "Id_Etablissement" VARCHAR(10) NOT NULL, 
  "Id_Matricule" VARCHAR(10) NOT NULL, 
  "Utilisateur" VARCHAR(50) DEFAULT "current_user"(), 
  "Id_Type_Affaire" INTEGER NOT NULL, 
  "Prix_Public_HT" DOUBLE PRECISION DEFAULT 0 NOT NULL, 
  "Pose_HT" DOUBLE PRECISION DEFAULT 0 NOT NULL, 
  "Forfait_HT" DOUBLE PRECISION DEFAULT 0 NOT NULL, 
  "Prix_Vente_HT" DOUBLE PRECISION DEFAULT 0 NOT NULL, 
  "Remise" DOUBLE PRECISION DEFAULT 0, 
  "Taux_TVA" DOUBLE PRECISION DEFAULT 0 NOT NULL, 
  "Taux_Acompte" DOUBLE PRECISION DEFAULT 0.0 NOT NULL, 
  "Remarque" TEXT DEFAULT ''::text NOT NULL, 
  "Date_Creation" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT now(), 
  "Date_Devis" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "Date_Signature" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "Date_Metre" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "Date_Envoi" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "Date_Reception" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "Date_Validation" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "Date_Debut_Fabrication" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "Date_Fin_Fabrication" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "Date_Expedition" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "Date_Pose" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "Date_Facturation" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "Date_Annulation" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "Projet" VARCHAR(250) DEFAULT ''::character varying NOT NULL, 
  "Id_Reference" VARCHAR(10) DEFAULT ''::character varying NOT NULL, 
  "Id_Condition_Livraison" VARCHAR(3) DEFAULT ''::character varying NOT NULL, 
  "Id_Condition_Paiement" VARCHAR(3) DEFAULT ''::character varying NOT NULL, 
  "Id_Promotion" VARCHAR(10) DEFAULT ''::character varying NOT NULL, 
  "Budget_TTC" DOUBLE PRECISION DEFAULT 0 NOT NULL, 
  "Date_Realisation" TIMESTAMP(0) WITHOUT TIME ZONE
) WITHOUT OIDS;
Ps : i'm using UniDac 4.1.4 version with PostgresQL 9.1. Running under Win 7 64bit and Delphi XE with latest update.

Posted: Fri 27 Jan 2012 10:04
by AlexP
Hello,

I could not reproduce the problem.
Please send a complete small sample to alexp*devart*com to demonstrate it.

Posted: Sat 28 Jan 2012 10:43
by swierzbicki
Hello,

I've sent you an sample application.

Posted: Mon 30 Jan 2012 12:17
by swierzbicki
Hello Alex,

Did you get my email ?

BR

Stephane W.

Posted: Mon 30 Jan 2012 13:05
by AlexP
Hello,

For solving this problem, you should set the QuoteNames option to true for both DataSets

Code: Select all

UniQuery.Options.QuoteNames:= true

and after this, tune the Master-Detail connection.

Posted: Mon 30 Jan 2012 15:22
by swierzbicki
Thank you !
This worked fine.

Posted: Mon 30 Jan 2012 15:32
by AlexP
Hello,

Glad to see that the problem was solved. If you have any other questions, feel free to contact us.
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/