working with "schema"

[fcodebue]

I want to use different schema into the same database. In this way I can manage different set of data that I simply connect via trigger.
So I have 1 database 5 schema and every schema contain my database...

I want to set "schema" on ZConnection and not run this command before every query

Code: Select all

SET search_path = test; 

where test is schema name

any idea?

[trupka]

I thing this will satisfy:

Code: Select all

 ZConnection1.ExecuteDirect('set local search_path to test;');

[fcodebue]

This approach rise some problems while using more than one connections to database.
We are trying to use different schemas from same database. These schemas have some table with the same name.
So we have schema arc and schema arc_demo with the same table PRS

Just try project in attachment

using this table in the two schemas

Code: Select all

CREATE TABLE arc_demo.prs
(
  id integer NOT NULL DEFAULT nextval('arc_demo.s_prs_codice_id'::regclass),
  utente_creazione character varying(8) DEFAULT ''::character varying,
  data_ora_creazione timestamp without time zone,
  utente character varying(8) DEFAULT ''::character varying,
  data_ora timestamp without time zone,
  codice character varying(30) DEFAULT ''::character varying,
  valore integer DEFAULT 0,
  CONSTRAINT prs_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE arc_demo.prs OWNER TO postgres;
GRANT ALL ON TABLE arc_demo.prs TO postgres;

-- Index: arc_demo.prs_codice

-- DROP INDEX arc_demo.prs_codice;

CREATE UNIQUE INDEX prs_codice
  ON arc_demo.prs
  USING btree
  (codice);


-- Trigger: t_prs_biu on arc_demo.prs

-- DROP TRIGGER t_prs_biu ON arc_demo.prs;

CREATE TRIGGER t_prs_biu
  BEFORE INSERT OR UPDATE
  ON arc_demo.prs
  FOR EACH ROW
  EXECUTE PROCEDURE arc_demo.p_biu();

  

[fcodebue]

Problems are not solved. After a lot of ZeosLib code studying I have understand how it manage schema.
Infact I can't use
ZConnection1.ExecuteDirect('set local search_path to test;');
or miore generic statement
SET search_path = test;
because zeos set schema in the follow way:

1. read sql statement
2. read schema wrote into first table into from clause
3. use this schema....

so if you use one of the statement above the schema will be resetted just before execute the query

Some idea ?
I hope to have time to insert schema property into database object and use this one... but after some hours that I'm coding... mmm it's bit hard without change a lot of things...