Hi,
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.
