I'm facing issues while inserting gzCompressed JSON into a MySQL BLOB Column. The size of JSON is very huge (~200MB).
Following is my Table Script:
CREATE TABLE `idb_mamfile_temp` (
`ID` bigint(20) NOT NULL,
`Message` blob,
`Action` smallint(6) NOT NULL DEFAULT '1',
`TimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`InternalID` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`InternalID`),
KEY `IDX_ID` (`ID`) USING BTREE,
KEY `IDX_Action` (`Action`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=311802 DEFAULT CHARSET=latin1;
And my php script to add data:
$compressedMessage = gzcompress($jsonMessage);
$sQuery = 'INSERT into idb_mamfile_temp ("ID", "MESSAGE", "ACTION") VALUES (123,' . $compressedMessage . ', 1)';
$result = $mysqli->query($sQuery);
echo $mysqli->error . "</br>";
echo print_r($mysqli->error_list, 1);
echo "</br>" . $result;
This message will be read by a java code which will do the processing. The MySQL throws the following exception:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"ID", "MESSAGE", "ACTION") VALUES (123,'x��{s�Ǚ��UP��7o�$c'��' at line 1
Array ( [0] => Array ( [errno] => 1064 [sqlstate] => 42000 [error] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"ID", "MESSAGE", "ACTION") VALUES (123,'x��{s�Ǚ��UP��7o�$c'��' at line 1 ) )
I tried putting single/double quotes around the compressed Message, but still in vain. Can anyone point me in the right direction?
