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

On 29 September 2010 19:15, Peter Bex <Peter.Bex@xs4all.nl> wrote:

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.

Okay, I understand what's happening. But does the planner need to
understand the type of literals in the select list if it's not used
anywhere else?

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

On Wed, Sep 29, 2010 at 07:33:53PM +0100, Thom Brown wrote:

Okay, I understand what's happening. But does the planner need to
understand the type of literals in the select list if it's not used
anywhere else?

Fields sent back to the client also carry their type with them.
There's no "unknown" type (and it wouldn't be very useful in any
case, because how would you go about displaying an unknown type?)

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

On 29 September 2010 19:41, Peter Bex <Peter.Bex@xs4all.nl> wrote:

On Wed, Sep 29, 2010 at 07:33:53PM +0100, Thom Brown wrote:

Okay, I understand what's happening.  But does the planner need to
understand the type of literals in the select list if it's not used
anywhere else?

Fields sent back to the client also carry their type with them.
There's no "unknown" type (and it wouldn't be very useful in any
case, because how would you go about displaying an unknown type?)

There is an unknown type...

test=# create table stuff (id serial);
NOTICE: CREATE TABLE will create implicit sequence "stuff_id_seq" for
serial column "stuff.id"
CREATE TABLE

test=# insert into stuff select c from generate_series(1,100) x(c);
INSERT 0 100

test=# PREPARE meow(unknown) AS
SELECT * from stuff where id < $1;
PREPARE
test=# execute meow(3);
id
----
1
2
(2 rows)

test=# PREPARE bark(unknown) AS
SELECT * from stuff where id < $1;
PREPARE
test=# execute bark('3'::unknown);
id
----
1
2
(2 rows)

The docs specifically make mention of it in the case of PREPARE.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Peter Bex <Peter.Bex@xs4all.nl> writes:

On Wed, Sep 29, 2010 at 07:33:53PM +0100, Thom Brown wrote:

Okay, I understand what's happening. But does the planner need to
understand the type of literals in the select list if it's not used
anywhere else?

Fields sent back to the client also carry their type with them.
There's no "unknown" type (and it wouldn't be very useful in any
case, because how would you go about displaying an unknown type?)

Well, actually there *is* an "unknown" type (OID 705), which is what
will be reported if there's a literal of unresolved type in the SELECT
list. That's how come you can do

regression=# select 'foo' as meow;
meow
------
foo
(1 row)

However, the issue here is not the output but the input: PREPARE is
complaining that the *input* parameter $1 has no determinate type.
If PREPARE doesn't know it, then the client isn't going to know it
either, and so it would be hard for the client to know what to send
to execute the statement.

regards, tom lane

On 29 September 2010 20:02, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Peter Bex <Peter.Bex@xs4all.nl> writes:

On Wed, Sep 29, 2010 at 07:33:53PM +0100, Thom Brown wrote:

Okay, I understand what's happening.  But does the planner need to
understand the type of literals in the select list if it's not used
anywhere else?

Fields sent back to the client also carry their type with them.
There's no "unknown" type (and it wouldn't be very useful in any
case, because how would you go about displaying an unknown type?)

Well, actually there *is* an "unknown" type (OID 705), which is what
will be reported if there's a literal of unresolved type in the SELECT
list.  That's how come you can do

regression=# select 'foo' as meow;
 meow
------
 foo
(1 row)

However, the issue here is not the output but the input: PREPARE is
complaining that the *input* parameter $1 has no determinate type.
If PREPARE doesn't know it, then the client isn't going to know it
either, and so it would be hard for the client to know what to send
to execute the statement.

We'll have to think of ways to work round this then as it's for a
database class in a common library we're building.

Thanks Tom

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

-----Original Message-----
From: Thom Brown [mailto:thom@linux.com]
Sent: Wednesday, September 29, 2010 2:08 PM
To: PGSQL Mailing List
Subject: Prepared statements and unknown types

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.

This appears to be allowed for MySQL and SQL Server.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Sorry, for being late to this conversation.
Possibly it works for SQL Server, because SS has SQL_VARIANT data type
(kind of "anytype").

Regards,
Igor Neyman

On 30 September 2010 14:36, Igor Neyman <ineyman@perceptron.com> wrote:

-----Original Message-----
From: Thom Brown [mailto:thom@linux.com]
Sent: Wednesday, September 29, 2010 2:08 PM
To: PGSQL Mailing List
Subject: Prepared statements and unknown types

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.

This appears to be allowed for MySQL and SQL Server.

Sorry, for being late to this conversation.
Possibly it works for SQL Server, because SS has SQL_VARIANT data type
(kind of "anytype").

Thanks for the info Igor. It's not really going to be a big issue,
just something we'll have to bear in mind.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935