Beefy Boxes and Bandwidth Generously Provided by pair Networks vroom
Syntactic Confectionery Delight
 
PerlMonks  

Re: dbd odbc sql big file blues

by CaptainDaddy (Initiate)
on Mar 19, 2013 at 01:47 UTC ( #1024182=note: print w/ replies, xml ) Need Help??


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;

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;

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.


Comment on Re: dbd odbc sql big file blues
Select or Download Code
Re^2: dbd odbc sql big file blues
by GrandFather (Cardinal) 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;
    True laziness is hard work

      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;

      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!

        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);

        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

        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>

        > 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";
        }


Re^2: dbd odbc sql big file blues
by mje (Deacon) 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);

    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']];
      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.
        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.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (12)
As of 2014-04-19 05:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (478 votes), past polls