Inserting an PDF to SqlServer using Perl

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks!
I am trying to update a table on SQL Server by inserting a PDF file, but... I am using var type "image" on the SQL Server table field, it gets there, but all truncated. It might be some sort of conversion I am missing, that's why I think it could be done using Perl. Has anyone here done something like that before?

...
my $user = $mydb::Config::odbc_def->{ $db } { user };
my $pass = $mydb::Config::odbc_def->{ $db } { pass };
$dbh = DBI->connect("DBI:ODBC:$db",$user, $pass, {RaiseError => 1});

# Insert pdf file attached with the reference number.

my $sql="UPDATE main SET pdf='$pdf_file[0]' WHERE number='$number' AND
+ ref='$ref'";
      $sth = $dbh->prepare($sql);
      $sth->execute() || die $sth->errstr;
      $dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n";
[download]

Thanks for the help!

Comment on Inserting an PDF to SqlServer using Perl Download Code

Replies are listed 'Best First'.
Re: Inserting an PDF to SqlServer using Perl by almut (Canon) on Jul 02, 2007 at 21:17 UTC
A PDF file can potentially contain any nasty char, including quotes etc., so it's not a good idea to interpolate that binary data into the SQL query... (I'm assuming your `$pdf_file[0]` is holding the file's contents, not the filename). Have you tried using placeholders instead? I.e. put `pdf = ?` in the query, and then pass the data using `$sth->execute($pdf_data)`	[reply] [d/l] [select]
Re^2: Inserting an PDF to SqlServer using Perl by Anonymous Monk on Jul 02, 2007 at 21:40 UTC
Yes, it holds the file's content, not just the file name.	[reply]