SQLite / System.Data.SQLite / User defined function registered with other name than "REGEXP" results in an exception

Question

Following the examples in StackOverflow Create User Defined Functions and sqlite net sqlitefunction not working, I defined a UDF for testing purposes:

[SQLiteFunction(Name = "REGEXP", Arguments = 2, FuncType = FunctionType.Scalar)]
public class FirstUdf : SQLiteFunction
{
   public override object Invoke(object[] args)
   {
      Messages.Notify($"{this.GetType().Name}:Invoke");
      return true;
   }
}

I try to execute a DataReader with the following function:

private void PerformUdfTest(string funcName)
{
   var commandText = $"select Node.* from Node where Node.Labels {funcName} 'testvalue'";
   Messages.Notify($"SQL command: '{commandText}'");

   using (var command = _connection.CreateCommand())
   {
      command.CommandText = commandText;

      using (var reader = command.ExecuteReader())
      {
         while (reader.Read())
            ; 
      }
   }
}

where funcName is the registration name given in the SQLiteFunctionAttribute above.

If the UDF is registered as "REGEXP", everything works and the callback is executed.

But, if I change the registration name to anything else, e.g.

[SQLiteFunction(Name = "MyUdf", Arguments = 2, FuncType = FunctionType.Scalar)]

I get an Exception:

System.Data.SQLite.SQLiteException (0x800007BF): SQL logic error
near "MyUdf": syntax error
   at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   at SQLiteUdf.QueryTest.PerformUdfTest(String funcName)
   at SQLiteUdf.QueryTest.PerformQueries()

System.Data.SQLite Version is 1.0.116.0, .NET Version 4.8.

What am I missing here?

Yes. The content of the table attribute and the value given in the statement. Both can be evaluated in the well-working case. In the second case, the statement is not executed at all. — Areb, Jul 01 '22 at 15:03
Yes, but in this query you are using the `REGEXP` operator syntax instead of the function syntax. See the [docs](https://sqlite.org/lang_expr.html#the_like_glob_regexp_match_and_extract_operators). — Mark Benningfield, Jul 01 '22 at 15:38
That was far too simple - I must have been operationally blind. Thanks to Mark and Shawn. — Areb, Jul 01 '22 at 16:36

score 2 · Answer 1 · answered Jul 01 '22 at 16:44

2

The statement simply has to be corrected:

var commandText = $"select Node.* from Node where {funcName} (Node.Labels, 'testvalue')";

Thanks to Mark and Shawn.

answered Jul 01 '22 at 16:44

Areb

31
3

SQLite / System.Data.SQLite / User defined function registered with other name than "REGEXP" results in an exception

1 Answers1