/ Check-in [1f9fa585]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the ".parameter" command to the CLI.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 1f9fa58541dc974989eee9c9a5d453956f7dbcf42965ece2db2cb5dee3f3f5e2
User & Date: drh 2019-02-28 20:10:52
Context
2019-03-01
18:07
In a query that uses a partial index, the expression that is the WHERE clause of the partial index must always be true. Use this fact to avoid evaluating identical terms in the WHERE clause of the query. check-in: 9b287962 user: drh tags: trunk
2019-02-28
20:10
Add the ".parameter" command to the CLI. check-in: 1f9fa585 user: drh tags: trunk
17:29
Add the new "bind_fallback" method to the "sqlite3" object in the TCL interface. check-in: c7f70b6d user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.in.

  2745   2745   #if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
  2746   2746     sqlite3WhereTrace = savedWhereTrace;
  2747   2747   #endif
  2748   2748   }
  2749   2749   
  2750   2750   /* Name of the TEMP table that holds bind parameter values */
  2751   2751   #define BIND_PARAM_TABLE "$Parameters"
         2752  +
         2753  +/* Create the TEMP table used to store parameter bindings */
         2754  +static void bind_table_init(ShellState *p){
         2755  +  sqlite3_exec(p->db,
         2756  +    "CREATE TABLE IF NOT EXISTS temp.[" BIND_PARAM_TABLE "](\n"
         2757  +    "  key TEXT PRIMARY KEY,\n"
         2758  +    "  value ANY\n"
         2759  +    ") WITHOUT ROWID;",
         2760  +    0, 0, 0);
         2761  +}
  2752   2762   
  2753   2763   /*
  2754   2764   ** Bind parameters on a prepared statement.
  2755   2765   **
  2756   2766   ** Parameter bindings are taken from a TEMP table of the form:
  2757   2767   **
  2758   2768   **    CREATE TEMP TABLE "$Parameters"(key TEXT PRIMARY KEY, value)
................................................................................
  3543   3553     "        --maxsize N     Maximum size for --hexdb or --deserialized database",
  3544   3554   #endif
  3545   3555     "        --new           Initialize FILE to an empty database",
  3546   3556     "        --readonly      Open FILE readonly",
  3547   3557     "        --zip           FILE is a ZIP archive",
  3548   3558     ".output ?FILE?           Send output to FILE or stdout if FILE is omitted",
  3549   3559     "     If FILE begins with '|' then open it as a pipe.",
         3560  +  ".parameter CMD ...       Manage SQL parameter bindings",
         3561  +  "   clear                   Erase all bindings",
         3562  +  "   init                    Initialize the TEMP table that holds bindings",
         3563  +  "   list                    List the current parameter bindings",
         3564  +  "   set PARAMETER VALUE     Given SQL parameter PARAMETER a value of VALUE",
         3565  +  "                           PARAMETER should start with '$', ':', '@', or '?'",
         3566  +  "   unset PARAMETER         Remove PARAMETER from the binding table",
  3550   3567     ".print STRING...         Print literal STRING",
  3551   3568   #ifndef SQLITE_OMIT_PROGRESS_CALLBACK
  3552   3569     ".progress N              Invoke progress handler after every N opcodes",
  3553   3570     "   --limit N                 Interrupt after N progress callbacks",
  3554   3571     "   --once                    Do no more than one progress interrupt",
  3555   3572     "   --quiet|-q                No output except at interrupts",
  3556   3573     "   --reset                   Reset the count for each input and interrupt",
................................................................................
  7073   7090           p->out = stdout;
  7074   7091           rc = 1;
  7075   7092         } else {
  7076   7093           sqlite3_snprintf(sizeof(p->outfile), p->outfile, "%s", zFile);
  7077   7094         }
  7078   7095       }
  7079   7096     }else
         7097  +
         7098  +  if( c=='p' && n>=3 && strncmp(azArg[0], "parameter", n)==0 ){
         7099  +    open_db(p,0);
         7100  +    if( nArg<=1 ) goto parameter_syntax_error;
         7101  +
         7102  +    /* .parameter clear
         7103  +    ** Clear all bind parameters by dropping the TEMP table that holds them.
         7104  +    */
         7105  +    if( nArg==2 && strcmp(azArg[1],"clear")==0 ){
         7106  +      sqlite3_exec(p->db, "DROP TABLE IF EXISTS temp.[" BIND_PARAM_TABLE "];",
         7107  +                   0, 0, 0);
         7108  +    }else
         7109  +
         7110  +    /* .parameter list
         7111  +    ** List all bind parameters.
         7112  +    */
         7113  +    if( nArg==2 && strcmp(azArg[1],"list")==0 ){
         7114  +      sqlite3_stmt *pStmt = 0;
         7115  +      int rx;
         7116  +      int len = 0;
         7117  +      rx = sqlite3_prepare_v2(p->db,
         7118  +             "SELECT max(length(key)) "
         7119  +             "FROM temp.[" BIND_PARAM_TABLE "];", -1, &pStmt, 0);
         7120  +      if( rx==SQLITE_OK && sqlite3_step(pStmt)==SQLITE_ROW ){
         7121  +        len = sqlite3_column_int(pStmt, 0);
         7122  +        if( len>40 ) len = 40;
         7123  +      }
         7124  +      sqlite3_finalize(pStmt);
         7125  +      pStmt = 0;
         7126  +      if( len ){
         7127  +        rx = sqlite3_prepare_v2(p->db,
         7128  +             "SELECT key, quote(value) "
         7129  +             "FROM temp.[" BIND_PARAM_TABLE "];", -1, &pStmt, 0);
         7130  +        while( sqlite3_step(pStmt)==SQLITE_ROW ){
         7131  +          utf8_printf(p->out, "%-*s %s\n", len, sqlite3_column_text(pStmt,0),
         7132  +                      sqlite3_column_text(pStmt,1));
         7133  +        }
         7134  +        sqlite3_finalize(pStmt);
         7135  +      }
         7136  +    }else
         7137  +
         7138  +    /* .parameter init
         7139  +    ** Make sure the TEMP table used to hold bind parameters exists.
         7140  +    ** Create it if necessary.
         7141  +    */
         7142  +    if( nArg==2 && strcmp(azArg[1],"init")==0 ){
         7143  +      bind_table_init(p);
         7144  +    }else
         7145  +
         7146  +    /* .parameter set NAME VALUE
         7147  +    ** Set or reset a bind parameter.  NAME should be the full parameter
         7148  +    ** name exactly as it appears in the query.  (ex: $abc, @def).  The
         7149  +    ** VALUE can be in either SQL literal notation, or if not it will be
         7150  +    ** understood to be a text string.
         7151  +    */
         7152  +    if( nArg==4 && strcmp(azArg[1],"set")==0 ){
         7153  +      int rx;
         7154  +      char *zSql;
         7155  +      sqlite3_stmt *pStmt;
         7156  +      const char *zKey = azArg[2];
         7157  +      const char *zValue = azArg[3];
         7158  +      bind_table_init(p);
         7159  +      zSql = sqlite3_mprintf(
         7160  +                  "REPLACE INTO temp.[" BIND_PARAM_TABLE "](key,value)"
         7161  +                  "VALUES(%Q,%s);", zKey, zValue);
         7162  +      if( zSql==0 ) shell_out_of_memory();
         7163  +      pStmt = 0;
         7164  +      rx = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
         7165  +      sqlite3_free(zSql);
         7166  +      if( rx!=SQLITE_OK ){
         7167  +        sqlite3_finalize(pStmt);
         7168  +        pStmt = 0;
         7169  +        zSql = sqlite3_mprintf(
         7170  +                   "REPLACE INTO temp.[" BIND_PARAM_TABLE "](key,value)"
         7171  +                   "VALUES(%Q,%Q);", zKey, zValue);
         7172  +        if( zSql==0 ) shell_out_of_memory();
         7173  +        rx = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
         7174  +        sqlite3_free(zSql);
         7175  +        if( rx!=SQLITE_OK ){
         7176  +          utf8_printf(p->out, "Error: %s\n", sqlite3_errmsg(p->db));
         7177  +          sqlite3_finalize(pStmt);
         7178  +          pStmt = 0;
         7179  +          rc = 1;
         7180  +        }
         7181  +      }
         7182  +      sqlite3_step(pStmt);
         7183  +      sqlite3_finalize(pStmt);
         7184  +    }else
         7185  +
         7186  +    /* .parameter unset NAME
         7187  +    ** Remove the NAME binding from the parameter binding table, if it
         7188  +    ** exists.
         7189  +    */
         7190  +    if( nArg==3 && strcmp(azArg[1],"unset")==0 ){
         7191  +      char *zSql = sqlite3_mprintf(
         7192  +          "DELETE FROM temp.[" BIND_PARAM_TABLE "] WHERE key=%Q", azArg[2]);
         7193  +      if( zSql==0 ) shell_out_of_memory();
         7194  +      sqlite3_exec(p->db, zSql, 0, 0, 0);
         7195  +      sqlite3_free(zSql);
         7196  +    }else
         7197  +    /* If no command name matches, show a syntax error */
         7198  +    parameter_syntax_error:
         7199  +    showHelp(p->out, "parameter");
         7200  +  }else
  7080   7201   
  7081   7202     if( c=='p' && n>=3 && strncmp(azArg[0], "print", n)==0 ){
  7082   7203       int i;
  7083   7204       for(i=1; i<nArg; i++){
  7084   7205         if( i>1 ) raw_printf(p->out, " ");
  7085   7206         utf8_printf(p->out, "%s", azArg[i]);
  7086   7207       }