Results 1 to 10 of 10

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
    3,552

    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
    Fanatic Member
    Join Date
    Aug 2020
    Posts
    944

    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
    3,552

    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
    Fanatic Member
    Join Date
    Aug 2020
    Posts
    944

    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
    3,552

    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
    3,552

    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
    Fanatic Member
    Join Date
    Aug 2020
    Posts
    944

    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
    3,552

    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
    6,350

    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.

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