Microsoft sql server and php

Microsoft SQL Server Functions (PDO_SQLSRV)

PDO_SQLSRV is a driver that implements the PHP Data Objects (PDO) interface to enable access from PHP to MS SQL Server (starting with SQL Server 2005) and SQL Azure databases.

Installation

The most recent version of the driver is available for download here: » SQLSRV download. The driver sources are hosted in a » public repository.

For more information about system requirements, see » SQLSRV System Requirements.

On Windows the PDO_SQLSRV extension is enabled by downloading and adding appropriate DLL files to your PHP extension directory and the corresponding entry to the php.ini file.

On Linux and macOS, the PDO_SQLSRV extension can be installed using » PECL. See the » installation tutorial for details.

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 driver-specific attributes with another 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.

PDO::SQLSRV_TXN_READ_UNCOMMITTED ( int ) This constant is an acceptable value for the SQLSRV DSN key TransactionIsolation. This constant sets the transaction isolation level for the connection to Read Uncommitted. PDO::SQLSRV_TXN_READ_COMMITTED ( int ) This constant is an acceptable value for the SQLSRV DSN key TransactionIsolation. This constant sets the transaction isolation level for the connection to Read Committed. PDO::SQLSRV_TXN_REPEATABLE_READ ( int ) This constant is an acceptable value for the SQLSRV DSN key TransactionIsolation. This constant sets the transaction isolation level for the connection to Repeateable Read. PDO::SQLSRV_TXN_SNAPSHOT ( int ) This constant is an acceptable value for the SQLSRV DSN key TransactionIsolation. This constant sets the transaction isolation level for the connection to Snapshot. PDO::SQLSRV_TXN_SERIALIZABLE ( int ) This constant is an acceptable value for the SQLSRV DSN key TransactionIsolation. This constant sets the transaction isolation level for the connection to Serializable. PDO::SQLSRV_ENCODING_BINARY ( int ) Specifies that data is sent/retrieved as a raw byte stream to/from the server without performing encoding or translation. This constant can be passed to PDOStatement::setAttribute, PDO::prepare, PDOStatement::bindColumn, and PDOStatement::bindParam. PDO::SQLSRV_ENCODING_SYSTEM ( int ) Specifies that data is sent/retrieved to/from the server as 8-bit characters as specified in the code page of the Windows locale that is set on the system. Any multi-byte characters or characters that do not map into this code page are substituted with a single byte question mark (?) character. This constant can be passed to PDOStatement::setAttribute, PDO::setAttribute, PDO::prepare, PDOStatement::bindColumn, and PDOStatement::bindParam. PDO::SQLSRV_ENCODING_UTF8 ( int ) Specifies that data is sent/retrieved to/from the server in UTF-8 encoding. This is the default encoding. This constant can be passed to PDOStatement::setAttribute, PDO::setAttribute, PDO::prepare, PDOStatement::bindColumn, and PDOStatement::bindParam. PDO::SQLSRV_ENCODING_DEFAULT ( int ) Specifies that data is sent/retrieved to/from the server according to PDO::SQLSRV_ENCODING_SYSTEM if specified during connection. The connection’s encoding is used if specified in a prepare statement. This constant can be passed to PDOStatement::setAttribute, PDO::setAttribute, PDO::prepare, PDOStatement::bindColumn, and PDOStatement::bindParam. PDO::SQLSRV_ATTR_QUERY_TIMEOUT ( int ) A non-negative integer representing the timeout period, in seconds. Zero (0) is the default and means no timeout. This constant can be passed to PDOStatement::setAttribute, PDO::setAttribute, and PDO::prepare. PDO::SQLSRV_ATTR_DIRECT_QUERY ( int ) Indicates that a query should be executed directly, without being prepared. This constant can be passed to PDO::setAttribute, and PDO::prepare. For more information, see » Direct and Prepared Statement Execution.

Table of Contents

Источник

Шаг 3. Подтверждение концепции: подключение к SQL с помощью PHP

Эта функция OpenConnection вызывается перед выполнением всех последующих функций.

 function OpenConnection() < $serverName = "tcp:myserver.database.windows.net,1433"; $connectionOptions = array("Database"=>"AdventureWorks", "Uid"=>"MyUser", "PWD"=>"MyPassword"); $conn = sqlsrv_connect($serverName, $connectionOptions); if($conn == false) die(FormatErrors(sqlsrv_errors())); return $conn; > 

Шаг 2. Выполнение запроса

Функция sqlsrv_query может использоваться для извлечения результирующего набора из запроса к базе данных SQL. Эта функция фактически принимает любой запрос и объект подключения, а затем возвращает результирующий набор для итеративного перебора с помощью sqlsrv_fetch_array().

 function ReadData() < try < $conn = OpenConnection(); $tsql = "SELECT [CompanyName] FROM SalesLT.Customer"; $getProducts = sqlsrv_query($conn, $tsql); if ($getProducts == FALSE) die(FormatErrors(sqlsrv_errors())); $productCount = 0; while($row = sqlsrv_fetch_array($getProducts, SQLSRV_FETCH_ASSOC)) < echo($row['CompanyName']); echo("
"); $productCount++; > sqlsrv_free_stmt($getProducts); sqlsrv_close($conn); > catch(Exception $e) < echo("Error!"); >> 

Шаг 3. Вставка строки

В этом примере показано, как безопасно выполнить инструкцию INSERT и передать параметры. Значения параметров защищают приложение от внедрения кода SQL.

 function InsertData() < try < $conn = OpenConnection(); $tsql = "INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT" . " INSERTED.ProductID VALUES ('SQL Server 1', 'SQL Server 2', 0, 0, getdate())"; //Insert query $insertReview = sqlsrv_query($conn, $tsql); if($insertReview == FALSE) die(FormatErrors( sqlsrv_errors())); echo "Product Key inserted is :"; while($row = sqlsrv_fetch_array($insertReview, SQLSRV_FETCH_ASSOC)) < echo($row['ProductID']); >sqlsrv_free_stmt($insertReview); sqlsrv_close($conn); > catch(Exception $e) < echo("Error!"); >> 

Шаг 4. Откат транзакции

Этот пример кода демонстрирует использование транзакций, в которых можно:

• начать транзакцию;
• вставить строку данных, обновить другую строку данных;
• зафиксировать транзакцию, если запросы на вставку и обновление выполнены успешно, или откатить транзакцию, если один из запросов вызвал ошибку.

 function Transactions() < try < $conn = OpenConnection(); if (sqlsrv_begin_transaction($conn) == FALSE) die(FormatErrors(sqlsrv_errors())); $tsql1 = "INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID,OrderQty,ProductID,UnitPrice) VALUES (71774, 22, 709, 33)"; $stmt1 = sqlsrv_query($conn, $tsql1); /* Set up and execute the second query. */ $tsql2 = "UPDATE SalesLT.SalesOrderDetail SET OrderQty = (OrderQty + 1) WHERE ProductID = 709"; $stmt2 = sqlsrv_query( $conn, $tsql2); /* If both queries were successful, commit the transaction. */ /* Otherwise, rollback the transaction. */ if($stmt1 && $stmt2) < sqlsrv_commit($conn); echo("Transaction was commited"); >else < sqlsrv_rollback($conn); echo "Transaction was rolled back.\n"; >/* Free statement and connection resources. */ sqlsrv_free_stmt( $stmt1); sqlsrv_free_stmt( $stmt2); > catch(Exception $e) < echo("Error!"); >> 

Источник

Step 3: Proof of concept connecting to SQL using PHP

This OpenConnection function is called near the top in all of the functions that follow.

 function OpenConnection() < $serverName = "tcp:myserver.database.windows.net,1433"; $connectionOptions = array("Database"=>"AdventureWorks", "Uid"=>"MyUser", "PWD"=>"MyPassword"); $conn = sqlsrv_connect($serverName, $connectionOptions); if($conn == false) die(FormatErrors(sqlsrv_errors())); return $conn; > 

Step 2: Execute query

The sqlsrv_query() function can be used to retrieve a result set from a query against SQL Database. This function essentially accepts any query and the connection object and returns a result set, which can be iterated over with the use of sqlsrv_fetch_array().

 function ReadData() < try < $conn = OpenConnection(); $tsql = "SELECT [CompanyName] FROM SalesLT.Customer"; $getProducts = sqlsrv_query($conn, $tsql); if ($getProducts == FALSE) die(FormatErrors(sqlsrv_errors())); $productCount = 0; while($row = sqlsrv_fetch_array($getProducts, SQLSRV_FETCH_ASSOC)) < echo($row['CompanyName']); echo("
"); $productCount++; > sqlsrv_free_stmt($getProducts); sqlsrv_close($conn); > catch(Exception $e) < echo("Error!"); >> 

Step 3: Insert a row

In this example, you’ll see how to execute an INSERT statement safely and pass parameters. Parameter values protect your application from SQL injection.

 function InsertData() < try < $conn = OpenConnection(); $tsql = "INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT" . " INSERTED.ProductID VALUES ('SQL Server 1', 'SQL Server 2', 0, 0, getdate())"; //Insert query $insertReview = sqlsrv_query($conn, $tsql); if($insertReview == FALSE) die(FormatErrors( sqlsrv_errors())); echo "Product Key inserted is :"; while($row = sqlsrv_fetch_array($insertReview, SQLSRV_FETCH_ASSOC)) < echo($row['ProductID']); >sqlsrv_free_stmt($insertReview); sqlsrv_close($conn); > catch(Exception $e) < echo("Error!"); >> 

Step 4: Roll back a transaction

This code example demonstrates the use of transactions in which you:

• Begin a transaction
• Insert a row of data, Update another row of data
• Commit your transaction if the insert and update were successful and roll back the transaction if one of them wasn’t

 function Transactions() < try < $conn = OpenConnection(); if (sqlsrv_begin_transaction($conn) == FALSE) die(FormatErrors(sqlsrv_errors())); $tsql1 = "INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID,OrderQty,ProductID,UnitPrice) VALUES (71774, 22, 709, 33)"; $stmt1 = sqlsrv_query($conn, $tsql1); /* Set up and execute the second query. */ $tsql2 = "UPDATE SalesLT.SalesOrderDetail SET OrderQty = (OrderQty + 1) WHERE ProductID = 709"; $stmt2 = sqlsrv_query( $conn, $tsql2); /* If both queries were successful, commit the transaction. */ /* Otherwise, rollback the transaction. */ if($stmt1 && $stmt2) < sqlsrv_commit($conn); echo("Transaction was commited"); >else < sqlsrv_rollback($conn); echo "Transaction was rolled back.\n"; >/* Free statement and connection resources. */ sqlsrv_free_stmt( $stmt1); sqlsrv_free_stmt( $stmt2); > catch(Exception $e) < echo("Error!"); >> 

Источник

sqlsrv_connect

Creates a connection resource and opens a connection. By default, the connection is attempted using Windows Authentication.

Syntax

 sqlsrv_connect( string $serverName [, array $connectionInfo]) 

Parameters

$serverName: A string specifying the name of the server to which a connection is being established. An instance name (for example, «myServer\instanceName») or port number (for example, «myServer, 1521») can be included as part of this string. For a complete description of the options available for this parameter, see the Server keyword in the ODBC Driver Connection String Keywords section of Using Connection String Keywords with SQL Native Client.

Beginning in version 3.0 of the Microsoft Drivers for PHP for SQL Server, you can also specify a LocalDB instance with «(localdb)\instancename» . For more information, see Support for LocalDB.

Also beginning in version 3.0 of the Microsoft Drivers for PHP for SQL Server, you can specify a virtual network name, to connect to an Always On availability group. For more information about Microsoft Drivers for PHP for SQL Server support for Always On availability groups, see Support for High Availability, Disaster Recovery.

$connectionInfo [OPTIONAL]: An associative array that contains connection attributes (for example, array(«Database» => «AdventureWorks»)). See Connection Options for a list of the supported keys for the array.

Return Value

A PHP connection resource. If a connection cannot be successfully created and opened, false is returned.

Remarks

If values for the UID and PWD keys are not specified in the optional $connectionInfo parameter, the connection will be attempted using Windows Authentication. For more information about connecting to the server, see How to: Connect Using Windows Authentication and How to: Connect Using SQL Server Authentication.

Example

The following example creates and opens a connection using Windows Authentication. The example assumes that SQL Server and the AdventureWorks database are installed on the local computer. All output is written to the console when the example is run from the command line.

 $uid, "PWD" => $pwd, "Database"=>"AdventureWorks"); */ $serverName = "(local)"; $connectionInfo = array( "Database"=>"AdventureWorks"); $conn = sqlsrv_connect( $serverName, $connectionInfo); if( $conn ) < echo "Connection established.\n"; >else < echo "Connection could not be established.\n"; die( print_r( sqlsrv_errors(), true)); >//----------------------------------------------- // Perform operations with connection. //----------------------------------------------- /* Close the connection. */ sqlsrv_close( $conn); ?> 

Источник

Connecting to the Server

The topics in this section describe the options and procedures for connecting to SQL Server with the Microsoft Drivers for PHP for SQL Server.

The Microsoft Drivers for PHP for SQL Server can connect to SQL Server by using Windows Authentication or by using SQL Server Authentication. By default, the Microsoft Drivers for PHP for SQL Server try to connect to the server by using Windows Authentication.

In This Section

Topic	Description
How to: Connect Using Windows Authentication	Describes how to establish a connection by using Windows Authentication.
How to: Connect Using SQL Server Authentication	Describes how to establish a connection by using SQL Server Authentication.
How to: Connect Using Azure Active Directory Authentication	Describes how to set the authentication mode and connect using Azure Active Directory identities.
How to: Connect on a Specified Port	Describes how to connect to the server on a specific port.
Connection Pooling	Provides information about connection pooling in the driver.
How to: Disable Multiple Active Resultsets (MARS)	Describes how to disable the MARS feature when making a connection.
Connection Options	Lists the options that are permitted in the associative array that contains connection attributes.
Support for LocalDB	Describes Microsoft Drivers for PHP for SQL Server support for the LocalDB feature, which was added in SQL Server 2012 (11.x).
Support for High Availability, Disaster Recovery	Discusses how your application can be configured to take advantage of the high-availability, disaster recovery features added in SQL Server 2012 (11.x).
Connecting to Microsoft Azure SQL Database	Discusses how to connect to an Azure SQL Database.
Connection Resiliency	Discusses the connection resiliency feature that reestablishes broken connections.

Источник