Design Decisions in the PDO Driver for SQL Server
I’ve recently talked with the SQL Server Driver for PHP team about some of the harder design decisions they have had to make while building the PDO interface for the sqlsrv driver. Some of these challenging decisions stemmed from the fact that the team had to interpret the PDO functional specification in places where the expected behavior wasn’t clearly defined. Others challenges arose in trying to match the behavior of PDO interfaces for other databases. In many cases, each PDO interface has slightly different implementations depending on the database, which meant that the team had to make choices about what they thought was best. I’ll walk you through some of the team’s design decisions in this post. Since the release of this driver is a beta release, I’m hoping this post will generate feedback from people who feel passionately about decisions the team has made. If there is enough feedback, there is still time to make changes to the driver before the final, supported version is released.
I think taking a quick look at the high-level architecture of the PDO driver might provide good background to some of the team’s design decisions. The diagram below shows that the 1.1 version of the sqlsrv driver was a layer on top of SQL Server Native Client (also known as SNAC, the MS implementation of ODBC). For the 2.0 version, the 1.1 code base was slightly re-worked so that both the native sqlsrv driver and the PDO driver could leverage a core functional layer. This allows the team to address core functional issues (that affect both drivers) separately from API issues for each driver.
Now let’s look at challenges the team faced around some PDO methods, attributes, and streaming.
PDO Methods
PDO::__construct: The PDO functional specification calls for allowing PDO constants in the $driver_options parameter. However, because of the way the sqlsrv driver is designed, some attributes cannot be set at the time a connection is being established. As a result, the team decided to only support custom attributes (as defined here: Connection Options) in the $driver_options parameter.
PDO::lastInsertId: The PDO functional specification does not define the failure behavior for this method. (i.e. What should happen if this method fails?) Additionally, the team found that some other PDO implementations don’t throw an exception even when the PDO::ATTR_ERRMODE is set to PDO::ERRMODE_EXCEPTION. As a result, the team opted for compatibility with other drivers: exceptions are suppressed when calling PDO::lastInsertId and an empty string (“”) is returned when this method call fails. This is consistent with at least some other PDO drivers that are available.
PDOStatement::bindParam: The team had difficulty in interpreting the PDO functional specification for this method. As a result, the following behavior was implemented:
- For a stored procedure with an INPUT parameter: If a type is specified for the parameter within the call to PDOStatement::bindParam, it is ignored in favor of the type of the variable that is passed to the method.
- For a stored procedure with an INPUT/OUTPUT parameter: If a type is specified for the parameter value within the call to PDOStatement::bindParam, then the type of the variable that is passed to the method must be compatible with the type specification.
- For a stored procedure with an OUTPUT parameter: If a type is specified for the parameter value within the call to PDOStatement::bindParam, then the variable will be converted to the specified type (e.g. PDO::PARAM_INT will cause the $var to become an integer upon return of the output parameter).
PDO Attributes
PDO::ATTR_AUTOCOMMIT: Because the PDO driver is built on top of SNAC (which has its own APIs for handling transactions), setting the PDO::ATTR_AUTOCOMMIT attribute to false to begin a transaction can put the PDO driver in a state that is out of sync with the SNAC driver. The result is that guarantees about the success or failure of the transaction cannot be made. As a result, the team decided to not support the PDO::ATTR_AUTOCOMMIT attribute. Instead, the recommended way to execute transactions is to use the PDO::beginTransaction, PDO::commit, and/or the PDO::rollback methods.
PDO::ATTR_TIMEOUT: It is unclear whether this attribute should apply to a connection or to a query. As a result, the team decided to not support this attribute. Instead, the LoginTimeout option in the $driver_options parameter of PDO::__construct is supported to govern connection timeouts, and the PDO::SQLSRV_ATTR_QUERY_TIMEOUT attribute is supported in the PDOStatement::setAttribute and PDO::prepare methods to govern query timeouts.
Streaming
The native sqlsrv driver leverages PHP streams to stream data to and from the database. However, the PDO interface does not provide an obvious way to stream data from the database (within the limitations of the ODBC API). As a result, the team decided (for now) not to support the ability to stream data from the database. The team has, in fact, figured out multiple ways in which they could support streaming data from the database, each with its own limitations and caveats. Since it is not clear how important this is to developers, the team is looking for suggestions about how (or if) this feature should be surfaced. (For more information, see Comparing the SQLSRV and PDO APIs.)
Again, the team is looking for feedback on these design decisions. You can comment below, comment on the SQL Server Driver for PHP Team Blog, or comment in the forum. All feedback is greatly appreciated.
Thanks.
-Brian