Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

DBeaver, How to declare variables and use them?

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

like image 683
Elliott Urrutia Avatar asked Jun 12 '19 15:06

Elliott Urrutia


People also ask

How do I set variables in DBeaver?

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.

How do you DECLARE a variable in SQL and use it in query?

Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.

How do you use variables in query?

The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.

How do you DECLARE and initialize a variable in SQL?

Initialization is an optional thing while declaring. By default, DECLARE initializes variable to NULL. Using the keyword 'AS' is optional. To declare more than one local variable, use a comma after the first local variable definition, and then define the next local variable name and data type.


3 Answers

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.

enter image description here

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 |
like image 60
nicoschl Avatar answered Oct 16 '22 19:10

nicoschl


Yes you can, using :.

An example:

SELECT * FROM "SYSIBM".SYSDUMMY1
WHERE IBMREQD = :YOUR_VARIABLE
like image 38
Nifriz Avatar answered Oct 16 '22 20:10

Nifriz


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".

like image 1
Tom Renish Avatar answered Oct 16 '22 20:10

Tom Renish