Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Re^3: dbd odbc sql big file blues

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


in reply to Re^2: dbd odbc sql big file blues
in thread dbd odbc sql big file blues

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!


Comment on Re^3: dbd odbc sql big file blues
Download Code
Re^4: dbd odbc sql big file blues
by mje (Curate) on Mar 19, 2013 at 16:55 UTC

    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

      Thanks a bunch for helping me with this; So I'm using unixODBC, FreeTDS, and DBD::ODBC. As I understand it FreeTDS is the ODBC Driver, correct? Also are there any other differences between the packages we are using?

      Thanks a bunch for helping me with this; So I'm using unixODBC, FreeTDS, and DBD::ODBC. As I understand it FreeTDS is the ODBC Driver, correct? Also are there any other differences between the packages we are using?

        sorry this was an accidental repeat.

Re^4: dbd odbc sql big file blues
by CaptainDaddy (Initiate) on Mar 20, 2013 at 18:51 UTC

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


      Glad your working now. You've totally changed your stack now as you are now using a different Perl module and you're not using the freeTDS ODBC driver any more. I tried the freeTDS ODBC driver and got the same problem you did.

        One strange thing: I'm using the servername in my DSN that I defined in freetds.conf. So am I or aren't I using freeTDS with dbd::Sybase?
        Thanks.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (11)
As of 2014-12-21 12:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (104 votes), past polls