Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Utilize sqlite3_expanded_sql to retrieve sql statements with bindings #1678

Open
smith-xyz opened this issue Feb 2, 2023 · 1 comment
Open

Comments

@smith-xyz
Copy link

Summary

With sqlite3_expanded_sql() you can retrieve the expanded version of the sql statement with the bindings. This is useful mostly for debugging purposes but could be useful if one has a disaster recovery process utilizing the prepared sql statements. Since sqlite3 has provided this as part of the api it may be nice to utilize here instead of creating an alternative process for creating that expanded sql.

Proposed implementation

Wondering if maybe Statement could have a expandedSql() method that could be used for this - always just pulls the sql:

db.serialize(() => {
  db.run("CREATE TABLE lorem (info TEXT)");
  const stmt = db.prepare("INSERT INTO lorem VALUES (?)");
  console.log(stmt.expandedSql()); // returns "INSERT INTO lorem VALUES (?)"
  stmt.bind("Ipsum");
  console.log(stmt.expandedSql()); // returns "INSERT INTO lorem VALUES ("Ipsum")"
  stmt.run();
  stmt.finalize();
});

Also could see this being in the trace as well.

Here is my quick use of the api performs the same result on a prepared statement:

int main(int argc, const char *const argv[])
{
  sqlite3 *db;
  sqlite3_open_v2(argv[1], &db, SQLITE_OPEN_READONLY, NULL);

  int rc;
  sqlite3_stmt *res;
  char *query = "";
  query = "SELECT count(*) FROM `test_table` WHERE `id` = ?;";
  rc = sqlite3_prepare_v2(db, query, -1, &res, 0);
  char *expanded = "";
  expanded = sqlite3_expanded_sql(res);
  printf("ExpandedQueryBefore=%s\n", query);

  if (rc == SQLITE_OK)
  {
    sqlite3_bind_int(res, 1, 1);
  }
 expanded = sqlite3_expanded_sql(res);
  printf("ExpandedQueryAfter=%s\n", expanded);
  int step = sqlite3_step(res);
 
  sqlite3_finalize(res);
  return 0;
}
@smith-xyz smith-xyz changed the title Utilize sqlite3_expanded_sql to print sql statements with bindings Utilize sqlite3_expanded_sql to retrieve sql statements with bindings Feb 2, 2023
@daniellockyer
Copy link
Member

Sounds like a good idea to me 🙂 I'd happily welcome a PR

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants