Error when use json_extract() on sqliteodbc-CodePudding

I use sqliteodbc from http://www.ch-werner.de/sqliteodbc/ and this code in ms word

str_cnxn = "Driver={SQLite3 ODBC Driver};Database=" & db_path
Dim Cnxn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Cnxn.Open str_cnxn
rst.Open "Select *,json_extract(pdf_links, '$.root')  as root from book where book_id=10", Cnxn, adOpenForwardOnly, adLockReadOnly
Debug.Print rst!book_name, rst!Root
rst.Close

I got this error

no such function:json_extract

Same SQL statement on same DB run successfully on SQLite Db Browser https://sqlitebrowser.org/

CodePudding user response：

Do you understand that when you use SQLiteODBC and SQLite Db Browser you are using two completely different versions of SQLite?

The latest available SQLiteODBC version is grossly outdated. If you want to use JSON functions via SQLiteODBC, you need to compile it yourself. I have recently built a Windows version of SQLiteODBC from source. The installer is available from GitHub repo. You would need to uninstall the currently installed driver and install a new one (either the copy you compile yourself or my copy). Note, if you build the driver yourself, there is no guarantee it will work until you do it right. The build scripts included with the source are broken, and additional modifications needs to be done to include a recent copy of SQLite. My copy includes SQLite 3.39 and it has JSON functionality working properly.