Re: Prepared statements and unknown types

On Wed, Sep 29, 2010 at 07:08:22PM +0100, Thom Brown wrote:
> Could someone explain why the following doesn't work?
>
> test=# PREPARE meow(unknown) AS
> test-# SELECT $1 as meow;
> ERROR: could not determine data type of parameter $1
>
> The problem is that using PDO in PHP, prepared statements aren't
> possible if values are used instead of columns in the select list.

The type is always string for data that's sent; it's converted to
an appropriate type when the destination of the parameter is determined.
If you know the type, you can do

PREPARE meow(text) AS
SELECT $1 as meow;

or

PREPARE meow(unknown) AS
SELECT $1::text as meow;

You can also send a parameter as a specific type using the C interface,
but that requires support from the language/library you're using.

> This appears to be allowed for MySQL and SQL Server.

I don't know how they handle that. Perhaps they try to read your mind.
Perhaps PHP adds some kind of type conversion for types it knows for
those two interfaces.

Cheers,
Peter
--
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
is especially attractive, not only because it can be economically
and scientifically rewarding, but also because it can be an aesthetic
experience much like composing poetry or music."
-- Donald Knuth