[Feature] Support for stored procedures in MySQL

[sharat87]

Add support for stored procedures in MySQL to be invoked from the MySQL plugin's queries.

[marcius]

Steps to reproduce 1) Create a stored procedure returning a table data

delimiter $$
create procedure sp_test()
begin
select * from sample_table;
end $$
delimiter ;

2) Create a DB Query calling the stored procedure Reference: https://dev.mysql.com/doc/refman/5.7/en/call.html

call sp_test();

3) Click on Run button

The expected behaviour is getting all sample_table rows/columns.

Instead it is shown just a column "affectedRows" with a single line. This is the full HTTP response that shows that actual data is missing.

{
    "responseMeta": {
        "status": 200,
        "success": true
    },
    "data": {
        "body": [
            {
                "affectedRows": 0
            }
        ],
        "isExecutionSuccess": true,
        "request": {
            "query": "call sp_test();",
            "properties": {
                "preparedStatement": false
            }
        }
    }
}

[Nikhil-Nandagopal]

@mohanarpit @trishaanand this sounds like a great use case for us to handle. Is it easy to do?

[nidhi-nair]

I think the challenge here is to identify a callable from a prepared statement. @trishaanand, without parsing, can we introduce this with a switch for now instead?

[sharat87]

We probably don't need full parsing of the SQL for just this. We identify read-queries vs write-queries based on the first word in the query. Currently, we look for select or show. We should probably do call as well. But then what happens if the stored proc contains an update query instead of a select query like in the example in this issue? Need to confirm those as well.

Ref code: https://github.com/appsmithorg/appsmith/blob/release/app/server/appsmith-plugins/mysqlPlugin/src/main/java/com/external/plugins/MySqlPlugin.java#L387

On that note, the checking itself also fails sometimes. We could fix this together: https://github.com/appsmithorg/appsmith/issues/3107.

[marcius]

+1 for a quick&dirty solution as a switch

To fully manage stored procedure calls, i.e. to get if it's DML or a SELECT statement and to process the returned data,
maybe this SO answer can be useful: https://stackoverflow.com/a/42169952

[sumitsum]

From another user:

Tip:  I want to call a MySQL stored procedure that performs a select and get the results.  You can in an Arrowsmith query using the following trick:  call test2('BA01'); select "dummy" as dummy where false;

Without the dummy select, it will call the procedure but not return the result.

[maximometascript]

Will this get fixed?

[Nikhil-Nandagopal]

@maximometascript you can still achieve this by following this guide https://docs.appsmith.com/connect-data/concepts/dynamic-binding-in-queries#invoking-stored-procedures

[maximometascript]

@maximometascript you can still achieve this by following this guide https://docs.appsmith.com/connect-data/concepts/dynamic-binding-in-queries#invoking-stored-procedures

I'm afraid that doesn't appear to work for MySQL stored procedures. Can you confirm others have gotten this to work?

[Nikhil-Nandagopal]

@maximometascript you may have to turn prepared statements off when trying to invoke this but yes this used to work though i haven't tried it recently.

[sumitsum]

Hi @maximometascript can you please share what issue you are facing ? Are you seeing any error ? If so, can you please share the error description.