Page 1 of 1
working with "schema"
Posted: 06.07.2009, 15:34
by 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
where test is schema name
any idea?
Posted: 07.07.2009, 15:45
by trupka
I thing this will satisfy:
Code: Select all
ZConnection1.ExecuteDirect('set local search_path to test;');
Posted: 14.07.2009, 10:19
by 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();
Posted: 20.11.2009, 09:57
by 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...