v5:database:microsoft_sql_server
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
v5:database:microsoft_sql_server [2016/01/13 01:53] – ↷ Page moved from database:microsoft_sql_server to v5:database:microsoft_sql_server mnewnham | v5:database:microsoft_sql_server [2019/12/29 20:21] – [Description] mnewnham | ||
---|---|---|---|
Line 2: | Line 2: | ||
~~NOTOC~~ | ~~NOTOC~~ | ||
===== Description ===== | ===== Description ===== | ||
- | There are 2 supported drivers that allow connections to Microsoft SQL Server databases. | + | There are 2 supported drivers that allow connections to Microsoft SQL Server databases |
+ | ==== mssqlnative | ||
+ | This driver, which runs on both Windows and Linux clients, | ||
+ | |||
+ | More about the Microsoft SQL Server client for Linux [[https:// | ||
+ | |||
+ | ==== mssql==== | ||
+ | This allows connections from Unix based clients using the freetds libraries. | ||
+ | |||
+ | More about the FreeTDS libraries [[http:// | ||
==== SQL Server 2003 ==== | ==== SQL Server 2003 ==== | ||
Connecting to SQL server 2003 creates a difficulty There is no support for native client connections, | Connecting to SQL server 2003 creates a difficulty There is no support for native client connections, | ||
Line 13: | Line 22: | ||
^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| | ||
== Alternatives == | == Alternatives == | ||
- | [[database: | + | [[v5:database: |
- | [[database: | + | [[v5:database: |
</ | </ | ||
- | **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 |
- | 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 |
+ | * On Windows clients, | ||
+ | * 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, | + | However, in order to improve portability, |
[RequiredDate] => 1996-08-01 00: | [RequiredDate] => 1996-08-01 00: | ||
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(' | |
- | $db = newAdoConnection(' | + | |
$db-> | $db-> | ||
$db-> | $db-> | ||
</ | </ | ||
- | ------------------------------------ | ||
- | <WRAP right box round 300px> | ||
- | This behaviour is only available in ADOdb V 5.21.0 and higher | ||
- | </ | ||
- | === Modifying The Format Of The Date === | ||
- | When methods like [[dictionary: | ||
+ | ===== Unicode Strings ===== | ||
+ | You can set the character set to UTF-8 using: | ||
<code php> | <code php> | ||
- | /** | + | $db = ADONewConnection(' |
- | * Create the same column Against a MySQL, Oracle, SQL Server database | + | $db-> |
- | */ | + | $db->connect('SERVER\SQLEXPRESS',' |
- | $dict->addColumnSql('some_table',' | + | </ |
- | /* | + | ===== Using Windows Authentication ===== |
- | * Retrieve | + | To use Windows, instead of SQL Server authentication, |
- | */ | + | <code php> |
- | $d = $db->getOne('SELECT some_date_column FROM some_table'); | + | $db = ADONewConnection(' |
+ | $db->connect('SERVER\SQLEXPRESS', | ||
+ | </ | ||
+ | ------------------------------ | ||
+ | ===== 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, // | ||
- | print $d; | + | In the example, our database is running on a server 192.168.0.65: |
- | + | < | |
- | /* | + | # tsql -LH 192.168.0.65 |
- | * With SQL Server, prints | + | |
- | * With Other Databases, prints | + | InstanceName SQLEXPRESS |
- | */ | + | IsClustered No |
+ | Version 12.0.2000.8 | ||
+ | tcp 55644 | ||
+ | np \\STUDY\pipe\MSSQL$SQLEXPRESS\sql\query | ||
</ | </ | ||
+ | The response from the command tells us the | ||
+ | * The SQL server instance is visible from the client | ||
+ | * the service is running on port 55644 | ||
- | To create a portable application, | + | We can now create a connection |
<code php> | <code php> | ||
- | $db = newAdoConnection(' | + | $db = ADONewConnection(' |
- | $db->setConnectionParameter('Type91Format',' | + | $db->connect('192.168.0.65, |
- | $db-> | + | |
- | + | ||
- | $d = $db-> | + | |
- | + | ||
- | print $d; | + | |
- | + | ||
- | /* | + | |
- | * Now prints a compatible value 1996-07-16 | + | |
- | */ | + | |
</ | </ | ||
- | + | -------------------------------------- | |
- | This setting modifies the format of the SQL Server [[https:// | + | |
- | + | ||
- | $db-> | + | |
- | + | ||
- | to modify the format of type 93 fields. | + | |
- | + | ||
- | ** These settings have no effect if '' | + | |
- | + | ||
- | + | ||
- | ------------------------------ | + | |
===== mssql (Unix) ===== | ===== mssql (Unix) ===== | ||
Line 113: | Line 111: | ||
^ADOdb V6|Yes| | ^ADOdb V6|Yes| | ||
== Alternatives == | == Alternatives == | ||
- | [[database: | + | [[v5:database: |
</ | </ | ||
- | **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 | ||
Line 162: | Line 160: | ||
^ADOdb V6|Yes| | ^ADOdb V6|Yes| | ||
</ | </ | ||
- | 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: |
{{tag> | {{tag> |
v5/database/microsoft_sql_server.txt · Last modified: 2022/11/15 02:13 by mnewnham