Re^2: module & MSSQL driver recommendations for linux

[ksublondie]

It looks like the sql server is running v 10.0.1600.

I've tried this in multiple tables and multiple dbs, all on the same sql server, using a modified version of the code from the DBD::Sybase documentation. One of my attempts is shown in the node referenced in my OP: sql2008 Filestream. The current table configuration in this example has col 1 varchar(40), col 2 datetime, col 3 uniqueidentifier, col 4 varchar(50), and col 5 varbinary(MAX) where col 5 is the column I'm trying to use as the blob column.

My latest attempt is on a different db, same server, into col 7 where the table is as follows: col 1 int, col 2, int, col 3 varchar(50), col 4 money, col 5 varchar(20), col 6 bit, col 7 image, col 8 varchar(MAX).

my $imagefile = $cgi->upload('image');
my $image;
while(<$imagefile>) { $image .= $_; }
my $binary = pack("H*", $image);

warn length($binary);
#my $size = length($image);
my $size = length($binary);
warn $size;

#according to documentation, "ct_data_info(CS_GET) will fail if the IM
+AGE/TEXT data for which the CS_IODESC is being fetched is NULL."     
+           
$dbh->do("UPDATE item SET image = ''  WHERE id = '".$cgi->param('id').
+"' and image is null");
# first we need to find the CS_IODESC data for the data
my  $sth = $dbh->prepare("select image from item where id = ?");
$sth->execute($cgi->param('id'));
while($sth->fetch) {    # don't care about the data!
      $sth->syb_ct_data_info('CS_GET', 1);
}

# OK - we have the CS_IODESC values, so do the update:
$sth->syb_ct_prepare_send() ;
# Set the size of the new data item (that we are inserting), and make
# the operation unlogged
$sth->syb_ct_data_info('CS_SET', 1, {total_txtlen => $size, log_on_upd
+ate => 0});

# now transfer the data (in a single chunk, this time)
#  $sth->syb_ct_send_data($binary, length($binary));
#  $sth->syb_ct_send_data($image, length($image));
# open the file, and store it in the db in 1024 byte chunks.
while($size){
        my $to_read=$size>1024 ? 1024 : $size;
        my $buff=substr($binary,0,$to_read,'');
#       my $buff=substr($image,0,$to_read,'');
        my $bytesread=length $buff;
#       my $bytesread=read(IN,$buff,$to_read);
        $size-=$bytesread;
#       $size-=length $buff;
warn "sending data $bytesread $size";
        $sth->syb_ct_send_data($buff, $bytesread) ;

}
  # commit the operation
  $sth->syb_ct_finish_send() ;

(Forgive the obvious sql injection problems. I plan on fixing this once I can get the d@mned thing to work!)

In all my attempts so far, I get the identical error as I posted in sql2008 Filestream and with the poster in inserting images in ms sql server.

DBD::Sybase::st syb_ct_send_data failed: Server message number=102 sev
+erity=15 state=1 line=1 server=SQL2008\SQL2008
text=Incorrect syntax near '0x00000000000000000000000000000000'.

I've been able to successfully insert/update blobs from windows scripts to this server on the same tables, but haven't had any luck with the FreeTDS/mssql combination.

Pray tell, WHICH drivers work?

[mje]

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.

[ksublondie]

If you read the links in my OP, one of them is a link to FreeTDS discussing the problem and another is using FreeTDS with DBD::ODBC. It appears that FreeTDS has issues.

Thanks for the driver recommendation! I'll try that combination and hopefully get it to work.