Re: [sqlite] problem with blobs (perl code) workaround

[Jim Dodgen]

thanks for the help, 

I tried one ot the workarounds noted in 
http://rt.cpan.org/NoAuth/Bug.html?id=14595 
which had you force the data type to SQL_BLOB
this makes things work!! horray!!

I included the complete test program for reference to others



---- code snippit ----
use DBI qw(:sql_types);

print "original slurped file size $fn=".length($data)."\n";;
{
  my $sth = $dbh->prepare("INSERT or replace INTO foo (nm, val) VALUES (?, ?)");
  $sth->bind_param(1, $fn);
  $sth->bind_param(2, $data, {TYPE => SQL_BLOB});
  $sth->execute();
  $sth->finish;
}


--- complete test program ---
#!/usr/bin/perl -w
#
# jim dodgen 2005
use Carp;
use DBI;
use DBI qw(:sql_types);
use strict;

my %attr = (PrintError => 1,
            RaiseError => 0,
            AutoCommit => 0);
my $dbh = DBI->connect("dbi:SQLite:slurp.db","","",\%attr);
if (!defined($dbh))
{
  croak("could not connect to db");
}
{
  my $sth = $dbh->prepare("drop table foo");
  if (defined $sth)
  {
    $sth->execute();
    $sth->finish;
  }
}
{
  my $sth = $dbh->prepare("create table foo (nm, val, primary key (nm))");
  $sth->execute();
  $sth->finish;
}

undef $/;
my ($fn) = @ARGV; # name of big file
open (IN, $fn);
my $data = <IN>;  # slurp complete file into variable
close IN;

print "original slurped file size $fn=".length($data)."\n";;
{
  my $sth = $dbh->prepare("INSERT or replace INTO foo (nm, val) VALUES (?, ?)");
  $sth->bind_param(1, $fn);
  $sth->bind_param(2, $data, {TYPE => SQL_BLOB});
  $sth->execute();
  $sth->finish;
}
undef $data; # JUST TO FREE SOME SPACE

my $max = $dbh->selectrow_array("SELECT MAX(LENGTH(val)) FROM foo");
$dbh->{LongReadLen} = $max+10;
print "largest BLOB $max\n";

{
  my $sth = $dbh->prepare("select nm, val from foo where nm = ?");
  my $stat = $sth->execute($fn);
  my ($nm, $out) = $sth->fetchrow_array;
  $sth->finish;
  
  print "size of $nm BLOB returned from query ".length($out)."\n";
  open (NEW1, ">out_".$fn);
  binmode NEW1;
  print NEW1 $out;
  close NEW1;
}
$dbh->commit;
$dbh->disconnect;