SQL Query Logging for SQLite

Enable Query Logging in SQLite 3

There is no easy way to do this like with MySQL, but there are some options:

One:

Some wrapper-libraries have something like this built-in. But to find a wrapper library you would probably first need to identify the target language. Perl DBI? Python? C++?

Two:

I would not (in any way) recommend the following for a "production-grade" solution, but if you are mainly experimenting and/or debugging, then you might try examining the rollback journal just prior to the end of each transaction. See here about the rollback journal: http://www.sqlite.org/tempfiles.html How you would detect 'the end of each transaction' would be up to your code and/or the breakpoints in your debugger.

I must emphasize again: what I just mentioned above would be a total hack-around, and I feel dirty even having mentioned it.

Three:

You could ask on the (very active and gracious) sqlite mailing list, but they would probably just reemphasize sqlite3_trace.

... other random thing:

On a somewhat (barely?) related note, when you start a './sqlite3' command prompt session, you can type:

.explain

which enables interesting and instructive verbose output for each query executed at the prompt.

More Info I Just Found:

One of the flags that can be passed to 'sqlite3_config()' is SQLITE_CONFIG_LOG. This is another way (in addition to the trace API) to set a callback and receive status information from the sqlite library periodically. I think it is mainly for error log messages.

SQL query logging for SQLite?

Take a look at the sqlite Trace API. You have to implement the callback yourself.

void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*), void*);

The callback function registered by sqlite3_trace() is invoked at various times when an SQL statement is being run by sqlite3_step(). The callback returns a UTF-8 rendering of the SQL statement text as the statement first begins executing. Additional callbacks occur as each triggered subprogram is entered.

Logging SQL queries in android

You can apply your own SQLiteDatabase.CursorFactory to the database. (See the openDatabase parameters.) This will allow you to create your own subclass of Cursor, which keeps the query in an easily accessible field.

edit: In fact, you may not even have to subclass Cursor. Just have your factory's newCursor() method return a standard SQLiteCursor, but log the query before doing so.

Is there a way to log SQLite queries?

You could extend SQLite3 class and wrap the SQLite3Stmt object by overriding the prepare method. In your wrapper implement the bindValue and execute methods and call them on the SQLite3Stmt object (add logging here). To finish it off you could add the magic __get and __call functions for any methods/properties you're not intercepting.

For this to work you have to change "new SQLite3" to "new SQLite3Debug" (assuming your extended class is called this).

SQLite trace for logging

The main error in your code is that you derefence the raw pointers
passed to the callback instead of reinterpreting (casting) them.
Also the meaning of those pointers is different for the different
events.

Here is an example how to trace the various events and how to convert
the raw pointers to the "correct" types, using a literal closure
as callback. The comments explaining the meaning of the p and x
argument are taken from SQL Trace Event Codes.

let traceMask = SQLITE_TRACE_STMT|SQLITE_TRACE_PROFILE|SQLITE_TRACE_ROW|SQLITE_TRACE_CLOSE

sqlite3_trace_v2(database, UInt32(traceMask), { (reason, context, p, x) -> Int32 in
    switch Int32(reason) {
    case SQLITE_TRACE_STMT:
        // The P argument is a pointer to the prepared statement.
        // The X argument is a pointer to a string which is the unexpanded SQL text 
        guard
            let pStmt = OpaquePointer(p),
            let cSql = x?.assumingMemoryBound(to: CChar.self)
        else {
            return 0
        }

        let sql = String(cString: cSql) // The unexpanded SQL text
        let expandedSql = String(cString: sqlite3_expanded_sql(pStmt)) // The expanded SQL text
        print("SQLITE_TRACE_STMT:", expandedSql)

    case SQLITE_TRACE_PROFILE:
        // The P argument is a pointer to the prepared statement and the X argument points
        // to a 64-bit integer which is the estimated of the number of nanosecond that the
        // prepared statement took to run.
        guard
            let pStmt = OpaquePointer(p),
            let duration = x?.load(as: UInt64.self)
        else {
            return 0
        }

        let milliSeconds = Double(duration)/Double(NSEC_PER_MSEC)
        let sql = String(cString: sqlite3_sql(pStmt)) // The unexpanded SQL text
        print("SQLITE_TRACE_PROFILE:", milliSeconds, "ms for statement:", sql)

    case SQLITE_TRACE_ROW:
        // The P argument is a pointer to the prepared statement and the X argument is unused.
        guard
            let pStmt = OpaquePointer(p)
        else {
            return 0
        }

        print("SQLITE_TRACE_ROW")

    case SQLITE_TRACE_CLOSE:
        // The P argument is a pointer to the database connection object and the X argument is unused.
        guard
            let database = OpaquePointer(p)
        else {
            return 0
        }

        print("SQLITE_TRACE_CLOSE")

    default:
        break
    }
    return 0
}, nil)

Of course you can restrict the trace mode to the events that you
are interesting in, e.g.

let traceMask = SQLITE_TRACE_STMT

to trace only prepared statements.

How to enable LOGging of all internal SQL cmd's of the Android?

Ok, as usual - if you want to do something - do making it only by yourself)))

try (Cursor cursor = resolver.query(uri, PROJECTION, null, null, null))

if in this command line to put in the last argument 100% error string - for example "LIKE bananas" - on the execution stage we will get an error "android.database.sqlite.SQLiteException: near "bananas": syntax error (code 1)"
with corresponding staсktrace which will include also and the full SQL command - in which that error was found.
Thus we can rather easily to get the knowledge - by WHICH EXACTLY SQL request a particular URI request is being made. And it will work on real phones!!! And so we can easily understand - and why suddenly we do not have an access to a particular data column, which seems to be present in the results of the query(according to online HELP for devs), but for some reason on certain models of phones they do not. And now it becomes easier to understand - and how you can rewrite your request, on the basis of "stacktraced exactly SQL" as it a reference - but with a guarantee of obtaining the necessary data as a result of its execution!

---

Related Topics