Setting 'search_path' via SQL with DBExpress not working

[ConwyValleySystems]

Hello,
We are experiencing an issue with the latest version (v5.1.1) running with PostgreSQL 14, compiling under Delphi 11.0 Alexandria.
We have migrated the data in our database from the 'public' schema to our own schema called 'petrog'. Having done this, we have then dropped the tables in 'public', so the tables only exist in 'petrog'. When we connect to the database, we use SQL to set the search path:

SQLQueryFieldDef.SQL.Add('ALTER DATABASE petrog SET search_path TO petrog,public;');
SQLQueryFieldDef.ExecSQL;

Having done this, if we then immediately call

SQLQueryFieldDef.SQL.Add('SHOW search_path;');
SQLQueryFieldDef.Active := true;
s := SQLQueryFieldDef.fields[0].Value;

then s is returning as 'public', not 'petrog,public'. As a result, our software is then unable to write data to any table e.g.

EUpdateError details: relation "windowposn" does not exist

as it is looking in the 'public' schema where no tables exist, whereas it should be looking in the 'petrog' schema. Every table has an associated TSQLTable component, and the 'SchemaName' property for each of these is set to 'petrog'.

We have also tried

'ALTER ROLE petrog SET search_path = petrog,public;'
and
'ALTER ROLE postgres IN DATABASE petrog SET search_path = petrog,public;'

with the same result.

So our question is - why is the search_path failing to set correctly when we execute any of the above SQL statements in our software which uses the DBExpress drivers? If we execute the exact same SQL statements in PGAdmin, the search_path is then set correctly.

Can anyone help? It would be greatly appreciated!

Barrie Wells

[evgeniym]

Hi Barrie,
Thank you for contacting us!

When using SQLConnection and dbExpress Driver for PostgreSQL, the SchemaName parameter is used to set search paths.
You can read more about setting this parameter in the file
%DevartFolder%/dbExpress/PostgreSql/Readme.html
where %DevartFolder% is the installation path of dbExpress Driver for PostgreSQL

Let us know if any questions!

Regards,
Evgeniy

[ConwyValleySystems]

Hi Evgeniy,

Thanks for this very useful reply. We had overlooked the fact that this advanced documentation existed in the README file.

However, we are still having some issues. We are working in Delphi 11.0 Alexandria, and so the comment

Starting with CodeGear RAD Studio 2007 you should assign parameter values to TSQLConnection and TCRSQLConnection at run time in this way:

SQLConnection.Params.Values['Option Name'] := 'Option Value';

suggests to us that we should be calling something along the lines of:

SQLConnection.Params.Values['SchemaName'] := 'petrog'

However, this or similar attempts such as

SQLConnection.Params.Add('SchemaName=petrog');

are having no effect. Can you confirm that this is the correct approach? The alternative suggestion, which is suggested as only being applicable when setting extended driver options in Delphi 2006 and lower IDE versions, is

const
coSchemaName = TSQLConnectionOption(28); // string
. . .
SQLConnection1.SQLConnection.SetOption(coSchemaName, Integer(PChar('test')));

For us, this code does not currently compile. If this is the code we should be basing our solution on, what units would we need to add to the 'uses' list in order for this to work?

With thanks and best regards

Barrie

[evgeniym]

Hi Barrie,
Kindly note that SQLConnection.Params.Values['SchemaName'] := 'shema_name'; is the correct way to work with parameters in Delphi 11.0.

Unfortunately, we were unable to reproduce the issue with SQLConnection.Params.Add('SchemaName=test').
Could you send us an example that uses dbExpress Driver for PostgreSQL and demonstrates incorrect behavior?
You can submit an example using the form on our website: https://devart.com/company/contactform.html

Regards,
Evgeniy

[ConwyValleySystems]

Hi Evgeniy

Thank you for this information. We have now been able to confirm that

SQLConnection.Params.Values['SchemaName'] := 'test'

does work in a simple example program, and as a result we have then been able to address the issue in our software.

Thank you very much for your support. We would politely suggest that the documentation in your README could be updated to make it a little clearer, as despite the fact it does state that the "new" way of doing things (RAD Studio 2007 and onwards) is as above, the subsequent examples for each option (e.g. SchemaName) still refer to the "old" style of doing things (RAD Studio 2006 and earlier) and as a result this did cause us a little bit of confusion. It would be great also if the documentation could be accessed from the Help menu in the RAD Studio IDE, as this would highlight the fact that such extensive documentation is available (we had overlooked this).

Many thanks again for your excellent support on this issue.

Best regards,

Barrie

[evgeniym]

Hi Barrie,
Thanks for using our product!
It's always a pleasure to help our customers.
Kindly be informed that we will consider fixing the documentation for dbExpress Driver for PostgreSQL.

Best regards from Ukraine,
Evgeniy