Re: bytea & perl
| От | SCassidy@overlandstorage.com |
|---|---|
| Тема | Re: bytea & perl |
| Дата | |
| Msg-id | OF1B3554D3.F91A5F48-ON882572E5.0059D9D0-882572E5.005E74A6@overlandstorage.com обсуждение исходный текст |
| Ответ на | bytea & perl (Tom Allison <tom@tacocat.net>) |
| Ответы |
Re: bytea & perl
(<tom@tacocat.net>)
Re: bytea & perl (Tom Allison <tom@tacocat.net>) |
| Список | pgsql-general |
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 |
|
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
----------------------------------------------------------------------------------------------
В списке pgsql-general по дате отправления: