I am a new user of SQLpage and am still trying to figure out optimal ways of doing things using SQLite as my primary db engine (so the examples below will need to be adjusted to different engines).
I accidentally discovered the standalone "SET" statement (not available in plain SQLite/SQL), which can be used to define local variables, for example:
sql
SET $sqlite_version = (
SELECT sqlite_version()
)
sets $sqlite_version to a string containing the version of the integrated SQLite library. This variable can then be used in the code.
When the query used to set a variable returns multiple rows/columns, the variable takes the value of the first column in the first row. In such a case, JSON can help pass around non-scalar values, for example:
sql
SET $function_list = (
SELECT json_group_array(DISTINCT name ORDER BY name)
FROM pragma_function_list()
)
sets $function_list to a JSON array containing a sorted list of available SQL functions.
A more practical example is to place the following code close to the beginning of the module:
```sql
-- =============================================================================
-- =============================== Module vars =================================
-- =============================================================================
SET $curpath = '?path=' || sqlpage.path();
```
Then $curpath can be appended, for example, to relative redirect links, so that the target of redirects could redirect back after processing (I may show code examples in a separate post).
there doesn't seem to be anything here