Ethan Allen Ethan Allen - 7 months ago 52
SQL Question

Should I use PDO PARAM_LOB or PARAM_STR for MySQL TEXT type?

Should I use PDO

PARAM_LOB
or
PARAM_STR
for MySQL
TEXT
type? I expect my data to be 5000+ characters.

$stmt->bindParam(':notes', $notes, PDO::PARAM_STR);


OR

$stmt->bindParam(':notes', $notes, PDO::PARAM_LOB);

Answer

If you're going to be using large chunks of data, as you have mentioned in your use-case, then yes -- I would use PDO::PARAM_LOB to manipulate your data using data streams.

According to the PHP documentation:

At some point in your application, you might find that you need to store "large" data in your database. Large typically means "around 4kb or more", although some databases can happily handle up to 32kb before data becomes "large". Large objects can be either textual or binary in nature. PDO allows you to work with this large data type by using the PDO::PARAM_LOB type code in your PDOStatement::bindParam() or PDOStatement::bindColumn() calls. PDO::PARAM_LOB tells PDO to map the data as a stream, so that you can manipulate it using the PHP Streams API.

And using it like this:

<?php
$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->prepare("select contenttype, imagedata from images where id=?");
$stmt->execute(array($_GET['id']));
$stmt->bindColumn(1, $type, PDO::PARAM_STR, 256);
$stmt->bindColumn(2, $lob, PDO::PARAM_LOB);
$stmt->fetch(PDO::FETCH_BOUND);

header("Content-Type: $type");
fpassthru($lob);