How to return huge data(more than 10Lakh characters) that is created dynamically inside the store procedure

Help Procedural SQL
1

I have storedd procedure which takes a huge list of data and returns an error message against each invalid record. Ex. Input: (1,2,3) Output: (1 : invalid, 3 : invalid)
I used longtext to contruct the error message. But i see it truncates the value after it reaches around 10Lakh Characters.
Is there better way of doing it?
Tried with JSON Array , look like procedure cannot return non-scalar value.

2

Where are you trying to return the data to? And how big exactly is the string or json object you are trying to return?

You may need to set the max_allowed_packet global variable to be larger.

Stored procedures can return json scalar objects, which can be json arrays, but they can’t return actual SingleStore array objects directly back to the client app.

Another possible solution would be to break up your return value to a rowset of smaller values (assuming its a collection – have one row for each element of the collection) and let your client app concatenate the pieces back together.

3

Hi Hanson,
I am returning the string to GoLang Application.
String Length as I observed was having more than 10 Lakh Characters. But expected string length can go 10 times more than that.

Can you provide details on how to use rowset.
Here the input will be like , [{id: 1},{id:2}].
After validating the input, error string is constructed dynamically in the SP like
[{id:1, msg: ‘User is invalid’}, {id:2, msg: ‘User is not found’}]

4

You could retrieve multiple rows from the database as a result of one stored procedure call.

Your client app (in GoLang) can call the SP using “ECHO procName(args)” and you can have the stored proc procName return a QUERY type value that has the output schema you want.

See this docs page for an example with Python:

Alternatively, you can have your SP output a set of rows using ECHO SELECT. You can return multiple result sets, but of course you only may need to return one row set that way – that’s fine. ECHO SELECT might be more convenient for you depending on your app.

We support connectivity compatible with MySQL, so from Go, you may be able to do something like what’s described in this page:

1 Like
5

Hi Hanson, Do we have any limitations on the number of resultsets that SP can return. So far I don’t see that in Documentation. So just wanted confirmation on if any variable plays a role here.

6

I don’t think there is any specific limitation wrt SPs. What happens when you try to run this query SELECT "aaaa...<repeated 2 million times>" Does this also gets trimmed?