Problems with extensions in sqlite3.dll and sqlite-jdbc

[magicprinc]

There is something wrong with some extensions...
They work in SQLite3 shell, but won't load or work correctly in sqlite3.dll and sqlite-jdbc (java sqlite driver).
test.zip

I have attached a java example.
wget https://github.com/xerial/sqlite-jdbc/releases/download/3.36.0.3/sqlite-jdbc-3.36.0.3.jar
wget https://jsoup.org/packages/jsoup-1.14.3.jar
javac -encoding utf8 -cp .:sqlite-jdbc-3.36.0.3.jar:jsoup-1.14.3.jar Extension.java

; with extensions downloading
java -cp .:sqlite-jdbc-3.36.0.3.jar:jsoup-1.14.3.jar Extension

; without extensions downloading
java -cp .:sqlite-jdbc-3.36.0.3.jar Extension

If you use Mac, you probably have to edit Extension.java and change
String ext = (File.separatorChar == '\') ? ".dll" : ".so";
to
String ext = ".dylib";

On my computer:
json1, stats can't be loaded
unicode can be loaded*, but doesn't work at all

[magicprinc]

bug.zip
Groovy version of the same app.
You don't have to download dependencies and compile.
Install groovy and run:
groovy Extension.groovy

[nalgeon]

Thanks for the report! I have confirmed the problem with the stats extension, currently investigating. Will look into others too.

[nalgeon]

json1 extension

This extension is already compiled into sqlite-jdbc, so the loading fails. It's OK.

stats extension

For some reason, the authors of sqlite-jdbc decided to compile extension-functions.c into the driver (a very strange decision IMO — to bundle the driver with some arbitrary extension). This extension defines some functions named as the ones in stats — so the stats won't load.

I allowed function redefinition for stats in 0.12.3, so now it should work just fine.

unicode

This extension won't load via load_extension() SQL function due to SQLite restrictions:

The load_extension() function will fail if the extension attempts to modify or delete an SQL function or collating sequence

There is no workaround, other than using C API or SQLite CLI.

[magicprinc]

I have an idea
how to help poor SQLite3.dll and sqlite-jdbc users :-)

Could you, please, make a twin extension, let's say: nunicode
with renamed function names, let's say, n+original name: nupper, nlower, etc.
(n comes from MSSQL's N prefix: "Prefix Unicode character string constants with the letter N. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters. E.g. N'unicode string'")

Then one could use this extension in apps like heidisql, where it is impossible to call C-API... 🙏

[nalgeon]

That's a great idea! I've added nupper() and nlower() functions to unicode extension in release 0.13.1.