How to insert compressed data using PDO?

Question

I'm trying to insert a large serialized object into a MySQL database using PDO. Attempting to insert directly gives:

PDOStatement::execute() [pdostatement.execute]: SQLSTATE[08S01]: Communication link failure: 1153 Got a packet bigger than 'max_packet_allowed' bytes

There seem to be a few possible ways to tackle this but my first tack is gzcompress, bringing it down from 2383731 to 155955 bytes (using compression level 6). But am now struggling to insert the result for a different reason:

PDOStatement::execute() [pdostatement.execute]: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'lob) VALUES ('[some binary data spued out here]'

Here's the basic gist of the code:

  $value = gzcompress(serialize($lob));
  $stmt = $conn->prepare("INSERT INTO saved (lob) VALUES (:value)");
  $stmt->bindParam(':value', $value, PDO::PARAM_LOB);
  $stmt->execute();

The examples in the documentation all seem to be using file streams rather than binary data stored in a string so am not sure this is valid. Could anyone advise?

Which value has [PDO::ATTR_EMULATE_PREPARES](http://www.php.net/manual/en/pdo.setattribute.php) got? Have a look at http://stackoverflow.com/questions/10113562/pdo-mysql-use-pdoattr-emulate-prepares-or-not (accepted answer). — VMai, Jul 09 '14 at 22:45
Not sure what it defaults to but have tried setting it to both true and false and saw the same error for both. — Steve Chambers, Jul 09 '14 at 23:09

score 1 · Accepted Answer · answered Jul 10 '14 at 05:34

1

The error sounds like you need to add backticks around the field name:

INSERT INTO saved (`lob`) VALUES (:value)

answered Jul 10 '14 at 05:34

meda

45,103
14
92
122

Perfect! The actual column name was 'all' but had changed the names here to protect the innocent - my own stupidity in forgetting that `all` is a [reserved word](http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html). Many thanks. – Steve Chambers Jul 10 '14 at 15:08
1

I was reculant to post this because lob is reserved only in oracle but great :D – meda Jul 10 '14 at 15:11

How to insert compressed data using PDO?

1 Answers1