in reply to dbd odbc sql big file blues

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.

Replies are listed 'Best First'.
Re^2: dbd odbc sql big file blues
by GrandFather (Saint) on Mar 19, 2013 at 02:52 UTC

    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]

        I suggest it is a problem with your ODBC Driver as when I run my example above with your inserts and the Easysoft ODBC Driver I get:

        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;
#}
$s = $h->prepare("insert into mje values (?)");

# 350kBites -> largest number is 2^(8*350000)-1
for(my $i = 75; $i<2**28000000;$i=$i*2){
    print"did";
    my $insert='g'x$i;
    $s->bind_param(1,$insert,DBI::SQL_VARBINARY);
    $s->execute() or print" not ";
    $s->finish();
    my $sz=length($insert);
    print " succeed. size: $sz\n";
}
$Data::Dumper::Indent = 0;
my $r = $h->selectall_arrayref(q/select len(a) from mje/);
print Dumper($r);

        [download]

        I stopped it at 314572800 bytes as I was running out of memory. The output was:

        '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
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
did succeed. size: 38400
did succeed. size: 76800
did succeed. size: 153600
did succeed. size: 307200
did succeed. size: 614400
did succeed. size: 1228800
did succeed. size: 2457600
did succeed. size: 4915200
did succeed. size: 9830400
did succeed. size: 19660800
did succeed. size: 39321600
did succeed. size: 78643200
did succeed. size: 157286400
did succeed. size: 314572800

        [download]
[reply]
[d/l]
[select]

        The below code works as desired, as you will notice I switched to DBD:Sybase,

        > perl sybase_trial.pl<br>
the file table is there!!!<br>
did succeed. size: 75<br>
did succeed. size: 150<br>
did succeed. size: 300<br>
did succeed. size: 600<br>
did succeed. size: 1200<br>
did succeed. size: 2400<br>
did succeed. size: 4800<br>
did succeed. size: 9600<br>
did succeed. size: 19200<br>
did succeed. size: 38400<br>
did succeed. size: 76800<br>
did succeed. size: 153600<br>
did succeed. size: 307200<br>
did succeed. size: 614400<br>
did succeed. size: 1228800<br>
did succeed. size: 2457600<br>
did succeed. size: 4915200<br>
did succeed. size: 9830400<br>
did succeed. size: 19660800<br>
did succeed. size: 39321600<br>
did succeed. size: 78643200<br>

        [download]

        > cat sybase_trial.pl
        #connect to the database, set some properties
        #
        $user = "...";
        $pass = "...";
        $dsn="dbi:Sybase:server=...;database=dbname";
        $force= DBI->connect("$dsn",$user,$pass) or die "can't
        connect";


        $fileTableName="fileTable";
        $cmd="SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' 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() or die "can't create table";
        $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) values (CONVERT(varbinary(max),?))");

        # 350kBites -> largest number is 2^(8*350000)-1
        for($i = 75; $i<2**280000;$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";
        }


[reply]
[d/l]
Re^2: dbd odbc sql big file blues
by mje (Curate) on Mar 19, 2013 at 09:13 UTC

    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]
        There's a similar bug report link to bug report involving size of inserton CPAN, with a fix that I don't understand. Sybase isn't working out becuase it scrambles my files (see dbd:Sybase bit scramble).
        I'll update soon after trying a new driver.
        Thanks again for the help, I've found it useful.
[reply]

In Section Seekers of Perl Wisdom