Page 1 of 1
Can you set a session variable with a query parameter?
Posted: Tue 25 Aug 2020 15:00
by trevor123
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;
Re: Can you set a session variable with a query parameter?
Posted: Thu 27 Aug 2020 13:01
by oleg0k
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
for a specific Query.
wbr, Oleg
Devart Team
Re: Can you set a session variable with a query parameter?
Posted: Fri 28 Aug 2020 16:13
by trevor123
Thank you, that works
Re: Can you set a session variable with a query parameter?
Posted: Mon 18 Jan 2021 17:03
by trevor123
Can I set a session variable when I connect?
Thanks
Re: Can you set a session variable with a query parameter?
Posted: Mon 18 Jan 2021 17:16
by trevor123
So that all queries in the session run with app.tenet_id set to the connection value!
Re: Can you set a session variable with a query parameter?
Posted: Tue 19 Jan 2021 11:16
by MaximG
PgDAC doesn't offer any special features for handling the SET command. Statements involving SET commands are handled according to the PostgreSQL documentation.
Re: Can you set a session variable with a query parameter?
Posted: Tue 02 Feb 2021 11:20
by trevor123
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));
Re: Can you set a session variable with a query parameter?
Posted: Tue 09 Feb 2021 13:52
by oleg0k
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