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

Re^3: module & MSSQL driver recommendations for linux

by mje (Curate)
on Sep 20, 2012 at 08:11 UTC ( #994605=note: print w/ replies, xml ) Need Help??


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

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.


Comment on Re^3: module & MSSQL driver recommendations for linux
Select or Download Code
Re^4: module & MSSQL driver recommendations for linux
by ksublondie (Pilgrim) on Sep 20, 2012 at 15:18 UTC
    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://994605]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (11)
As of 2015-07-02 22:32 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 (46 votes), past polls