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

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

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

    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]

        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?

[reply]

        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?

[reply]

      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]

        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.

[reply]

In Section Seekers of Perl Wisdom