Everything

php

pdo_pgsql

php.internals

Christopher Kings-Lynne

21 years ago

Hi,

If anyone on this list is the maintainer for ext/pdo_pgsql, could they
please reply to me?

I am wondering how much of the PDO interface pdo_pgsql supports, and
where I can add the missing support (eg. SQLSTATE).

Chris

Edin Kadribasic

21 years ago

Hi,

Most of the PDO interface is supported in pgsql driver. However we do not
use native pgsql prepare/execute but PDO's built in emulation for it. If you
could look into how could native api be used in pdo_pgsql that would be
great.

Btw. now you have the commit access to the module.

Edin

----- Original Message -----
From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
To: "php-dev" <internals@lists.php.net>
Sent: Tuesday, March 22, 2005 3:36 AM
Subject: [PHP-DEV] pdo_pgsql

Christopher Kings-Lynne

21 years ago

> Most of the PDO interface is supported in pgsql driver. However we do not
> use native pgsql prepare/execute but PDO's built in emulation for it. If you
> could look into how could native api be used in pdo_pgsql that would be
> great.
>

Ooer.  How on earth do you simulate the parameter markers?  Are they
required to be '?'?  Do you deal with dollar quoting, etc.?

Chris

Dan Scott

21 years ago

Hi Chris:

As documented in http://www.php.net/manual/en/function.pdo-prepare.php:

"The SQL statement can contain zero or more named (:name) or question
mark (?) parameter markers for which real values will be substituted
when the statement is executed."

Anything beyond this would be an extension to the PDO spec that would
have to be implemented within the actual pdo_pgsql driver.

Dan

On Tue, 22 Mar 2005 22:36:50 +0800, Christopher Kings-Lynne
<chriskl@familyhealth.com.au> wrote:

Christopher Kings-Lynne

21 years ago

Hi Dan,

What I mean is, will PDO happily parse this PostgreSQL statement and
substitute the two variables correctly?

SELECT '?', ? from "?" where ? = $_$?$_$;

Chris

Dan Scott wrote:

Wez Furlong

21 years ago

Depends on how you define "correctly" ;-)
Chances are that it will raise an error.

I'm not sure if this should really be considered a problem; that's a
really obtuse SQL statement.  As a non-pgsqler, I have a hard time
figuring out where the variables are.

I welcome native postgres prepare/bind/execute support, and don't mind
adjusting the pdo_sql_parser.re code to cater for rewriting ? or
:named style substitutions into postgres style; it already includes
some logic for rewriting ? to :named and vice-versa, so the
modifications should be reasonably simple.

--Wez.


On Wed, 23 Mar 2005 09:01:09 +0800, Christopher Kings-Lynne
<chriskl@familyhealth.com.au> wrote:

Christopher Kings-Lynne

21 years ago

> Depends on how you define "correctly" ;-)
> Chances are that it will raise an error.
>
> I'm not sure if this should really be considered a problem; that's a
> really obtuse SQL statement.  As a non-pgsqler, I have a hard time
> figuring out where the variables are.

Well, anything that's not perfect is a problem :)

Dollar quoting is new in pgsql 8, it's sort of like 'heredocs'.
Basically it's to avoid having to escape ' in your function bodies, or
other strings, eg:

CREATE FUNCTION test(int) RETURNS int AS
$tagname$
SELECT 23 FROM table WHERE a > $1 AND b = 'hello'
$tagname$ LANGUAGE SQL;

The 'tagname' is the dollar quote identifier, and everything from that
dollar quote opener to the end one will be considered a string literal.
Notice I do not have to escape the '' in the function body.

It a perfectly reasonable thing, even encouraged thing to do.  We'll
just have to upgrade the SQL parser to cope :P

Prepared query parameters are indicated with $n syntax in PostgreSQL,
just to make things tricky :D

ie:

SELECT * FROM blah WHERE a = $1 AND b = $2;

The $1 will get replaced with first param, $2 with second, etc.

Maybe support should be added for this style of notation?  It'd be
pretty easy I guess.

> I welcome native postgres prepare/bind/execute support, and don't mind
> adjusting the pdo_sql_parser.re code to cater for rewriting ? or
> :named style substitutions into postgres style; it already includes
> some logic for rewriting ? to :named and vice-versa, so the
> modifications should be reasonably simple.

Might be necessary.

Chris

Wez Furlong

21 years ago

The problem with using $ in the query is that you'd need to \ escape
them; remember that PHP uses $ to interpolate its own variables.

IMO, adding $ as another way to name parameters is overkill, as we
already have :named style.

--Wez.


On Wed, 23 Mar 2005 11:33:34 +0800, Christopher Kings-Lynne
<chriskl@familyhealth.com.au> wrote:

Christopher Kings-Lynne

21 years ago

Wez Furlong wrote:

> The problem with using $ in the query is that you'd need to \ escape
> them; remember that PHP uses $ to interpolate its own variables.
>
> IMO, adding $ as another way to name parameters is overkill, as we
> already have :named style.

So you are proposing that we rewrite :name and ? params into $n format
and use pgsql's native prepare/bind functions to do the binding?  Or we
just abandon native binding on pgsql?

BTW, isn't the current sql parser broken for mysql backquoting anyway?

eg: SELECT * FROM `my_weird ? table`;

And in case you're wondering, yes that is a perfectly legal mysql table
name.

Chris

Wez Furlong

21 years ago

> So you are proposing that we rewrite :name and ? params into $n format
> and use pgsql's native prepare/bind functions to do the binding?

Yes.

> BTW, isn't the current sql parser broken for mysql backquoting anyway?
>
> eg: SELECT * FROM `my_weird ? table`;
>
> And in case you're wondering, yes that is a perfectly legal mysql table
> name.

Probably, but again, I'm not too worried about this right now.  It's
impossible to cover everything 100%; I think a reasonable way out for
people that have to work with such an atrocity is to set an attribute
to tell PDO not to bother parsing the query.

--Wez.

Jason Sweat

21 years ago

On Wed, 23 Mar 2005 09:01:09 +0800, Christopher Kings-Lynne
<chriskl@familyhealth.com.au> wrote:

> Hi Dan,
>
> What I mean is, will PDO happily parse this PostgreSQL statement and
> substitute the two variables correctly?
>
> SELECT '?', ? from "?" where ? = $_$?$_$;

Oracle has bound variables, but you can't use a bind variable for a
table or field in the sql statement.

Regards,
Jason
http://blog.casey-sweat.us/

Christopher Kings-Lynne

21 years ago

>>SELECT '?', ? from "?" where ? = $_$?$_$;
>
>
> Oracle has bound variables, but you can't use a bind variable for a
> table or field in the sql statement.

And I'm not using them for pgsql in the statement above.

My point was that the parser should ignore '?', "?" and $_$?$_$ which
are the three ways of quoting in PostgreSQL.

Chris

Christopher Kings-Lynne

21 years ago

> Anything beyond this would be an extension to the PDO spec that would
> have to be implemented within the actual pdo_pgsql driver.

Also, I guess any extension to PDO would be silly, as it defeats he
point of a standard interface...

Chris