/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/shell.c.in.

2745
2746
2747
2748
2749
2750
2751










2752
2753
2754
2755
2756
2757
2758
....
3543
3544
3545
3546
3547
3548
3549







3550
3551
3552
3553
3554
3555
3556
....
7073
7074
7075
7076
7077
7078
7079








































































































7080
7081
7082
7083
7084
7085
7086
#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
  sqlite3WhereTrace = savedWhereTrace;
#endif
}

/* Name of the TEMP table that holds bind parameter values */
#define BIND_PARAM_TABLE "$Parameters"











/*
** Bind parameters on a prepared statement.
**
** Parameter bindings are taken from a TEMP table of the form:
**
**    CREATE TEMP TABLE "$Parameters"(key TEXT PRIMARY KEY, value)
................................................................................
  "        --maxsize N     Maximum size for --hexdb or --deserialized database",
#endif
  "        --new           Initialize FILE to an empty database",
  "        --readonly      Open FILE readonly",
  "        --zip           FILE is a ZIP archive",
  ".output ?FILE?           Send output to FILE or stdout if FILE is omitted",
  "     If FILE begins with '|' then open it as a pipe.",







  ".print STRING...         Print literal STRING",
#ifndef SQLITE_OMIT_PROGRESS_CALLBACK
  ".progress N              Invoke progress handler after every N opcodes",
  "   --limit N                 Interrupt after N progress callbacks",
  "   --once                    Do no more than one progress interrupt",
  "   --quiet|-q                No output except at interrupts",
  "   --reset                   Reset the count for each input and interrupt",
................................................................................
        p->out = stdout;
        rc = 1;
      } else {
        sqlite3_snprintf(sizeof(p->outfile), p->outfile, "%s", zFile);
      }
    }
  }else









































































































  if( c=='p' && n>=3 && strncmp(azArg[0], "print", n)==0 ){
    int i;
    for(i=1; i<nArg; i++){
      if( i>1 ) raw_printf(p->out, " ");
      utf8_printf(p->out, "%s", azArg[i]);
    }







>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







2745
2746
2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
2758
2759
2760
2761
2762
2763
2764
2765
2766
2767
2768
....
3553
3554
3555
3556
3557
3558
3559
3560
3561
3562
3563
3564
3565
3566
3567
3568
3569
3570
3571
3572
3573
....
7090
7091
7092
7093
7094
7095
7096
7097
7098
7099
7100
7101
7102
7103
7104
7105
7106
7107
7108
7109
7110
7111
7112
7113
7114
7115
7116
7117
7118
7119
7120
7121
7122
7123
7124
7125
7126
7127
7128
7129
7130
7131
7132
7133
7134
7135
7136
7137
7138
7139
7140
7141
7142
7143
7144
7145
7146
7147
7148
7149
7150
7151
7152
7153
7154
7155
7156
7157
7158
7159
7160
7161
7162
7163
7164
7165
7166
7167
7168
7169
7170
7171
7172
7173
7174
7175
7176
7177
7178
7179
7180
7181
7182
7183
7184
7185
7186
7187
7188
7189
7190
7191
7192
7193
7194
7195
7196
7197
7198
7199
7200
7201
7202
7203
7204
7205
7206
7207
#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_WHERETRACE)
  sqlite3WhereTrace = savedWhereTrace;
#endif
}

/* Name of the TEMP table that holds bind parameter values */
#define BIND_PARAM_TABLE "$Parameters"

/* Create the TEMP table used to store parameter bindings */
static void bind_table_init(ShellState *p){
  sqlite3_exec(p->db,
    "CREATE TABLE IF NOT EXISTS temp.[" BIND_PARAM_TABLE "](\n"
    "  key TEXT PRIMARY KEY,\n"
    "  value ANY\n"
    ") WITHOUT ROWID;",
    0, 0, 0);
}

/*
** Bind parameters on a prepared statement.
**
** Parameter bindings are taken from a TEMP table of the form:
**
**    CREATE TEMP TABLE "$Parameters"(key TEXT PRIMARY KEY, value)
................................................................................
  "        --maxsize N     Maximum size for --hexdb or --deserialized database",
#endif
  "        --new           Initialize FILE to an empty database",
  "        --readonly      Open FILE readonly",
  "        --zip           FILE is a ZIP archive",
  ".output ?FILE?           Send output to FILE or stdout if FILE is omitted",
  "     If FILE begins with '|' then open it as a pipe.",
  ".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 '$', ':', '@', or '?'",
  "   unset PARAMETER         Remove PARAMETER from the binding table",
  ".print STRING...         Print literal STRING",
#ifndef SQLITE_OMIT_PROGRESS_CALLBACK
  ".progress N              Invoke progress handler after every N opcodes",
  "   --limit N                 Interrupt after N progress callbacks",
  "   --once                    Do no more than one progress interrupt",
  "   --quiet|-q                No output except at interrupts",
  "   --reset                   Reset the count for each input and interrupt",
................................................................................
        p->out = stdout;
        rc = 1;
      } else {
        sqlite3_snprintf(sizeof(p->outfile), p->outfile, "%s", zFile);
      }
    }
  }else

  if( c=='p' && n>=3 && strncmp(azArg[0], "parameter", n)==0 ){
    open_db(p,0);
    if( nArg<=1 ) goto parameter_syntax_error;

    /* .parameter clear
    ** Clear all bind parameters by dropping the TEMP table that holds them.
    */
    if( nArg==2 && strcmp(azArg[1],"clear")==0 ){
      sqlite3_exec(p->db, "DROP TABLE IF EXISTS temp.[" BIND_PARAM_TABLE "];",
                   0, 0, 0);
    }else

    /* .parameter list
    ** List all bind parameters.
    */
    if( nArg==2 && strcmp(azArg[1],"list")==0 ){
      sqlite3_stmt *pStmt = 0;
      int rx;
      int len = 0;
      rx = sqlite3_prepare_v2(p->db,
             "SELECT max(length(key)) "
             "FROM temp.[" BIND_PARAM_TABLE "];", -1, &pStmt, 0);
      if( rx==SQLITE_OK && sqlite3_step(pStmt)==SQLITE_ROW ){
        len = sqlite3_column_int(pStmt, 0);
        if( len>40 ) len = 40;
      }
      sqlite3_finalize(pStmt);
      pStmt = 0;
      if( len ){
        rx = sqlite3_prepare_v2(p->db,
             "SELECT key, quote(value) "
             "FROM temp.[" BIND_PARAM_TABLE "];", -1, &pStmt, 0);
        while( sqlite3_step(pStmt)==SQLITE_ROW ){
          utf8_printf(p->out, "%-*s %s\n", len, sqlite3_column_text(pStmt,0),
                      sqlite3_column_text(pStmt,1));
        }
        sqlite3_finalize(pStmt);
      }
    }else

    /* .parameter init
    ** Make sure the TEMP table used to hold bind parameters exists.
    ** Create it if necessary.
    */
    if( nArg==2 && strcmp(azArg[1],"init")==0 ){
      bind_table_init(p);
    }else

    /* .parameter set NAME VALUE
    ** Set or reset a bind parameter.  NAME should be the full parameter
    ** name exactly as it appears in the query.  (ex: $abc, @def).  The
    ** VALUE can be in either SQL literal notation, or if not it will be
    ** understood to be a text string.
    */
    if( nArg==4 && strcmp(azArg[1],"set")==0 ){
      int rx;
      char *zSql;
      sqlite3_stmt *pStmt;
      const char *zKey = azArg[2];
      const char *zValue = azArg[3];
      bind_table_init(p);
      zSql = sqlite3_mprintf(
                  "REPLACE INTO temp.[" BIND_PARAM_TABLE "](key,value)"
                  "VALUES(%Q,%s);", zKey, zValue);
      if( zSql==0 ) shell_out_of_memory();
      pStmt = 0;
      rx = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
      sqlite3_free(zSql);
      if( rx!=SQLITE_OK ){
        sqlite3_finalize(pStmt);
        pStmt = 0;
        zSql = sqlite3_mprintf(
                   "REPLACE INTO temp.[" BIND_PARAM_TABLE "](key,value)"
                   "VALUES(%Q,%Q);", zKey, zValue);
        if( zSql==0 ) shell_out_of_memory();
        rx = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
        sqlite3_free(zSql);
        if( rx!=SQLITE_OK ){
          utf8_printf(p->out, "Error: %s\n", sqlite3_errmsg(p->db));
          sqlite3_finalize(pStmt);
          pStmt = 0;
          rc = 1;
        }
      }
      sqlite3_step(pStmt);
      sqlite3_finalize(pStmt);
    }else

    /* .parameter unset NAME
    ** Remove the NAME binding from the parameter binding table, if it
    ** exists.
    */
    if( nArg==3 && strcmp(azArg[1],"unset")==0 ){
      char *zSql = sqlite3_mprintf(
          "DELETE FROM temp.[" BIND_PARAM_TABLE "] WHERE key=%Q", azArg[2]);
      if( zSql==0 ) shell_out_of_memory();
      sqlite3_exec(p->db, zSql, 0, 0, 0);
      sqlite3_free(zSql);
    }else
    /* If no command name matches, show a syntax error */
    parameter_syntax_error:
    showHelp(p->out, "parameter");
  }else

  if( c=='p' && n>=3 && strncmp(azArg[0], "print", n)==0 ){
    int i;
    for(i=1; i<nArg; i++){
      if( i>1 ) raw_printf(p->out, " ");
      utf8_printf(p->out, "%s", azArg[i]);
    }