MySQL Forums
Forum List  »  Stored Procedures

How to get row to JSON
Posted by: Kevin Waterson
Date: January 16, 2020 09:48PM

I am trying to get a stored procedure to return the json representation of row in a table.
This test works:


DROP procedure IF EXISTS tbl_list;
DELIMITER //
Create procedure tbl_list(table_name Varchar(40), id INT)
BEGIN
SET @A:= CONCAT('Select * from',' ',table_name,' WHERE id=',id);
Prepare stmt FROM @A;
EXECUTE stmt;
END //
CALL tbl_list('profession', 3);


However, when I try to convert this to return JSON, the procedure compiles fine, but when run, I get the following error.
SQL Error(1241): Operand should contain 1 columns(s).
I understand the error is about the number of columns, and seems to do with the first SET statement.. Not sure when setting something causing an error, or if it is the @fields variable...


DROP procedure IF EXISTS row_to_json;
DELIMITER //
Create procedure row_to_json(table_name Varchar(50), id INT)
BEGIN
SET @fields = (
'select group_concat(\'`\', column_name, \'`\')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=',' ',table_name
);
SET @stmt:= CONCAT('SELECT JSON_OBJECT(', @fields ,') from ',table_name,' WHERE id=',id);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
CALL row_to_json('profession', 3);

Options: ReplyQuote


Subject
Views
Written By
Posted
How to get row to JSON
909
January 16, 2020 09:48PM
256
January 17, 2020 12:47AM
158
June 11, 2020 02:34PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.