Book/sqlsrv-Phpdoc专题

Microsoft SQL Server Driver for PHP

目录

• 简介
• 安装／配置
  • 需求
  • 安装
  • 运行时配置
  • 资源类型
• 预定义常量
• SQLSRV 函数
  • sqlsrv_begin_transaction — Begins a database transaction
  • sqlsrv_cancel — Cancels a statement
  • sqlsrv_client_info — Returns information about the client and specified connection
  • sqlsrv_close — Closes an open connection and releases resourses associated with the connection
  • sqlsrv_commit — Commits a transaction that was begun with sqlsrv_begin_transaction
  • sqlsrv_configure — Changes the driver error handling and logging configurations
  • sqlsrv_connect — Opens a connection to a Microsoft SQL Server database
  • sqlsrv_errors — Returns error and warning information about the last SQLSRV operation performed
  • sqlsrv_execute — Executes a statement prepared with sqlsrv_prepare
  • sqlsrv_fetch_array — Returns a row as an array
  • sqlsrv_fetch_object — Retrieves the next row of data in a result set as an object
  • sqlsrv_fetch — Makes the next row in a result set available for reading
  • sqlsrv_field_metadata — Retrieves metadata for the fields of a statement prepared by sqlsrv_prepare or sqlsrv_query
  • sqlsrv_free_stmt — Frees all resources for the specified statement
  • sqlsrv_get_config — Returns the value of the specified configuration setting
  • sqlsrv_get_field — Gets field data from the currently selected row
  • sqlsrv_has_rows — Indicates whether the specified statement has rows
  • sqlsrv_next_result — Makes the next result of the specified statement active
  • sqlsrv_num_fields — Retrieves the number of fields (columns) on a statement
  • sqlsrv_num_rows — Retrieves the number of rows in a result set
  • sqlsrv_prepare — Prepares a query for execution
  • sqlsrv_query — Prepares and executes a query
  • sqlsrv_rollback — Rolls back a transaction that was begun with sqlsrv_begin_transaction
  • sqlsrv_rows_affected — Returns the number of rows modified by the last INSERT, UPDATE, or DELETE query executed
  • sqlsrv_send_stream_data — Sends data from parameter streams to the server
  • sqlsrv_server_info — Returns information about the server

The SQLSRV extension allows you to access Microsoft SQL Server and SQL Azure databases. The 3.0 release of the driver supports SQL Server, beginning with SQL Server 2005, including SQL Server 2012 and SQL Server 2012 LocalDB. (For more information about LocalDB, see » PHP Driver for SQL Server Support for LocalDB and » SQL Server 2012 Express LocalDB.)

The SQLSRV extension is supported by Microsoft and available for download here: » http://msdn.microsoft.com/en-us/sqlserver/ff657782.aspx. SQL Server 2012 LocalDB can be downloaded here: » http://go.microsoft.com/fwlink/?LinkID=237665.

安装／配置

目录

• 需求
• 安装
• 运行时配置
• 资源类型

需求

The SQLSRV extension can be used on the following operating systems:

• Windows Vista Service Pack 2 or later
• Windows Server 2008 Service Pack 2 or later
• Windows Server 2008 R2
• Windows 7

The SQLSRV extension requires that the Microsoft SQL Server 2012 Native Client be installed on the same computer that is running PHP. If the Microsoft SQL Server 2012 Native Client is not already installed, click the appropriate link below to download it:

• » Download the x86 package
• » Download the x64 package

The SQLSRV download comes 8 driver files, four of which are for PDO support. If you are running non-thread-safe PHP (PHP 5.3), use the php_sqlsrv_53_nts.dll file. (You should use a non-thread-safe version if you are using IIS as your web server). If you are running thread-safe PHP, use the php_sqlsrv_53_ts.dll file. Similarly for PHP 5.4, use the php_sqlsrv_54_nts.dll or php_sqlsrv_54_ts.dll depending on whether your PHP installation is non-thread-safe or thread-safe.

The most recent version of the driver is available for download here: » SQLSRV 4.0 download. If you need support for PHP 5.2 and/or PHP compiled with VC6, use the 2.0 release of the driver: » SQLSRV 2.0 download.

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

安装

The SQLSRV extension is enabled by adding appropriate DLL file to your PHP extension directory and the corresponding entry to the php.ini file. The SQLSRV download comes 8 driver files, four of which are for PDO support. If you are running non-thread-safe PHP (PHP 5.3), use the php_sqlsrv_53_nts.dll file. (You should use a non-thread-safe version if you are using IIS as your web server). If you are running thread-safe PHP, use the php_sqlsrv_53_ts.dll file. Similarly for PHP 5.4, use the php_sqlsrv_54_nts.dll or php_sqlsrv_54_ts.dll depending on whether your PHP installation is non-thread-safe or thread-safe.

The most recent version of the driver is available for download here: » SQLSRV 4.0 download. If you need support for PHP 5.2 and/or PHP compiled with VC6, use the 2.0 release of the driver: » SQLSRV 2.0 download.

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

The SQLSRV extension is only compatible with PHP 5 running on Windows. Since version 4.0 the SQLSRV extension is compatilbe only with PHP 7.0 running on Linux or Windows.

运行时配置

这些函数的行为受 php.ini 中的设置影响。

The following table lists the configuration options available in the SQLSRV extension. For more information about these options, see » Handling SQLSRV Warnings and Errors.

资源类型

Connection resource

A connection resource returned by <span class="function">sqlsrv_connect.

Statement resource

A statement resource returned by <span class="function">sqlsrv_query or by <span class="function">sqlsrv_prepare.

预定义常量

下列常量由此扩展定义，且仅在此扩展编译入 PHP 或在运行时动态载入时可用。

SQLSRV_FETCH_ASSOC (int)
Forces <span class="function">sqlsrv_fetch_array to return an associative array when passed as a parameter.

SQLSRV_FETCH_NUMERIC (int)
Forces <span class="function">sqlsrv_fetch_array to return an array with numeric when passed as a parameter.

SQLSRV_FETCH_BOTH (int)
Forces <span class="function">sqlsrv_fetch_array to return an array with both associative and numeric keys when passed as a parameter (the default behavior).

SQLSRV_ERR_ALL (int)
Forces <span class="function">sqlsrv_errors to return both errors and warings when passed as a parameter (the default behavior).

SQLSRV_ERR_ERRORS (int)
Forces <span class="function">sqlsrv_errors to return errors only (no warnings) when passed as a parameter.

SQLSRV_ERR_WARNINGS (int)
Forces <span class="function">sqlsrv_errors to return warnings only (no errors) when passed as a parameter.

SQLSRV_LOG_SYSTEM_ALL (int)
Turns on logging of all subsystems when passed to sqlsrv_configure as a parameter.

SQLSRV_LOG_SYSTEM_CONN (int)
Turns on logging of connection activity when passed to sqlsrv_configure as a parameter.

SQLSRV_LOG_SYSTEM_INIT (int)
Turns on logging of initialization activity when passed to sqlsrv_configure as a parameter.

SQLSRV_LOG_SYSTEM_OFF (int)
Turns off logging of all subsystems when passed to sqlsrv_configure as a parameter.

SQLSRV_LOG_SYSTEM_STMT (int)
Turns on logging of statement activity when passed to sqlsrv_configure as a parameter.

SQLSRV_LOG_SYSTEM_UTIL (int)
Turns on logging of error function activity when passed to sqlsrv_configure as a parameter.

SQLSRV_LOG_SEVERITY_ALL (int)
Specifies that errors, warnings, and notices will be logged when passed to sqlsrv_configure as a parameter.

SQLSRV_LOG_SEVERITY_ERROR (int)
Specifies that errors will be logged when passed to sqlsrv_configure as a parameter.

SQLSRV_LOG_SEVERITY_NOTICE (int)
Specifies that notices will be logged when passed to sqlsrv_configure as a parameter.

SQLSRV_LOG_SEVERITY_WARNING (int)
Specifies that warnings will be logged when passed to sqlsrv_configure as a parameter.

SQLSRV_NULLABLE_YES (int)
Indicates that a column is nullable.

SQLSRV_NULLABLE_NO (int)
Indicates that a column is not nullable.

SQLSRV_NULLABLE_UNKNOWN (int)
Indicates that it is not known if a column is nullable.

SQLSRV_PARAM_IN (int)
Indicates an input parameter when passed to <span class="function">sqlsrv_query or <span class="function">sqlsrv_prepare.

SQLSRV_PARAM_INOUT (int)
Indicates a bidirectional parameter when passed to sqlsrv_query or <span class="function">sqlsrv_prepare.

SQLSRV_PARAM_OUT (int)
Indicates an output parameter when passed to sqlsrv_query or <span class="function">sqlsrv_prepare.

SQLSRV_PHPTYPE_INT (int)
Specifies an integer PHP data type. For usage information, see » How to: Specify PHP Types.

SQLSRV_PHPTYPE_DATETIME (int)
Specifies a datetime PHP data type. For usage information, see » How to: Specify PHP Types.

SQLSRV_PHPTYPE_FLOAT (int)
Specifies a float PHP data type. For usage information, see » How to: Specify PHP Types.

SQLSRV_PHPTYPE_STREAM (int)
Specifies a PHP stream. This constant works like a function and accepts an encoding constant. See the SQLSRV_ENC_* constants. For usage information, see » How to: Specify PHP Types.

SQLSRV_PHPTYPE_STRING (int)
Specifies a string PHP data type. This constant works like a function and accepts an encoding constant. See the SQLSRV_ENC_* constants. For usage information, see » How to: Specify PHP Types.

SQLSRV_ENC_BINARY (int)
Specifies that data is returned as a raw byte stream from the server without performing encoding or translation. For usage information, see » How to: Specify PHP Types.

SQLSRV_ENC_CHAR (int)
Data is returned in 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 is the default encoding. For usage information, see » How to: Specify PHP Types.

UTF-8 (int)
Specifies that data is returned with UTF-8 encoding. For usage information, see » How to: Specify PHP Types.

SQLSRV_SQLTYPE_BIGINT (int)
Describes the bigint SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_BINARY (int)
Describes the binary SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_BIT (int)
Describes the bit SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_CHAR (int)
Describes the char SQL Server data type. This constant works like a function and accepts a parameter indicating the number characters. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_DATE (int)
Describes the date SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_DATETIME (int)
Describes the datetime SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_DATETIME2 (int)
Describes the datetime2 SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_DATETIMEOFFSET (int)
Describes the datetimeoffset SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_DECIMAL (int)
Describes the decimal SQL Server data type. This constant works like a function and accepts two parameters indicating (in order) precision and scale. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_FLOAT (int)
Describes the float SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_IMAGE (int)
Describes the image SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_INT (int)
Describes the int SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_MONEY (int)
Describes the money SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_NCHAR (int)
Describes the nchar SQL Server data type. This constant works like a function and accepts a single parameter indicating the character count. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_NUMERIC (int)
Describes the numeric SQL Server data type. This constant works like a function and accepts two parameter indicating (in order) precision and scale. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_NVARCHAR (int)
Describes the nvarchar SQL Server data type. This constant works like a function and accepts a single parameter indicating the character count. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_NVARCHAR('max') (int)
Describes the nvarchar(MAX) SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_NTEXT (int)
Describes the ntext SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_REAL (int)
Describes the real SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_SMALLDATETIME (int)
Describes the smalldatetime SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_SMALLINT (int)
Describes the smallint SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_SMALLMONEY (int)
Describes the smallmoney SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_TEXT (int)
Describes the text SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_TIME (int)
Describes the time SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_TIMESTAMP (int)
Describes the timestamp SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_TINYINT (int)
Describes the tinyint SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_UNIQUEIDENTIFIER (int)
Describes the uniqueidentifier SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_UDT (int)
Describes the UDT SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_VARBINARY (int)
Describes the varbinary SQL Server data type. This constant works like a function and accepts a single parameter indicating the byte count. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_VARBINARY('max') (int)
Describes the varbinary(MAX) SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_VARCHAR (int)
Describes the varchar SQL Server data type. This constant works like a function and accepts a single parameter indicating the character count. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_VARCHAR('max') (int)
Describes the varchar(MAX) SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_SQLTYPE_XML (int)
Describes the XML SQL Server data type. For usage information, see » How to: Specify SQL Types.

SQLSRV_TXN_READ_UNCOMMITTED (int)
Indicates a transaction isolation level of READ UNCOMMITTED. This value is used to set the TransactionIsolation level in the $connectionOptions array passed to <span class="function">sqlsrv_connect.

SQLSRV_TXN_READ_COMMITTED (int)
Indicates a transaction isolation level of READ COMMITTED. This value is used to set the TransactionIsolation level in the $connectionOptions array passed to <span class="function">sqlsrv_connect.

SQLSRV_TXN_REPEATABLE_READ (int)
Indicates a transaction isolation level of REPEATABLE READ. This value is used to set the TransactionIsolation level in the $connectionOptions array passed to <span class="function">sqlsrv_connect.

SQLSRV_TXN_SNAPSHOT (int)
Indicates a transaction isolation level of SNAPSHOT. This value is used to set the TransactionIsolation level in the $connectionOptions array passed to <span class="function">sqlsrv_connect.

SQLSRV_TXN_READ_SERIALIZABLE (int)
Indicates a transaction isolation level of SERIALIZABLE. This value is used to set the TransactionIsolation level in the $connectionOptions array passed to <span class="function">sqlsrv_connect.

SQLSRV_CURSOR_FORWARD (int)
Indicates a forward-only cursor. For usage information, see » Specifying a Cursor Type and Selecting Rows.

SQLSRV_CURSOR_STATIC (int)
Indicates a static cursor. For usage information, see » Specifying a Cursor Type and Selecting Rows.

SQLSRV_CURSOR_DYNAMIC (int)
Indicates a dynamic cursor. For usage information, see » Specifying a Cursor Type and Selecting Rows.

SQLSRV_CURSOR_KEYSET (int)
Indicates a keyset cursor. For usage information, see » Specifying a Cursor Type and Selecting Rows.

SQLSRV_CURSOR_BUFFERED (int)
Creates a client-side cursor query. Lets you access rows in any order. For usage information, see » Specifying a Cursor Type and Selecting Rows.

SQLSRV_SCROLL_NEXT (int)
Specifies which row to select in a result set. For usage information, see » Specifying a Cursor Type and Selecting Rows.

SQLSRV_SCROLL_PRIOR (int)
Specifies which row to select in a result set. For usage information, see » Specifying a Cursor Type and Selecting Rows.

SQLSRV_SCROLL_FIRST (int)
Specifies which row to select in a result set. For usage information, see » Specifying a Cursor Type and Selecting Rows.

SQLSRV_SCROLL_LAST (int)
Specifies which row to select in a result set. For usage information, see » Specifying a Cursor Type and Selecting Rows.

SQLSRV_SCROLL_ABSOLUTE (int)
Specifies which row to select in a result set. For usage information, see » Specifying a Cursor Type and Selecting Rows.

SQLSRV_SCROLL_RELATIVE (int)
Specifies which row to select in a result set. For usage information, see » Specifying a Cursor Type and Selecting Rows.

sqlsrv_begin_transaction

Begins a database transaction

说明

bool <span class="methodname">sqlsrv_begin_transaction ( <span class="methodparam">resource $conn )

The transaction begun by <span class="function">sqlsrv_begin_transaction includes all statements that were executed after the call to <span class="function">sqlsrv_begin_transaction and before calls to sqlsrv_rollback or <span class="function">sqlsrv_commit. Explicit transactions should be started and committed or rolled back using these functions instead of executing SQL statements that begin and commit/roll back transactions. For more information, see » SQLSRV Transactions.

参数

conn
The connection resource returned by a call to <span class="function">sqlsrv_connect.

返回值

成功时返回 true， 或者在失败时返回 false。

范例

示例 #1 sqlsrv_begin_transaction example

The following example demonstrates how to use <span class="function">sqlsrv_begin_transaction together with <span class="function">sqlsrv_commit and <span class="function">sqlsrv_rollback.

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"userName", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
    die( print_r( sqlsrv_errors(), true ));
}

/* Begin the transaction. */
if ( sqlsrv_begin_transaction( $conn ) === false ) {
     die( print_r( sqlsrv_errors(), true ));
}

/* Initialize parameter values. */
$orderId = 1; $qty = 10; $productId = 100;

/* Set up and execute the first query. */
$sql1 = "INSERT INTO OrdersTable (ID, Quantity, ProductID)
          VALUES (?, ?, ?)";
$params1 = array( $orderId, $qty, $productId );
$stmt1 = sqlsrv_query( $conn, $sql1, $params1 );

/* Set up and execute the second query. */
$sql2 = "UPDATE InventoryTable 
          SET Quantity = (Quantity - ?) 
          WHERE ProductID = ?";
$params2 = array($qty, $productId);
$stmt2 = sqlsrv_query( $conn, $sql2, $params2 );

/* If both queries were successful, commit the transaction. */
/* Otherwise, rollback the transaction. */
if( $stmt1 && $stmt2 ) {
     sqlsrv_commit( $conn );
     echo "Transaction committed.<br />";
} else {
     sqlsrv_rollback( $conn );
     echo "Transaction rolled back.<br />";
}
?>

以上例程的输出类似于：

参见

• sqlsrv_commit
• sqlsrv_rollback

sqlsrv_cancel

Cancels a statement

说明

bool <span class="methodname">sqlsrv_cancel ( <span class="methodparam">resource $stmt )

Cancels a statement. Any results associated with the statement that have not been consumed are deleted. After <span class="function">sqlsrv_cancel has been called, the specified statement can be re-executed if it was created with <span class="function">sqlsrv_prepare. Calling <span class="function">sqlsrv_cancel is not necessary if all the results associated with the statement have been consumed.

参数

stmt
The statement resource to be cancelled.

返回值

成功时返回 true， 或者在失败时返回 false。

范例

示例 #1 sqlsrv_cancel example

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}

$sql = "SELECT Sales FROM Table_1";

$stmt = sqlsrv_prepare( $conn, $sql);

if( $stmt === false ) {
     die( print_r( sqlsrv_errors(), true));
}

if( sqlsrv_execute( $stmt ) === false) {
     die( print_r( sqlsrv_errors(), true));
}

$salesTotal = 0;
$count = 0;

while( ($row = sqlsrv_fetch_array( $stmt)) && $salesTotal <=100000)
{
     $qty = $row[0];
     $price = $row[1];
     $salesTotal += ( $price * $qty);
     $count++;
}

echo "$count sales accounted for the first $$salesTotal in revenue.<br />";

// Cancel the pending results. The statement can be reused.
sqlsrv_cancel( $stmt);
?>

注释

The main difference between sqlsrv_cancel and sqlsrv_free_stmt is that a statement resource cancelled with sqlsrv_cancel can be re-executed if it was created with <span class="function">sqlsrv_prepare. A statement resource cancelled with sqlsrv_free_statement cannot be re-executed.

参见

• sqlsrv_free_stmt
• sqlsrv_prepare

sqlsrv_client_info

Returns information about the client and specified connection

说明

array <span class="methodname">sqlsrv_client_info ( <span class="methodparam">resource $conn )

Returns information about the client and specified connection

参数

conn
The connection about which information is returned.

返回值

Returns an associative array with keys described in the table below. Returns false otherwise.

范例

示例 #1 sqlsrv_client_info example

<?php
$serverName = "serverName\sqlexpress";
$connOptions = array("UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connOptions );

if( $conn === false ) {
    die( print_r( sqlsrv_errors(), true));
}

if( $client_info = sqlsrv_client_info( $conn)) {
    foreach( $client_info as $key => $value) {
        echo $key.": ".$value."<br />";
    }
} else {
    echo "Error in retrieving client info.<br />";
}
?>

参见

• sqlsrv_server_info

sqlsrv_close

Closes an open connection and releases resourses associated with the connection

说明

bool <span class="methodname">sqlsrv_close ( <span class="methodparam">resource $conn )

Closes an open connection and releases resourses associated with the connection.

参数

conn
The connection to be closed.

返回值

成功时返回 true， 或者在失败时返回 false。

范例

示例 #1 sqlsrv_close example

<?php
$serverName = "serverName\sqlexpres";
$connOptions = array("UID"=>"username", "PWD"=>"password", "Database"=>"dbname");
$conn = sqlsrv_connect( $serverName, $connOptions );
if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}

//-------------------------------------
// Perform database operations here.
//-------------------------------------

// Close the connection.
sqlsrv_close( $conn );
?>

参见

• sqlsrv_connect

sqlsrv_commit

Commits a transaction that was begun with <span class="function">sqlsrv_begin_transaction

说明

bool <span class="methodname">sqlsrv_commit ( <span class="methodparam">resource $conn )

Commits a transaction that was begun with <span class="function">sqlsrv_begin_transaction. The connection is returned to auto-commit mode after <span class="function">sqlsrv_commit is called. The transaction that is committed includes all statements that were executed after the call to sqlsrv_begin_transaction. Explicit transactions should be started and committed or rolled back using these functions instead of executing SQL statements that begin and commit/roll back transactions. For more information, see » SQLSRV Transactions.

参数

conn
The connection on which the transaction is to be committed.

返回值

成功时返回 true， 或者在失败时返回 false。

范例

示例 #1 sqlsrv_commit example

The following example demonstrates how to use <span class="function">sqlsrv_commit together with <span class="function">sqlsrv_begin_transaction and <span class="function">sqlsrv_rollback.

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"userName", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
    die( print_r( sqlsrv_errors(), true ));
}

/* Begin the transaction. */
if ( sqlsrv_begin_transaction( $conn ) === false ) {
     die( print_r( sqlsrv_errors(), true ));
}

/* Initialize parameter values. */
$orderId = 1; $qty = 10; $productId = 100;

/* Set up and execute the first query. */
$sql1 = "INSERT INTO OrdersTable (ID, Quantity, ProductID)
         VALUES (?, ?, ?)";
$params1 = array( $orderId, $qty, $productId );
$stmt1 = sqlsrv_query( $conn, $sql1, $params1 );

/* Set up and execute the second query. */
$sql2 = "UPDATE InventoryTable 
         SET Quantity = (Quantity - ?) 
         WHERE ProductID = ?";
$params2 = array($qty, $productId);
$stmt2 = sqlsrv_query( $conn, $sql2, $params2 );

/* If both queries were successful, commit the transaction. */
/* Otherwise, rollback the transaction. */
if( $stmt1 && $stmt2 ) {
     sqlsrv_commit( $conn );
     echo "Transaction committed.<br />";
} else {
     sqlsrv_rollback( $conn );
     echo "Transaction rolled back.<br />";
}
?>

参见

• sqlsrv_begin_transaction
• sqlsrv_rollback

sqlsrv_configure

Changes the driver error handling and logging configurations

说明

bool <span class="methodname">sqlsrv_configure ( <span class="methodparam">string $setting , mixed $value )

Changes the driver error handling and logging configurations.

参数

setting
The name of the setting to set. The possible values are "WarningsReturnAsErrors", "LogSubsystems", and "LogSeverity".

value
The value of the specified setting. The following table shows possible values:

返回值

成功时返回 true， 或者在失败时返回 false。

参见

• » SQLSRV Error Handling.
• » Logging SQLSRV Activity.

sqlsrv_connect

Opens a connection to a Microsoft SQL Server database

说明

resource <span class="methodname">sqlsrv_connect ( <span class="methodparam">string $serverName [, <span class="type">array $connectionInfo ] )

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.

参数

serverName
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).

connectionInfo
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.

返回值

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

范例

示例 #1 Connect using Windows Authentication.

<?php
$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.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
?>

示例 #2 Connect by specifying a user name and password.

<?php
$serverName = "serverName\\sqlexpress"; //serverName\instanceName
$connectionInfo = array( "Database"=>"dbName", "UID"=>"userName", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
?>

示例 #3 Connect on a specified port.

<?php
$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.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
?>

注释

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")).

参见

• sqlsrv_close
• sqlsrv_errors
• sqlsrv_query

sqlsrv_errors

Returns error and warning information about the last SQLSRV operation performed

说明

mixed <span class="methodname">sqlsrv_errors ([ <span class="methodparam">int $errorsOrWarnings ] )

Returns error and warning information about the last SQLSRV operation performed.

参数

errorsOrWarnings
Determines whether error information, warning information, or both are returned. If this parameter is not supplied, both error information and warning information are returned. The following are the supported values for this parameter: SQLSRV_ERR_ALL, SQLSRV_ERR_ERRORS, SQLSRV_ERR_WARNINGS.

返回值

If errors and/or warnings occurred on the last sqlsrv operation, an array of arrays containing error information is returned. If no errors and/or warnings occurred on the last sqlsrv operation, null is returned. The following table describes the structure of the returned arrays:

范例

示例 #1 functionname example

<?php
$serverName = "serverName/sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}

/* Set up a query to select an invalid column name. */
$sql = "SELECT BadColumnName FROM Table_1";

/* Execution of the query will fail because of the bad column name. */
$stmt = sqlsrv_query( $conn, $sql );
if( $stmt === false ) {
    if( ($errors = sqlsrv_errors() ) != null) {
        foreach( $errors as $error ) {
            echo "SQLSTATE: ".$error[ 'SQLSTATE']."<br />";
            echo "code: ".$error[ 'code']."<br />";
            echo "message: ".$error[ 'message']."<br />";
        }
    }
}
?>

注释

By default, warnings generated on a call to any SQLSRV function are treated as errors. This means that if a warning occurs on a call to a SQLSRV function, the function returns false. However, warnings that correspond to SQLSTATE values 01000, 01001, 01003, and 01S02 are never treated as errors. For information about changing this behavior, see sqlsrv_configure and the WarningsReturnAsErrors setting.

参见

• sqlsrv_configure

sqlsrv_execute

Executes a statement prepared with <span class="function">sqlsrv_prepare

说明

bool <span class="methodname">sqlsrv_execute ( <span class="methodparam">resource $stmt )

Executes a statement prepared with <span class="function">sqlsrv_prepare. This function is ideal for executing a prepared statement multiple times with different parameter values.

参数

stmt
A statement resource returned by <span class="function">sqlsrv_prepare.

返回值

成功时返回 true， 或者在失败时返回 false。

范例

示例 #1 sqlsrv_execute example

This example demonstrates how to prepare a statement with <span class="function">sqlsrv_prepare and re-execute it multiple times (with different parameter values) using <span class="function">sqlsrv_execute.

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false) {
    die( print_r( sqlsrv_errors(), true));
}

$sql = "UPDATE Table_1
        SET OrderQty = ?
        WHERE SalesOrderID = ?";

// Initialize parameters and prepare the statement. 
// Variables $qty and $id are bound to the statement, $stmt.
$qty = 0; $id = 0;
$stmt = sqlsrv_prepare( $conn, $sql, array( &$qty, &$id));
if( !$stmt ) {
    die( print_r( sqlsrv_errors(), true));
}

// Set up the SalesOrderDetailID and OrderQty information. 
// This array maps the order ID to order quantity in key=>value pairs.
$orders = array( 1=>10, 2=>20, 3=>30);

// Execute the statement for each order.
foreach( $orders as $id => $qty) {
    // Because $id and $qty are bound to $stmt1, their updated
    // values are used with each execution of the statement. 
    if( sqlsrv_execute( $stmt ) === false ) {
          die( print_r( sqlsrv_errors(), true));
    }
}
?>

注释

When you prepare a statement that uses variables as parameters, the variables are bound to the statement. This means that if you update the values of the variables, the next time you execute the statement it will run with updated parameter values. For statements that you plan to execute only once, use sqlsrv_query.

参见

• sqlsrv_prepare
• sqlsrv_query

sqlsrv_fetch_array

Returns a row as an array

说明

array <span class="methodname">sqlsrv_fetch_array ( <span class="methodparam">resource $stmt [, int $fetchType [, <span class="type">int $row [, <span class="methodparam">int $offset ]]] )

Returns the next available row of data as an associative array, a numeric array, or both (the default).

参数

stmt
A statement resource returned by sqlsrv_query or sqlsrv_prepare.

fetchType
A predefined constant specifying the type of array to return. Possible values are SQLSRV_FETCH_ASSOC, SQLSRV_FETCH_NUMERIC, and SQLSRV_FETCH_BOTH (the default).

A fetch type of SQLSRV_FETCH_ASSOC should not be used when consuming a result set with multiple columns of the same name.

row
Specifies the row to access in a result set that uses a scrollable cursor. Possible values are SQLSRV_SCROLL_NEXT, SQLSRV_SCROLL_PRIOR, SQLSRV_SCROLL_FIRST, SQLSRV_SCROLL_LAST, SQLSRV_SCROLL_ABSOLUTE and, SQLSRV_SCROLL_RELATIVE (the default). When this parameter is specified, the fetchType must be explicitly defined.

offset
Specifies the row to be accessed if the row parameter is set to SQLSRV_SCROLL_ABSOLUTE or SQLSRV_SCROLL_RELATIVE. Note that the first row in a result set has index 0.

返回值

Returns an array on success, null if there are no more rows to return, and false if an error occurs.

范例

示例 #1 Retrieving an associative array.

<?php
$serverName = "serverName\instanceName";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo );
if( $conn === false ) {
    die( print_r( sqlsrv_errors(), true));
}

$sql = "SELECT FirstName, LastName FROM SomeTable";
$stmt = sqlsrv_query( $conn, $sql );
if( $stmt === false) {
    die( print_r( sqlsrv_errors(), true) );
}

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
      echo $row['LastName'].", ".$row['FirstName']."<br />";
}

sqlsrv_free_stmt( $stmt);
?>

示例 #2 Retrieving a numeric array.

<?php
$serverName = "serverName\instanceName";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo );
if( $conn === false ) {
    die( print_r( sqlsrv_errors(), true));
}

$sql = "SELECT FirstName, LastName FROM SomeTable";
$stmt = sqlsrv_query( $conn, $sql );
if( $stmt === false) {
    die( print_r( sqlsrv_errors(), true) );
}

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC) ) {
      echo $row[0].", ".$row[1]."<br />";
}

sqlsrv_free_stmt( $stmt);
?>

注释

Not specifying the fetchType or explicitly using the SQLSRV_FETCH_TYPE constant in the examples above will return an array that has both associative and numeric keys.

If more than one column is returned with the same name, the last column will take precedence. To avoid field name collisions, use aliases.

If a column with no name is returned, the associative key for the array element will be an empty string ("").

参见

• sqlsrv_connect
• sqlsrv_query
• sqlsrv_errors
• sqlsrv_fetch

sqlsrv_fetch_object

Retrieves the next row of data in a result set as an object

说明

mixed <span class="methodname">sqlsrv_fetch_object ( <span class="methodparam">resource $stmt [, string $className [, <span class="type">array $ctorParams [, <span class="methodparam">int $row [, int $offset ]]]] )

Retrieves the next row of data in a result set as an instance of the specified class with properties that match the row field names and values that correspond to the row field values.

参数

stmt
A statement resource created by <span class="function">sqlsrv_query or <span class="function">sqlsrv_execute.

className
The name of the class to instantiate. If no class name is specified, stdClass is instantiated.

ctorParams
Values passed to the constructor of the specified class. If the constructor of the specified class takes parameters, the ctorParams array must be supplied.

row
The row to be accessed. This parameter can only be used if the specified statement was prepared with a scrollable cursor. In that case, this parameter can take on one of the following values:

• SQLSRV_SCROLL_NEXT
• SQLSRV_SCROLL_PRIOR
• SQLSRV_SCROLL_FIRST
• SQLSRV_SCROLL_LAST
• SQLSRV_SCROLL_ABSOLUTE
• SQLSRV_SCROLL_RELATIVE

offset
Specifies the row to be accessed if the row parameter is set to SQLSRV_SCROLL_ABSOLUTE or SQLSRV_SCROLL_RELATIVE. Note that the first row in a result set has index 0.

返回值

Returns an object on success, null if there are no more rows to return, and false if an error occurs or if the specified class does not exist.

范例

示例 #1 sqlsrv_fetch_object example

The following example demonstrates how to retrieve a row as a stdClass object.

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}

$sql = "SELECT fName, lName FROM Table_1";
$stmt = sqlsrv_query( $conn, $sql);
if( $stmt === false ) {
     die( print_r( sqlsrv_errors(), true));
}

// Retrieve each row as an object.
// Because no class is specified, each row will be retrieved as a stdClass object.
// Property names correspond to field names.
while( $obj = sqlsrv_fetch_object( $stmt)) {
      echo $obj->fName.", ".$obj->lName."<br />";
}
?>

注释

If a class name is specified with the optional $className parameter and the class has properties whose names match the result set field names, the corresponding result set values are applied to the properties. If a result set field name does not match a class property, a property with the result set field name is added to the object and the result set value is applied to the property. The following rules apply when using the $className parameter:

• Field-property name matching is case-sensitive.
• Field-property matching occurs regardless of access modifiers.
• Class property data types are ignored when applying a field value to a property.
• If the class does not exist, the function returns false and adds an error to the error collection.

Regardless of whether the $className parameter is supplied, if a field with no name is returned, the field value will be ignored and a warning will be added to the error collection.

When consuming a result set that has multiple columns with the same name, it may be better to use <span class="function">sqlsrv_fetch_array or the combination of <span class="function">sqlsrv_fetch and <span class="function">sqlsrv_get_field.

参见

• sqlsrv_fetch
• sqlsrv_fetch_array

sqlsrv_fetch

Makes the next row in a result set available for reading

说明

mixed <span class="methodname">sqlsrv_fetch ( <span class="methodparam">resource $stmt [, int $row [, <span class="type">int $offset ]] )

Makes the next row in a result set available for reading. Use <span class="function">sqlsrv_get_field to read the fields of the row.

参数

stmt
A statement resource created by executing <span class="function">sqlsrv_query or <span class="function">sqlsrv_execute.

row
The row to be accessed. This parameter can only be used if the specified statement was prepared with a scrollable cursor. In that case, this parameter can take on one of the following values:

• SQLSRV_SCROLL_NEXT
• SQLSRV_SCROLL_PRIOR
• SQLSRV_SCROLL_FIRST
• SQLSRV_SCROLL_LAST
• SQLSRV_SCROLL_ABSOLUTE
• SQLSRV_SCROLL_RELATIVE

offset
Specifies the row to be accessed if the row parameter is set to SQLSRV_SCROLL_ABSOLUTE or SQLSRV_SCROLL_RELATIVE. Note that the first row in a result set has index 0.

返回值

Returns true if the next row of a result set was successfully retrieved, false if an error occurs, and null if there are no more rows in the result set.

范例

示例 #1 sqlsrv_fetch example

The following example demonstrates how to retrieve a row with <span class="function">sqlsrv_fetch and get the row fields with <span class="function">sqlsrv_get_field.

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}

$sql = "SELECT Name, Comment 
        FROM Table_1
        WHERE ReviewID=1";
$stmt = sqlsrv_query( $conn, $sql);
if( $stmt === false ) {
     die( print_r( sqlsrv_errors(), true));
}

// Make the first (and in this case, only) row of the result set available for reading.
if( sqlsrv_fetch( $stmt ) === false) {
     die( print_r( sqlsrv_errors(), true));
}

// Get the row fields. Field indices start at 0 and must be retrieved in order.
// Retrieving row fields by name is not supported by sqlsrv_get_field.
$name = sqlsrv_get_field( $stmt, 0);
echo "$name: ";

$comment = sqlsrv_get_field( $stmt, 1);
echo $comment;
?>

参见

• sqlsrv_get_field
• sqlsrv_fetch_array
• sqlsrv_fetch_object

sqlsrv_field_metadata

Retrieves metadata for the fields of a statement prepared by <span class="function">sqlsrv_prepare or <span class="function">sqlsrv_query

说明

mixed <span class="methodname">sqlsrv_field_metadata ( <span class="methodparam">resource $stmt )

Retrieves metadata for the fields of a statement prepared by <span class="function">sqlsrv_prepare or <span class="function">sqlsrv_query. <span class="function">sqlsrv_field_metadata can be called on a statement before or after statement execution.

参数

stmt
The statement resource for which metadata is returned.

返回值

Returns an array of arrays on success. Otherwise, false is returned. Each returned array is described by the following table:

For more information, see » sqlsrv_field_metadata in the Microsoft SQLSRV documentation.

范例

示例 #1 sqlsrv_field_metadata example

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"AdventureWorks", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
   die( print_r( sqlsrv_errors(), true));
}

$sql = "SELECT * FROM Table_1";
$stmt = sqlsrv_prepare( $conn, $sql );

foreach( sqlsrv_field_metadata( $stmt ) as $fieldMetadata ) {
    foreach( $fieldMetadata as $name => $value) {
       echo "$name: $value<br />";
    }
      echo "<br />";
}
?>

参见

• sqlsrv_client_info

sqlsrv_free_stmt

Frees all resources for the specified statement

说明

bool <span class="methodname">sqlsrv_free_stmt ( <span class="methodparam">resource $stmt )

Frees all resources for the specified statement. The statement cannot be used after sqlsrv_free_stmt has been called on it. If sqlsrv_free_stmt is called on an in-progress statement that alters server state, statement execution is terminated and the statement is rolled back.

参数

stmt
The statement for which resources are freed. Note that null is a valid parameter value. This allows the function to be called multiple times in a script.

返回值

成功时返回 true， 或者在失败时返回 false。

范例

示例 #1 sqlsrv_free_stmt example

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}

$stmt = sqlsrv_query( $conn, "SELECT * FROM Table_1");
if( $stmt === false ) {
     die( print_r( sqlsrv_errors(), true));
}

/*-------------------------------
     Process query results here.
-------------------------------*/

/* Free the statement resources. */
sqlsrv_free_stmt( $stmt);

?>

注释

The main difference between <span class="function">sqlsrv_free_stmt and <span class="function">sqlsrv_cancel is that a statement resource cancelled with sqlsrv_cancel can be re-executed if it was created with <span class="function">sqlsrv_prepare. A statement resource cancelled with sqlsrv_free_statement cannot be re-executed.

参见

• sqlsrv_cancel

sqlsrv_get_config

Returns the value of the specified configuration setting

说明

mixed <span class="methodname">sqlsrv_get_config ( <span class="methodparam">string $setting )

Returns the value of the specified configuration setting.

参数

setting
The name of the setting for which the value is returned. For a list of configurable settings, see <span class="function">sqlsrv_configure.

返回值

Returns the value of the specified setting. If an invalid setting is specified, false is returned.

参见

• sqlsrv_configure

sqlsrv_get_field

Gets field data from the currently selected row

说明

mixed <span class="methodname">sqlsrv_get_field ( <span class="methodparam">resource $stmt , int $fieldIndex [, <span class="type">int $getAsType ] )

Gets field data from the currently selected row. Fields must be accessed in order. Field indices start at 0.

参数

stmt
A statement resource returned by <span class="function">sqlsrv_query or <span class="function">sqlsrv_execute.

fieldIndex
The index of the field to be retrieved. Field indices start at 0. Fields must be accessed in order. i.e. If you access field index 1, then field index 0 will not be available.

getAsType
The PHP data type for the returned field data. If this parameter is not set, the field data will be returned as its default PHP data type. For information about default PHP data types, see » Default PHP Data Types in the Microsoft SQLSRV documentation.

返回值

Returns data from the specified field on success. Returns false otherwise.

范例

示例 #1 sqlsrv_get_field example

The following example demonstrates how to retrieve a row with <span class="function">sqlsrv_fetch and get the row fields with <span class="function">sqlsrv_get_field.

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}

$sql = "SELECT Name, Comment 
        FROM Table_1
        WHERE ReviewID=1";
$stmt = sqlsrv_query( $conn, $sql);
if( $stmt === false ) {
     die( print_r( sqlsrv_errors(), true));
}

// Make the first (and in this case, only) row of the result set available for reading.
if( sqlsrv_fetch( $stmt ) === false) {
     die( print_r( sqlsrv_errors(), true));
}

// Get the row fields. Field indices start at 0 and must be retrieved in order.
// Retrieving row fields by name is not supported by sqlsrv_get_field.
$name = sqlsrv_get_field( $stmt, 0);
echo "$name: ";

$comment = sqlsrv_get_field( $stmt, 1);
echo $comment;
?>

参见

• sqlsrv_fetch
• sqlsrv_fetch_array
• sqlsrv_fetch_object

sqlsrv_has_rows

Indicates whether the specified statement has rows

说明

bool <span class="methodname">sqlsrv_has_rows ( <span class="methodparam">resource $stmt )

Indicates whether the specified statement has rows.

参数

stmt
A statement resource returned by <span class="function">sqlsrv_query or <span class="function">sqlsrv_execute.

返回值

Returns true if the specified statement has rows and false if the statement does not have rows or if an error occurred.

范例

示例 #1 sqlsrv_has_rows example

<?php
$server = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password" )
$conn = sqlsrv_connect( $server, $connectionInfo );

$stmt = sqlsrv_query( $conn, "SELECT * FROM Table_1");

if ($stmt) {
   $rows = sqlsrv_has_rows( $stmt );
   if ($rows === true)
      echo "There are rows. <br />";
   else 
      echo "There are no rows. <br />";
}
?>

参见

• sqlsrv_num_rows
• sqlsrv_query

sqlsrv_next_result

Makes the next result of the specified statement active

说明

mixed <span class="methodname">sqlsrv_next_result ( <span class="methodparam">resource $stmt )

Makes the next result of the specified statement active. Results include result sets, row counts, and output parameters.

参数

stmt
The statement on which the next result is being called.

返回值

Returns true if the next result was successfully retrieved, false if an error occurred, and null if there are no more results to retrieve.

范例

示例 #1 sqlsrv_next_result example

The following example executes a batch query that inserts into a table and then selects from the table. This produces two results on the statement: one for the rows affected by the INSERT and one for the rows returned by the SELECT. To get to the rows returned by the SELECT, <span class="function">sqlsrv_next_result must be called to move past the first result.

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array("Database"=>"dbName", "UID"=>"userName", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

$query = "INSERT INTO Table_1 (id, data) VALUES (?,?); SELECT * FROM TABLE_1;";
$params = array(1, "some data");
$stmt = sqlsrv_query($conn, $query, $params);

// Consume the first result (rows affected by INSERT) without calling sqlsrv_next_result.
echo "Rows affected: ".sqlsrv_rows_affected($stmt)."<br />";

// Move to the next result and display results.
$next_result = sqlsrv_next_result($stmt);
if( $next_result ) {
   while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
      echo $row['id'].": ".$row['data']."<br />"; 
   }
} elseif( is_null($next_result)) {
     echo "No more results.<br />";
} else {
     die(print_r(sqlsrv_errors(), true));
}
?>

参见

• sqlsrv_query
• sqlsrv_fetch_array
• sqlsrv_rows_affected

sqlsrv_num_fields

Retrieves the number of fields (columns) on a statement

说明

mixed <span class="methodname">sqlsrv_num_fields ( <span class="methodparam">resource $stmt )

Retrieves the number of fields (columns) on a statement.

参数

stmt
The statement for which the number of fields is returned. <span class="function">sqlsrv_num_fields can be called on a statement before or after statement execution.

返回值

Returns the number of fields on success. Returns false otherwise.

范例

示例 #1 sqlsrv_num_fields example

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
   die( print_r( sqlsrv_errors(), true));
}

$sql = "SELECT * FROM Table_1";
$stmt = sqlsrv_query($conn, $sql);
if( $stmt === false) {
   die( print_r( sqlsrv_errors(), true));
}

$numFields = sqlsrv_num_fields( $stmt );

while( sqlsrv_fetch( $stmt )) {
   // Iterate through the fields of each row.
   for($i = 0; $i < $numFields; $i++) { 
      echo sqlsrv_get_field($stmt, $i)." ";
   }
   echo "<br />";
}
?>

参见

• sqlsrv_field_metadata
• sqlsrv_fetch
• sqlsrv_get_field

sqlsrv_num_rows

Retrieves the number of rows in a result set

说明

mixed <span class="methodname">sqlsrv_num_rows ( <span class="methodparam">resource $stmt )

Retrieves the number of rows in a result set. This function requires that the statement resource be created with a static or keyset cursor. For more information, see sqlsrv_query, sqlsrv_prepare, or » Specifying a Cursor Type and Selecting Rows in the Microsoft SQLSRV documentation.

参数

stmt
The statement for which the row count is returned. The statement resource must be created with a static or keyset cursor. For more information, see sqlsrv_query, <span class="function">sqlsrv_prepare, or » Specifying a Cursor Type and Selecting Rows in the Microsoft SQLSRV documentation.

返回值

Returns the number of rows retrieved on success and false if an error occurred. If a forward cursor (the default) or dynamic cursor is used, false is returned.

范例

示例 #1 sqlsrv_num_rows example

<?php
$server = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password" );
$conn = sqlsrv_connect( $server, $connectionInfo );

$sql = "SELECT * FROM Table_1";
$params = array();
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
$stmt = sqlsrv_query( $conn, $sql , $params, $options );

$row_count = sqlsrv_num_rows( $stmt );

if ($row_count === false)
   echo "Error in retrieveing row count.";
else
   echo $row_count;
?>

参见

• sqlsrv_has_rows
• sqlsrv_rows_affected

sqlsrv_prepare

Prepares a query for execution

说明

mixed <span class="methodname">sqlsrv_prepare ( <span class="methodparam">resource $conn , string $sql [, array $params [, <span class="type">array $options ]] )

Prepares a query for execution. This function is ideal for preparing a query that will be executed multiple times with different parameter values.

参数

conn
A connection resource returned by <span class="function">sqlsrv_connect.

sql
The string that defines the query to be prepared and executed.

params
An array specifying parameter information when executing a parameterized query. Array elements can be any of the following:

• A literal value
• A PHP variable
• An array with this structure: array($value [, $direction [, $phpType [, $sqlType]]])

The following table describes the elements in the array structure above:

options
An array specifying query property options. The supported keys are described in the following table:

返回值

Returns a statement resource on success and false if an error occurred.

范例

示例 #1 sqlsrv_prepare example

This example demonstrates how to prepare a statement with <span class="function">sqlsrv_prepare and re-execute it multiple times (with different parameter values) using <span class="function">sqlsrv_execute.

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false) {
    die( print_r( sqlsrv_errors(), true));
}

$sql = "UPDATE Table_1
        SET OrderQty = ?
        WHERE SalesOrderID = ?";

// Initialize parameters and prepare the statement. 
// Variables $qty and $id are bound to the statement, $stmt.
$qty = 0; $id = 0;
$stmt = sqlsrv_prepare( $conn, $sql, array( &$qty, &$id));
if( !$stmt ) {
    die( print_r( sqlsrv_errors(), true));
}

// Set up the SalesOrderDetailID and OrderQty information. 
// This array maps the order ID to order quantity in key=>value pairs.
$orders = array( 1=>10, 2=>20, 3=>30);

// Execute the statement for each order.
foreach( $orders as $id => $qty) {
    // Because $id and $qty are bound to $stmt1, their updated
    // values are used with each execution of the statement. 
    if( sqlsrv_execute( $stmt ) === false ) {
          die( print_r( sqlsrv_errors(), true));
    }
}
?>

注释

When you prepare a statement that uses variables as parameters, the variables are bound to the statement. This means that if you update the values of the variables, the next time you execute the statement it will run with updated parameter values. For statements that you plan to execute only once, use sqlsrv_query.

参见

• sqlsrv_execute
• sqlsrv_query

sqlsrv_query

Prepares and executes a query

说明

mixed <span class="methodname">sqlsrv_query ( <span class="methodparam">resource $conn , string $sql [, array $params [, <span class="type">array $options ]] )

Prepares and executes a query.

参数

conn
A connection resource returned by <span class="function">sqlsrv_connect.

sql
The string that defines the query to be prepared and executed.

params
An array specifying parameter information when executing a parameterized query. Array elements can be any of the following:

• A literal value
• A PHP variable
• An array with this structure: array($value [, $direction [, $phpType [, $sqlType]]])

The following table describes the elements in the array structure above:

options
An array specifying query property options. The supported keys are described in the following table:

返回值

Returns a statement resource on success and false if an error occurred.

范例

示例 #1 sqlsrv_query example

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password" );
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}

$sql = "INSERT INTO Table_1 (id, data) VALUES (?, ?)";
$params = array(1, "some data");

$stmt = sqlsrv_query( $conn, $sql, $params);
if( $stmt === false ) {
     die( print_r( sqlsrv_errors(), true));
}
?>

注释

For statements that you plan to execute only once, use <span class="function">sqlsrv_query. If you intend to re-execute a statement with different parameter values, use the combination of <span class="function">sqlsrv_prepare and <span class="function">sqlsrv_execute.

参见

• sqlsrv_prepare
• sqlsrv_execute

sqlsrv_rollback

Rolls back a transaction that was begun with <span class="function">sqlsrv_begin_transaction

说明

bool <span class="methodname">sqlsrv_rollback ( <span class="methodparam">resource $conn )

Rolls back a transaction that was begun with <span class="function">sqlsrv_begin_transaction and returns the connection to auto-commit mode.

参数

conn
The connection resource returned by a call to <span class="function">sqlsrv_connect.

返回值

成功时返回 true， 或者在失败时返回 false。

范例

示例 #1 sqlsrv_rollback example

The following example demonstrates how to use <span class="function">sqlsrv_begin_transaction together with <span class="function">sqlsrv_commit and <span class="function">sqlsrv_rollback.

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"userName", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
    die( print_r( sqlsrv_errors(), true ));
}

/* Begin the transaction. */
if ( sqlsrv_begin_transaction( $conn ) === false ) {
     die( print_r( sqlsrv_errors(), true ));
}

/* Initialize parameter values. */
$orderId = 1; $qty = 10; $productId = 100;

/* Set up and execute the first query. */
$sql1 = "INSERT INTO OrdersTable (ID, Quantity, ProductID)
         VALUES (?, ?, ?)";
$params1 = array( $orderId, $qty, $productId );
$stmt1 = sqlsrv_query( $conn, $sql1, $params1 );

/* Set up and execute the second query. */
$sql2 = "UPDATE InventoryTable 
         SET Quantity = (Quantity - ?) 
         WHERE ProductID = ?";
$params2 = array($qty, $productId);
$stmt2 = sqlsrv_query( $conn, $sql2, $params2 );

/* If both queries were successful, commit the transaction. */
/* Otherwise, rollback the transaction. */
if( $stmt1 && $stmt2 ) {
     sqlsrv_commit( $conn );
     echo "Transaction committed.<br />";
} else {
     sqlsrv_rollback( $conn );
     echo "Transaction rolled back.<br />";
}
?>

参见

• sqlsrv_begin_transaction
• sqlsrv_commit

sqlsrv_rows_affected

Returns the number of rows modified by the last INSERT, UPDATE, or DELETE query executed

说明

int <span class="methodname">sqlsrv_rows_affected ( <span class="methodparam">resource $stmt )

Returns the number of rows modified by the last INSERT, UPDATE, or DELETE query executed. For information about the number of rows returned by a SELECT query, see sqlsrv_num_rows.

参数

stmt
The executed statement resource for which the number of affected rows is returned.

返回值

Returns the number of rows affected by the last INSERT, UPDATE, or DELETE query. If no rows were affected, 0 is returned. If the number of affected rows cannot be determined, -1 is returned. If an error occurred, false is returned.

范例

示例 #1 sqlsrv_rows_affected example

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password" );
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}

$sql = "UPDATE Table_1 SET data = ? WHERE id = ?";

$params = array("updated data", 1);

$stmt = sqlsrv_query( $conn, $sql, $params);

$rows_affected = sqlsrv_rows_affected( $stmt);
if( $rows_affected === false) {
     die( print_r( sqlsrv_errors(), true));
} elseif( $rows_affected == -1) {
      echo "No information available.<br />";
} else {
      echo $rows_affected." rows were updated.<br />";
}
?>

参见

• sqlsrv_num_rows

sqlsrv_send_stream_data

Sends data from parameter streams to the server

说明

bool <span class="methodname">sqlsrv_send_stream_data ( <span class="methodparam">resource $stmt )

Send data from parameter streams to the server. Up to 8 KB of data is sent with each call.

参数

stmt
A statement resource returned by <span class="function">sqlsrv_query or <span class="function">sqlsrv_execute.

返回值

Returns true if there is more data to send and false if there is not.

范例

示例 #1 sqlsrv_send_stream_data example

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password" );
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}

$sql = "UPDATE Table_1 SET data = ( ?) WHERE id = 100";

// Open parameter data as a stream and put it in the $params array.
$data = fopen( "data://text/plain,[ Lengthy content here. ]", "r");
$params = array( &$data);

// Prepare the statement. Use the $options array to turn off the
// default behavior, which is to send all stream data at the time of query
// execution.
$options = array("SendStreamParamsAtExec"=>0);
$stmt = sqlsrv_prepare( $conn, $sql, $params, $options);

sqlsrv_execute( $stmt);

// Send up to 8K of parameter data to the server 
// with each call to sqlsrv_send_stream_data.
$i = 1;
while( sqlsrv_send_stream_data( $stmt)) {
      $i++;
}
echo "$i calls were made.";
?>

参见

• sqlsrv_prepare
• sqlsrv_query

sqlsrv_server_info

Returns information about the server

说明

array <span class="methodname">sqlsrv_server_info ( <span class="methodparam">resource $conn )

Returns information about the server.

参数

conn
The connection resource that connects the client and the server.

返回值

Returns an array as described in the following table:

范例

示例 #1 sqlsrv_server_info example

<?php
$serverName = "serverName\sqlexpress";
$conn = sqlsrv_connect( $serverName);
if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}

$server_info = sqlsrv_server_info( $conn);
if( $server_info )
{
    foreach( $server_info as $key => $value) {
       echo $key.": ".$value."<br />";
    }
} else {
      die( print_r( sqlsrv_errors(), true));
}
?>

参见

• sqlsrv_client_info

目录

• sqlsrv_begin_transaction — Begins a database transaction
• sqlsrv_cancel — Cancels a statement
• sqlsrv_client_info — Returns information about the client and specified connection
• sqlsrv_close — Closes an open connection and releases resourses associated with the connection
• sqlsrv_commit — Commits a transaction that was begun with sqlsrv_begin_transaction
• sqlsrv_configure — Changes the driver error handling and logging configurations
• sqlsrv_connect — Opens a connection to a Microsoft SQL Server database
• sqlsrv_errors — Returns error and warning information about the last SQLSRV operation performed
• sqlsrv_execute — Executes a statement prepared with sqlsrv_prepare
• sqlsrv_fetch_array — Returns a row as an array
• sqlsrv_fetch_object — Retrieves the next row of data in a result set as an object
• sqlsrv_fetch — Makes the next row in a result set available for reading
• sqlsrv_field_metadata — Retrieves metadata for the fields of a statement prepared by sqlsrv_prepare or sqlsrv_query
• sqlsrv_free_stmt — Frees all resources for the specified statement
• sqlsrv_get_config — Returns the value of the specified configuration setting
• sqlsrv_get_field — Gets field data from the currently selected row
• sqlsrv_has_rows — Indicates whether the specified statement has rows
• sqlsrv_next_result — Makes the next result of the specified statement active
• sqlsrv_num_fields — Retrieves the number of fields (columns) on a statement
• sqlsrv_num_rows — Retrieves the number of rows in a result set
• sqlsrv_prepare — Prepares a query for execution
• sqlsrv_query — Prepares and executes a query
• sqlsrv_rollback — Rolls back a transaction that was begun with sqlsrv_begin_transaction
• sqlsrv_rows_affected — Returns the number of rows modified by the last INSERT, UPDATE, or DELETE query executed
• sqlsrv_send_stream_data — Sends data from parameter streams to the server
• sqlsrv_server_info — Returns information about the server