B4J Library SQLite Enable Loading of Extensions

NOTE this is for B4J only (Erel has stated that you can't enable extension loading on Android/iOS - link)

Small Java library with two methods that enable or disable loading of extensions for SQLite.

• EnableLoading(SQL) - enable loading of SQLite extensions
• DisableLoading(SQL) - disable loading of SQLite extensions

You can get some precompiled extensions here.

GitHub - nalgeon/sqlean: The ultimate set of SQLite extensions

The ultimate set of SQLite extensions. Contribute to nalgeon/sqlean development by creating an account on GitHub.

github.com

Extensions must be in the same folder as your JAR (or Objects folder during debug) - alternatively (not tested) you can use the full path when calling load_extension. You must load the correct library for your OS (eg DLL for Windows and SO for Linux)

How to use;

Dim SQL As SQL
SQL.InitializeSQLite(File.DirApp,"Test.sqlite",True)

Log(SQL.IsInitialized)

Dim e As EnableSQLiteExtensions

e.EnableLoading(SQL) ' Enable loading
SQL.ExecNonQuery("SELECT load_extension('crypto.dll')") ' Extension should be in the same folder as your JAR or Objects folder
e.DisableLoading(SQL) ' You can disable loading once you have loaded your extension

Log(SQL.ExecQuerySingleResult("select hex(crypto_md5('abc'));"))

 

[tchart]

There are also many extensions here;

sqlpkg - SQLite Extension Hub

 

[aeric]

Hi Trevor,
I tested your example with sqlite-jdbc-3.39.3.0 it works but not work for sqlite-jdbc-3.7.2

java.lang.NoClassDefFoundError: org/sqlite/SQLiteConnection

Can you put information on the minimum version?

 

[tchart]

Hi Trevor,
I tested your example with sqlite-jdbc-3.39.3.0 it works but not work for sqlite-jdbc-3.7.2

java.lang.NoClassDefFoundError: org/sqlite/SQLiteConnection

Can you put information on the minimum version?

Hey Aeric. I’ll confirm tomorrow. Thanks for posting as I’m using 3.50 and didn’t think to test older versions.

 

[aeric]

Hey Aeric. I’ll confirm tomorrow. Thanks for posting as I’m using 3.50 and didn’t think to test older versions.

From Google AI it said minimum is 3.7.15 and it may depends on what extensions.

 

[tchart]

From Google AI it said minimum is 3.7.15 and it may depends on what extensions.

Its a bit vague as its on the Xerial side - SQLite could load extensions since 2006 (3.3.6) if it was compiled with that option.

The JavaDoc for Xerial suggests 3.8.11 as the minimum but the method was changed in 3.18 to use native bindings.

I'd say stick to 3.18 and above.

 

[Claudio Oliveira]

I've been waiting for this for AGES!
Thank you so much, @tchart!

 

[tchart]

I've been waiting for this for AGES!
Thank you so much, @tchart!

@Claudio Oliveira I read through the notes for the latest JDBC driver (3.51.0.0) that a test was added to test enabling extensions via connection string.

I tested with 3.51.0.0 and you can now enable extensions using the connection string (so there is no need for my library unless you prefer to use InitializeSQLite).

UPDATE: you can actually use InitializeSQLite as well.

SQL.InitializeSQLite(File.DirApp,"Test.sqlite?enable_load_extension=true",True)

SQL.Initialize("org.sqlite.JDBC",$"jdbc:sqlite:${File.Combine(File.DirApp,"Test.sqlite")}?enable_load_extension=true"$)

SQL.ExecNonQuery("SELECT load_extension('crypto.dll')")  
SQL.ExecNonQuery("SELECT load_extension('zstd.dll')")
  
Log(SQL.ExecQuerySingleResult("select hex(crypto_md5('abc'));"))
Log(SQL.ExecQuerySingleResult("select zstd_compress('hello123');"))

 

[Claudio Oliveira]

Hey @tchart !

I read through the notes for the latest JDBC driver (3.51.0.0) that a test was added to test enabling extensions via connection string.

I read it too, and was about to test it today. Thank you for being faster than me and posting usage examples!
Even so, congrats for you library and thank you for sharing it.
SQLite extensions open up a wide range of amazing functionalities.
As a hard SQLite user, I've been missing this for a long time...
Thank you!

 

[Claudio Oliveira]

Hey @tchart

SQL.InitializeSQLite(File.DirApp,"Test.sqlite?enable_load_extension=true",True)

This doesn't work for an existing database file. It works only when the CreateIfNecessary parameter is true.
Java.io assumes that "?enable_load_extension=true" is part of the filename.

The line

mSQL.InitializeSQLite(File.DirApp, "FPROPS.DB?enable_load_extension=true", False)

Causes this error

java.io.FileNotFoundException: D:\Documentos\Projetos Java\FILE PROPS\Objects\FPROPS.DB?enable_load_extension=true

On the other hand, the connection string method works perfectly.