Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling

module & MSSQL driver recommendations for linux

by ksublondie (Pilgrim)
on Sep 18, 2012 at 19:24 UTC ( #994337=perlquestion: print w/ replies, xml ) Need Help??
ksublondie has asked for the wisdom of the Perl Monks concerning the following question:

I have a linux server that is accessing a ms sql2008 db using DBD::Sybase and the FreeTDS driver. Everything is working fine except with blobs. Apparently, there is an issue working with blobs and FreeTDS with mssql.
sql2008 Filestream
inserting images in ms sql server

Can anyone recommend a perl module/db driver combination that definitively works to manipulate mssql blobs?


Comment on module & MSSQL driver recommendations for linux
Re: module & MSSQL driver recommendations for linux
by mje (Deacon) on Sep 19, 2012 at 09:47 UTC

    Would you mind telling us what version of MS SQL Server you have, showing us the schema for the table you are inserting to and a small bit of Perl code which fails.

    I maintain DBD::ODBC and use various drivers which happily insert into binary columns. In fact the DBD::ODBC test suite contains tests which do that.

      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?

        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;


        # # 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 '' # 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 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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://994337]
Approved by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (5)
As of 2014-09-02 10:31 GMT
Find Nodes?
    Voting Booth?

    My favorite cookbook is:

    Results (21 votes), past polls