ramblings on PHP, SQL, the web, politics, ultimate frisbee and what else is on in my life
back

PostgreSQL is already there!

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



Re: PostgreSQL is already there!

Nice.

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.

Re: PostgreSQL is already there!

"Again there is no clear standard for this."

But there is: SQL 2003 (ISO 9075:2003)

Reposting what I posted on Ulf W's blog the other day:

The question mark syntax is the SQL standard syntax. It applies to both the PREPARE syntax as well as the Call Level Interface. In both cases, parameters are specified as:

dynamic parameter specification ::= question mark

Another indication can be found in the BindParameter routine of the CLI:

BindParameter (
StatementHandle IN INTEGER,
ParameterNumber IN SMALLINT,
InputOutputMode IN SMALLINT,
ValueType IN SMALLINT,
ParameterType IN SMALLINT,
ColumnSize IN INTEGER,
DecimalDigits IN SMALLINT,
ParameterValue DEF ANY,
BufferLength IN INTEGER,
StrLen_or_Ind DEF INTEGER )
RETURNS SMALLINT

see, no name, only ParameterNumber.

(sources: ISO 9075-2:2003, p 177, 178; ISO 9075-3:2003, 124-126)

kind regards,

Roland BOuman

Re: PostgreSQL is already there!

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.

Some extracts from the relevant section are below:

6.4 <value specification> and <target specification>

Function: Specify one or more values, host parameters, SQL parameters, dynamic parameters, or host variables.

Format
<value specification> ::=
<literal> ...
| <host parameter specification>
| <dynamic parameter specification>
| <embedded variable specification>

<simple value specification> ::=
<literal>
| <host parameter name>
| <embedded variable name>

<target specification> ::=
| <host parameter specification>
| <dynamic parameter specification>
| <embedded variable specification>

<simple target specification> ::=
<host parameter specification>
| <SQL parameter reference>
| <column reference>
| <embedded variable name>

<host parameter specification> ::= <host parameter name> [ <indicator parameter> ]

<dynamic parameter specification> ::= <question mark>

<embedded variable specification> ::= <embedded variable name> [ <indicator variable> ]

and in

20.1 <embedded SQL host program>
Function: Specify an <embedded SQL host program>

<embedded variable name> ::= <colon><host identifier>

So, the standard does define how :identifier names are used, and I agree that these are much more useful than ? parameters.

Regards,

Ken

Re: PostgreSQL is already there!

Just looking at the standard, and host parameters look like variables that the "host" provides, not the client application. Things like SQL_STATE are host parameters.

Before you can post a comment please solve the following captcha.
your name