Is there a way to get the results with the same data type of the column with ServiceStack.Redis using Redisql package?

Question

I want to connect to Redis with ServiceStack.Redis package with c# with below statement.

        var redisManager = new RedisManagerPool("127.0.0.1:6380");
        var redis = redisManager.GetClient();

        redis.Custom("DEL", "DB");

        redis.Custom("REDISQL.CREATE_DB", "DB");
        redis.Custom("REDISQL.EXEC", "DB", "CREATE TABLE TABLE1(A INT, B TEXT);");
        redis.Custom("REDISQL.CREATE_STATEMENT", "DB", "INSERTINTOTABLE1STMT", "INSERT INTO TABLE1 VALUES(?1,?2)");
        redis.Custom("REDISQL.EXEC_STATEMENT", "DB", "INSERTINTOTABLE1STMT", 1, "Value1");
        redis.Custom("REDISQL.EXEC_STATEMENT", "DB", "INSERTINTOTABLE1STMT", 2, "Value2");
        var res = redis.Custom("REDISQL.EXEC", "DB", "SELECT * FROM TABLE1");

Queries are executed fine. Column 'A' of Table1 is defined as an integer. But final command that retrieves all rows from TABLE1 return data of 'A' column as Text because result is RedisText type.

Is there a way to get the results with the same data type of the column ?

Answer 1

127.0.0.1:6379> REDISQL.V2.CREATE_DB DB
1) 1) "OK"
127.0.0.1:6379> REDISQL.EXEC DB COMMAND "CREATE TABLE TABLE1(A INT, B TEXT);"
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> REDISQL.EXEC DB COMMAND "INSERT INTO TABLE1 VALUES (1,'A'),(2,'B');"
1) 1) "DONE"
2) 1) (integer) 2
127.0.0.1:6379> REDISQL.EXEC DB COMMAND "SELECT  * FROM TABLE1;"
1) 1) "RESULT"
2) 1) "A"
   2) "B"
3) 1) "INT"
   2) "TEXT"
4) 1) (integer) 1
   2) "A"
5) 1) (integer) 2
   2) "B"
127.0.0.1:6379> REDISQL.EXEC DB COMMAND "SELECT  * FROM TABLE1;" json
"{\"rows\":[{\"A\":1,\"B\":\"A\"},{\"A\":2,\"B\":\"B\"}],\"number_of_rows\":2,\"columns\":{\"A\":\"INT\",\"B\":\"TEXT\"}}"

So as you mention, the problem is ServiceStack that returns a simple RedisText instead of interpreter the correct data in the redis protocol.

In the example above I am using RediSQL V2 (now know as zeeSQL https://zeesql.com) that returns also the type of the columns.

Moreover, we can return directly JSON, with the correct typing.

More info about all the commands in zeeSQL are here: https://doc.zeesql.com/references

Unfortunately I don't believe there is much we can do, beside a suggestion to the ServiceStack maintainers.

The obvious suggestion is to switch to RediSQL V2 / zeeSQL. We maintain perfect backward compatibility with RediSQL V1.

You just have to prefix your commands with REDISQL.V1 instead of just REDISQL.