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
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
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
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
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:
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
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:
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: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
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:
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:
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: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
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
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:
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 :PPrepared query parameters are indicated with $n syntax in PostgreSQL,
just to make things tricky :Die:
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 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
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.
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/
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
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