Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
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 wandering the Monastery: (17)
As of 2015-07-01 18:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (16 votes), past polls