Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Re^2: module & MSSQL driver recommendations for linux

by ksublondie (Friar)
on Sep 19, 2012 at 19:19 UTC ( #994504=note: print w/ replies, xml ) Need Help??


in reply to Re: module & MSSQL driver recommendations for linux
in thread module & MSSQL driver recommendations for linux

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?


Comment on Re^2: module & MSSQL driver recommendations for linux
Select or Download Code
Replies are listed 'Best First'.
Re^3: module & MSSQL driver recommendations for linux
by mje (Curate) on Sep 20, 2012 at 08:11 UTC

    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.

      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.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (12)
As of 2015-07-30 11:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (271 votes), past polls