Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

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.

http://lists.ibiblio.org/pipermail/freetds/2009q1/024257.html
sql2008 Filestream
inserting images in ms sql server

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

Thanks!

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;

        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.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (18)
As of 2014-09-16 12:49 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (17 votes), past polls