Can one assign variables in sqlite3
(1) By sqliteguy on 2024-08-23 22:40:21 [link] [source]
I looked everywhere for setting up variables using sqlite3; alas, it seems it is only available in sql server , or using using python. Which is way out of my league. Scenario: I'd like to setup variables XXX YYY ZZZ which would save me the work of multiple copy/paste for each variable. ================= How do I assign: Amad to variable XXX N170 to variable YYY Definition to variable ZZZ ................ SELECT Book, Chapter, Verse FROM Bible WHERE Scripture LIKE '%XXX%'; CREATE VIEW v1 AS SELECT Book, Chapter, Verse FROM Bible WHERE Scripture LIKE '%XXX%'; SELECT * from v1 ; UPDATE Bible SET Scripture = REPLACE(Scripture, 'XXX','XXX<not>YYY</not>'); INSERT INTO Notes (Book, Chapter, Verse, ID, Note) SELECT Book, Chapter, Verse, 'YYY',"<RF>ZZZ<Rf>" FROM v1; --Note here: I'm using quotes to eliminate conflicts with apostrophes within a string. DROP VIEW v1; .................... There is no problem if it cannot be done within sqlite3; I was just thinking of a way to bypass multiple copy/paste. Thanks; noob, newbie pick one.
(2) By anonymous on 2024-08-24 00:06:56 in reply to 1 [link] [source]
If nothing else, you could always make a temp table with one row and join the table in all your queries...
(3.3) By Aask (AAsk1902) on 2024-08-24 00:56:33 edited from 3.2 in reply to 1 [source]
Study the session shown below, heeding the comments shown between /* ... */
PS:
- Note sqlite> is the standard prompt if you want to try any expression ad hoc; you need to copy the target line without sqlite>
- Execute .param init first.
- See here also.
SQLite version 3.46.1 2024-08-13 09:16:08 (UTF-16 console I/O)
Enter ".help" for usage hints.
sqlite> /* SQLite Parameters Demonstration */
sqlite>
sqlite> .header on
sqlite> .mode columns
sqlite>
sqlite> /* SQLite Version in use */
sqlite> .ver
SQLite 3.46.1 2024-08-13 09:16:08 c9c2ab54ba1f5f46360f1b4f35d849cd3f080e6fc2b6c60e91b16c63f69a1e33
zlib version 1.3
msvc-1939 (64-bit)
sqlite>
sqlite> /* Using SQLite on platform */
sqlite> .shell ver
Microsoft Windows [Version 10.0.22631.4037]
sqlite>
sqlite> DROP TABLE IF EXISTS tblSample; /* used below for demo */
sqlite>
sqlite> /* Set up parameters */
sqlite> .param init
sqlite>
sqlite> /* Cannot add comments on .param lines */
sqlite> /* Parameters ARE CASE SENSITIVE */
sqlite> /* Literal parameter values are specified verbatim i.e. without quotes */
sqlite> .parameter set :XXX Amad
sqlite> .parameter set :YYY N170
sqlite>
sqlite> /* Use Parameters */
sqlite> select :XXX as XXX,:YYY as YYY;
XXX YYY
---- ----
Amad N170
sqlite>
sqlite> /* List all Parameters */
sqlite> .param list
:XXX 'Amad'
:YYY 'N170'
sqlite>
sqlite> /* Alternatively */
sqlite> select * from temp.sqlite_parameters;
key value
---- -----
:XXX Amad
:YYY N170
sqlite>
sqlite> /* Remove Parameter */
sqlite> .param unset :XXX
sqlite>
sqlite> /* Can't CREATE or DROP temp.sqlite_parameters */
sqlite>
sqlite> /* temp.sqlite_parameters is created after .param init */
sqlite>
sqlite> /* Once it Exists, you can also use it to add/update parameters */
sqlite>
sqlite> update temp.sqlite_parameters set value = 2.344 where key = ':YYY';
sqlite>
sqlite> select :YYY as NewValue;
NewValue
--------
2.344
sqlite>
sqlite> insert into temp.sqlite_parameters values(':newParam',123456789);
sqlite> SELECT * from temp.sqlite_parameters;
key value
--------- ---------
:YYY 2.344
:newParam 123456789
sqlite>
sqlite> /* select the parameter */
sqlite> SELECT :newParam;
:newParam
---------
123456789
sqlite>
sqlite> /* using parameters in SQL statements */
sqlite> /* Create a table first */
sqlite> Create table if not exists tblSample as select * from ( Values('January',100),('newValue',2.344));
sqlite>
sqlite> /* Use a parameter in SQL */
sqlite> SELECT * from tblSample where column2=(select :YYY);
column1 column2
-------- -------
newValue 2.344
sqlite>
sqlite> /* You can compute parameters */
sqlite> Insert into tblSample Values('SQLite', 123.456);
sqlite>
sqlite> SELECT * from tblSample where column2 > (select 100+:YYY);
column1 column2
------- -------
SQLite 123.456
sqlite>
sqlite> /* Clear all Parameters */
sqlite> .param clear
sqlite> /* For Help */
sqlite>
sqlite> /* EITHER */
sqlite> .help .param
.parameter CMD ... Manage SQL parameter bindings
clear Erase all bindings
init Initialize the TEMP table that holds bindings
list List the current parameter bindings
set PARAMETER VALUE Given SQL parameter PARAMETER a value of VALUE
PARAMETER should start with one of: $ : @ ?
unset PARAMETER Remove PARAMETER from the binding table
sqlite>
sqlite> /* OR */
sqlite> .param
.parameter CMD ... Manage SQL parameter bindings
clear Erase all bindings
init Initialize the TEMP table that holds bindings
list List the current parameter bindings
set PARAMETER VALUE Given SQL parameter PARAMETER a value of VALUE
PARAMETER should start with one of: $ : @ ?
unset PARAMETER Remove PARAMETER from the binding table
sqlite>
(4) By anonymous on 2024-08-24 00:36:00 in reply to 1 [link] [source]
If you mean variables in the CLI, then No. 16 at https://sqlite.org/cli.html mentions this.
A poor example, but this can be run in https://sqlite.org/fiddle/ as a start.
.mode box
.param set :XXX 'Amad'
.param set :YYY 'N170'
.param set :ZZZ 'Definition'
select :XXX, :YYY, :ZZZ;
select 1 as col_name where 'TAmadE' like '%' || :XXX || '%';
(5) By sqliteguy on 2024-08-24 01:57:50 in reply to 4 [link] [source]
Thanks @Aask and @Anonymous; scheduled power outage tomorrow, so I opened all links and this page then printed as pdf. That will give me time to read until battery drains; thanks. BTW, in terminal I pasted the OP in a text file and used .read text.txt; gives me something to study for quite awhile. Thanks again.
(6.1) By Holger J (holgerj) on 2024-08-25 14:22:53 edited from 6.0 in reply to 1 [link] [source]
The question is: "Is SQL supposed to have variables?" Look at the ISO SQL standard or try Oracle or PostgreSQL. Since SQL is a set oriented, non procedural language, it doesn't support variables. Some front ends do, like PostgreSQL's psql or the SQLite shell. If you want to use variables directly in SQL, something is wrong with your approach to SQL. Only PL/SQL for writing functions and procedures has variables, loops and so on. Sometimes it can be helpful to use it. Only MS SQL Server doesn't make the distinction between SQL and its procedural counterpart and thus confuses people.
(7.2) By Aask (AAsk1902) on 2024-08-25 09:05:32 edited from 7.1 in reply to 6.0 [link] [source]
With SQLite, the variables are available in the CLI: they are like SQL parameters (key/value pairs).
With MSSQL, stored procedure definitions allow declaration of variables which are used for loops, parameters, building dynamic SQL etc.
If variables were not available (or disallowed), you can make a [temp] table with one row and join the table in all your queries... as suggested:
CREATE TEMP TABLE sqlite_parameters(
key TEXT PRIMARY KEY,
value
) WITHOUT ROWID;
(8) By ingo on 2024-08-25 15:32:27 in reply to 1 [link] [source]
Would CTE's be an option?
sqlite> with vars(XXX, YYY, ZZZ) as
...> (values('Amad', 'N170', 'Definition'))
...> select * from vars;
Amad|N170|Definition
(9) By sqliteguy on 2024-08-25 16:06:45 in reply to 8 [link] [source]
OK' I'll check that out also.
(10) By Vadim Goncharov (nuclight) on 2024-10-26 21:32:17 in reply to 1 [link] [source]
As there is no server in SQLite, your variables, in any form you could imagine it, will still exist in the same process where some part (probably in high-level scripting language) is doing query and other part being DQLite library itself. Thus, the most right solution is to use :paramname bindings in SQLite API - either from SQLite Shell .param as already been shown in discussion above, or in equivalent wrapper for SQLite API calls in language you use.
(11) By Joel Jucá (joeljuca) on 2024-10-27 12:26:07 in reply to 8 [link] [source]
I’ve successfully used CTE to emulate this “variable-setting behavior, it works great and is probably the easiest way to do it in SQL.