planets

links

buttons

So I took my concerns over prepared statements to the #postgresql IRC channel on freenode. I pointed out that I think there should be a way to get server side handling of placeholders in statements but without the additional overhead of a second round trip or the drawbacks of overly generic query plans due to not being able to use the parameters in the planning stage. Some people have noted that this feature is available in MSSQL. It also seems to be available in PostgreSQL in the form of PQexecParams and its even exposed in ext/pgsql, though it's only used for sequence reading in PDO_PGSQL. It would be really cool if it could be used when emulating prepared statements (probably with a PostgreSQL PDO attribute).

As you may know PostgreSQL uses $[name] to denote placeholders, while Oracle uses :[name] and MySQL uses the ? syntax that I think ODBC used first. Again there is no clear standard for this. I prefer the approach with a name. It makes for more readable code and makes it possible to use the same parameter multiple times in a statement. However as more and more libraries are build on top of PDO, even if I only want to ever work with one RDBMS, when using these libraries, I currently have no choice but to let PDO parse and decide on its own if to rewrite the query. So it would be nice if PDO could also have a mode where it does not attempt to parse and rewrite placeholders at all.

UPDATE [27/06/2008]:
Expanded the request to have PDO provide a mode to just pass through the prepared statement.

Comments

One thing I did notice is though whilst PQexecParams makes a distinction between text & binary parameters, the pg_query_params() provides no way of telling PQexecParams().

Even in PHP6, where strings will have the distinction of being binary, or unicode(text) I guess it wouldn't be able to infer that for backward compatibility reasons.

I don't think PDO will rewrite SQL if it determines the parameterised SQL is using the same syntax as the databas supports. Can't see how it could work otherwise unless all database vendors agree on a/the standard.

Actually named (rather than ?) ARE defined in the SQL standard, for SQL embedded in host languages such as COBOL or FORTRAN or C and others.
The syntax describes how :name parameters can be used instead of ? parameters. This can be found in ISO/IEC 9075-2:2003 (E), aka SQL — Part 2: Foundation.