Vertica variables not useable

[StefanoRomano71]

Hello,
we use dbeaver to create vertica scripts with variables but unfortunately they did not work.

Example:
\set schema_par our_db \set table_par our_tbl select * from :schema_par.:table_par;

Then dbeaver ask for parameter values (which i can set), but later on trow a syntax error:
SQL Error [4856] [42601]: [Vertica]VJDBC ERROR: Syntax error at or near ""
[Vertica]VJDBC ERROR: Syntax error at or near ""
com.vertica.util.ServerException: [Vertica]VJDBC ERROR: Syntax error at or near ""

Even if I use only the select statement with vars i receive a different error:
SQL Error [4856] [42601]: [Vertica]VJDBC ERROR: Syntax error at or near "$1"
[Vertica]VJDBC ERROR: Syntax error at or near "$1"
com.vertica.util.ServerException: [Vertica]VJDBC ERROR: Syntax error at or near "$1"

Can someone help me understand why we can't use vars in dbeaver while using vertica?
Is there any seting of the driver/sw we can use?

Thanks in advance.

SR

[serge-rider]

By default DBeaver uses : as parameters binding. Looks like in this case it means something different.
You could try to change dynamic parameters prefix in preferences (SQL Editor->SQL Processing->Parameters).

[StefanoRomano71]

I tried to change to § with no luck, same error:
SQL Error [4856] [42601]: [Vertica]VJDBC ERROR: Syntax error at or near "§"
[Vertica]VJDBC ERROR: Syntax error at or near "§"
com.vertica.util.ServerException: [Vertica]VJDBC ERROR: Syntax error at or near "§"

It seems the variable substituted row is bad composed.

Also consider Vertica support ":" as variable declaration definer and we use it to set var in our script.
But also those declaration doesn't work because is with the "\set" command and i can understand escaping backslash can be difficult.

Eg:
\echo :schemainput ### in vertica sql echoes the :schemainput variable,
in dbeaver it ask for var value and then throw the error:
SQL Error [4856] [42601]: [Vertica]VJDBC ERROR: Syntax error at or near ""
[Vertica]VJDBC ERROR: Syntax error at or near ""
com.vertica.util.ServerException: [Vertica]VJDBC ERROR: Syntax error at or near ""

\set schema_par :schemainput ### in vertica this set the var schema_par with value from schemainput value
in dbeaver, it ask for var value and then throw the error:
SQL Error [4856] [42601]: [Vertica]VJDBC ERROR: Syntax error at or near ""
[Vertica]VJDBC ERROR: Syntax error at or near ""
com.vertica.util.ServerException: [Vertica]VJDBC ERROR: Syntax error at or near ""

Anyway thanks a lot for your great product.

SR

[serge-rider]

You don't need to change script - you need to change DBeaver preferences (change parameters prefix to something else than ":" ).
So DBeaver won't try to substitute parameters. Vertica should do all the job.

[StefanoRomano71]

Serge-rider, we want dbeaver use parameter (if possible).
If not, we can continue to use find-and-replace while in dbeaver to execute script, and replace back with parameters when saving for external execution.

SR

[serge-rider]

I can't figure out how to handle this.
Should DBeaver handle :param as a dynamic parameter?
IF so then what can be fixed/changed to let Vertica execute these queries correctly?

[serge-rider]

Dynamic parameters are now configurable (in preferences -> SQL Editor->SQL Processing)