Specifying a Cursor Type and Selecting Rows

The SQLSRV driver lets you create a result set with rows that you can access in any order, depending on the cursor type. For information on how to specify a cursor using the PDO_SQLSRV driver, see PDO::prepare. The rest of this topic discusses how to use cursors in the SQLSRV driver.

Cursor Types

When you create a result set with sqlsrv_query or with sqlsrv_prepare, you can specify the type of cursor. By default, a forward-only cursor is used, which lets you move one row at a time starting at the first row of the result set until you reach the end of the result set. This was the only cursor type available in version 1.0 of the Microsoft Drivers for PHP for SQL Server.

Starting with version 1.1 of the Microsoft Drivers for PHP for SQL Server, you can create a result set with a scrollable cursor, which allows you to access any row in the result set, in any order. The following table lists the values that can be passed to the Scrollable option in sqlsrv_query or sqlsrv_prepare.

Option	Description
SQLSRV_CURSOR_FORWARD	Lets you move one row at a time starting at the first row of the result set until you reach the end of the result set. This is the default cursor type. sqlsrv_num_rows returns an error for result sets created with this cursor type. forward is the abbreviated form of SQLSRV_CURSOR_FORWARD.
SQLSRV_CURSOR_STATIC	Lets you access rows in any order but will not reflect changes in the database. static is the abbreviated form of SQLSRV_CURSOR_STATIC.
SQLSRV_CURSOR_DYNAMIC	Lets you access rows in any order and will reflect changes in the database. sqlsrv_num_rows returns an error for result sets created with this cursor type. dynamic is the abbreviated form of SQLSRV_CURSOR_DYNAMIC.
SQLSRV_CURSOR_KEYSET	Lets you access rows in any order. However, a keyset cursor does not update the row count if a row is deleted from the table (a deleted row is returned with no values). keyset is the abbreviated form of SQLSRV_CURSOR_KEYSET.

If a query generates multiple result sets, the Scrollable option applies to all result sets.

Selecting Rows in a Result Set

After you create a static, dynamic, or keyset result set, you can use sqlsrv_fetch, sqlsrv_fetch_array, or sqlsrv_fetch_object to specify a row.

The following table describes the values you can specify in the row parameter.

Parameter	Description
SQLSRV_SCROLL_NEXT	Specifies the next row. This is the default value, if you do not specify the row parameter for a scrollable result set.
SQLSRV_SCROLL_PRIOR	Specifies the row before the current row.
SQLSRV_SCROLL_FIRST	Specifies the first row in the result set.
SQLSRV_SCROLL_LAST	Specifies the last row in the result set.
SQLSRV_SCROLL_ABSOLUTE	Specifies the row specified with the offset parameter.
SQLSRV_SCROLL_RELATIVE	Specifies the row specified with the offset parameter from the current row. Negative numbers are allowed.

Example

Description

The following example shows the effect of the various cursors. On line 33 of the example, you see the first of three query statements that specify different cursors. Two of the query statements are commented. Each time you run the program, use a different cursor type to see the effect of the database update on line 47.

Code

<?php
$server = "server_name";
$conn = sqlsrv_connect( $server, array( 'Database' => 'test' ));
if ( $conn === false ) {
   die( print_r( sqlsrv_errors(), true ));
}

$stmt = sqlsrv_query( $conn, "DROP TABLE dbo.ScrollTest" );
if ( $stmt !== false ) { 
   sqlsrv_free_stmt( $stmt ); 
}

$stmt = sqlsrv_query( $conn, "CREATE TABLE ScrollTest (id int, value char(10))" );
if ( $stmt === false ) {
   die( print_r( sqlsrv_errors(), true ));
}

$stmt = sqlsrv_query( $conn, "INSERT INTO ScrollTest (id, value) VALUES(?,?)", array( 1, "Row 1" ));
if ( $stmt === false ) {
   die( print_r( sqlsrv_errors(), true ));
}

$stmt = sqlsrv_query( $conn, "INSERT INTO ScrollTest (id, value) VALUES(?,?)", array( 2, "Row 2" ));
if ( $stmt === false ) {
   die( print_r( sqlsrv_errors(), true ));
}

$stmt = sqlsrv_query( $conn, "INSERT INTO ScrollTest (id, value) VALUES(?,?)", array( 3, "Row 3" ));
if ( $stmt === false ) {
   die( print_r( sqlsrv_errors(), true ));
}

$stmt = sqlsrv_query( $conn, "SELECT * FROM ScrollTest", array(), array( "Scrollable" => 'keyset' ));
// $stmt = sqlsrv_query( $conn, "SELECT * FROM ScrollTest", array(), array( "Scrollable" => 'dynamic' ));
// $stmt = sqlsrv_query( $conn, "SELECT * FROM ScrollTest", array(), array( "Scrollable" => 'static' ));

$rows = sqlsrv_has_rows( $stmt );
if ( $rows != true ) {
   die( "Should have rows" );
}

$result = sqlsrv_fetch( $stmt, SQLSRV_SCROLL_LAST );
$field1 = sqlsrv_get_field( $stmt, 0 );
$field2 = sqlsrv_get_field( $stmt, 1 );
echo "\n$field1 $field2\n";

$stmt2 = sqlsrv_query( $conn, "delete from ScrollTest where id = 3" );
// or
// $stmt2 = sqlsrv_query( $conn, "UPDATE ScrollTest SET id = 4 WHERE id = 3" );
if ( $stmt2 !== false ) { 
   sqlsrv_free_stmt( $stmt2 ); 
}

$result = sqlsrv_fetch( $stmt, SQLSRV_SCROLL_LAST );
$field1 = sqlsrv_get_field( $stmt, 0 );
$field2 = sqlsrv_get_field( $stmt, 1 );
echo "\n$field1 $field2\n";

sqlsrv_free_stmt( $stmt );
sqlsrv_close( $conn );
?>

See Also

Concepts

Retrieving Data