Page 1 of 1
Change Schema in ZConnection with PostgreSQL
Posted: 28.07.2022, 02:21
by huelbert
Goodnight
I'm starting a project with Zeos version 8.0.0-beta + PostgreSQL, the intention is to create several Schemas in a single database, one for each client.
In ZConnection I am not able to set the Schema.
Can you help me
Re: Change Schema in ZConnection with PostgreSQL
Posted: 28.07.2022, 08:11
by marsupilami
Hello huelbert,
usually this should work if you specify the schema in the Catalog property of TZConnection. If this doesn't work, please let me know.
Best regards,
Jan
Re: Change Schema in ZConnection with PostgreSQL
Posted: 28.07.2022, 13:53
by huelbert
Good Morning
I did a test putting a ZConnection and coded the properties as the code:
Code: Select all
ZConnection1.Disconnect;
ZConnection1.Protocol := 'postgresql';
ZConnection1.ClientCodepage := 'UTF8';
ZConnection1.HostName := 'localhost';
ZConnection1.Port := 5432;
ZConnection1.Catalog := edSchema.Text;
ZConnection1.User := 'postgres';
ZConnection1.Password := 'masterkey';
ZConnection1.Database := 'delphi-factoring';
ZConnection1.Connected := True;
It only connects to the public schema.
I am sending the example in the attachment.
Re: Change Schema in ZConnection with PostgreSQL
Posted: 29.07.2022, 17:09
by MJFShark
I'm not sure setting catalog works for Pg. Pg's "current schema" handling is a bit different than other databases. I just run
set search_path = MySchema
after login.
-Mark
Re: Change Schema in ZConnection with PostgreSQL
Posted: 29.07.2022, 20:26
by huelbert
Could you give an example in which property I put "set search_path = MySchema"
Re: Change Schema in ZConnection with PostgreSQL
Posted: 29.07.2022, 20:44
by aehimself
I have 0 experience with PG; however, the above statement looks like an SQL command.
Did you try
Code: Select all
ZConnection1.ExecuteDirect('set search_path = MySchema');
?
Re: Change Schema in ZConnection with PostgreSQL
Posted: 29.07.2022, 21:12
by huelbert
that's right...thank you
Re: Change Schema in ZConnection with PostgreSQL
Posted: 02.08.2022, 11:38
by marsupilami
The schema search path is what kept me from implementing anything because I was not sure how to handle things correctly. Anyway - currently I see two ways to improve the situation:
- We just overwrite the schema using something like "SET search_path TO <catalogname as specified in TZConnection>". This way we would effectivly disable the public schema and the users schema, if it exists.
- We do a "show search_path" and the execute something like "SET search_path T0 <catalogname as specified in TZConnection>,<original search_path>"
Opinions?
Re: Change Schema in ZConnection with PostgreSQL
Posted: 03.08.2022, 06:21
by aehimself
I support the second option, especially since MSSQL does this the same way (sending the USE command after login).