Transient global variables for SQL

[gramian]

Dear All,

I would like to suggest an addition to the SQL language, allowing to set global variables which are non-persistent.

I propose a like command:

SET myvar = true;

which stores a variable myvar in memory and is accessible by all SQL queries (maybe restricted to the same database?). To avoid collisions with LET-variable these need to be prefixed with something different than $, like @. After a restart, these variables are gone as they transient, just like the transiently stored key-value pairs in the Redis module.

Specifically, my purpose for those global variables is a locking mechanism preventing the same SQL script to be started multiple times concurrently without making this state persistent, in particularly being part of a database backup.

Notes:

• A RESET command is not necessary as one could just SET the variable to NULL.
• SET may collide with the console's SET command.

[lvca]

I really like this idea. They could be stored in RAM in a ConcurrentHashMap<String,Object>. About the syntax, are there other DBMS that have something similar we can look it up?

[gramian]

For example DuckDB: https://duckdb.org/docs/stable/sql/statements/set_variable

[lvca]

I like this syntax.

[lvca]

Ok, few details for the implementation. This will be supported in the "sqlscript" only. Variables are not persistent and will be saved in a new property "variables" of type ConcurrentHashMap<String,Object> inside the LocalDatabase class. At closing of the database, the map is cleared.

The variables must be reachable from the context, so the SQL and Cypher can access to them by using the $ prefix. Example:

SET age = 34;
SELECT $age

Should return 34.

There is no need for a get variable statement, you can return it with just a SELECT.

Variables can be strings, numbers (saved as long), json/maps {"test": "ciao"}, dates date("2025-12-01") and datetime("2025-12-01 10:30:00")

[gramian]

Very very cool!

Some remarks:

• A Boolean type would be useful for making flags.
• What should happen when a LET and SET use the same symbol?
• The console parses its SET command first, meaning in its current for a global variable named language is not possible from the console.

[lvca]

Ok for the booleans.

In order to avoid issues with the console (that has its own variables), how about this instead?

SET VARIABLE checkBalance = true;

In case of overlapping, the local query context wins.

[gramian]

How about SET GLOBAL or just SET TEMP? I think this could be more intuitive, as LET declares also variables.

[lvca]

I like SET GLOBAL. I'll work on a PR asap

[lvca]

Implemented: #3201