PHP - sqlsrv 함수 사용 시 주의 사항

아래 srv_num_rows() 함수에 파라미터를 누락하면 select 쿼리 결과의 행수가 구해지지 않는다.

 

<?
$connectionInfo = array("Database" => MSSQL_DB, "UID" => MSSQL_USER, "PWD" => MSSQL_PASSWORD, "CharacterSet" => "UTF-8");
$conn = sqlsrv_connect(MSSQL_HOST, $connectionInfo);

$sql = "SELECT TOP 3 * FROM S_MALL_ORDERS";
$params = array();
$options =  array("Scrollable" => SQLSRV_CURSOR_KEYSET); // 추가

$result = sqlsrv_query($conn, $sql, $params, $options) or die("die");

if (sqlsrv_has_rows($result)) {
	$rowCount = sqlsrv_num_rows($result); // 파라미터를 누락하면 null
	echo $rowCount;
    
	while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
		print_r($row);
	}
    
    /*
    for ($i = 0; $row = sqlsrv_fetch_array($result); $i++) {
    
    }
    */
}
?>

 

파라미터 Scrollable의 상수는 여기(php.net)에서 확인 가능한데,  드라이버 설명을 하다보니 또 자세한건 MS 공식 페이지가 링크 되어 있다.

 

Cursor Types (SQLSRV Driver)

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.
SQLSRV_CURSOR_CLIENT_BUFFERED	Lets you access rows in any order. Creates a client-side cursor query. buffered is the abbreviated form of SQLSRV_CURSOR_CLIENT_BUFFERED.

 

구버전 PHP 환경에서 업그레이드하여 mssql_query() 함수를 sqlsrv_query()로 변경한 경우, 위 사항을 고려한다면 함수명만 변경할 것이 아니라 파라미터를 전부 붙여줘야 한다.

 

또한 아래의 사항을 고려해야 한다.

 

mssql_fetch_assoc() 함수는 PHP 7.0 버전에서 제거 되었지만, 이전 버전에서 구성된 소스 코드에는 사용 되었을 수 있다.

하지만 아래 SQLSRV Functions 목록에서 보다시피 sqlsrv_fetch_assoc() 같은 함수는 없다.

sqlsrv_fetch_array() 함수에 파라미터로 지정해야 한다.

 

$result = sqlsrv_query($conn, $sql, array(), array('Scrollable' => SQLSRV_CURSOR_KEYSET));

while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) { // 파라미터
	print_r($row);
}

 

SQLSRV Functions

• 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

 

 

참고문헌 및 관련링크.

1. php.net - SQLSRV Functions
2. php.net - sqlsrv_query
3. microsoft - Specifying a cursor type and selecting rows