Connecting to mssql with php

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); ?> 

Источник

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!"); >> 

Источник

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.

Источник

sqlsrv_connect

Opens a connection to a Microsoft SQL Server database. By default, the connection is attempted using Windows Authentication. To connect using SQL Server Authentication, include «UID» and «PWD» in the connection options array.

Parameters

The name of the server to which a connection is established. To connect to a specific instance, follow the server name with a backward slash and the instance name (e.g. serverName\sqlexpress).

An associative array that specifies options for connecting to the server. If values for the UID and PWD keys are not specified, the connection will be attempted using Windows Authentication. For a complete list of supported keys, see » SQLSRV Connection Options.

Return Values

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

Examples

Example #1 Connect using Windows Authentication.

$serverName = «serverName\\sqlexpress» ; //serverName\instanceName

// Since UID and PWD are not specified in the $connectionInfo array,
// The connection will be attempted using Windows Authentication.
$connectionInfo = array( «Database» => «dbName» );
$conn = sqlsrv_connect ( $serverName , $connectionInfo );

if( $conn ) echo «Connection established.
» ;
>else echo «Connection could not be established.
» ;
die( print_r ( sqlsrv_errors (), true ));
>
?>

Example #2 Connect by specifying a user name and password.

$serverName = «serverName\\sqlexpress» ; //serverName\instanceName
$connectionInfo = array( «Database» => «dbName» , «UID» => «userName» , «PWD» => «password» );
$conn = sqlsrv_connect ( $serverName , $connectionInfo );

if( $conn ) echo «Connection established.
» ;
>else echo «Connection could not be established.
» ;
die( print_r ( sqlsrv_errors (), true ));
>
?>

Example #3 Connect on a specified port.

$serverName = «serverName\\sqlexpress, 1542» ; //serverName\instanceName, portNumber (default is 1433)
$connectionInfo = array( «Database» => «dbName» , «UID» => «userName» , «PWD» => «password» );
$conn = sqlsrv_connect ( $serverName , $connectionInfo );

if( $conn ) echo «Connection established.
» ;
>else echo «Connection could not be established.
» ;
die( print_r ( sqlsrv_errors (), true ));
>
?>

Notes

By default, the sqlsrv_connect() uses connection pooling to improve connection performance. To turn off connection pooling (i.e. force a new connection on each call), set the «ConnectionPooling» option in the $connectionOptions array to 0 (or false ). For more information, see » SQLSRV Connection Pooling.

The SQLSRV extension does not have a dedicated function for changing which database is connected to. The target database is specified in the $connectionOptions array that is passed to sqlsrv_connect. To change the database on an open connection, execute the following query «USE dbName» (e.g. sqlsrv_query($conn, «USE dbName»)).

See Also

• sqlsrv_close() — Closes an open connection and releases resourses associated with the connection
• sqlsrv_errors() — Returns error and warning information about the last SQLSRV operation performed
• sqlsrv_query() — Prepares and executes a query

Источник

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.

Источник