DBeaver, How to declare variables and use them?

Question

i just want to know if it is possible to declare variables on the DBeaver´s sql editor and use them on a query

score 89 · Answer 1 · answered Oct 09 '19 at 16:23

You have to enable variable processing in the "SQL Processing" settings of DBeaver -> Window -> Preferences -> Database -> Editors -> SQL Editor -> SQL Processing. There is a block on Parameters with settings you can change. See the Dynamic Parameter binding section on the wiki.

You should then be able to do:

@set date = '2019-10-09'

SELECT ${date}::DATE, ${date}::TIMESTAMP WITHOUT TIME ZONE

which produces:

| date       | timestamp           |
|------------|---------------------|
| 2019-10-09 | 2019-10-09 00:00:00 |

Note that `@set` is a DBeaver command. If you append `;` it'll become part of the variable value! — Álvaro González, Nov 23 '22 at 11:08

score 24 · Answer 2 · answered Jun 13 '19 at 15:09

24

Yes you can, using :.

An example:

SELECT * FROM "SYSIBM".SYSDUMMY1
WHERE IBMREQD = :YOUR_VARIABLE

answered Jun 13 '19 at 15:09

Nifriz

1,033
1
10
21

3

my idea is to declare that variable on the top of de code for example above the "Select" declare a value to that variable like ..... my_variable = "success" .. – Elliott Urrutia Jun 13 '19 at 21:54
I think this is the only way – Nifriz Jun 14 '19 at 06:27
FYI, OOTB I was able to run using: `:foo` `$foo` and `${foo}` – Adam Hopkins Aug 24 '22 at 21:34
1

You can use `@set` to define variables with either syntax. DBeaver will populate any placeholder that's defined in preferences. – Álvaro González Nov 23 '22 at 11:10
But how do you define the variable? Dbeaver is finicky and throws a syntax error when using something like this my_var = "hello" select :my_var – n3rd Nov 29 '22 at 16:49
1

You have to write `@set` all **lowercase**! Using `@SET` did not work for me using DBeaver version 22.3.0.202212041619. – Peter A Dec 12 '22 at 11:56

score 5 · Answer 3 · answered Jul 05 '22 at 17:17

5

Based on the incredibly helpful post from @nicoschl, here are a couple of minor improvements:

-- using declarations
@set datex_start = cast('2120-01-01' as date) as date_start;
-- datex_start is the var name
-- casting the value in the declaration saves us the work later
-- the var can be given a default fieldname (e.g. "date_start")

-- run as a standalone command since the subsequent SELECT statement doesn't return values when it's all run together

select 
    ${datex_start}
;

This will return a value "2120-01-01" with a fieldname of "date_start".

answered Jul 05 '22 at 17:17

Tom Renish

398
4
7

set with ';' at the end defines variable with that ';' at the end in dbeaver , so if you need clean value do set without ending ';' – vasq Oct 10 '22 at 03:13
On Oracle I ended up using `@set i_date = (date '2000-01-01')` (which makes it relatively simple to copypaste storedproc queries and just prepend the input parameters with `:` like `select :i_date as col1 from dual` – Pasi Savolainen Feb 13 '23 at 13:57

score 5 · Answer 4 · edited Nov 29 '22 at 18:00

5

In the DBeaver SQL editor you can type the following:

-- define variable
@set my_var='hello SQL world'

-- call variable
select :my_var

You can also use ${my_var} to reference the variable; $my_var however did not work for me. I am using DBeaver v. 21.1.

edited Nov 29 '22 at 18:00

Eric Aya

69,473
35
181
253

answered Nov 29 '22 at 16:55

n3rd

173
1
6

rd1218 · Answer 5 · 2022-09-05T13:42:32.817

-1

You have to enable at Dbeaver settings: Top Window > Preferences > and then see print below (updated 2022/08).

edited Sep 05 '22 at 13:42

answered Aug 30 '22 at 12:45

rd1218

134
12

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 04 '22 at 12:15

DBeaver, How to declare variables and use them?

5 Answers5

Linked