DBD::ODBC works for me with the MS SQL Server ODBC Driver (Windows), the MS SQL Server Native Client (Windows) and the Easysoft SQL Server ODBC Driver (Linux).
use strict;
use warnings;
use DBI;
use Test::More;
use Config;
open(my $f, "<", "pm_994504.jpg") or die "$!";
binmode($f);
my $jpg = do {local $/ = <$f>};
my $h = DBI->connect;
$h->{RaiseError} = 1;
$h->{PrintError} = 0;
{
diag("\n");
diag("Perl $Config{PERL_REVISION}.$Config{PERL_VERSION}.$Config{PE
+RL_SUBVERSION}\n");
diag("osname=$Config{osname}, osvers=$Config{osvers}, archname=$Co
+nfig{archname}\n");
diag("Using DBI $DBI::VERSION\n");
diag("Using DBD::ODBC $DBD::ODBC::VERSION\n");
diag("Using DBMS_NAME " . DBI::neat($h->get_info(17)) . "\n");
diag("Using DBMS_VER " . DBI::neat($h->get_info(18)) . "\n");
my $driver_name = DBI::neat($h->get_info(6));
diag("Using DRIVER_NAME $driver_name\n");
diag("Using DRIVER_VER " . DBI::neat($h->get_info(7)) . "\n");
diag("odbc_has_unicode " . $h->{odbc_has_unicode} . "\n");
}
eval {
$h->do(q/drop table mje/);
};
$h->do(q/create table mje (a varbinary(max), b image)/);
my $s = $h->prepare(q/insert into mje values(?,?)/);
$s->execute($jpg, $jpg);
# you were using pack so here it is with pack as well
my $binary = pack("H*", $jpg);
$s->execute($binary, $binary);
$s = $h->prepare(q/select * from mje/);
$s->{LongReadLen} = 800_000;
$s->execute;
my $r = $s->fetchall_arrayref;
is($r->[0][0], $jpg, "jpeg in varbinary column");
is($r->[0][1], $jpg, "jpeg in image column");
is($r->[1][0], $binary, "packed jpeg in varbinary column");
is($r->[1][1], $binary, "packed jpeg in image column");
done_testing;
Output
#
# Perl 5.12.4
# osname=linux, osvers=2.6.24-28-server, archname=i686-linux-gnu-threa
+d-multi-64int
# Using DBI 1.623
# Using DBD::ODBC 1.39
# Using DBMS_NAME 'Microsoft SQL Server'
# Using DBMS_VER '10.50.1617'
# Using DRIVER_NAME 'libessqlsrv.so'
# Using DRIVER_VER '01.05.0004'
# odbc_has_unicode 0
ok 1 - jpeg in varbinary column
ok 2 - jpeg in image column
ok 3 - packed jpeg in varbinary column
ok 4 - packed jpeg in image column
1..4
It did not work with the freeTDS driver I have (I've no idea why). It produced Error converting characters into server's character set. Some character(s) could not be converted (SQL-HY000) at pm_994504.pl line 35, <$f> line 1.
BTW, just so you know, I maintain DBD::ODBC and I also work for Easysoft.
UPDATE: the second insert was not using $binary and similary with the test at the end.