[SOLVED] Perl DBI / MS ODBC Driver (LinuxL:RHEL) / SQL-Server: How to insert/update BLOB varbinary(max) data?

Perl DBI / MS ODBC Driver (LinuxL:RHEL) / SQL-Server: How to insert/update BLOB varbinary(max) data?

New to SQL-Server. I'm attempting to load a pdf to a SQL-Server table (data type varbinary(max)) via PERL/MS ODBC driver/DBD::ODBC using the following (simplified) code:

use DBI qw(:sql_types);

open my $pdfFH, "test.pdf";
my @pdf = <$pdfFH>; close $pdfFH;
my $pdfStr = join('', @pdf);

my $dbh = <...valid db-handle ...>;
my $sth = $dbh->prepare(qq(
insert into 
    TestTable(Report)
values
    (?)));
$sth->bind_param(1,$pdfStr,DBI::SQL_VARBINARY);
$sth->execute;

Error:

DBD::ODBC::st bind_param failed: [Microsoft][ODBC Driver 17 for SQL Server]Invalid precision value (SQL-HY104) at ./t_sqlserver.pl line 37.
DBD::ODBC::st execute failed: [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error (SQL-07002) at ./t_sqlserver.pl line 38.

I am able to successfully load other data types. An alternative is to load the pdf locally from the file system using OPENROWSET(BULK...) but I would prefer to load directly to avoid moving the file from Linux to Windows.

Solution

The driver should be clever enough to guess the correct type most of the times. Try binding the parameter without specifying the type at all.