Re: bytea & perl

Hi,

First, I would advise never using " insert into xx values (y,x)" without
explicitly naming the columns; same for select statements - never use
select * (a table change can mess things up).

By the way, I just noticed in the release notes for the very latest couple
of versions of DBD:Pg that some placeholder changes were made. You might
want to check the release notes and your version of DBD:Pg about your
placeholder issues.

You might want to try using $dbh->quote instead of what you are using for
quoting values, since it is database-specific. Something like:
my ($stmt, $list, @data);
my @list=("it's", 'a', 'quick', 'brown', 'fox', 'that', 'jumped',
'over');
$list.=(join ', ',(map {$dbh->quote($_)} @list));

$stmt=<<"EOF";
select id1, txtval1 from test1 where txtval1 in ($list)
EOF
print "stmt:\n$stmt\n";

$sth=$dbh->prepare($stmt) || errexit("bad prepare for stmt $stmt,
error: $DBI::errstr");
$rc=$sth->execute() || errexit("can't execute statement:\n$stmt; DB
error: $DBI::errstr");
while (@data = $sth->fetchrow_array) {
foreach (@data) { $_='' unless defined}
next if ($data[0] eq '');
print '',(join "\t",@data),"\n";
}
#check for problems with premature termination
errexit($sth->errstr) if $sth->err;

This produces the output:
stmt:
select id1, txtval1 from test1 where txtval1 in ('it''s', 'a',
'quick', 'brown', 'fox', 'that', 'jumped', 'over')

24 quick
25 brown
26 fox

I currently have PostgreSQL 7.4, DBI 1.46, DBD:Pg 1.32. You may have
newer versions with different behavior.

Here is some simple bytea stuff that works (a simple test I was playing
with a while back for storing images, and displaying them back via the
web). I still had to escape certain characters, not just tell DBD that I
was using bytea:

my $infile='/var/www/html/test_scr_cap.png'; #image file
my ($buf, $imgdata);
open (IMG, "<$infile") or die "Cannot open $infile, $!";
while (read(IMG,$buf,512)) {
$imgdata.=$buf;
}
close IMG;
print "Size of imgdata is ",length($imgdata),"\n";

my $stmt=<<"EOF";
INSERT into imagedata (idval, imagedata) values (1, ?)
EOF
$sth=$dbh->prepare($stmt) || errexit("bad prepare for stmt $stmt,
error: $DBI::errstr");
my $rc=$sth->bind_param(1, escape_bytea($imgdata), { pg_type =>
DBD::Pg::PG_BYTEA });

$rc=$sth->execute() || errexit("can't execute statement:\n$stmt\nreturn
code $rc: DB error: $DBI::errstr");
$dbh->commit();

#retrieve the data:
$stmt=<<"EOF";
SELECT idval, imagedata from imagedata where idval = 1
EOF
my $outfile='/var/www/html/test_scr_cap_out.png';
open (IMG, ">$outfile") or die "Cannot open $outfile, $!";
execute_db_statement($stmt, __LINE__);
my ($idval, $imagedata_read);
$sth->bind_col(1, \$idval);
$sth->bind_col(2, \$imagedata_read);
$sth->fetch;
print "size of data read is ",length($imagedata_read),"\n";
print IMG $imagedata_read;
close IMG;
print "Output file is $outfile\n"; #when viewed again, image looks
fine.

sub escape_bytea {
my ($instring)=(at)_;
my $returnstring=join ('',map {
my $tmp=ord($_);
($tmp >= 32 and $tmp <= 126 and $tmp != 92) ? $_ :
sprintf('\%03o',$tmp);} split (//,$instring));
return $returnstring;
} # end sub escape_bytea
sub execute_db_statement {
#this subroutine will prepare and execute a statement for the database,
and errexit if it fails either step
my ($statement, $lineno)=(at)_;
my ($rc);
#get basic machine info
$sth=$dbh->prepare($statement) || errexit("bad prepare for stmt
$statement at line $lineno, error: $DBI::errstr");
$rc=$sth->execute() || errexit("can't execute
statement:\n$statement\n at line $lineno, ", "return code $rc: DB
error: $DBI::errstr");
} # end sub execute_db_statement

Hope this helps.

Susan Cassidy

Tom Allison <tom(at)tacocat(dot)net>
Sent by: pgsql-general-owner(at)postgresql(dot)org
05/23/2007 06:21 PM

To
General PostgreSQL List <pgsql-general(at)postgresql(dot)org>
cc

Subject
[GENERAL] bytea & perl

I've been running into problems with some characters that I believe
can be solved using bytea variable type instead of varchar()
I'm picking up data from email and trying to put it into a table.

I'm trying to "merge" two different types of SQL and I'm really not
sure how this can be done...

I had a previous version of my SQL that looked like:
my $sth = $dbh->prepare("insert into quarantine values (?,?)");
$sth->bind_param(1, $idx);
$sth->bind_param(2, $text, { pg_type => DBD::Pg::PG_BYTEA });
$sth->execute();
In this case I was inserting an entire email content into the second
parameter as type bytea. Originally I was doing this as text and
running into problems inserting records when there were weird characers.
I want to be able to use the BYTEA data type for inserting records
into another SQL that looks like:

my $sql=<<SQL;
insert into tokens (token)
select values.token
from (values TOKEN_LIST_STRING ) as values(token)
left outer join tokens t using (token)
where t.token_idx is null
SQL

NOTE: TOKEN_LIST_STRING is replaced with an escaped list of values of
the format:
VALUES ( ('the'), ('quick'), ('brown'), ('fox'), ('jumped')) as values
(token)
use perl regex.
The details are something like:
my $string = "(E'" . join($glue, map{quotemeta } @$tokens) . "')";
Which will return something like (E'that\s') and (E'char\:escaping\(is
\)\"hard\"') in place of the ('the')

And this too is failing to insert on some weird characters.
I'm not sure which ones because when it does fail, it tends to be one
of 100's and I haven't been able to write a script to test each one.
And I'm not convinced that is the correct way to procede.

Can someone help me become a postgres guru?

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

----------------------------------------------------------------------------------------------
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------