Change Schema in ZConnection with PostgreSQL

[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

[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

[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.

[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

[huelbert]

Could you give an example in which property I put "set search_path = MySchema"

[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');

?

[huelbert]

that's right...thank you

[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?

[aehimself]

I support the second option, especially since MSSQL does this the same way (sending the USE command after login).