2

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.

cgeen
  • 23
  • 3
  • 1
    Does it fail if you don't specify the type at all? Does it fail when you try just SQL_BINARY? – choroba Jul 19 '21 at 15:17
  • Thank you! It works without specifying type. I could have sworn I tried that initially without success. Much appreciated! – cgeen Jul 20 '21 at 12:43

1 Answers1

1

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.

choroba
  • 231,213
  • 25
  • 204
  • 289