Missing sqlite3_clear_bindings API

[aliasliao]

Accroding to https://www.sqlite.org/c3ref/clear_bindings.html, to clear stmt binding, sqlite3_clear_bindings needs to be called.

However, only reset is present in SQLiteAPI currently.

P.S. What I want to implement is something like this:

  async batchRun(sql: string, records: BindingSpec[]): Promise<void> {
    for await (const stmt of this.sqlite3.statements(this.db, sql)) {
      for (const record of records) {
        this.sqlite3.bind_collection(stmt, record);
        await this.sqlite3.step(stmt);
        await this.sqlite3.reset(stmt);
        // need to clear binding here, but there is no such API
      }
    }
  }

[aliasliao]

Find a workaround:

  async batchRun(sql: string, records: BindingSpec[]): Promise<void> {
    for await (const stmt of this.sqlite3.statements(this.db, sql)) {
      for (const record of records) {
        this.sqlite3.bind_collection(stmt, record);
        await this.sqlite3.step(stmt);
        await this.sqlite3.reset(stmt);

        // sqlite3_clear_bindings
        const nBindings = this.sqlite3.bind_parameter_count(stmt);
        for (let i = 1; i <= nBindings; ++i) {
          this.sqlite3.bind_null(stmt, i);
        }
      }
    }
  }

However, this will significantly impact performance when there are a large number of records. I hope that sqlite3_clear_bindings will be added to the API list 🙏.

[rhashimoto]

It's a reasonable thing to add. I'll put it on my TODO list, but not at high priority.

The underlying C function is exported by the WebAssembly module (the argument to SQLite.Factory() creating the API wrappers), so you should be able to make a call like this as a workaround:

  module._sqlite3_clear_bindings(stmt);

[sgbeal]

For instance, I would like to override sqlite3.bind_collection to enable binding named parameters without requiring a prefix in the object field name:

FWIW, i don't believe that would be possible because the prefix is part of the SQL code and the object keys have to match those exactly. The object being passed to bind_collection() has no way of knowing (without inspecting the SQL, but That Way Lies Madness) which prefix is used for any given named parameter.

[aliasliao]

For instance, I would like to override sqlite3.bind_collection to enable binding named parameters without requiring a prefix in the object field name:

FWIW, i don't believe that would be possible because the prefix is part of the SQL code and the object keys have to match those exactly. The object being passed to bind_collection() has no way of knowing (without inspecting the SQL, but That Way Lies Madness) which prefix is used for any given named parameter.

What I mean is that it's possible to implement this binding at the API level (like better-sqlite3).

By modifying sqlite3.bind_collection, this can be achieved:

sqlite3.bind_collection = function(stmt, bindings) {
  verifyStatement(stmt);
  const isArray = Array.isArray(bindings);
  const nBindings = sqlite3.bind_parameter_count(stmt);
  for (let i = 1; i <= nBindings; ++i) {
    let key = isArray ? i - 1 : sqlite3.bind_parameter_name(stmt, i);
+    
+    if (typeof key === 'string') {
+      key = key.slice(1);
+    }
+
    const value = bindings[key];
    if (value !== undefined) {
      sqlite3.bind(stmt, i, value);
    }
  }
  return SQLite.SQLITE_OK;
};

[sgbeal]

sqlite3.bind_parameter_name(stmt, i);

Aha, i had completely forgotten about that function.

[rhashimoto]

I'm not planning to support API wrapper extension. You can build/copy your own wrapper support functions, or you can fork.

In the particular case of the internal verifyStatement() checks, I don't think it adds much value any more. I thought that it might when the sqlite3_prepare_v2() wrapper returned a non-standard object, but now that all statement pointers are provided by the statements() iterator in 1.0, I think the mistake of passing a non-statement pointer is much less likely.

[aliasliao]

Okay, I tried ignoring verifyStatement and directly calling the C API, and it worked. Thanks!