Stored Procedure with Query type input parameter using mysql connector for C#

Help Procedural SQL
1

I am trying to invoke a Procedure which as Query type as input parameter in C# using mysql connector. Can anyone guide how to achieve this?

here is a sample procedure I created
DELIMITER //
CREATE OR REPLACE PROCEDURE sampletableproc(sampledata query(idd int))
AS
BEGIN
echo SELECT id,fullname FROM sampletable a join sampledata sd on a.id = sd.idd;
END

I tried to run below statement but getting an error

DECLARE v QUERY(a INT) = select 1;
CALL sampletableproc(v)

how do we execute this in studio and C#?

2

One of the reasons why your example doesn’t work is that MemSQL doesn’t currently support declaring variables outside Stored Procedure (We plan to do this in future). So to use Query Type Variable, you have to declare it within a stored procedure.

So, using your example, it should be written something like this:

DELIMITER //

CREATE OR REPLACE PROCEDURE innerSP(q Query(idd int))
AS    
DECLARE
BEGIN
   echo SELECT id,fullname FROM sampletable a join sampledata sd on a.id = sd.idd;
END//

CREATE OR REPLACE PROCEDURE sampletableproc(someInt int)
AS
DECLARE 
    q QUERY(idd int) = select someInt;
BEGIN
    call innerSP(q);
END//

DELIMITER ;
call sampletableproc(1);
3

our input is not a scalar variable its a list of integer values and I don’t think the example you have given solves the problem.

Also if i put the sample procedure which i have provided in (Kafka based) Pipeline it works fine so I am just trying to understand how that process sending multiple records in a Kafka topic message to this procedure query type variable

4

If you want to send a array of integer as input, you can use Arrays: Arrays – A Hidden Gem in SingleStore

If you give me more details about what you do want to do with the standalone stored procedure, I can help you out.

When the stored procedure is called for a pipeline, MemSQL internally creates the query type variable that is sent as an input to the stored procedure. If you are trying to replicate this behavior, you can’t declare variables outside stored procedures (E.g. see User-defined variables in MEMSQL? - Procedural SQL - SingleStore Forums) (In your example, the query DECLARE v QUERY(a INT) = select 1; will fail as it is being called outside a stored procedure). You have to declare all variables in context of a stored procedure and then this stored procedure can call your sampletableproc()

5

as part of POC, I am trying MemSQL Pipeline with KAFKA as source and Stored Procedure as target and Inside procedure we have CRUD operation based on key but looks like that approach is throwing an error here is the reference link

I am happy to wait for version 7 but when we want to move our existing application to Memsql from Sql Server we use TVP( Table Valued Parameters) widely and looking at Query type parameter it similar to TVP in sql server so if we need to call any MemSQL procedure which has Query type parameter from C# how do we achieve this ?

6

If you can give me your table DDLs and your stored procedure, I can try and replicate the issue on my end and then suggest appropriate solution.

Since you can’t declare Query Type Variables directly (have to do that inside a stored procedure), another solution is to use TO_QUERY() (See SingleStoreDB Cloud · SingleStore Documentation)
If you can convert your query into a string in C#, you can then pass that string to stored procedure and then use TO_QUERY() to convert that string to a Query Type Variable. See the examples in the link. Hopefully, this should solve your problem to port your application from SQL Server to MemSQL.