如何处理错误和警告
默认情况下,SQL Server Driver for PHP 将警告视为错误;如果调用会生成错误或警告的 sqlsrv 函数,则将返回 false。本主题演示如何禁用该默认行为以及如何将警告与错误分开处理。
备注
将警告视为错误的默认行为有一些例外情况。与 SQLSTATE 值 01000、01001、01003 和 01S02 对应的警告永远不会被视为错误。
示例
下面的代码示例使用两个用户定义函数 DisplayErrors 和 DisplayWarnings 来处理错误和警告。第一个示例演示如何通过执行以下操作将警告与错误分开处理:
- 禁用将警告视为错误的默认行为。
- 创建一个存储过程来更新员工的假期小时数并将剩余的假期小时数作为输出参数返回。如果员工的可用假期小时数小于零,该存储过程将输出一条警告。
- 通过为每个员工调用存储过程来更新多个员工的假期小时数,并显示与出现的任何警告和错误对应的消息。
- 显示每个员工的剩余假期小时数。
请注意,第一次调用 sqlsrv 函数 (sqlsrv_configure) 期间,会将警告视为错误。因为警告已添加到错误集合中,所以不必将警告与错误分开检查。然而,在随后调用 sqlsrv 函数的过程中,不会将警告视为错误,因此必须显式检查有无警告和有无错误。
还请注意,该示例代码将在每次调用 sqlsrv 函数之后检查有无错误。这是推荐的做法。
此示例假定在本地计算机上已安装了 SQL Server 和 AdventureWorks 数据库。从命令行运行此示例时,所有的输出都将写入控制台。针对新安装的 AdventureWorks 数据库运行此示例时,它会产生三个警告和两个错误。前两个警告是连接到数据库时生成的标准警告。出现第三个警告的原因是,员工的可用假期小时数更新为一个小于零的值。出现这两个错误的原因是,员工的可用假期小时数更新为一个小于 -40 个小时的值,这与对表的约束冲突。
<?php
/* Turn off the default behavior of treating errors as warnings.
Note: Turning off the default behavior is done here for demonstration
purposes only. If setting the configuration fails, display errors and
exit the script. */
if( sqlsrv_configure("WarningsReturnAsErrors", 0) === false)
{
DisplayErrors();
die;
}
/* Connect to the local server using Windows Authentication and
specify the AdventureWorks database as the database in use. */
$serverName = "(local)";
$connectionInfo = array("Database"=>"AdventureWorks");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
/* If the connection fails, display errors and exit the script. */
if( $conn === false )
{
DisplayErrors();
die;
}
/* Display any warnings. */
DisplayWarnings();
/* Drop the stored procedure if it already exists. */
$tsql1 = "IF OBJECT_ID('SubtractVacationHours', 'P') IS NOT NULL
DROP PROCEDURE SubtractVacationHours";
$stmt1 = sqlsrv_query($conn, $tsql1);
/* If the query fails, display errors and exit the script. */
if( $stmt1 === false)
{
DisplayErrors();
die;
}
/* Display any warnings. */
DisplayWarnings();
/* Free the statement resources. */
sqlsrv_free_stmt( $stmt1 );
/* Create the stored procedure. */
$tsql2 = "CREATE PROCEDURE SubtractVacationHours
@EmployeeID int,
@VacationHours smallint OUTPUT
AS
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - @VacationHours
WHERE EmployeeID = @EmployeeID;
SET @VacationHours = (SELECT VacationHours
FROM HumanResources.Employee
WHERE EmployeeID = @EmployeeID);
IF @VacationHours < 0
BEGIN
PRINT 'WARNING: Vacation hours are now less than zero.'
END;";
$stmt2 = sqlsrv_query( $conn, $tsql2 );
/* If the query fails, display errors and exit the script. */
if( $stmt2 === false)
{
DisplayErrors();
die;
}
/* Display any warnings. */
DisplayWarnings();
/* Free the statement resources. */
sqlsrv_free_stmt( $stmt2 );
/* Set up the array that maps employee ID to used vacation hours. */
$emp_hrs = array (7=>4, 8=>5, 9=>8, 11=>50);
/* Initialize variables that will be used as parameters. */
$employeeId = 0;
$vacationHrs = 0;
/* Set up the parameter array. */
$params = array(
array(&$employeeId, SQLSRV_PARAM_IN),
array(&$vacationHrs, SQLSRV_PARAM_INOUT)
);
/* Define and prepare the query to substract used vacation hours. */
$tsql3 = "{call SubtractVacationHours(?, ?)}";
$stmt3 = sqlsrv_prepare($conn, $tsql3, $params);
/* If the statement preparation fails, display errors and exit the script. */
if( $stmt3 === false)
{
DisplayErrors();
die;
}
/* Display any warnings. */
DisplayWarnings();
/* Loop through the employee=>vacation hours array. Update parameter
values before statement execution. */
foreach(array_keys($emp_hrs) as $employeeId)
{
$vacationHrs = $emp_hrs[$employeeId];
/* Execute the query. If it fails, display the errors. */
if( sqlsrv_execute($stmt3) === false)
{
DisplayErrors();
die;
}
/* Display any warnings. */
DisplayWarnings();
/*Move to the next result returned by the stored procedure. */
if( sqlsrv_next_result($stmt3) === false)
{
DisplayErrors();
die;
}
/* Display any warnings. */
DisplayWarnings();
/* Display updated vacation hours. */
echo "EmployeeID $employeeId has $vacationHrs ";
echo "remaining vacation hours.\n";
}
/* Free the statement and connection resources. */
sqlsrv_free_stmt( $stmt3 );
sqlsrv_close( $conn );
/* ------------- Error Handling Functions --------------*/
function DisplayErrors()
{
$errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
foreach( $errors as $error )
{
echo "Error: ".$error['message']."\n";
}
}
function DisplayWarnings()
{
$warnings = sqlsrv_errors(SQLSRV_ERR_WARNINGS);
if(!is_null($warnings))
{
foreach( $warnings as $warning )
{
echo "Warning: ".$warning['message']."\n";
}
}
}
?>