RC6 Sqlite Whether to enable full-text search engine version 5

[saddemon]

Hi buddies

Now there is no open permission, how can I have the corresponding permission

SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION
This option is used to enable or disable the sqlite3_load_extension() interface independently of the load_extension() SQL function. The sqlite3_enable_load_extension() API enables or disables both the C-API sqlite3_load_extension() and the SQL function load_extension(). There should be two additional arguments. When the first argument to this interface is 1, then only the C-API is enabled and the SQL function remains disabled. If the first argument to this interface is 0, then both the C-API and the SQL function are disabled. If the first argument is -1, then no changes are made to state of either the C-API or the SQL function. The second parameter is a pointer to an integer into which is written 0 or 1 to indicate whether sqlite3_load_extension() interface is disabled or enabled following this call. The second parameter may be a NULL pointer, in which case the new setting is not reported back.

Thanks for a guiding hand.

[Schmidt]

I'm not exactly sure, what you mean (or need) -
but in case you want to use FTS5 - this extension is compiled in - and enabled by default.

Private Sub Form_Load()
  Dim Cnn As cConnection
  Set Cnn = New_c.Connection(, DBCreateInMemory)
      Cnn.Execute "Create Virtual Table Email Using fts5(sender, subject, body)"
      
      Cnn.ExecCmd "Insert Into Email Values(?,?,?)", "A", "B", "C"
      Cnn.ExecCmd "Insert Into Email Values(?,?,?)", "X", "Y", "Z"
       
  Debug.Print Cnn.GetRs("Select * From Email(?)", "A OR X").RecordCount  'should give 2
  Debug.Print Cnn.GetRs("Select * From Email(?)", "A AND B").RecordCount 'should give 1
  Debug.Print Cnn.GetRs("Select * From Email(?)", "A AND X").RecordCount 'should give 0
End Sub

HTH

Olaf

[saddemon]

Attachment 186288
Hello
RC6 cannot load sqlite third-party components by the load method, which requires permission
Thank you very much!

[wqweto]

Attachment 186288
Hello
RC6 cannot load sqlite third-party components by the load method, which requires permission
Thank you very much!

RC6 cannot possibly load x64 extensions. Check you extension DLL bitness.

cheers,
</wqw>

[saddemon]

Ensure that the dynamic libraries loaded are 32-bit

   
Set PUB_TmpDB = New_c.Connection(, DBCreateInMemory)
PUB_TmpDB.Execute "select load_extension('" & App.Path & "\plugins\simple.dll');"

Error msg:Run-time error '-2147221504': Cannot execute SQL-Statement: not authorized.

[Schmidt]

Ensure that the dynamic libraries loaded are 32-bit

   
Set PUB_TmpDB = New_c.Connection(, DBCreateInMemory)
PUB_TmpDB.Execute "select load_extension('" & App.Path & "\plugins\simple.dll');"

Error msg:Run-time error '-2147221504': Cannot execute SQL-Statement: not authorized.

IIRC, extension-loading via SQL-statement is disabled in my RC6-paired compilation of the SQLite-lib...
(hence the "not authorized" message).

This is done for security-reasons, and because I've compiled a lot of the "official" extensions already into the cairo_sqlite-binary by default.

Nevertheless, you can override this (temporarily) via an exported "SQLite-Flat-API",
which you just have to declare "manually" - as in the following example-code:

Option Explicit

Private Declare Function sqlite3_enable_load_extension Lib "cairo_sqlite" (ByVal DBHdl As Long, ByVal OnOff As Long) As Long

Private Sub Form_Load()
  Dim PUB_TmpDB As cConnection
  Set PUB_TmpDB = New_c.Connection(, DBCreateInMemory)
  
  sqlite3_enable_load_extension PUB_TmpDB.DBHdl, 1 'enable extension-loading via SQL-statement temporarily
  
      PUB_TmpDB.Execute "select load_extension('" & App.Path & "\plugins\simple.dll');"
  
  sqlite3_enable_load_extension PUB_TmpDB.DBHdl, 0 'and disable it again, after loading
End Sub

HTH

P.S.: This still leaves the question, what kind of extension you're trying to load?
... (as said, the cairo_sqlite.dll-companion might already contain it, like e.g. the FTS5-, Zip-, CSV-extensions)

Olaf

[saddemon]

Thank you very much for your help!

This is the extension I want to use, an extension that separates Chinese phrases into words
P.S.:Here is the github link for this extension
https://github.com/wangfenjin/simple

[talatoncu]

IIRC, extension-loading via SQL-statement is disabled in my RC6-paired compilation of the SQLite-lib...
(hence the "not authorized" message).

This is done for security-reasons, and because I've compiled a lot of the "official" extensions already into the cairo_sqlite-binary by default.

Nevertheless, you can override this (temporarily) via an exported "SQLite-Flat-API",
which you just have to declare "manually" - as in the following example-code:

Option Explicit

Private Declare Function sqlite3_enable_load_extension Lib "cairo_sqlite" (ByVal DBHdl As Long, ByVal OnOff As Long) As Long

Private Sub Form_Load()
  Dim PUB_TmpDB As cConnection
  Set PUB_TmpDB = New_c.Connection(, DBCreateInMemory)
  
  sqlite3_enable_load_extension PUB_TmpDB.DBHdl, 1 'enable extension-loading via SQL-statement temporarily
  
      PUB_TmpDB.Execute "select load_extension('" & App.Path & "\plugins\simple.dll');"
  
  sqlite3_enable_load_extension PUB_TmpDB.DBHdl, 0 'and disable it again, after loading
End Sub

HTH

P.S.: This still leaves the question, what kind of extension you're trying to load?
... (as said, the cairo_sqlite.dll-companion might already contain it, like e.g. the FTS5-, Zip-, CSV-extensions)

Olaf

Dear Olaf,

I want to use the regexp function from this extension:

https://github.com/asg017/sqlite-regex/tree/main

When I want to load this extension as you have explained

db.Execute "select load_extension('j:\regex0.dll')"

I got the error,

"The specified module could not be found"

I have downloaded dll (sqlite-regex-v0.2.3-loadable-windows-x86_64.zip)from this page:

https://github.com/asg017/sqlite-regex/releases

Thank you very much for ypur help.

Regards

Talat Oncu

[Schmidt]

Dear Olaf,

I want to use the regexp function from this extension:

https://github.com/asg017/sqlite-regex/tree/main

When I want to load this extension as you have explained

db.Execute "select load_extension('j:\regex0.dll')"

I got the error,

"The specified module could not be found"

I have downloaded dll (sqlite-regex-v0.2.3-loadable-windows-x86_64.zip)from this page:

https://github.com/asg017/sqlite-regex/releases

Thank you very much for ypur help.

Regards

Talat Oncu

You need to specify 32Bit-extension-dlls in your select-call...
(your ...windows-x86_64.zip hints, that you've downloaded the 64bit-ones)...

Olaf

[Resurrected]

Private Sub Form_Load()
  Dim Cnn As cConnection
  Set Cnn = New_c.Connection(, DBCreateInMemory)
      Cnn.Execute "Create Virtual Table Email Using fts5(sender, subject, body, tokenize = 'porter unicode61')"
      
      Cnn.ExecCmd "Insert Into Email Values(?,?,?)", "A", "B", "C"
      Cnn.ExecCmd "Insert Into Email Values(?,?,?)", "X", "Y", "Z"
       
  Debug.Print Cnn.GetRs("Select * From Email(?)", "A OR X").RecordCount  'should give 2
  Debug.Print Cnn.GetRs("Select * From Email(?)", "A AND B").RecordCount 'should give 1
  Debug.Print Cnn.GetRs("Select * From Email(?)", "A AND X").RecordCount 'should give 0
End Sub

This: Debug.Print Cnn.GetRs("Select * From Email(?)", "A OR X").RecordCount 'should give 2
Will causes the app to freeze.

I have SQLiteBrowser on the same DB file (tokenize = 'porter unicode61'), it will not.

By the way, it has been many years since last RC6 update.

How are you recently?

[jpbro]

/nevermind - missed the part about the tokenizer being the problem, that appears to be the case/

[jpbro]

Interestingly, vbRichClient5 doesn't freeze with this code (but RC6 freezes):

Option Explicit

Private Sub Form_Load()
  Dim Cnn As cConnection
  Set Cnn = New_c.Connection(, DBCreateInMemory)
      Cnn.Execute "Create Virtual Table Email Using fts5(sender, subject, body, tokenize = 'porter unicode61')"
      
      Cnn.Execute "Insert Into Email Values('A', 'B', 'C')"
      Cnn.Execute "Insert Into Email Values('X', 'Y', 'Z')"
       
  Debug.Print Cnn.OpenRecordset("Select * From Email('A OR X')").RecordCount  'should give 2
End Sub

So looks like either an RC6 issue, or an issue with the SQLite version (3.39.2) used by RC6.

[softv]

Just a while ago, I also faced the same issue (freezing while querying an fts5 table). So, searched in the net on "vbforums olaf RC6 fts5 and match" and was fortunate to spot this thread's link. Hopefully, dear Olaf, you will be able to provide us a solution when you find time, amidst your hectic schedules. Thanks in advance. Actually, before this issue, it so happened that I faced the issue of not being able to load an extension too. I searched in the net in my usual way but could not find any thread but this thread answers that question too. So, I am doubly fortunate, so to say.

Actually, before seeing this thread, started reading some stuff in the net and understood that the extension loading has to be done the way you have described, olaf. So, tried some code of my own but never could come up with a working code. Your ready-made code in this thread is a boon, that way.

Forever indebted to your monumental services, dear Olaf. Forever. Thanks a TON.

Note: I just quickly checked out but could not succeed in loading the 32-bit regexp or unicode or math dll (of sqlean library) using the code given by you, Olaf. May be I am doing some mistake OR may be these particular DLLs cannot be loaded, as such, due to some reasons. I await your reply.

Kind Regards.

[softv]

// Interestingly, vbRichClient5 doesn't freeze with this code (but RC6 freezes): //

Thanks a LOT for this valuable info (and very many other such info and code, which you always provide, in various threads), dear Jpbro.

Kind Regards.

[softv]

Dear Olaf,

Success. . With RC6 itself I was able to use fts5 (with tokenizer too) from inside VB6. So, I am able to experience extremely fast searching from inside my free app too. I am using 'DB Browser' freeware for my databases' management. Yesterday night, reading the message (on tokenizer) of JpBro again, I thought 'Okay, why not create yet another fts table without tokenizer?'. I did the same and searching on that new fts5 table worked inside my app. Later, added tokenizer and created another fts5 table. Searching on that also worked within my free app. So, as of now, facing no issues. So far so good.

Deleted the fts5 tables created earlier and checked with the new fts5 tables. All working well from inside my app.

Note-1: Did check whether fts5 search on the earlier tables work after creating the new fts5 tables. They did not. It resulted in freezing as experienced earlier.
Note-2: From inside 'DB Browser', searching on new and earlier fts5 tables worked always. Why the earlier fts5 tables alone caused the freezing problem with RC6, I don't know.

Thanks a TON, as always. God Bless you! God Bless all!

Kind Regards.