By default, DBI treats data as strings and tries to bind them as VARCHAR variables.
This works quite well for most data types, but poses a problem for binary types such as binary large object (BLOB) values.
To ask DBI to explicitly bind an input value as a specific type:
- 1. use DBI qw/:sql_types/; to import the standard SQL type constants.
- 2. Call bind_param() for each input variable before calling execute(). bind_param() accepts the following arguments:
- a. Number of the parameter (starting at 1).
- b. Input variable to bind.
- c. Anonymous hash representing the data type.
- 3. Call execute() with no arguments.
#!/usr/bin/perl
use strict;
use warnings;
use DBI qw/:sql_types/;
use CGI;
use mydb qw/db_connect/;
my $conn = db_connect();
my $sql = q{
INSERT INTO menu.pictures(id, picture)
VALUES (?, ?)
};
# Prepare the statement
my $stmt = $conn->prepare($sql);
# Create a variable to hold the item ID
my $menu_number = 1;
# Bind the ID as an input parameter
$stmt->bind_param(1, $menu_number, { TYPE => SQL_INTEGER });
# Create a variable with the location of our BLOB file
my $menu_pic = '/home/lynn/burger.png';
# Bind the BLOB as an input parameter, setting the db2_file attribute to true
$stmt->bind_param(2, $menu_pic, { db2_file => 1 });
# Execute the statement
my $result = $stmt->execute();
# Check the results of the statement and print success or failure message
if ($result) {
print "Successfully inserted a picture into the menu.";
}
else {
print "Failed with the following error: " . $conn->errstr;
}