Error 3141, invalid JSON text argument 1 to function JSON_EXTRACT within a SP

user_4021126 0
Viewed 2886

I have been trying to figure out why mysql shows me the next error.

CALL syncSAP_ncmCodes("[{\"AbsEntry\": 1,\"NcmCode\": \"01010101\",\"Descrip\": \"No existe en el catálogo\", \"GroupCode\": \"S\"}]")  Error Code: 3141. Invalid JSON text in argument 1 to function json_extract: "The document root must not follow by other values." at position

When I call it in this way

 CALL syncSAP_ncmCodes("[{\"AbsEntry\": 1,\"NcmCode\": \"01010101\",\"Descrip\": \"No existe en el catálogo\", \"GroupCode\":\"S\"}]");

this is the SP

CREATE DEFINER=`admin`@`%` PROCEDURE `syncSAP_ncmCodes`(IN json_response mediumtext)
BEGIN
    set @tot_elements = JSON_LENGTH(json_response);
    set @cts = CURRENT_TIMESTAMP();
    set @i = 0;
while @i < @tot_elements do
    SET @ncmCode = json_response->"$[0]";

    set @AbsEntry= ITEM_JSON(@ncmCode, '$.AbsEntry');
    set @NcmCode= ITEM_JSON(@ncmCode, '$.NcmCode');
    set @Descrip= ITEM_JSON(@ncmCode, '$.Descrip');
    set @GroupCode= ITEM_JSON(@ncmCode, '$.GroupCode');

    INSERT INTO `IK-Inventario`.`cat_sap_ncmCodes`
        (
            `AbsEntry`,`NcmCode`,
            `Descrip`,`GroupCode`,
            `ncmCodeSource`,
            `enabled`,
            `created_by`,`created_at`,`updated_at`,`updated_by`
        )
    VALUES
        (
            @AbsEntry,
            @NcmCode,
            @Descrip,
            @GroupCode,
            "SAP",
            1,
            "SYSTEM",@cts,@cts,"SYSTEM"
        )
    ON duplicate key update
                `AbsEntry`= @AbsEntry,
                `NcmCode`= @NcmCode,
                `Descrip`= @Descrip,
                `GroupCode`= @GroupCode,
                `ncmCodeSource`="SAP",
                `enabled`=1,
                `updated_at`= @cts,
                `updated_by`="SYSTEM";

    set @i = @i +1;
end while;
select concat("RDS:syncncm_Codes:",@cts,": ",@i," records processed.") AS result;
END

this is the table

CREATE TABLE `cat_sap_ncmCodes` (
  `AbsEntry` int(11) NOT NULL,
  `NcmCode` varchar(45) NOT NULL,
  `Descrip` varchar(100) DEFAULT NULL,
  `GroupCode` varchar(45) DEFAULT NULL,
  `ncmCodeSource` varchar(45) DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_by` varchar(45) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `enabled` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`NcmCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This is the ITEM_JSON function 

CREATE DEFINER=`admin`@`%` FUNCTION `ITEM_JSON`(jsonData TEXT, prop VARCHAR(100)) RETURNS text CHARSET utf8
BEGIN
DECLARE theValue TEXT;
#SELECT JSON_UNQUOTE(JSON_EXTRACT(jsonData, prop)) INTO theValue;
#RETURN theValue;
RETURN JSON_UNQUOTE(JSON_EXTRACT(jsonData, prop));
END

I've already try to use the JSON_EXTRACT function with the same argument from a isolate sql script and its working, but when I try to use it within the SP shows me that error.

Can somebody help me, giving me some idead where I can search?

stored-procedures mysql json
1 Answers

This was a had nut to crack.

This is copied out of mysql Workbench, if you use phpmyadmin or other means to insert procedures or functions you may need DELIMITER at the start and end.

the problem is that your text is latin1 and my system and most other today are utf8.

So your code could not handle your Spanish catálogo, with an a everysthing run smothly, with á it could identify json.

After changing the input parameters to latin1 it could find the text correctly

I also rewrote the part where you go over all elements of the json.

The last is the procedure call as you see you didn't need \" at all

CREATE DEFINER=`root`@`localhost` PROCEDURE `syncSAP_ncmCodes`(IN json_response mediumtext CHARSET latin1)
BEGIN
DECLARE _test  mediumtext CHARSET latin1;
    set @tot_elements = JSON_LENGTH(json_response);
    set @cts = CURRENT_TIMESTAMP();
    set @i = 0;
while @i < @tot_elements do
    SELECT JSON_EXTRACT(json_response,CONCAT("$[",@i,"]")) INTO @NcmCode1;

    set @AbsEntry= ITEM_JSON( @NcmCode1, '$.AbsEntry');
    set @NcmCode= ITEM_JSON( @NcmCode1, '$.NcmCode');
    set @Descrip= ITEM_JSON( @NcmCode1, '$.Descrip');
    set @GroupCode= ITEM_JSON( @NcmCode1, '$.GroupCode');

    INSERT INTO `cat_sap_ncmCodes`
        (
            `AbsEntry`,`NcmCode`,
            `Descrip`,`GroupCode`,
            `ncmCodeSource`,
            `enabled`,
            `created_by`,`created_at`,`updated_at`,`updated_by`
        )
    VALUES
        (
            @AbsEntry,
            @NcmCode,
            @Descrip,
            @GroupCode,
            "SAP",
            1,
            "SYSTEM",@cts,@cts,"SYSTEM"
        )
    ON duplicate key update
                `AbsEntry`= @AbsEntry,
                `NcmCode`= @NcmCode,
                `Descrip`= @Descrip,
                `GroupCode`= @GroupCode,
                `ncmCodeSource`="SAP",
                `enabled`=1,
                `updated_at`= @cts,
                `updated_by`="SYSTEM";

    set @i = @i +1;
end while;
select concat("RDS:syncncm_Codes:",@cts,": ",@i," records processed.") AS result;

END

The Function

CREATE DEFINER=`root`@`localhost` FUNCTION `ITEM_JSON`(jsonData TEXT CHARSET latin1, prop VARCHAR(100)) RETURNS text CHARSET utf8
    DETERMINISTIC
BEGIN
DECLARE theValue TEXT;
#SELECT JSON_UNQUOTE(JSON_EXTRACT(jsonData, prop)) INTO theValue;
#RETURN theValue;
RETURN JSON_UNQUOTE(JSON_EXTRACT(jsonData, prop));
RETURN 1;
END

And finally the call to insert the data

CAll syncSAP_ncmCodes('[{"AbsEntry": 1,"NcmCode": "01010101",   "Descrip": "No existe en el catálogo",  "GroupCode": "S"}]');

This runs on my msql 8.0.20 on windows, it could be that you must change one bit or another, depending on os

# AbsEntry, NcmCode, Descrip, GroupCode, ncmCodeSource, created_by, created_at, updated_by, updated_at, enabled
1, 01010101, No existe en el catálogo, S, SAP, SYSTEM, 2020-06-06 18:30:50, SYSTEM, 2020-06-06 18:30:50, 1
user_51935360
Related