Обсуждение: 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
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