# pdo_pgsql - Thread: https://externals.io/message/15530 - Original archive: https://news-web.php.net/php.internals/15530 - Started by Christopher Kings-Lynne on 2005-03-22T02:36:29+00:00 - Messages: 13 - Reply by email to internals@lists.php.net - More for agents: search with https://externals.io/search.md?q={query} · JSON API at https://externals.io/api · documentation at https://externals.io/llms.txt --- ## #15530 — Christopher Kings-Lynne — 2005-03-22T02:36:29+00:00 URL: https://externals.io/message/15530#15530 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 --- ## #15536 — Edin Kadribasic — 2005-03-22T12:13:23+00:00 URL: https://externals.io/message/15530#15536 · In reply to: #15530 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" To: "php-dev" 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 --- ## #15539 — Christopher Kings-Lynne — 2005-03-22T14:36:50+00:00 URL: https://externals.io/message/15530#15539 · In reply to: #15536 > 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 --- ## #15540 — Dan Scott — 2005-03-22T15:19:58+00:00 URL: https://externals.io/message/15530#15540 · In reply to: #15539 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 wrote: > > […] > > Ooer. How on earth do you simulate the parameter markers? Are they > required to be '?'? Do you deal with dollar quoting, etc.? > > Chris --- ## #15547 — Christopher Kings-Lynne — 2005-03-23T01:01:09+00:00 URL: https://externals.io/message/15530#15547 · In reply to: #15540 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 > wrote: > > > […] --- ## #15548 — Christopher Kings-Lynne — 2005-03-23T01:02:00+00:00 URL: https://externals.io/message/15530#15548 · In reply to: #15540 > 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 --- ## #15549 — Wez Furlong — 2005-03-23T03:24:51+00:00 URL: https://externals.io/message/15530#15549 · In reply to: #15547 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 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: > > […] --- ## #15550 — Christopher Kings-Lynne — 2005-03-23T03:33:34+00:00 URL: https://externals.io/message/15530#15550 · In reply to: #15549 > 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 --- ## #15551 — Wez Furlong — 2005-03-23T03:47:50+00:00 URL: https://externals.io/message/15530#15551 · In reply to: #15550 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 wrote: > > […] > > 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. > > > […] > > Might be necessary. > > Chris --- ## #15553 — Christopher Kings-Lynne — 2005-03-23T03:54:13+00:00 URL: https://externals.io/message/15530#15553 · In reply to: #15551 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 --- ## #15554 — Wez Furlong — 2005-03-23T04:04:57+00:00 URL: https://externals.io/message/15530#15554 · In reply to: #15553 > 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. --- ## #15555 — Jason Sweat — 2005-03-23T04:06:17+00:00 URL: https://externals.io/message/15530#15555 · In reply to: #15547 On Wed, 23 Mar 2005 09:01:09 +0800, Christopher Kings-Lynne 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/ --- ## #15556 — Christopher Kings-Lynne — 2005-03-23T04:11:26+00:00 URL: https://externals.io/message/15530#15556 · In reply to: #15555 > > […] > > > 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