Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

dbd odbc sql big file blues

by Anonymous Monk
on Mar 18, 2013 at 23:33 UTC ( #1024176=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hello all, I've recently began inserting files into a sql server 2008 R2 db from perl using dbi, dbd:odbc, and freetds. I cand connect, query, and insert file ( varbinary(max), DBI:VARBINARY). Most of the time. However, when the files get to a certain size (I think that's the cause) I get the 'HY090 invalid string or buffer length error' rejection. If someone has a pointer or two I would really appreciate it; I'll try to get my code up here soon.

Comment on dbd odbc sql big file blues
Re: dbd odbc sql big file blues
by CaptainDaddy (Initiate) on Mar 19, 2013 at 01:47 UTC

    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.

      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!

      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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1024176]
Approved by NetWallah
Front-paged by NetWallah
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (10)
As of 2014-09-30 13:23 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (369 votes), past polls