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.

How about this:

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

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?

Probably not.

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

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.)

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.

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!!]

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.

Got it! Thanks!

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.)

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