ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools


v5:database:microsoft_sql_server

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
v5:database:microsoft_sql_server [2016/01/13 02:06] – ↷ Links adapted because of a move operation mnewnhamv5:database:microsoft_sql_server [2022/11/15 02:13] (current) – [Common Connection Problems] mnewnham
Line 2: Line 2:
 ~~NOTOC~~ ~~NOTOC~~
 ===== Description ===== ===== 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+There are 2 supported drivers that allow connections to Microsoft SQL Server databases from SQL Server 2005 upwards 
-==== SQL Server 2003 ==== +==== mssqlnative ==== 
-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. +This driver, which runs on both Windows and Linux clients, allows connections to Microsoft SQL Server databases versions 2008 and up, and uses the Microsoft Native Client libraries. This driver is recommended for Windows Clients running PHP 5.3 and higherand Linux Clients running PHP 7.0 and higher, and provides the fastest PHP connection. 
-------------------------------------+ 
 +More about the Microsoft SQL Server client for Linux [[https://github.com/Microsoft/msphpsql|here]] 
 + 
 +==== mssql==== 
 +This allows connections from Unix based clients using the freetds librariesIt can be used with version 5.x of PHP 
 + 
 +More about the FreeTDS libraries [[http://www.freetds.org/|here]] 
 ====== mssqlnative ====== ====== mssqlnative ======
 <WRAP right box round 300px> <WRAP right box round 300px>
Line 13: Line 20:
 ^Status|Active((This driver is actively supported by ADOdb project members))| ^Status|Active((This driver is actively supported by ADOdb project members))|
 ^Windows|Yes| ^Windows|Yes|
-^Unix|No|+^Unix|Yes|
 ^ADOdb V5|Yes| ^ADOdb V5|Yes|
 ^ADOdb V6|Yes| ^ADOdb V6|Yes|
Line 19: Line 26:
 [[v5:database:pdo#pdo_sqlsrv|PDO driver for SQL Server]]\\ [[v5:database:pdo#pdo_sqlsrv|PDO driver for SQL Server]]\\
 [[v5:database:odbc#odbc_mssql|ODBC driver for SQL Server]]\\ [[v5:database:odbc#odbc_mssql|ODBC driver for SQL Server]]\\
 +== See Also ==
 +[[v5:reference:connection:setconnectionparameter#sqlserver|setConnectionParameter()]]
 </WRAP> </WRAP>
  
-**This is the preferred driver for connecting to a SQL server database from Windows clients.**+**This is the preferred driver for connecting to a SQL server database from both Windows and Linux 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. +The original driver was contributed by Microsoft and provides connections to SQL server databases greater than SQL Server 2003 
 +  * On Windows clients, It requires PHP version 5.3 or greater, Microsoft native client software and the appropriate PHP driver.  
 +  * On Linux clients, PHP version 7.0 and higher is required.
  
 Sequences are supported in native mode in SQL Sever 2012, and emulated in earlier versions.  Sequences are supported in native mode in SQL Sever 2012, and emulated in earlier versions. 
Line 38: Line 49:
    
  
-However, in order to improve portability, the default behaviour or the **ADOdb** mssqlnative driver is to return the date as a string:+However, in order to improve portability, the default behaviour of the **ADOdb** mssqlnative driver is to return the date as a string:
  
   [RequiredDate] => 1996-08-01 00:00:00.000000   [RequiredDate] => 1996-08-01 00:00:00.000000
Line 44: Line 55:
 In order to return the dateTime as an object, the following action is required: In order to return the dateTime as an object, the following action is required:
 <code php> <code php>
- +$db = ADONewConnection('mssqlnative');
-$db = newAdoConnection('mssqlnative');+
 $db->setConnectionParameter('ReturnDatesAsStrings',false); $db->setConnectionParameter('ReturnDatesAsStrings',false);
 $db->connect('SERVER\SQLEXPRESS','user','password','NORTHWND'); $db->connect('SERVER\SQLEXPRESS','user','password','NORTHWND');
 </code> </code>
------------------------------------- 
-<WRAP right box round 300px> 
-This behaviour is only available in ADOdb V 5.21.0 and higher 
-</WRAP> 
-=== Modifying The Format Of The Date === 
-When methods like [[v5:dictionary:createtablesql]] are used against SQL Server databases, the 'D' [[v5:dictionary: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. 
  
 +===== Unicode Strings =====
 +You can set the character set to UTF-8 using:
 <code php> <code php>
-/** +$db = ADONewConnection('mssqlnative'); 
-* Create the same column Against a MySQL, Oracle, SQL Server database +$db->setConnectionParameter('characterSet','UTF-8'); 
-*/ +$db->connect('SERVER\SQLEXPRESS','user','password','NORTHWND');
-$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 +
-*/   +
 </code> </code>
  
-To create a portable application, use the following connection setting: +===== Using Windows Authentication ===== 
 +To use Windows, instead of SQL Server authentication, pass NULL as the user and password as shown below
 <code php> <code php>
-$db = newAdoConnection('mssqlnative'); +$db = ADONewConnection('mssqlnative'); 
-$db->setConnectionParameter('Type91Format','Y-m-d'); +$db->connect('SERVER\SQLEXPRESS',NULL,NULL,'NORTHWND'); 
-$db->connect('SERVER\SQLEXPRESS','user','password','NORTHWND');+</code>
  
-$d $db->getOne('SELECT some_date_column FROM some_table');+===== Common Connection Problems ===== 
 +Recent versions of SQL Server fail to connect with an error ''Message: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: The certificate chain was issued by an authority that is not trusted.''
  
-print $d;+This problem can be resolved by adding
  
-/* +<code php> 
-* Now prints a compatible value  1996-07-16 +$db->setConnectionParameter('TrustServerCertificate',1);
-*/   +
 </code> </code>
  
-This setting modifies the format of the SQL Server [[https://msdn.microsoft.com/en-us/library/ms712408(v=vs.85).aspx|Type 91]] field. You can also use+before issuing the ''$db->connect()'' statement
  
-  $db->setConnectionParameter('Type93Format','Y-m-d'); 
  
-to modify the format of type 93 fields.+------------------------------ 
 +===== How to create a connection under Linux ===== 
 +Establishing a connection to a SQL server database from a Linux client can be difficult, but by installing the freetds tools as well as the Microsoft ones provides a highly useful tool, //**tsql**//
  
-** These settings have no effect if ''returnDatesAsStrings'' is set to false.**+In the example, our database is running on a server 192.168.0.65: 
 +<code> 
 +# tsql -LH 192.168.0.65 
 +    ServerName STUDY 
 +   InstanceName SQLEXPRESS 
 +    IsClustered No 
 +        Version 12.0.2000.8 
 +            tcp 55644 
 +             np \\STUDY\pipe\MSSQL$SQLEXPRESS\sql\query 
 +</code> 
 +The response from the command tells us the 
 +  The SQL server instance is visible from the client 
 +  the service is running on port 55644
  
 +We can now create a connection string:
  
-------------------------------+<code php> 
 +$db = ADONewConnection('mssqlnative'); 
 +$db->connect('192.168.0.65,55644',$user,$pass,'NORTHWND'); 
 +</code> 
 +--------------------------------------
  
 ===== mssql (Unix) ===== ===== mssql (Unix) =====
Line 115: Line 126:
 [[v5:database:pdo#pdo_mssql|PDO driver for FreeTDS]] [[v5:database:pdo#pdo_mssql|PDO driver for FreeTDS]]
 </WRAP> </WRAP>
-**This is the preferred driver for connecting to a SQL server database from Linux/Unix clients.**+**This is the preferred driver for connecting to a SQL server database from Linux/Unix clients, when the PHP version is less than 7.0**
 This driver provides access to Microsoft SQL Server databases from unix systems via Freetds This driver provides access to Microsoft SQL Server databases from unix systems via Freetds
  
-{{tag>[SQL_Server unix active freetds]}}+{{tag>[SQL_Server unix active freetds tier1]}}
 -------------------------------------- --------------------------------------
 ===== mssql (Windows) ===== ===== mssql (Windows) =====
Line 162: Line 173:
 ^ADOdb V6|Yes| ^ADOdb V6|Yes|
 </WRAP> </WRAP>
-This driver appears to provide Unicode enhancements to the mssql driver+This driver appears to provide Unicode enhancements to the mssql driver. You can set character sets using the [[v5:reference:connection:setconnectionparameter|setConnectionParameter()]] command
  
 {{tag>["Microsoft SQL Server" unicode freetds windows]}} {{tag>["Microsoft SQL Server" unicode freetds windows]}}
v5/database/microsoft_sql_server.1452647183.txt.gz · Last modified: 2017/04/21 11:25 (external edit)