How to enable REGEX for RC6 SQLite?

I can use REGEX in some softwares such as DB Browser For SQLite.

The SQL looks like this:

Code:

---

... WHERE KEYWORD REGEXP '^[a-z\s]+$'

---

But it will not work when I am working with RC6.

because Regex in SQLite is a application-defined function, which DB Browser has implemented
https://www.sqlite.org/lang_expr.html

Quote:

---

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL function named "regexp" is added at run-time, then the "X REGEXP Y" operator will be implemented as a call to "regexp(Y,X)".

---

Olaf has said he is developing RegEx for RC6. Don't know how far it has progressed now.

Quote:

---

Originally Posted by SearchingDataOnly

Olaf has said he is developing RegEx for RC6. Don't know how far it has progressed now.

---

Or compile the sqlite.dll yourself enabling the RegEx-Extension.

If you download the source-code for sqlite, you'll find a folder "misc" in the folder-tree beneath "ext", and there is "regexp.c"

Quote:

---

Originally Posted by Zvoni

Or compile the sqlite.dll yourself enabling the RegEx-Extension.

If you download the source-code for sqlite, you'll find a folder "misc" in the folder-tree beneath "ext", and there is "regexp.c"

---

What Olaf is talking about RegEx doesn't seem to be just for sqlite-db. I guess he wanted to develop a generic RegEx engine that could replace JS.RegEx, which is exactly what many of us were expecting.

Correction: Not compile sqlite.dll, but compile the regex.dll-extension yourself, and then load the extension

Quote:

---

Originally Posted by SearchingDataOnly

What Olaf is talking about RegEx doesn't seem to be just for sqlite-db. I guess he wanted to develop a generic RegEx engine that could replace JS.RegEx, which is exactly what many of us were expecting.

---

Might be, but wouldn't have anything to do with OP's question.
OP's question is about using the REGEXP-Operator/Function within SQL-Statements.
And in DB Browser's case i'd guess they compiled the extension and linked it statically into DB Browser itself (not into sqlite3.dll!), but "load" it into the engine on startup

Quote:

---

Originally Posted by Zvoni

Might be, but wouldn't have anything to do with OP's question.
OP's question is about using the REGEXP-Operator/Function within SQL-Statements.
And in DB Browser's case i'd guess they compiled the extension and linked it statically into DB Browser itself (not into sqlite3.dll!), but "load" it into the engine on startup

---

I seem to remember that RC6 could use VB6 functions as custom functions for sqlite.

Quote:

---

Originally Posted by SearchingDataOnly

I seem to remember that RC6 could use VB6 functions as custom functions for sqlite.

---

No idea, since i never used RC6, but wouldn't surprise me, since SQLite offers to use custom Functions --> https://www.sqlite.org/appfunc.html

Quote:

---

Applications that use SQLite can define custom SQL functions that call back into application code to compute their results. The custom SQL function implementations can be embedded in the application code itself, or can be loadable extensions.

---

since SQLite is written in C, it's probable that the "off-the-shelf" dll's are compile with cdecl.
I seem to remember Olaf mentioning compiling SQLite with stdcall for RichClient, so AddressOf should work

The cRegExp-Class (as an SQLite-independent helper-class) is not (yet) contained in the recent RC6-version.
Also SQLites regexp-C-module is not compiled into the cairo_sqlite-binary.

What *is* supported though (for a long time already) - as SearchingDataOnly pointed out above,
is an interface to implement ones own "UDFs" (user-defined-functions) in a little Project-Private Class.

If you (in addition to the "RC6"-ref) include an additional reference into your project ("MS VBScript RegExp 5.5"),
and put the following into a Class, named: cRegExpUDF

Code:

---

Option Explicit

Implements RC6.IFunction

Private RX As RegExp

Private Sub Class_Initialize()
  Set RX = New RegExp 'create a RegExp-instance
      RX.Global = True: RX.MultiLine = True 'set its default-state to "/gm"
End Sub

Private Property Get iFunction_DefinedNames() As String
  iFunction_DefinedNames = "RegExp" 'tell SQLite, which functionname we are using
End Property

Private Sub iFunction_Callback(ByVal ZeroBasedNameIndex As Long, ByVal ParamCount As Long, UDF As cUDFMethods)
On Error GoTo 1
  If ParamCount <> 2 Then UDF.SetResultError "RegExp needs two parameters!": Exit Sub
 
  If UDF.GetType(2) = SQLite_NULL Then 'if the second param (the Field or Expression to search) is Null...
    UDF.SetResultNull '...then return a Null here as well
   
  Else 'normal case (no further sanity-checks)
    Dim Pat As String: Pat = UDF.GetText(1) 'read the first param (which contains the RegExp-Pattern)
    If Left$(Pat, 4) = "(?i)" Then 'check for a leading "ignore-case" marker
        RX.IgnoreCase = True:  RX.Pattern = Mid$(Pat, 5) 'if found, then remove it from the pattern (VBScript.RegEx doesn't understand it)
    Else
        RX.IgnoreCase = False: RX.Pattern = Pat
    End If
    UDF.SetResultInt32 RX.Test(UDF.GetText(2))
  End If
 
1 If Err Then UDF.SetResultError "Reg-Expression is not resolvable (wrong pattern?)"
End Sub

---

... then the following Form-Code should produce decent results:

Code:

---

Option Explicit

Private Sub Form_Load()
  Dim Cnn As cConnection, Rs As cRecordset
  Set Cnn = New_c.Connection(, DBCreateInMemory)
      Cnn.AddUserDefinedFunction New cRegExpUDF '<- this way we make the new UDF "known" to SQLite
     
      Cnn.Execute "Create Table T(ID Integer Primary Key, Fld1 Text)"
      Cnn.ExecCmd "Insert Into T(Fld1) Values(?)", "text with an Uppercase letter in it"
      Cnn.ExecCmd "Insert Into T(Fld1) Values(?)", "text with only lowercase letters"
      Cnn.ExecCmd "Insert Into T(Fld1) Values(?)", "another lowercase text"
 
  'first test without a leading "ignore-case-marker" in the regexp-pattern
  Set Rs = Cnn.GetRs("Select group_concat(ID) From T Where Fld1 RegExp ?", "^[a-z\s]+$")
  Debug.Print "Matching ID-List: (" & Rs(0).Value & ")"
 
  'second test will force "ignore-case" by preceding the pattern with "(?i)"
  Set Rs = Cnn.GetRs("Select group_concat(ID) From T Where Fld1 RegExp ?", "(?i)^[a-z\s]+$")
  Debug.Print "Matching ID-List (ignore-case): (" & Rs(0).Value & ")"
End Sub

---

HTH

Olaf

Thank you so so so much, my dear Olaf. The society has to remain ever indebted to you, I humbly feel.

Well, if I need to add more UDFs (similar to RegExp) to the database, have I to create classes for each one of them? Or, is it possible to encapsulate all UDFs in one class itself? If so, how to do it? Kindly let me know if and when your time permits.

Thanks a ton, once again.

Kind Regards.

Dear Olaf,

As in the case of RegExp, the UDFs have to be necessarily functions residing in a DLL only? Normal VB functions cannot be used as UDFs? If normal VB functions can also be used as UDFs, then how to achieve the same?

Because, I tried the following just a while ago:
--
1. Created my own class (cMyUDF1) with my own function (MyUDF1) inside it.

2. I referenced the 'MyUDF1' function inside the 'iFunction_Callback' function as "UDF.SetResultInt32 MyUDF1".

3. Function_DefinedNames = "MyUDF1" is present inside 'iFunction_DefinedNames' function.

4. I have the "Cnn.AddUserDefinedFunction New cMyUDF1" statement in my form
--

The above has not worked so far. The function 'MyUDF1' is not called.

I am not doing it correctly at all, I think. Or perhaps, the UDFs referenced have to be residing in a DLL only.

In either case, kindly guide/educate me please, when your time permits.

Tons of Thanks, as always.

Kind Regards.

Quote:

---

Originally Posted by softv

As in the case of RegExp, the UDFs have to be necessarily functions residing in a DLL only?
Normal VB functions cannot be used as UDFs?
If normal VB functions can also be used as UDFs, then how to achieve the same?

---

No.
No.
This way:

Code:

---

Option Explicit

Implements IFunction

Private Sub iFunction_Callback(ByVal ZeroBasedNameIndex As Long, ByVal ParamCount As Long, UDF As cUDFMethods)
  Select Case ZeroBasedNameIndex
    Case 0 'AddSomething (the first entry in the comma-separated list in iFunction_DefinedNames)
      If ParamCount = 2 Then 'the "normal case"
        UDF.SetResultDouble UDF.GetDouble(1) + UDF.GetDouble(2) 'parameter-indices are one-based
      Else 'an example, how to raise an error, if something cannot be handled by your function
        UDF.SetResultError "AddSomething: we need two parameters!"
      End If
     
    Case 1 'StrReverse (the second entry in the comma-separated list in iFunction_DefinedNames)
      If UDF.GetType(1) = SQLite_NULL Then 'another error-handling-example
        'here we don't raise an error...
        'instead we "delegate" the current param-content to the sqlite-function-result
        UDF.SetResultToParam 1
      Else 'process the parameter and deliver the result
        'the Get-Functions of the UDF-Object have builtin Type-Conversion,
        'so in this case .GetText(1) autoconverts the content of the
        'field- or expression (given in parameter 1) always into a VB-UnicodeString,
        'regardless it the Field contains an Integer, a Double, a Date or a BLOB
        UDF.SetResultText StrReverse$(UDF.GetText(1))
      End If
  End Select
End Sub

'here we define two function-names as a comma-separated "list"
Private Property Get iFunction_DefinedNames() As String
  iFunction_DefinedNames = "AddSomething,StrReverse"
End Property

---

The above demo-code was taken straight from the Class: cMyFunctions
(from the SQLite-NWind-Demo in the official SQLite-Tutorial )

Olaf

Thanks a TON, Olaf, for your very quick reply, as usual.

That was fantastic.

Sincere apologies for not looking into the demo projects already. I do have a folder already with the "SQLiteDemos" extracted in them. But, I have so far seen the "_Hello World" example only. :)
I had planned to see the 'Nwind-Demo', anyway, as soon as I can, because I am very much interested to understand and implement FTS. :)

God Bless you, olaf. God Bless all.

Kind Regards.