Matlab JDBC drivers - How to pass a table variable equivalent to an SQL stored procedure

Is it possible to send a table variable to a stored procedure in matlab in such a way that it is treated as a table by the SQL stored procedure allowing for an unknown number rows to be sent.

This must be defined programatically as well.

Answer 1

[Piyush Kumar]

Hi,

Please check the list of data types supported by MySQL.

Passing MATLAB Data to MySQL Stored Procedure Using JSON

If you need to pass multiple rows from MATLAB to a MySQL stored procedure, you can't directly use a table data type because MySQL doesn't support it. Instead, you can use JSON to achieve this.

% Create a table "MyTable" in MySQL
CREATE TABLE MyTable
(
    Column1 INT,
    Column2 VARCHAR(50)
);

% Create a stored procedure that processes JSON input
DELIMITER //

CREATE PROCEDURE InsertFromJSON(IN jsonData JSON)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE n INT DEFAULT JSON_LENGTH(jsonData);
    WHILE i < n DO
        INSERT INTO MyTable (Column1, Column2)
        VALUES (
            JSON_UNQUOTE(JSON_EXTRACT(jsonData, CONCAT('$[', i, '].Column1'))),
            JSON_UNQUOTE(JSON_EXTRACT(jsonData, CONCAT('$[', i, '].Column2')))
        );
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

% Test the stored procedure from MySQL 
SET @jsonData = '[{"Column1": 10, "Column2": "Hi1"}, {"Column1": 20, "Column2": "Hello"}, {"Column1": 30, "Column2": "Hey"}]';
CALL InsertFromJSON(@jsonData);

SELECT * FROM MyTable;

To achieve the same using MATLAB,

% Establish a database connection
conn = database('database_name', 'username', 'password', 'Vendor', 'MySQL', 'Server', 'localhost', 'PortNumber', 3306);

% Example data
data = struct('Column1', {10, 20, 30}, 'Column2', {'Hi2', 'Hello1', 'Hey1'});
jsonData = jsonencode(data);

% Define the SQL query to call the stored procedure
sqlquery = sprintf('CALL InsertFromJSON(''%s'')', jsonData);

% Execute the SQL query
exec(conn, sqlquery);

This will pass the JSON data from MATLAB to the stored procedure, which will then insert the data into the "MyTable" table.

Answer 2

[埃博拉酱]

SQL PROCEDURE GENERALLY DOES NOT ACCEPT TABLES AS INPUT PARAMETERS, WHICH IS INDEPENDENT OF MATLAB. IN GENERAL, YOU NEED TO CREATE A TEMPORARY TABLE, INSERT MATLAB DATA, AND THEN REFERENCE THE TABLE IN PROCEDURE.