Results 1 to 14 of 14

Thread: How to enable REGEX for RC6 SQLite?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Aug 2016
    Posts
    112

    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.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: How to enable REGEX for RC6 SQLite?

    because Regex in SQLite is a application-defined function, which DB Browser has implemented
    https://www.sqlite.org/lang_expr.html
    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)".
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3
    Frenzied Member
    Join Date
    Aug 2020
    Posts
    1,421

    Re: How to enable REGEX for RC6 SQLite?

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

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: How to enable REGEX for RC6 SQLite?

    Quote Originally Posted by SearchingDataOnly View Post
    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"
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    Frenzied Member
    Join Date
    Aug 2020
    Posts
    1,421

    Re: How to enable REGEX for RC6 SQLite?

    Quote Originally Posted by Zvoni View Post
    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.

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: How to enable REGEX for RC6 SQLite?

    Correction: Not compile sqlite.dll, but compile the regex.dll-extension yourself, and then load the extension
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: How to enable REGEX for RC6 SQLite?

    Quote Originally Posted by SearchingDataOnly View Post
    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8
    Frenzied Member
    Join Date
    Aug 2020
    Posts
    1,421

    Re: How to enable REGEX for RC6 SQLite?

    Quote Originally Posted by Zvoni View Post
    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.

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: How to enable REGEX for RC6 SQLite?

    Quote Originally Posted by SearchingDataOnly View Post
    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
    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
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  10. #10
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: How to enable REGEX for RC6 SQLite?

    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
    Last edited by Schmidt; Dec 7th, 2022 at 07:22 AM.

  11. #11
    Addicted Member
    Join Date
    Apr 2017
    Location
    India
    Posts
    234

    Re: How to enable REGEX for RC6 SQLite?

    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.

  12. #12
    Addicted Member
    Join Date
    Apr 2017
    Location
    India
    Posts
    234

    Re: How to enable REGEX for RC6 SQLite?

    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.

  13. #13
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: How to enable REGEX for RC6 SQLite?

    Quote Originally Posted by softv View Post
    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
    Last edited by Schmidt; Dec 16th, 2023 at 04:54 AM.

  14. #14
    Addicted Member
    Join Date
    Apr 2017
    Location
    India
    Posts
    234

    Re: How to enable REGEX for RC6 SQLite?

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width