Lyndley has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I'm having a problem trying to retrieve a value from a field set as varbinary(MAX) on an MS SQL 2005 backend through DBIx::Simple.

Now I'm not big on DBI but am figuring bits out. I found that inserting and retrieving data worked find if the back was set to varbinary(8000) but since the data would likely exceed 8k varbinary(MAX) was required at which point SELECTs would fail.

I've discovered how to use the LongTruncOk & LongReadLen attributes and these have got me a bit further, I can now retrieve data but the problem I'm having now is that the data always seems to truncate to 1 character.

Can anyone help me retrieve data correctly from a varbinary(MAX)?

On the backend there is a table t_test containing :

Simplified example :

#!perl
use strict;
use Error qw(:try);
use DBIx::Simple;
use Sys::Hostname;
use DBI;

my $dsnString='dbi:ODBC:Driver={SQL Native Client};Server='.hostname.'
+;Database=db_simon;Trusted_Connection=yes;';
my $dbh =DBIx::Simple->connect($dsnString,,) || throw Error::Simple("U
+nable to connect to Database using DSN String \"".$dsnString."\".");

$dbh->{'dbh'}->{'AutoCommit'} =  0;
$dbh->{'dbh'}->{'RowCacheSize'}    =  0;
$dbh->{'dbh'}->{'LongTruncOk'}    =  1;
$dbh->{'dbh'}->{'LongReadLen'} = 50000; # actual for varbinary(MAX) = 
+2147483647
#$dbh->{'dbh'}->{'RaiseError'} = 1;


# insert some data
my $data = "x" x 1000;
$dbh->query('INSERT INTO t_test (c_data) VALUES (?)', $data) or die $d
+bh->error;

# and retrieve
my $results = $dbh->query('SELECT c_id,c_data FROM t_test')->hashes or
+ die $dbh->error;
foreach my $result(@{$results}) {
    print $result->{'c_id'}."-".substr($result->{'c_data'},0,10)."... 
+(".length($result->{'c_data'})." chars returned)\n";
}

$dbh->disconnect;

[download]

Thanks all.

Replies are listed 'Best First'.
Re: dbix::simple & varbinary(MAX)
by Juerd (Abbot) on May 29, 2008 at 12:32 UTC

    As DBIx::Simple is just a wrapper for DBI, the issue is very probably burried somewhere in the DBD, ODBC, or MS SQL. Unfortunately, I'm not familiar with those so I can't help you with that.

    However, I should note that you should probably not call your DBIx::Simple object "$dbh", because people will expect that a variable named $dbh isa DBI handle. Also, you shouldn't use the DBIx::Simple object as a hash reference, contrary to DBI's object. That is, write $db->dbh instead of $dbh->{'dbh'}. This has nothing to do with the problem at hand, but changing your style will prevent mistakes later.

[reply]

      Thanks, hadn't spotted that dbh method.

      Yep have been through DBD::ODBC, DBI and DBIx::Simple pages and not found anything besides "RaisEerror", "LongTruncOk" & "longReadLen" which I've tried.

      I guess I can look again.

[reply]
Re: dbix::simple & varbinary(MAX)
by Lyndley (Novice) on May 29, 2008 at 13:12 UTC

    OK Well I seem to have got round the issue by switching from varbinary(MAX) to ntext/text. Especially since ntext, text and image are to be depreciated in future versions but hey it works.

    In lieu of someone offering up something better I think this is going to be my solution.

[reply]
Re: dbix::simple & varbinary(MAX)
by Anonymous Monk on Jun 26, 2008 at 23:45 UTC
    I just encountered this same problem. It appears to be an issue with DBD::ODBC itself. You can patch dbdimp.c and rebuild your Perl module and that should fix it. Here's a diff against DBD::ODBC 1.16: 
    (root@ravyn 16:40:05)# diff dbdimp.c ~root/dbdimp.new.c 
1897d1896
<           case SQL_VARBINARY:
1933a1933,1940
>           case SQL_VARBINARY:
>           fbh->ftype = SQL_C_BINARY;
>         case SQL_VARCHAR:
>         case SQL_WVARCHAR:
>           if (fbh->ColDef == 0) {
>             fbh->ColDisplaySize = DBIc_LongReadLen(imp_sth);
>           }
>           break;

    [download]
    Credit goes to Fumiaki Yoshimatsu for solving the problem a couple years ago (I found the solution on Google) - not sure why it's not in the mainline DBD::ODBC code, but it works well enough for my applications, so I'm not complaining.
[reply]
[d/l]

      I don't know where you got that 1.16 distribution from because the diff does not make sense against 1.16 code at that point which already contains:

                case SQL_VARBINARY:
          case SQL_BINARY:
            fbh->ftype = SQL_C_BINARY;
            break;
#if defined(WITH_UNICODE)
          case SQL_WCHAR:
          case SQL_WVARCHAR:
            fbh->ftype = SQL_C_WCHAR;
            /* MS SQL returns bytes, Oracle returns characters ... */
            fbh->ColDisplaySize*=sizeof(WCHAR);
            fbh->ColLength*=sizeof(WCHAR);
            break;
#endif /* WITH_UNICODE */

      [download]

      However, the salient point of it is setting ColDisplaySize.

      The reason why it is not in DBD::ODBC is no one has told the current maintainer about it - namely me - I just came across this thread. This will be included in 1.17.

[reply]
[d/l]
        > This will be included in 1.17. I am soooo glad to read this! I am sorry I was a newbie at that time and just posted my patch to DBI-users email list so that slipped from your radar. http://www.mail-archive.com/dbi-users@perl.org/msg26962.html Anyway, I can't wait to see this included! Thanks!
[reply]

Back to Seekers of Perl Wisdom