How to view the SQLite query that is reaching the .db/.sqlite file from my application?

Question

I know that SQL Server has many tools including profilers and some tools that connect to the db and allow you to view the queries that reach it and the values that are then returned.

In my Qt Desktop app, upon clicking a button, a query is prepared, values are bound and it is executed, but the results are wrong. Interested in debugging this query, I would like to be able to view the final query that reaches the DB in order to determine if it is wrong if there were db unrelated bugs that eventually caused the query to fail.

How can I achieve this?

Answer 1

You can use sqlite3_expanded_sql() to see the query a prepared statement is executing, including the values bound to parameters in the query.

Example:

#include <iostream>
#include <sqlite3.h>

int main(void) {
  sqlite3 *db;
  if (sqlite3_open(":memory:", &db) != SQLITE_OK) {
    std::cerr << sqlite3_errmsg(db) << '\n';
    return 1;
  }

  if (sqlite3_exec(db, "CREATE TABLE foo(bar)", nullptr, nullptr, nullptr)
      != SQLITE_OK) {
    std::cerr << sqlite3_errmsg(db) << '\n';
    return 1;
  }

  sqlite3_stmt *stmt;
  if (sqlite3_prepare_v2(db, "SELECT * FROM foo WHERE bar = ?", -1, &stmt,
                         nullptr) != SQLITE_OK) {
    std::cerr << sqlite3_errmsg(db) << '\n';
    return 1;
  }
  sqlite3_bind_text(stmt, 1, "example", -1, SQLITE_STATIC);

  char *sql = sqlite3_expanded_sql(stmt);
  // prints out SELECT * FROM foo WHERE bar = 'example'
  std::cout << sql << '\n';
  
  sqlite3_free(sql);
  sqlite3_finalize(stmt);
  sqlite3_close(db);

  return 0;
}