Can you set a session variable with a query parameter?

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
trevor123
Posts: 7
Joined: Thu 22 Jan 2015 13:29

Can you set a session variable with a query parameter?

Post by trevor123 » Tue 25 Aug 2020 15:00

The following code works fine:

Code: Select all

SET app.current_tenant='8f8266ba-efdb-4fe3-ad16-fc4a295fe8a7';
SELECT * FROM tenant_user;
However, with :tid, I get Syntax Error at or near $1

Code: Select all

SET app.current_tenant=:tid;
SELECT * FROM tenant_user;

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: Can you set a session variable with a query parameter?

Post by oleg0k » Thu 27 Aug 2020 13:01

Hello,
This behavior of TPgQuery is caused by the specifics of PostgreSQL protocol version 3, we cannot affect it in any way. To prevent the issue, you can set the UnpreparedExecute property to True

Code: Select all

Options.UnpreparedExecute := True
for a specific Query.

wbr, Oleg
Devart Team

trevor123
Posts: 7
Joined: Thu 22 Jan 2015 13:29

Re: Can you set a session variable with a query parameter?

Post by trevor123 » Fri 28 Aug 2020 16:13

Thank you, that works

trevor123
Posts: 7
Joined: Thu 22 Jan 2015 13:29

Re: Can you set a session variable with a query parameter?

Post by trevor123 » Mon 18 Jan 2021 17:03

Can I set a session variable when I connect?

Thanks

trevor123
Posts: 7
Joined: Thu 22 Jan 2015 13:29

Re: Can you set a session variable with a query parameter?

Post by trevor123 » Mon 18 Jan 2021 17:16

So that all queries in the session run with app.tenet_id set to the connection value!

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Can you set a session variable with a query parameter?

Post by MaximG » Tue 19 Jan 2021 11:16

PgDAC doesn't offer any special features for handling the SET command. Statements involving SET commands are handled according to the PostgreSQL documentation.

trevor123
Posts: 7
Joined: Thu 22 Jan 2015 13:29

Re: Can you set a session variable with a query parameter?

Post by trevor123 » Tue 02 Feb 2021 11:20

To get the behaviour I wanted, I added the following code to the AfterConnect even of the PgConnection

PgConnectionSOP.ExecSQL( 'SET app.current_tenant = ' + QuotedStr(tenant_id));

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: Can you set a session variable with a query parameter?

Post by oleg0k » Tue 09 Feb 2021 13:52

Hello,
Glad to see that the issue was resolved.
Feel free to contact us if you have any further questions about our products.

wbr, Oleg
Devart Team

Post Reply