In C# how to provide parameters to sql statements using StackExchange.Redis Execute method using Redis with Redisql module

Question

There is very limited information regarding the usage of Redisql module with C#.

I am using StackExchange.Redis nuget package v2.2.4 to connect Redis v5.0.7 with Redisql module installed. I am developing .NET 5 C# Application that connects and create a database and a table with predefined values.

Below is the code block that works fine and as expected.

    ConnectionMultiplexer muxer = ConnectionMultiplexer.Connect("127.0.0.1:6380");
    IDatabase conn = muxer.GetDatabase();

    conn.Execute("DEL", "DB");

    conn.Execute("REDISQL.CREATE_DB", "DB");
    conn.Execute("REDISQL.EXEC", "DB", "CREATE TABLE TABLE1(A INT, B TEXT);");
    conn.Execute("REDISQL.EXEC", "DB" ,"INSERT INTO TABLE1 VALUES(1, 'Value1');");
    conn.Execute("REDISQL.EXEC", "DB" ,"INSERT INTO TABLE1 VALUES(2, 'Value2');");
    var res = conn.Execute("REDISQL.EXEC", "DB", "SELECT * FROM TABLE1");

But what i want to do is to execute insert statements with db parameters instead of providing the values directly in the sql statements. As there is literally no examples or documentations on that I cannot find a way to do that.

I tried to rewrite the insert statement as below but it gives and error

conn.Execute("REDISQL.EXEC", "DB", "INSERT INTO TABLE1 VALUES(?1, ?2);", 1, "Value1");

StackExchange.Redis.RedisServerException: "Wrong number of arguments, it accepts 3, you provide 5" at StackExchange.Redis.ConnectionMultiplexer.ExecuteSyncImpl[T](Message message, ResultProcessor1 processor, ServerEndPoint server) in /_/src/StackExchange.Redis/ConnectionMultiplexer.cs:line 2817\n at StackExchange.Redis.RedisBase.ExecuteSync[T](Message message, ResultProcessor1 processor, ServerEndPoint server) in //src/StackExchange.Redis/RedisBase.cs:line 54\n at StackExchange.Redis.RedisDatabase.Execute(String command, ICollection`1 args, CommandFlags flags) in //src/StackExchange.Redis/RedisDatabase.cs:line 1204\n at StackExchange.Redis.RedisDatabase.Execute(String command, Object[] args) in /_/src/StackExchange.Redis/RedisDatabase.cs:line 1200\n at deneme.Program.Main(String[] args) in /Users/serhatonal/Projects/deneme/deneme/Program.cs:23

After that I changed the script as follows

        ConnectionMultiplexer muxer = ConnectionMultiplexer.Connect("127.0.0.1:6380");
        IDatabase conn = muxer.GetDatabase();

        conn.Execute("DEL", "DB");

        conn.Execute("REDISQL.CREATE_DB", "DB");
        conn.Execute("REDISQL.EXEC", "DB", "CREATE TABLE TABLE1(A INT, B TEXT);");
        
        conn.Execute("REDISQL.CREATE_STATEMENT", "DB", "INSERTINTOTABLE1STMT", "INSERT INTO TABLE1 VALUES(?1,?2)");

        conn.Execute("REDISQL.EXEC_STATEMENT", "DB", "INSERTINTOTABLE1STMT", 1, "Value1" );
        conn.Execute("REDISQL.EXEC_STATEMENT", "DB", "INSERTINTOTABLE1STMT", 2, "Value2");

        var res = conn.Execute("REDISQL.EXEC", "DB", "SELECT * FROM TABLE1");

It gives below error while executing REDISQL.CREATE_STATEMENT line as described in the documentation https://redisql.redbeardlab.com/references/#redisqlexec_statement

System.ArgumentOutOfRangeException: "Specified argument was out of the range of valid values. (Parameter 'Command 'REDISQL.CREATE_STATEMENT' exceeds library limit of 23 bytes')" at StackExchange.Redis.CommandBytes..ctor(String value) in //src/StackExchange.Redis/CommandBytes.cs:line 109\n at StackExchange.Redis.CommandMap.GetBytes(String command) in >//src/StackExchange.Redis/CommandMap.cs:line 181\n at >StackExchange.Redis.RedisDatabase.ExecuteMessage..ctor(CommandMap map, >Int32 db, CommandFlags flags, String command, ICollection1 args) in >/_/src/StackExchange.Redis/RedisDatabase.cs:line 3720\n at >StackExchange.Redis.RedisDatabase.Execute(String command, ICollection1 >args, CommandFlags flags) in >//src/StackExchange.Redis/RedisDatabase.cs:line 1203\n at >StackExchange.Redis.RedisDatabase.Execute(String command, Object[] args) >in //src/StackExchange.Redis/RedisDatabase.cs:line 1200\n at >deneme.Program.Main(String[] args) in >/Users/serhatonal/Projects/deneme/deneme/Program.cs:23

In our realtime scenario we have many sqls that uses multiple type parameters so it is not elegant to continue with sql including parameters as strings.

Any help is appreciated

Answer 1

From the error message, it sounds like redisql doesn't accept parameters, so: you'll probably need to inline the values yourself, taking care to avoid SQL injection. That's a topic for the module vendor.

Re the 23-byte limit: interesting that this is the first time I've seen this overflown, but: yes, we can increase that. Sorry.

Answer 2

Simone from RediSQL.

So, you are right about pretty much anything.

RediSQL V1 does not support passing arguments to the EXEC, it was my mistake, and it is a bad design. Fortunately we moved on with RediSQL V2 which support passing arguments to the EXECs.

Your solution to create a statement is the correct one. Statements are suppose to be used when you want to repeat, multiple times, the same query, each time with different arguments.

So you are definitely on a good track.

The second problem is due to StackExchange own limit. Here there is no much we do about.

They are storing the command in a 3 ulongs for fast memory allocation: https://github.com/StackExchange/StackExchange.Redis/blob/main/src/StackExchange.Redis/CommandBytes.cs#L28

It turns out to be (3 * 8) - 1 = 23 bytes and REDISQL.CREATE_STATEMENT is 24 bytes. It does not fit.

Solution to this problem would be to rename the REDISQL.CREATE_STATEMENT into something like REDISQL.NEW_STATEMENT, you can rename Redis commands adding something like this in your config:

rename-command REDISQL.CREATE_STATEMENT REDISQL.NEW_STATEMENT

I understand it is a cumbersome process, but it is the only solution I see.

I would really suggest moving to RediSQL V2 or zeeSQL (https://zeesql.com and documentation in https://doc.zeesql.com).

It would have made these two problem disappears.

Sorry for not answering earlier, but I didn't receive the notification. I believe I fix them now.