SQLite Forum

.param converts text to number - How to avoid this
Login

.param converts text to number - How to avoid this

(1) By Sathya Rao (gksathyarao) on 2020-11-03 09:05:35 [link] [source]

Hi, When I define a param having integer it stores result value as below

sqlite> .param set start_dt "2020-10-01" sqlite> select * from sqlite_parameters; start_dt|2009

How to prevent this? I want value to stored as string instead of result of numeric value

(2.2) By Keith Medcalf (kmedcalf) on 2020-11-03 10:31:46 edited from 2.1 in reply to 1 [source]

You have to quote a string.

sqlite> .param set t "'2010-01-01'"
sqlite> .param list
t '2010-01-01'
sqlite>

This is because the expression is evaluated and the outer quotes are stripped by the tokenizer. So if you really want a string you have to make it look like one. Note that this only applies if the expression can be evaluated -- if it cannot be evaluated then it must be a string:

sqlite> .param set $t "'2010-01-01'"
sqlite> .param set $u sin(5)
sqlite> .param set $v '2010-01-01 00:00:00'
sqlite> .param list
$t '2010-01-01'
$u -9.58924274663138453967e-01
$v '2010-01-01 00:00:00'
sqlite> select $t, $u, $v;
┌────────────┬────────────────────┬─────────────────────┐
│     $t     │         $u         │         $v          │
├────────────┼────────────────────┼─────────────────────┤
│ 2010-01-01 │ -0.958924274663138 │ 2010-01-01 00:00:00 │
└────────────┴────────────────────┴─────────────────────┘

ie, there is no internal function called goupta():

sqlite> .param set t goupta(1)
sqlite> .param list
t 'goupta(1)'
sqlite>

(3) By jake on 2020-11-03 10:29:38 in reply to 1 [link] [source]

It seems that the SQLite CLI is evaluating your date as the expression 2020 minus 10 minus 1 = 2009. Placing parentheses around the date seems to do the trick in preserving the value.

Reviewing the docs, it's not clear to me when or how .param set evaluates expressions. Here are some examples of the current behaviour:

SQLite version 3.34.0 2020-11-02 00:40:05
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .param init
sqlite> .mode table
sqlite> .param set $d '2020-10-01'
sqlite> SELECT * FROM sqlite_parameters;
+-----+-------+
| key | value |
+-----+-------+
| $d  | 2009  |
+-----+-------+
sqlite> .param set $d ('2020-10-01')
sqlite> SELECT * FROM sqlite_parameters;
+-----+------------+
| key |   value    |
+-----+------------+
| $d  | 2020-10-01 |
+-----+------------+
sqlite> .param set $expr1 Abs(-10)
sqlite> .param set $expr2 Upper('abc')
sqlite> .param set $expr3 SubStr('abcdef', 1, 3)
.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> .param set $expr3 "SubStr('abcdef', 1, 3)"
sqlite> SELECT * FROM sqlite_parameters;
+--------+------------+
|  key   |   value    |
+--------+------------+
| $d     | 2020-10-01 |
| $expr1 | 10         |
| $expr2 | ABC        |
| $expr3 | abc        |
+--------+------------+