http://www.perlmonks.org?node_id=1024176

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

Hello all, I've recently began inserting files into a sql server 2008 R2 db from perl using dbi, dbd:odbc, and freetds. I cand connect, query, and insert file ( varbinary(max), DBI:VARBINARY). Most of the time. However, when the files get to a certain size (I think that's the cause) I get the 'HY090 invalid string or buffer length error' rejection. If someone has a pointer or two I would really appreciate it; I'll try to get my code up here soon.

Replies are listed 'Best First'.
Re: dbd odbc sql big file blues
by CaptainDaddy (Initiate) on Mar 19, 2013 at 01:47 UTC

    Sorry for the misspellings above, that was me. Allow me to attempt to illuminate.
    I can connect fine, and I use the

    $cmd=$dbh->prepare(....);
$cmd->execute();
$cmd->finish;

    [download]

    throughout. My file column is

    varbinary(max)

    , and to insert I do a

    $cmd=$dbh->prepare("insert into filestable (filescolumn) values(?)");
$cmd->bind_param(1,$binfilecontents, DBI:SQLVARBINARY);
$cmd->execute;
$cmd->finish;

    [download]

    This works great for most small files, gives me 0x for some, and for others (about 350kB) I get the hy090 error. I read somewhere that this indicates a negative buffer length somewhere, but am having no luck figuring out how to fix it. Please elighten me so I may progress in my training. Thanks.

[reply]
[d/l]
[select]

      Can you show us a small script that demonstrates the error?

      Hint: you can use the x operator to efficiently create large strings:

      my $str = '1' x 50;

print $str;

      [download]
      True laziness is hard work
[reply]
[d/l]

        Here's a reproduction of the error

        #
#
#connect to the database, set some properties
#
$host=...;
$port=...;
$user =...;
$pass = ...";
$dsn="dbi:ODBC:Driver=/usr/local/lib/libtdsodbc.so;SERVER=$host;PORT=$
+port;DATABASE=...;TDS_Version=7.2";
$dbh= DBI->connect($dsn,$user,$pass) or die "can't connect";

$fileTableName="fileTable";

$cmd="SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE T
+ABLE' AND TABLE_NAME='$fileTableName\'";
$fetchcmd= $dbh->prepare($cmd);
$fetchcmd->execute();
if(@row= $fetchcmd->fetchrow_array){
    print "the file table is there!!!\n";
}else {
    $createCmd=$dbh->prepare("create table $fileTableName (fileColumn 
+VARBINARY(MAX) not null)");
    $createCmd->execute();
    $insertCmd=$dbh->prepare("insert into $configTableName (fileColumn
+) values (?)");
    $insertCmd->bind_param(1,0,DBI::SQL_VARBINARY);
    $insertCmd->execute();
}
$fetchcmd->finish();


$insertcmd= $dbh-> prepare("insert into $fileTableName (fileColumn) va
+lues (?)");

# 350kBites -> largest number is 2^(8*350000)-1
for($i = 75; $i<2**28000000;$i=$i*2){
    print"did";
    $insert='g'x$i;
    $insertcmd->bind_param(1,$insert,DBI::SQL_VARBINARY);
    $insertcmd->execute() or print" not ";
    $insertcmd->finish();
    $sz=length($insert);
    print " succeed. size: $sz\n"; 
}
$dbh->disconnect;

        [download]

        The above code gives me the following output:

        > perl varbinary_failure_demo.pl
        the file table is there!!!
        did succeed. size: 75
        did succeed. size: 150
        did succeed. size: 300
        did succeed. size: 600
        did succeed. size: 1200
        did succeed. size: 2400
        did succeed. size: 4800
        did succeed. size: 9600
        did succeed. size: 19200
        DBD::ODBC::st execute failed: FreeTDSSQL ServerInvalid string or buffer length (SQL-HY090) at varbinary_failure_demo.pl line 45.
        did not succeed. size: 38400
        DBD::ODBC::st execute failed: FreeTDSSQL ServerInvalid string or buffer length (SQL-HY090) at varbinary_failure_demo.pl line 45.
        did not succeed. size: 76800
        DBD::ODBC::st execute failed: FreeTDSSQL ServerInvalid string or buffer length (SQL-HY090) at varbinary_failure_demo.pl line 45.
        did not succeed. size: 153600
        DBD::ODBC::st execute failed: FreeTDSSQL ServerInvalid string or buffer length (SQL-HY090) at varbinary_failure_demo.pl line 45.
        did not succeed. size: 307200
        DBD::ODBC::st execute failed: FreeTDSSQL ServerInvalid string or buffer length (SQL-HY090) at varbinary_failure_demo.pl line 45.
        did not succeed. size: 614400
        DBD::ODBC::st execute failed: FreeTDSSQL ServerInvalid string or buffer length (SQL-HY090) at varbinary_failure_demo.pl line 45.
        did not succeed. size: 1228800
        DBD::ODBC::st execute failed: FreeTDSSQL ServerInvalid string or buffer length (SQL-HY090) at varbinary_failure_demo.pl line 45.
        did not succeed. size: 2457600
        DBD::ODBC::st execute failed: FreeTDSSQL ServerInvalid string or buffer length (SQL-HY090) at varbinary_failure_demo.pl line 45.
        did not succeed. size: 4915200
        DBD::ODBC::st execute failed: FreeTDSSQL ServerInvalid string or buffer length (SQL-HY090) at varbinary_failure_demo.pl line 45.
        did not succeed. size: 9830400
        DBD::ODBC::st execute failed: FreeTDSSQL ServerInvalid string or buffer length (SQL-HY090) at varbinary_failure_demo.pl line 45.
        did not succeed. size: 19660800
        DBD::ODBC::st execute failed: FreeTDSSQL ServerInvalid string or buffer length (SQL-HY090) at varbinary_failure_demo.pl line 45.
        did not succeed. size: 39321600
        DBD::ODBC::st execute failed: FreeTDSSQL ServerInvalid string or buffer length (SQL-HY090) at varbinary_failure_demo.pl line 45.
        did not succeed. size: 78643200
        DBD::ODBC::st execute failed: FreeTDSSQL ServerInvalid string or buffer length (SQL-HY090) at varbinary_failure_demo.pl line 45.
        did not succeed. size: 157286400
        DBD::ODBC::st execute failed: FreeTDSSQL ServerInvalid string or buffer length (SQL-HY090) at varbinary_failure_demo.pl line 45.
        did not succeed. size: 314572800
        DBD::ODBC::st execute failed: FreeTDSSQL ServerInvalid string or buffer length (SQL-HY090) at varbinary_failure_demo.pl line 45.
        did not succeed. size: 629145600
        Out of memory!

[reply]
[d/l]

      This works fine for me with the Easysoft SQL Server ODBC Driver:

      use strict;
use warnings;
use DBI;
use Data::Dumper;

my $h = DBI->connect();
eval {
    $h->do(q/drop table mje/);
};

$h->do(q/create table mje (a varbinary(max))/);
my $t = $h->column_info(undef, undef, 'mje', 'a');
DBI::dump_results($t);

my $x = 'a' x 1000;
my $s = $h->prepare(q/insert into mje values(?)/);

foreach (1..15) {
    $s->execute($x);
    print "Inserted ", length($x), "\n";
    $x = $x x 2;
}
$Data::Dumper::Indent = 0;
my $r = $h->selectall_arrayref(q/select len(a) from mje/);
print Dumper($r);

      [download]

      outputs:

      'master', 'dbo', 'mje', 'a', '-3', 'varbinary', 0, 0, undef, undef, '1
+', undef, undef, '-3', undef, 0, 1, 'YES', '0', '0', '0', '0', undef,
+ undef, undef, undef, undef, undef, '37'
1 rows
Inserted 1000
Inserted 2000
Inserted 4000
Inserted 8000
Inserted 16000
Inserted 32000
Inserted 64000
Inserted 128000
Inserted 256000
Inserted 512000
Inserted 1024000
Inserted 2048000
Inserted 4096000
Inserted 8192000
Inserted 16384000
$VAR1 = [['1000'],['2000'],['4000'],['16000'],['32000'],['64000'],['12
+8000'],['256000'],['512000'],['1024000'],['2048000'],['4096000'],['81
+92000'],['16384000'],['8000']];

      [download]
[reply]
[d/l]
[select]
        I have managed to make the demo script that I wrote before work by switching to DBD:Sybase. I'll post the code and the output soon( I will attach it above, so it may be there if your reading this... hee hee) Thanks for helping me determine that it was (probably) not the scripting.
[reply]

Back to Seekers of Perl Wisdom