OLE-DB Functions (PDO_OLEDB)

Introduction

PDO_OLEDB is a driver that implements the PHP Data Objects (PDO) interface to enable access from PHP to Microsoft SQL Server and other databases on Windows through the OLE-DB interface.

PDO_OLEDB DSN

Description

The PDO_OLEDB Data Source Name (DSN) is composed of the following elements:

DSN prefix


The DSN prefix is
oledb: if an ADO connection string is used, or mssql: to connect to SQL Server using syntax similar to other PDO drivers.

host


The hostname on which the database server resides.

dbname


The name of the database.

Examples

Example #1 PDO_OLEDB DSN examples

The following examples show a PDO_OLEDB DSN for connecting to Microsoft SQL Server, Microsoft Access (through Jet), Microsoft Indexing Service, and an LDAP server through the Active Directory Services Data Services Objects driver:

mssql:host=localhost;dbname=testdb
oledb:Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\chn49a1.mdb;
oledb:Provider=MSIDXS
oledb:Provider=ADSDSOObject

 

Predefined Constants

The constants below are defined by this driver, and will only be available when the extension has been either compiled into PHP or dynamically loaded at runtime. In addition, these driver-specific constants should only be used if you are using this driver. Using oledb-specific attributes with the mysql driver may result in unexpected behaviour. PDO::getAttribute() may be used to obtain the PDO_ATTR_DRIVER_NAME attribute to check the driver, if your code can run against multiple drivers.

Attributes in the PDO_OLEDB driver are inherited. For example, if PDO::OLEDB_ATTR_ENCODING is set on a PDO, PDOStatement created from that object will use the same encoding unless a different value is given in the $drive_option array passed to PDO::prepare(). Likewise, bound columns and bound parameters use the same encoding as the PDOStatement object unless a different value is passed to PDOStatement::bindColumn() or PDOStatement::bindParam().

PDO::OLEDB_ATTR_USE_ENCRYPTION (bool)

If this attribute is set to TRUE on a PDO, the data transfer between the SQL-Server client and the server will be encrypted. Once the connection has been established, this attribute cannot be changed.

PDO::OLEDB_ATTR_USE_AUTO_TRANSLATION (bool)

If this attribute is set to TRUE on a PDO, the SQL-Server driver automatically translate between the codepage used by the client machine and the codepage used by the server machine.

Since in an web-application, the encoding of text sent to the web-browser is independent of the codepage setting on the machine, you most likely will not need to use this feature.

PDO::OLEDB_ATTR_USE_INTEGRATED_AUTHENTICATION (bool)

If this attribute is set to TRUE on a PDO, the SQL-Server driver will connect to the server using Windows Authentication. The user name and password passed to the PDO constructor will be ignored.

PDO::OLEDB_ATTR_USE_CONNECTION_POOLING (bool)

If this attribute is set to TRUE on a PDO, the OLE-DB connection pooling is enabled.

PDO::OLEDB_ATTR_GET_EXTENDED_METADATA (bool)

If this attribute is set to TRUE on a PDO or a PDOStatement, SQL-Server will send extra meta-data about a rowset. PDOStatement::getColumnMeta() will return incomplete information in if this attribute is set to FALSE (the default).

PDO::OLEDB_ATTR_CONVERT_DATATIME (bool)

If this attribute is set to TRUE on a PDO or a PDOStatement, date/time will be in the format “YYYY-MM-DD hh:mm:ss”. Otherwise date/time conversion will be performed by the OLE-DB driver.

This attribute is TRUE by default.

PDO::OLEDB_ATTR_APPLICATION_NAME (string)

This attribute provides SQL Server with the name of the client application. It will appear, for example, in SQL Server Manager’s Activity Monitor. Once the connection has been established, this attribute cannot be changed.

PDO::OLEDB_ATTR_ENCODING (string)

This attribute determines which text encoding is used for text output and how strings are interpreted on input. It can be set on a PDO, a PDOStatement, a bound column, or a bound variable.

The default is windows-1252 (Western European).

To see a list of encodings available, look in the PDO-OLEDB section of a phpinfo() print out.

PDO::OLEDB_ATTR_QUERY_ENCODING (string)

This attribute controls how strings passed to PDO::query() and PDO::exec() are converted to Unicode. If this attribute is not set, the driver uses the same encoding as PDO::OLEDB_ATTR_ENCODING.

There is only one scenario when you need to set this attribute: when the encoding of text stored in varchar columns does not actually match the codepage used by the database. For example, if you have UTF-8 text stored in a windows-1252 column, you would want to set PDO:: OLEDB_ATTR_ENCODING to utf-8 and PDO::OLEDB_ATTR_QUERY_ENCODING to windows-1252. Otherwise UTF-8 literal strings in a query will be decoded into their true Unicode representation, then down-converted to the Windows-1252 subset, with the likely result being strings of question-marks.

PDO::OLEDB_ATTR_CHAR_ENCODING (string)

This attribute controls how 8-bit strings (i.e. varchar columns) are converted to the encoding specified by PDO::OLEDB_ATTR_ENCODING—and vice-versa. By default, this is the same as PDO::OLEDB_ATTR_ENCODING, meaning no conversion occurs.

A usage scenario is when your database stores text in Windows-1252, but you want your application to communicate with the web-browser using UTF-8.

PDO::OLEDB_ATTR_TRUNCATE_STRING (bool)

If this attribute is set to TRUE on a PDO, a PDOStatement, or a bound variable, strings will be automatically truncated if the driver detects that it is wider than the width of the column.

 

Retrieving BLOBs

Binary objects are returned as PHP streams (unless PDO::ATTR_STRINGIFY_FETCHES is set). Only one stream can be opened at the same time. If a query returns two blobs, you have to use PDOStatement::fetchColumn() to obtain a handle of the first stream and read all its contents before opening the second stream.