Thread: text storage and parsing errors

For what may be really strange reasons I am trying to store emails
into a database table.  The idea is to take the entire original
message and store it into a table with two columns, an serial primary
key column and a column for the message.

Originally I thought I would just use column type text but changed
that to bytea based on the fact that the dbmail project uses bytea
for reasons of character support.

I'm running into problems with perl throwing an error of :'invalid
input syntax for type bytea'.
I have not yet been able to capture a message for any further testing
because the error tends to destroy the message in process...

the way that I'm using perl is to do a full prepare and execute
statements, which as I understand perl, will do all the character
escaping necessary to store the message.  meaning, If I have
characters like (') or (`) they should be escaped when they are
entered into the SQL parameter.  I am wondering if this is indeed the
case.

But I'm looking for any suggestions or considerations when trying to
d this approach to avoid this kind of text parsing problem.

tom wrote:

> the way that I'm using perl is to do a full prepare and execute
> statements, which as I understand perl, will do all the character
> escaping necessary to store the message.  meaning, If I have
> characters like (') or (`) they should be escaped when they are
> entered into the SQL parameter.  I am wondering if this is indeed the
> case.
>
> But I'm looking for any suggestions or considerations when trying to
> d this approach to avoid this kind of text parsing problem.

Assuming you're using DBD::Pg and a placeholder for the bytea parameter of your
query, you must specify its type explicitly, like this:

use DBD::Pg qw(:pg_types);
[...]
$s=$dbh->prepare("INSERT INTO the_table(contents) VALUES(?)");
$s->bind_param(1, $the_data, { pg_type => DBD::Pg::PG_BYTEA });
$s->execute;

Hope this helps,

--
 Daniel
 PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

    tom wrote:

> the way that I'm using perl is to do a full prepare and execute
> statements, which as I understand perl, will do all the character
> escaping necessary to store the message.  meaning, If I have
> characters like (') or (`) they should be escaped when they are
> entered into the SQL parameter.  I am wondering if this is indeed the
> case.
>
> But I'm looking for any suggestions or considerations when trying to
> d this approach to avoid this kind of text parsing problem.

Assuming you're using DBD::Pg and a placeholder for the bytea parameter of your
query, you must specify its type explicitly, like this:

use DBD::Pg qw(:pg_types);
[...]
$s=$dbh->prepare("INSERT INTO the_table(contents) VALUES(?)");
$s->bind_param(1, $the_data, { pg_type => DBD::Pg::PG_BYTEA });
$s->execute;

Hope this helps,

--
 Daniel
 PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org