- skip to content

# Database Abstraction Layer

### User Tools

- Login

### Site Tools

- Recent changes
- Media Manager
- Sitemap

### Sidebar

##### Resources

- Reference Manual
- User Guide
- The Data Dictionary
- ADOdb-xmlschema
- ADOdb Active Record
- Performance Monitoring
- Date/Time Library
- Session Management Library
- Unsupported Addons
- Coding Standards

##### Supported Databases

- Access
- ADO
- CSV
- Firebird
- Frontbase
- IBM DB2
- Informix
- Interbase
- LDAP
- Microsoft SQL Server
- MySQL
- Netezza
- ODBC
- ODBTP
- Oracle
- PDO
- Postgresql
- SAP ADS
- SAP ASE
- SAP Maxdb
- SAP Sqlanywhere
- Sqlite
- Sybase
- Plain Text
- Visual Foxpro

##### Other

- Feature Comparison Matrix
- Connection Matrix
- Include File Guide

v5:database:microsoft_sql_server

# Microsoft SQL Server

## Description

There are 2 supported drivers that allow connections to Microsoft SQL Server databases. The first, mssqlnative allows connections from Windows Clients to Microsoft SQL Server databases versions 2008 and up, and uses the Microsoft Native Client libraries. The second, mssql allows connections from Unix based clients using the freetds libraries.

### SQL Server 2003

Connecting to SQL server 2003 creates a difficulty There is no support for native client connections, and the alternative method, using php_mssql.dll was removed in PHP 5.3. The only alternative is to use a generic ODBC driver that does not require the Microsoft Native Client libraries.

# mssqlnative

##### Specification

Driver Name | mssqlnative
Data Provider | mssqlnative
Status | Active1)
Windows | Yes
Unix | No
ADOdb V5 | Yes
ADOdb V6 | Yes

##### Alternatives

PDO driver for SQL Server
ODBC driver for SQL Server

This is the preferred driver for connecting to a SQL server database from Windows clients.

This driver was contributed by Microsoft and provides connections to SQL server databases greater than version 2000. It requires PHP version 5.3 or greater, Microsoft native client software and the appropriate PHP driver.

Sequences are supported in native mode in SQL Sever 2012, and emulated in earlier versions.

### DateTime Fields

The default behavior for the PHP mssqlnative driver is to return date/time fields as a PHP dateTime object, that looks like this:

    [RequiredDate] => DateTime Object
    (
        [date] => 1996-08-01 00:00:00.000000
        [timezone_type] => 3
        [timezone] => America/Denver
    )

However, in order to improve portability, the default behaviour or the ADOdb mssqlnative driver is to return the date as a string:

    [RequiredDate] => 1996-08-01 00:00:00.000000

In order to return the dateTime as an object, the following action is required:

    $db = newAdoConnection('mssqlnative');
    $db->setConnectionParameter('ReturnDatesAsStrings',false);
    $db->connect('SERVER\SQLEXPRESS','user','password','NORTHWND');

This behaviour is only available in ADOdb V 5.21.0 and higher

#### Modifying The Format Of The Date

When methods like createtablesql are used against SQL Server databases, the 'D' metatype creates a dateTime field, as opposed to a date field. This means that in portable applications, the `Y-m-d` portion of the date needs to be processed out of the returned field.

    /**
    * Create the same column Against a MySQL, Oracle, SQL Server database
    */
    $dict->addColumnSql('some_table','some_date_column D Not Null');

    /*
    * Retrieve the data from column
    */
    $d = $db->getOne('SELECT some_date_column FROM some_table');

    print $d;

    /*
    * With SQL Server, prints  1996-07-16 00:00:00.000000
    * With Other Databases, prints  1996-07-16
    */

To create a portable application, use the following connection setting:

    $db = newAdoConnection('mssqlnative');
    $db->setConnectionParameter('Type91Format','Y-m-d');
    $db->connect('SERVER\SQLEXPRESS','user','password','NORTHWND');

    $d = $db->getOne('SELECT some_date_column FROM some_table');

    print $d;

    /*
    * Now prints a compatible value  1996-07-16
    */

This setting modifies the format of the SQL Server Type 91 field. You can also use

    $db->setConnectionParameter('Type93Format','Y-m-d');

to modify the format of type 93 fields.

These settings have no effect if `returnDatesAsStrings` is set to false.

## mssql (Unix)

##### Specification

Driver Name | mssql
Data Provider | mssql
Status | Active2)
Windows | No
Unix | Yes
ADOdb V5 | Yes
ADOdb V6 | Yes

##### Alternatives

PDO driver for FreeTDS

This is the preferred driver for connecting to a SQL server database from Linux/Unix clients. This driver provides access to Microsoft SQL Server databases from unix systems via Freetds

[SQL Server, unix, active, freetds]

## mssql (Windows)

##### Specification

Driver Name | mssql
Data Provider | mssql
Status | Obsolete3)
Windows | Yes
Unix | No
ADOdb V5 | Yes 4)
ADOdb V6 | No

This driver was the primary connection method to SQL Server databases up to PHP Version 5.2. The windows dll associated with this driver (php_mssql.dll) was removed from the standard PHP distribution in PHP Version 5.3. There is no community support for this driver.

## mssqlpo

##### Specification

Driver Name | mssqlpo
Data Provider | mssql
Status | Inactive5)
Windows | No
Unix | Yes
ADOdb V5 | Yes
ADOdb V6 | Yes

This driver provides undocumented portability extensions to the Unix mssql driver

[SQL Server, unix, windows, active, portable]

## mssql_n

##### Specification

Driver Name | mssqlpo
Data Provider | mssql
Status | Inactive6)
Windows | No
Unix | Yes
ADOdb V5 | Yes
ADOdb V6 | Yes

This driver appears to provide Unicode enhancements to the mssql driver

[Microsoft SQL Server, unicode, freetds, windows]
1) , 2) This driver is actively supported by ADOdb project members
3) This driver will be removed in ADOdb version 6
4) PHP Version 5.2 and lower
5) , 6) This driver is not actively supported or enhanced by ADOdb project members, but you can provide fixes and enhancements if you have the expertise
v5/database/microsoft_sql_server.txt · Last modified: 2016/01/12 17:06 by mnewnham

### Page Tools

- Show pagesource
- Old revisions
- Backlinks
- Rename Page
- Back to top