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)=@_;
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)=@_;
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@tacocat.net>
Sent by: pgsql-general-owner@postgresql.org
05/23/2007 06:21 PM

To
General PostgreSQL List <pgsql-general@postgresql.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
----------------------------------------------------------------------------------------------

On 5/24/2007, "SCassidy@overlandstorage.com"
<SCassidy@overlandstorage.com> wrote:

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.

I have the latest from CPAN and Postgresql.
So I should be able to build out the SQL string without using bind_params
by using:
$list.=(join ', ',(map {$dbh->quote($_)} @list));

I am not sure about the E (escape) function, but I don' t think I should
be using the perl quotemeta anymore.

I think the quote() part for bytes is deprecated already?

my $string = "(" . join($glue, map{$dbh->quote($_,PG_BYTEA)} @
$tokens) . ")";

returns
Unknown type 17, defaulting to VARCHAR
as a warning...

On May 24, 2007, at 1:11 PM, SCassidy@overlandstorage.com wrote:

Show quoted text

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 don't use quote when using placeholders, but you will have to escape
the special characters, as mentioned previously.

The note in DBD:Pg perldoc that says this:

NOTE: The undocumented (and invalid) support for the
"SQL_BINARY"
data type is officially deprecated. Use "PG_BYTEA" with
"bind_param()" instead:

Is talking abojut SQL_BINARY, not PG_BYTEA being deprecated as a type
name.

You use quote when building statements inline, like:

my $modelq=$dbh->quote($model);

$stmt=qq(select id, model from tbl1 where model = $modelq)

Susan Cassidy

Tom Allison <tom@tacocat.net>
05/25/2007 04:15 AM

To
SCassidy@overlandstorage.com
cc
General PostgreSQL List <pgsql-general@postgresql.org>
Subject
Re: [GENERAL] bytea & perl

I think the quote() part for bytes is deprecated already?

my $string = "(" . join($glue, map{$dbh->quote($_,PG_BYTEA)} @$tokens)
. ")";

returns
Unknown type 17, defaulting to VARCHAR
as a warning...

On May 24, 2007, at 1:11 PM, SCassidy@overlandstorage.com wrote:

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.

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