SQLite Forum

Command Line Shell set parameter as date
Login

Command Line Shell set parameter as date

(1.1) Originally by Bob C (rchapman) with edits by Richard Hipp (drh) on 2021-04-02 16:30:45 from 1.0 [link] [source]

Windows 10 Sqlite 3.35.3 2021-03-26

This is probably an artifact of my ignorance but how can you set a parameter's value to a date string (e.g. '2021-03-01') without having it converted to a number (e.g. '2017')

  sqlite> .param init
  sqlite> .param set :date 2021-03-01
  sqlite> .param list
  :date 2017
  sqlite> .param set :date "2021-03-01"
  sqlite> .param list
  :date 2017
  sqlite> .param set :date '2021-03-01'
  sqlite> .param list
  :date 2017

Thanks.

(2) By Richard Hipp (drh) on 2021-04-02 16:31:24 in reply to 1.1 [source]

How about this:

.param set :date "'2021-03-01'"

(3) By Bob C (rchapman) on 2021-04-02 20:50:41 in reply to 2 [link] [source]

Yes! Shame on me -- blush! I should have tried a few more combinations myself and avoided 'noise' on the forum. ;o

However it does give me a chance to thank you for Sqlite and express my admiration for your many contributions and my appreciation of the mentorship you provide through this forum and your presentations. Thanks Dr Hipp!

(4) By Harald Hanche-Olsen (hanche) on 2021-04-03 20:13:33 in reply to 2 [link] [source]

I couldn't help experimenting a little:

▶▶▶ .param init
▶▶▶ .param set :bond "'007'"
▶▶▶ .param list
:bond 7

Uh-oh. What happened to the double-oh in double-oh seven? Well, it seems the answer lies here:

▶▶▶ .schema sqlite_parameters
CREATE TABLE temp.sqlite_parameters(
  key TEXT PRIMARY KEY,
  value ANY
) WITHOUT ROWID;

Which raises the question: Why was the type of the value here specified as ANY, which by the rules given in section 3.1 of the Datatypes doc means it has INTEGER affinity? Was it intentional?

(5) By Keith Medcalf (kmedcalf) on 2021-04-03 23:27:13 in reply to 4 [link] [source]

Probably not.

If no type is provided (that is, use BLOB, or no affinity conversion) then it works more properly.

(6) By Larry Brasfield (larrybr) on 2021-04-04 13:38:03 in reply to 4 [link] [source]

That's an excellent question. I suspect the value's type affinity was intended to actually be "any". It works better that way (as Keith says.)

It would be helpful if you and/or others would make the best argument for retaining the current behavior for the sake of backward compatibility. I am inclined to think that where anybody has somehow come to rely on the current behavior, it is only by accident or extremely contrived usage, and that avoiding anomalies such as your "Uh-oh" will, overall, produce fewer problems for shell users. (It helps that .parameter is a relatively recent addition.)

(7) By Harald Hanche-Olsen (hanche) on 2021-04-05 14:03:32 in reply to 6 [link] [source]

Honestly, I can't think of any reason to retain the current behaviour, nor how anybody could be relying on it. Sorry to disappoint. ;-)

Anyhow, I compiled a version patched as follows:

--- src/shell.c.in
+++ src/shell.c.in
@@ -2913,11 +2913,11 @@
   sqlite3_db_config(p->db, SQLITE_DBCONFIG_WRITABLE_SCHEMA, -1, &wrSchema);
   sqlite3_db_config(p->db, SQLITE_DBCONFIG_WRITABLE_SCHEMA, 1, 0);
   sqlite3_exec(p->db,
     "CREATE TABLE IF NOT EXISTS temp.sqlite_parameters(\n"
     "  key TEXT PRIMARY KEY,\n"
-    "  value ANY\n"
+    "  value\n"
     ") WITHOUT ROWID;",
     0, 0, 0);
   sqlite3_db_config(p->db, SQLITE_DBCONFIG_WRITABLE_SCHEMA, wrSchema, 0);
   sqlite3_db_config(p->db, SQLITE_DBCONFIG_DEFENSIVE, defensiveMode, 0);
 }

And it does not show the anomaly.

Marginally related: Since I haven't found any documentation for how dot commands are interpreted, I consulted the source. As far as I can figure it out, the rules are as follows:

You may quote an argument with single or double quotes. If double quotes, backslash escapes are interpreted. If not quoted, the argument ends at the next space. If quoted, the quotes are stripped, and the argument is the result.

If the command is .parameters set, the value argument is first put verbatim into a REPLACE INTO sqlite_parameters statement. If that fails, the statement is run again, but now with the value argument quoted as a string. As a result the following dot commands will all insert the integer 7:

.param set :x 007
.param set :x '007'
.param set :x "007"

while the command

.param set :x "'007'"
will indeed insert the string '007' (assuming the patch above). This is pretty hard to guess, so it might be good to document it.

(8) By Bob C (rchapman) on 2021-04-05 14:34:08 in reply to 6 [link] [source]

Since many of the parameters I want to use are TEXT I would really like to see this changed(fixed? ;o). Is there a workaround?

[I really value this forum. It provides so many opportunities to learn!!]

(9) By Larry Brasfield (larrybr) on 2021-04-05 15:56:16 in reply to 8 [link] [source]

One workaround would be to execute this: PRAGMA writable_schema = 1; CREATE TABLE temp.sqlite_parameters(key TEXT PRIMARY KEY, value) WITHOUT ROWID; before the same table is created by use of .parameter init or set. This could be done in a file named .sqliterc in one's home directory.

Another is to delete the word "ANY" from the table creation statement in the shell source and (re)build the shell.

Another is to build the shell from the current trunk tip, or wait until the next release.

(10) By Bob C (rchapman) on 2021-04-05 16:08:21 in reply to 9 [link] [source]

Got it! Thanks!

(11) By Harald Hanche-Olsen (hanche) on 2021-04-05 16:46:06 in reply to 9 [link] [source]

Already fixed before I wrote my previous reply, I notice. Very good. I should have pulled before running my experiment.

(But the documentation for how the arguments to dot commands are parsed could still be improved. See my reply above.)

(12) By Larry Brasfield (larrybr) on 2021-04-05 16:54:39 in reply to 11 [link] [source]

I agree regarding the quoting rules. Those should always be clear, IMHO. We'll see some improvement soon.

(13) By Keith Medcalf (kmedcalf) on 2021-04-05 17:22:13 in reply to 9 [link] [source]

Note that in the CLI you will also have to turn off defensive mode if it is turned on before you can do this because defensive mode provides additional suspenders to prevent illicit modification of the database.

SQLite version 3.36.0 2021-04-03 23:27:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>   PRAGMA writable_schema = 1;
sqlite>   CREATE TABLE temp.sqlite_parameters(key TEXT PRIMARY KEY, value) WITHOUT ROWID;
Error: object name reserved for internal use: sqlite_parameters
sqlite> .dbconfig
          defensive on
            dqs_ddl off
            dqs_dml off
        enable_fkey on
        enable_qpsg off
     enable_trigger on
        enable_view on
     fts3_tokenizer off
 legacy_alter_table off
 legacy_file_format off
     load_extension on
   no_ckpt_on_close off
     reset_database off
        trigger_eqp off
     trusted_schema off
    writable_schema on
sqlite> .dbconfig defensive off
          defensive off
sqlite>   CREATE TABLE temp.sqlite_parameters(key TEXT PRIMARY KEY, value) WITHOUT ROWID;
sqlite> .dbconfig defensive on
          defensive on
sqlite>   PRAGMA writable_schema = 0;
sqlite>

Note that the .param init basically follows the above sequence internally. It enables writable_schema, turns off defensive mode, creates the table, then restores writable_schema and defensive mode to the state they were in before executing the command.