In the page for expressions (http://www.sqlite.org/lang_expr.html) check out the Parameters section and the REGEXP operator section:
"The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL function named "regexp" is added at run-time, then the "X REGEXP Y" operator will be implemented as a call to "regexp(Y,X)"." So whatever you're normally running has probably implemented its own regexp, which would need to be loaded to test it yourself. As to the other part, you <are> seeing the SQL. The question marks are for bound parameters, the question mark means "get the value from the bound variable". The statement text stays the same the whole time, only the bindings get updated. So if you're looking to log everything that it's doing you'll have to capture it binding those values as well. Hopefully I'm answering that right. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas Nyberg Sent: Monday, February 13, 2017 3:12 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Intercepting execution of sqlite3 command to see final query Hello, The premise of this is the following: I'm using sqlite3 in a django application. I'm having it log the SQL that it issues (once you get passed its magical ORM setup), but that command does not work. I.e. I have something like the following (the python is not important, but does set the stage a little): >>> regex = 'NET | INCOME' >>> Table.objects.filter(name__iregex=regex). When I have it print out the SQL sent, I get the following (this is a simplified, but equivalent version): SELECT * FROM "table" WHERE "table"."name" REGEXP '(?i)' || 'NET | INCOME' LIMIT 21 However if I execute that directly against the sqlite database i get the following error: sqlite3.OperationalError: no such function: REGEXP I thought that maybe this magic was handled in python's sqlite3 module, but if i do it there i get the same error. Finally I realized it might just be easier to intercept the sqlite3 call at the dynamic library level. To do this, I wrote the following code: test.c --------------------------------------- #define _GNU_SOURCE #include <stdio.h> #include <dlfcn.h> #include <sqlite3.h> SQLITE_API int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt **ppStmt, const char **pzTail) { int (*original_sqlite3_prepare)(sqlite3*, const char *, int, sqlite3_stmt**, const char **); original_sqlite3_prepare = dlsym(RTLD_NEXT, "sqlite3_prepare"); printf("\n\n***ORIGINAL SQL QUERY***\n\n\n"); printf("%s", zSql); printf("\n\n***END ORIGINAL SQL QUERY***\n\n\n"); fflush(stdout); return (*original_sqlite3_prepare)(db, zSql, nByte, ppStmt, pzTail); } --------------------------------------- I compiled it as follows: $ gcc -Wall -fPIC -shared -o mysqlite3_prepare.so test.c -ldl -lsqlite3 When I preload this before running my app, it prints out the following (slightly cleaned up to make easier to read by removing a couple irrelevant fields): SELECT "table"."id", "table"."name", FROM "table" WHERE "table"."name" REGEXP '(?i)' || ? I'm not sure what to do with this. It's even missing the final part. So obviously I'm looking at this in the wrong place. So I guess I have a couple questions: 1) How can I sort out what the final query actually is? Possibly I should be intercepting other calls or functions? 2) Is this obvious the readers here (thus making my other investigations unnecessary) what's going on? Thanks for any help! Cheers, Thomas P.S. Appologies if this is not appropriate here? Maybe it should be posted to sqlite-dev? _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
