PostgreSQLUniProvider : unable to get the master key value

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
swierzbicki
Posts: 451
Joined: Wed 19 Jan 2005 09:59

PostgreSQLUniProvider : unable to get the master key value

Post by swierzbicki » Fri 27 Jan 2012 08:20

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.
Top
AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by AlexP » Fri 27 Jan 2012 10:04

Hello,

I could not reproduce the problem.
Please send a complete small sample to alexp*devart*com to demonstrate it.
Top
swierzbicki
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Post by swierzbicki » Sat 28 Jan 2012 10:43

Hello,

I've sent you an sample application.
Top
swierzbicki
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Post by swierzbicki » Mon 30 Jan 2012 12:17

Hello Alex,

Did you get my email ?

BR

Stephane W.
Top
AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by AlexP » Mon 30 Jan 2012 13:05

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.
Top
swierzbicki
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Post by swierzbicki » Mon 30 Jan 2012 15:22

Thank you !
This worked fine.
Top
AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by AlexP » Mon 30 Jan 2012 15:32

Hello,

Glad to see that the problem was solved. If you have any other questions, feel free to contact us.
Top
Post Reply

Return to “Universal Data Access Components”