mySQL blobs and perl

New Articles

Howto create a unique identifier in perl.

perl File upload progress bar.

LPI Certification

There are plenty of articles on blobs, many more on perl and a countless number on mysql but very few on using mysql blob fields with perl. The few that I encountered assumed that the reader has a very wide understanding of perl.

This article tries to bridge that guide by not making such assumptions.

First of all we will be making use of DBI - the DataBase Independent inteface for perl. That means though this article deals with mysql, the code can easily work work other databases such as postgreSQL with little or no change. This code in fact happens to be a extracted from the mega upload perl edition. Please download it and look in the contrib folder for the full code and the sample database.

We will start of by importing the DBI module.

	use DBI;

The next step is to establish our connection to the database, of course you will have to replace the hostname etc to match your own configurations. In effect the following piece of code will attemtp to connect using DBI to a mysql database called filemanager on localhost RaiseError means it will blow a gaskett when something goes wrong.

	our $dbh = DBI->connect('DBI:mysql:filemanager:localhost',
                       'visual',  # user name
                       'not4you',  # password
                       { RaiseError => 1 });

Most drivers nowadays support prepared statements which means you can create an sql statement once and use it multiple times by binding different variables to it at different times. Though perl and java drivers support this it's not a feature that's available with php. This is what our prepared statement looks like.

	my $sth= $dbh->prepare(
		"INSERT INTO uploadedFiles(fileType,fileName,fileSize,fileData)".
		" VALUES ( ?,?,?,?) ");

Notice the question marks? you can loosely think of them as place holder to wich the parameters are to be bound. The most common way of doing so is to call the bind_param method in DBI. However when only a few columns are in use it's perhaps more convinient to pass them directly to the execute statement as you will see later.

Before we get any further it should be noted that database is not the best place to store a file. Usually when reading or writing to a blob field the entire contents are held in memory. If you are concerned about scalability forget that such a thing called blobs exist. Having said that though it's possible at least with java to pass in an input stream instead of a byte array as the parameter

	open(my $fh, 'index.gif' ) or die $!;
	read( $fh, $var, -s $fh );

The two lines of code above reads in the entire contents of the files into a variable which will be passed onto the database in the next step

	$sth->execute('image/gif','index.gif',1471,$var);

You will obviously have to replace the file name with something that matches your own server. In the case of megaupload the files are what the user will upload to your server. We will finish up by closing our connections.

	$sth->finish;
	$dbh->disconnect;
Copyright © Raditha Dissanayake 2013