SQLite User Forum

Minor CLI bug: parameters don't _quite_ work
Login

Minor CLI bug: parameters don't _quite_ work

(1.2) By ian wild (ianwild) on 2025-01-01 19:44:37 edited from 1.1 [source]

According to the documentation

The VALUE passed to ".param set KEY VALUE" can be either a SQL literal or any other SQL expression or query which can be evaluated to yield a value.

This suggests to me that the following was intended to do the obvious thing:

sqlite> .param set :one 1
sqlite> .param set :two :one+:one

But, alas:

sqlite> .param list
:one 1
:two NULL

This is because, at least for versions 3.46.0 to (the 2025-01-01 pre-release of) 3.48.0, no attempt is made to bind parameters in the VALUE part of a .param set.

Adding a couple of lines in the handler for .param set seems to fix this, with no ill effects anywhere else.

    if( nArg==4 && cli_strcmp(azArg[1],"set")==0 ){
      ...
      rx = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
      sqlite3_free(zSql);
      if(rx==SQLITE_OK)                         // patch
        bind_prepared_stmt(p, pStmt);           // patch
      if( rx!=SQLITE_OK ){...

Now,

sqlite> .param set :one 1
sqlite> .param set :two :one+:one
sqlite> .param list
:one 1
:two 2
as I'd expect.

(Edit: added a line of context lost during cut'n'paste)

(Edit2: tested against 3.48.0)