Inserting and Updating LONG/LOB Columns
Some databases let you insert into LONG/LOB columns using SQL statements with literal strings, like this:
INSERT INTO table_name (key_num, long_description) VALUES (42, '...')
Ignoring portability for the moment, that’s fine for simple
short textual strings, but soon runs into problems for anything else.
Firstly, most databases have a limit on the maximum length of an SQL
statement, and it’s usually far shorter than the maximum length
of a LONG/LOB column. Secondly, most databases have limits on which
characters can be included in literal strings. The DBD driver’s
quote() method will do its best, but it’s
often not possible to put all possible binary data values into a
string. Finally, coming back to portability, many databases are
strict about data typing and just don’t let you assign literal
strings to LONG/LOB columns.
So how do we avoid these problems? Here’s where placeholders come to our aid once again. We discussed placeholders in some detail in Chapter 5 so we’ll only cover LONG/LOB issues here.
To use placeholders, we’d implement the statement above using the DBI as:
use DBI qw(:sql_types);
$sth = $dbh->prepare( "
INSERT INTO table_name (key_num, long_description) VALUES (?, ?)
" );
$sth->bind_param( 1, 42 );
$sth->bind_param( 2, $long_description, SQL_LONGVARCHAR);
$sth->execute();Passing SQL_LONGVARCHAR as the optional TYPE
parameter to bind_ param()
gives the driver a strong hint that you’re binding a LONG/LOB type. Some drivers ...