Can we use exception more than once in one stored procedure??
Yes Mohan
You can have more than one EXCEPTION as long as they are enclosed within BEGIN…END blocks.
Let us know how it goes for you.
Thanks,
Hi,
I’m trying to store messages which were posted from a Kafka topic through Pipeline and Stored Procedure.
a) Pipeline was subscribed to Kafka topic.
b) Memsql Pipeline will trigger the stored procedure.
Everything is going good for positive scenario’s. In case of any corrupted message received from Kafka topic then rest of the valid messages in the same batch were getting Ignore.
For example:
if I have received 3 messages in the batch, out of which 1 is invalid then I’m expecting two valid records needs to be inserted into TABLE_1 and remaining one should be in Errors table.
My main procedure
–/
CREATE OR REPLACE PROCEDURE MAIN (batch QUERY(messageBody JSON) )
AS
DECLARE
var1 QUERY(messageBody JSON) = SELECT messageBody from batch where CONDITION;
var2 QUERY(messageBody JSON) = SELECT messageBody from batch where CONDITION;
BEGIN
CALL PROC1(var1);
CALL PROC2(var2);
END
/
–/
CREATE OR REPLACE PROCEDURE PROC1 (batch QUERY(messageBody JSON) )
AS
BEGIN
INSERT INTO T — here logic inserts into T table selecting from the batch
EXCEPTION
WHEN OTHERS THEN
INSERT INTO T_ERRORS — here logic inserts bad data into T_ERRORS table from the batch in case of any exception
END
/
–/
CREATE OR REPLACE PROCEDURE PROC2 (batch QUERY(messageBody JSON) )
AS
BEGIN
INSERT INTO T — here logic inserts into T table selecting from the batch
EXCEPTION
WHEN OTHERS THEN
INSERT INTO T_ERRORS — here logic inserts bad data into T_ERRORS table from the batch in case of any exception
END
/
I am expecting when my MAIN procedure calls PROC1 and PROC2, valid messages from the batch should insert into T table and invalid messages of any exception type should insert into T_ERRORS table. However, bad data is not being inserted to the error tables. Am I missing anything here?
Is it possible that the INSERT, when it fails, causes the transaction to eventually roll back, so the WHEN OTHERS clause’s effect is rolled back?
If that is the case, consider some other method, like checking for the bad data with app logic that won’t cause a rollback.
In our case PROC1 or PROC2 accepts the batch from pipeline and inserts the data and INSERT doesn’t roll back the transaction. we tried using ROLLBACK COMMAND, However we get an error message “Rollback cannot be used within the procedure from pipeline”.
Looks like we need to consider different approach here. Could you provide more details of using other methods.
I was thinking of an approach where you check the validity of each row in app logic before you try to insert it. Or, insert data in raw form in a staging table, then have another stage that checks validity of that, and moves the “good” data (after some transforms, maybe) to the final destination table.
Also, got to SingleStore | Develop with SQrL, Your SingleStore Copilot. and ask this “how to find bad data in a pipeline and ignore it, then go back later and fix it”
It gives some good information. Also, see
Thank you for the details. If we don’t want to rollback the full transaction and If we want to insert the errors into separate table like capturing the exceptions specially for column size. When data size is more than column size. Is there a best approach on this?
I’m not sure exactly what you mean. But if you want to capture “bad data” that is too big for a column, you could create an errors table with a longtext column and put it in there in string form.