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.

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.

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.

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

Community Additions

Show: