Thread: using bytea vartype non-prepared statements

I would like to build a sql statement in perl and execute it without
binding parameters if possible.
But I also need to use bytea variable type because I'm storing two
byte characters (Big5, utf8...)

In case of using a varchar and ASCII I would simply write a sql
statement like this:

INSERT INTO foo values('bar');
by building the SQL ...
my $sql = "INSERT INTO foo VALUES('$string')";
$dbh->do($sql);

I'm not sure if I can even do this if the underlying table has a
field of type bytea.

I was reading in the archives an entry which said I may not be able
to do this because the variable $string might contain null characters...

"If you are asking why the bind has to happen at all, it is partly
because
libpq does not support returning the data types yet, and partly because
unlike most other data types, it is very important that DBD::Pg (and
libpq,
and the backend) be told explicitly that a binary string is being used,
so that the length can be sent, as a null character may not represent
the
end of the string."

In order to address this I was using a SQL statement previously where
I knew that the number of parameters was only two and I could write
the perl to handle this:
     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 don't actually know before hand just how many
variables I need to bind.  Rather, I don't know at compile time.

Using these examples I am not sure how I can incorporate building a
SQL string like the first INSERT statement which will be able to
correctly handle byte data.
Or is this a case of my reading too much into it and just leaving
things up to the "magic" of the libraries to sort it out?

Tom Allison wrote:
> In order to address this I was using a SQL statement previously where I
> knew that the number of parameters was only two and I could write the
> perl to handle this:
>     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 don't actually know before hand just how many variables
> I need to bind.  Rather, I don't know at compile time.

I always have a small wrapper function that builds my queries for me.

Assuming you have a list of values and their types (or a list of
structures)...
1. Build a list of comma-separated qmarks: join(',', map {'?'} @params)
2. loop through your params looking up the corresponding pg_type for
your internal type - bind the param accordingly
3. execute

Or am I missing something here?

--
   Richard Huxton
   Archonet Ltd