How to compress and uncompress large text file into MySQL?

[VikR]

I'm using the Navicat console to run test queries. spssDATAfile' is a field of type blob. This works:

mysql> UPDATE jobs  SET spssDATAfile = (COMPRESS('999999999999'))  WHERE folderName =  'Client_12-02-2010-23-04-42';
Query OK, 1 rows affected (0.00 sec)

Yet the uncompress function returns null:

mysql> SELECT uncompress('spssDATAfile') FROM jobs  WHERE folderName =  'Client_12-02-2010-23-04-42';
+----------------------------+
| uncompress('spssDATAfile') |
+----------------------------+
| NULL                       |
+----------------------------+
1 rows in set (0.03 sec)

How can I get UNCOMPRESS to work?

Thanks very much in advance to all for any info.

Best,


-Vik

[VikR]

I solved that - I just needed to remove the single quotes from around the field name. However, when I try to compress and uncompress a large file, I run into a similar anomaly.

I’m storing a large text file to a MySQL blob field like so:

$theFile = fopen($thespssDATAfileName, "r+");
$uncompressedDATAFile = addslashes(fread($theFile, filesize($thespssDATAfileName)));
fclose($theFile);

$mysql_query = "UPDATE jobs ";
$mysql_query .= " SET spssDATAfile = (COMPRESS('$uncompressedDATAFile')) ";
$mysql_query .= " WHERE id =  '$theJobID'";
$QueryID = mysql_query($mysql_query);

All appears to go well, and data which appears to be properly compressed is stored in the correct field.

When I try to uncompress it like this:

$mysql_query = "SELECT UNCOMPRESS(spssDATAfile) FROM jobs ";
$mysql_query .= " WHERE id =  '$theJobID'";
$QueryID = mysql_query($mysql_query);
$theResult = mysql_result($QueryID, 0);

...I get a NULL result.

What am I missing?

Thanks in advance to all for any info.

[VikR]

I got the answer - I had to use a field of type longblob rather than of blob.