File handles

So, the other day, I was working on a piece of code in Perl and I hit this very "curious" problem.

The Perl script would automate a sqlite db creation. The db had some number of tables. Now as we know, there is no direct way of inserting images/video/audio files in a sqlite db and one has to adopt a rather round-about way to get it in. So I wrote a small C code and created its executable and then I got the Perl script to call this executable with the necessary arguments. While testing the script, I realised that the first image insert went through just fine, and then nothing else was getting inserted at all!

Googling up the problem led me to believe that sqlite hangs when inserting huge chunks of data and a good way to circumvent this problem is to add sleep statements everywhere. Now this did make sense as sqlite was never meant to handle large amounts of data (even the name suggests that = SQL+Lite). However, no amount of sleeping was helping .. The problem still persisted.

A bolt of lightening struck and it occured that possibly file handles are getting messed up. So, before calling the C executable, I closed the handle opened in the Perl code (DBI->disconnect). The C code would then open a handle to the db, write to it, and close the handle. On returning back to the Perl code, it would open another handle to write other stuff into it. A bit expensive in terms of time and memory accesses, but then, there has to be a trade off somewhere .... And since I did'nt have any more options, this was definitely worth and a try. And the script worked like a dream ! No need for any more sleeps (that is one solution I find very tacky, where-ever I encounter it).

So if you're facing a similar problem, you know what to do. Here's a bit of pseudo code to help a bit ....

#! /usr/bin/perl
use strict;
use warnings;
use DBI;

my $outfile = "/some/path/to/create/sqlitedb";
my $dbh = DBI->connect("dbi:SQLite:dbname=$outfile","","");

#write things into db using the $dbh handle.

#inserting an image into our db
$dbh->disconnect(); #close the handle
my $command = "./CExecutableFile image/file";
system($command); #execute the command
$dbh = DBI->connect("dbi:SQLite:dbname=$outfile","",""); #reopen the handle