skip to Main Content

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 2817n at StackExchange.Redis.RedisBase.ExecuteSync[T](Message message, ResultProcessor1 processor, ServerEndPoint server) in
//src/StackExchange.Redis/RedisBase.cs:line 54n at
StackExchange.Redis.RedisDatabase.Execute(String command,
ICollection`1 args, CommandFlags flags) in
/
/src/StackExchange.Redis/RedisDatabase.cs:line 1204n at
StackExchange.Redis.RedisDatabase.Execute(String command, Object[] args) in /_/src/StackExchange.Redis/RedisDatabase.cs:line 1200n 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 109n at
StackExchange.Redis.CommandMap.GetBytes(String command) in >//src/StackExchange.Redis/CommandMap.cs:line 181n at >StackExchange.Redis.RedisDatabase.ExecuteMessage..ctor(CommandMap map, >Int32 db, CommandFlags flags, String command, ICollection1 args) in >/_/src/StackExchange.Redis/RedisDatabase.cs:line 3720n at >StackExchange.Redis.RedisDatabase.Execute(String command, ICollection1 >args, CommandFlags flags) in >//src/StackExchange.Redis/RedisDatabase.cs:line 1203n at >StackExchange.Redis.RedisDatabase.Execute(String command, Object[] args) >in //src/StackExchange.Redis/RedisDatabase.cs:line 1200n 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

2

Answers


  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.

    Login or Signup to reply.
  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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search