B4J Question sQLite Function Extension Loading

[rgarnett1955]

Hi,

There are a lot of useful functions written in c and compiled as DLL's for sQLite. One example of these are nalgeon / sqlean hosted on Github:

https://github.com/nalgeon/sqlean.

This includes:

• crypto: hashing, encoding and decoding data
• define: user-defined functions and dynamic sql
• fileio: file input output
• fuzzy: fuzzy string matching and phonetics
• ipaddr: IP address manipulation
• math: math functions
• regexp: regular expressions
• stats: math statistics
• text: string functions
• unicode: Unicode support
• uuid: Universally Unique IDentifiers
• vsv: CSV files as virtual tables

To use these functions you must load them with the database using either the sqlite API: viz:

int sqlite3_load_extension(
  sqlite3 *db,          /* Load the extension into this database connection */
  const char *zFile,    /* Name of the shared library containing extension */
  const char *zProc,    /* Entry point.  Derived from zFile if 0 */
  char **pzErrMsg       /* Put error message here if not 0 */
);

A query can also be used to load extensions using the sql functions"

load_extension(X)
load_extension(X,Y)

Built-In Scalar SQL Functions

private Sub loadStatsExtension(db As SQL) As Boolean

    Dim rset As ResultSet
    
    Dim qryStr As String = $"select load_extension('C:\Eng\RFChannelMonitorDB\sQLiteVTab\sqLitePivotVTab\bin\Debug\stats.dll');"$
    
    Try
        rset = db.ExecQuery(qryStr)
    Catch
        Log("clkDatabase Line 32" & LastException)
        Return False
    End Try
    Return True
End Sub

This will not work in b4x because for security reasons the loading of extensions is disabled by default for sQlite databases. Running the code produces the following error:

java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (not authorized)

To overcome this the following C API command must be executed:

int sqlite3_enable_load_extension(sqlite3 *db, int onoff);

Enabling of extension loading can also be done when sqLite is compiled using a compiler directive option: SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION.

Enable Or Disable Extension Loading

The question I have is how can I access the sqlite3_enable_load_extension function through B4x when using sqlite-jdbc.

Just to clarify I am not writing about stored sql procedures here I'm writing about functions that can be called in any sqLite query.

I have tried to find documentation on sqlite-jdbc in order to use some java code in B4j, but I was unable to work anything out. Perhaps there is a clever java/b4x person out there who could help.

If this could be done it unlocks a whole host of very useful functions, such as stddev, median, percentiles, math functions like sine, cos and cryptography.

Of course all these functions can be implemented using B4x code on the results of sQlite queries, but this is hardly an efficient method.

regards
Rob