Php sqlsrv вызов хранимой процедуры

Хранимые процедуры на PHP и Microsoft SQL Server

Хотя это не столь распространенная комбинация, как PHP и MySQL, PHP и Microsoft SQL Server могут быть мощной командой. Вы можете легко и эффективно запрашивать базы данных SQL Server, используя уровень абстракции базы данных PEAR, так же как и базу данных MySQL. Но как только вы начинаете пытаться использовать одно из основных преимуществ SQL Server над MySQL, а именно хранимые процедуры, быстро становятся очевидными некоторые проблемы:

Во-первых, ваш PHP-код часто почти такой же беспорядочный, как если бы вы динамически создавали операторы SQL для выполнения. Возьмите следующее определение хранимой процедуры:

GetCustomerList @StoreId int, 
@CustomerType varchar(50)

и рассмотрим код PHP, необходимый для построения оператора SQL, который будет выполнять эту процедуру из некоторой отправки страницы:

$sql = "EXEC GetCustomerList @StoreId '" . $_GET['CustomerType'] . "'" ; 
> 
 
// Assume you have an open PEAR database connection ($pearDB) 
$arrResults = $pearDB->getAll($sql);

Не совсем самый читаемый или эстетически приятный кусок кода, не так ли?

Во-вторых, что делать, когда вы хотите сделать что-то более сложное, чем вызвать хранимую процедуру, которая просто запрашивает список результатов? Скажем, например, что вы хотите получить возвращаемые значения или использовать выходные параметры в ваших хранимых процедурах? Там нет ничего встроенного непосредственно в библиотеку базы данных PEAR, что позволит это сделать.

Наконец, и самое главное, подумайте о безопасности. Приведенный выше код, который создает строку SQL, необходимую для вызова процедуры GetCustomerList Поскольку значение $_GET[‘CustomerType’] Большинство из нас слишком часто читали об атаках с использованием SQL-инъекций, чтобы воспринимать их как должное (если нет, я настоятельно рекомендую вам ознакомиться с ними сейчас).

К счастью, в PHP встроены некоторые функции, которые могут помочь свести к минимуму вероятность таких атак – например, «волшебные кавычки» и связанная с ними функция «полоски». Эта функциональность PHP может использоваться для «автоматического» экранирования одинарных кавычек во всех строках, вводимых через значения GET POST Однако, если вы совсем не похожи на меня, вам может показаться, что работать с магическими кавычками немного неудобно через некоторое время. Кроме того, я лично считаю, что чем меньше глобальных параметров, от которых я зависит, тем лучше – я слишком много раз переносил свой код на новые машины, чтобы зависеть от того, чтобы идентичные конфигурации серверов были чем-то иным, кроме исключения из правила.

Enter: класс SqlCommand

Класс SqlCommand Основное использование довольно просто, содержит только 6 общедоступных открытых методов (необязательные параметры указаны в квадратных скобках):

SqlCommand([$sCommandText], [$bGetReturnValue])

Реализация класса, обычно используемая для определения имени хранимой процедуры.

addParam($sParamName, [$oValue], [$sType], [$iLength], [$bForOutput])

Настройте параметр, который должен быть передан в хранимую процедуру. Показанная здесь опция $ sType является точным именем SQL Server типа переменной. В настоящее время поддерживаются следующие значения: bit, int, smallint, tinyint, real, float, money, text, char, varchar, datetime и smalldatetime.

Выполнить без получения набора результатов (например, для вставки / обновления / удаления).

Выполните и получите набор результатов (например, для операторов выбора).

Получить возвращаемое значение хранимой процедуры.

Получить значение любого выходного параметра, определенного в хранимой процедуре.

Чтобы фактически использовать класс SqlCommand SqlCommand Затем вы можете выполнить вашу хранимую процедуру с возможностью возврата набора результатов или нет ( getAll() execute() Попутно объект SqlCommand

Например, для вызова определенной хранимой процедуры, определенной выше, вы должны использовать следующие строки кода:

$oQuery = new SqlCommand('GetCustomerList'); 
$oQuery->addParam('StoreId', $_GET['StoreId'], 'int'); 
$oQuery->addParam('CustomerType', $_GET['CustomerType'], 'varchar', 50); 
 
// Assume you have an open PEAR database connection ($pearDB) 
$arrResults = $oQuery->getAll($pearDB);

Это кажется немного более читабельным, чем динамическое построение строки SQL для самостоятельного вызова этой процедуры, не так ли? И так как вы определяете каждый параметр для хранимой процедуры с фактическим типом поля базы данных, класс SqlCommand Например:

Как насчет возвращаемых значений и выходных параметров?

Хорошо, я упоминал об этом, не так ли? Что ж, возможность получения возвращаемых значений включена по умолчанию с классом SqlCommand Итак, в нашем примере, если вы хотите получить фактическое возвращаемое значение процедуры GetCustomerList $oQuery->getReturnValue()

Выходные параметры почти так же легко получить, но при настройке объекта SqlCommand Давайте предположим, что у нас есть другая хранимая процедура, которая на самом деле использует некоторые выходные параметры, например:

GetStoreCustomerCount @StoreId int, @CustomerCount int OUTPUT

В этом случае вы можете изменить конфигурацию класса SqlCommand

$oQuery = new SqlCommand('GetStoreCustomerCount'); 
$oQuery->addParam('StoreId', $_GET['StoreId'], 'int'); 
$oQuery->addParam('CustomerCount', null, 'int'); 
 
// One extra method call is required to configure a parameter for output 
$oQuery->setParamDirection('CustomerCount', true); 
 
// Assume you have an open PEAR database connection ($pearDB) 
$arrResults = $oQuery->getAll($pearDB); 
 
// Now retrieve the value of your output parameter 
$count = $oQuery->getOutputValue('CustomerCount');

Это все, что нужно сделать. Вы можете настроить любое количество выходных параметров и получить их значения (а также возвращаемое значение хранимой процедуры), а также любые результаты запроса, которые ваши хранимые процедуры получат, с помощью SELECT

Что на самом деле происходит за кулисами?

Если вы когда-нибудь выполняли хранимую процедуру непосредственно через Microsoft Query Analyzer, вы будете знать, что возвращаемые значения и выходные параметры там тоже не возвращаются автоматически – вам придется проделать небольшую дополнительную работу. Например, чтобы найти возвращаемое значение для хранимой процедуры, вам нужно написать что-то вроде этого:

DECLARE @res int 
EXEC @res = GetCustomerList 1, 'SomeType' 
SELECT @res

Результат запроса для SELECT @res Получение значений выходных параметров выглядит примерно так:

DECLARE @out_value int 
EXEC GetStoreCustomerCount 1, @out_value OUTPUT 
SELECT @out_value

Каждое возвращаемое значение или выходной параметр, который вы хотите отслеживать, должны сначала быть соответствующим образом объявлены (правильного типа данных), а затем включены в ваш фактический вызов хранимой процедуры. Сами значения могут быть возвращены с помощью SELECT Класс SqlCommand

Вывод

Хранимые процедуры – это мощный инструмент, который следует широко использовать при разработке программного обеспечения, взаимодействующего с SQL Server. Хотя вы можете использовать их непосредственно в PHP – подобно динамически создаваемым операторам запросов – ваш код может выиграть от использования более структурированного подхода. Класс SqlCommand

Не забудьте скачать код из этой статьи здесь .

Источник

How to: Retrieve Input and Output Parameters Using the SQLSRV Driver

This topic demonstrates how to use the SQLSRV driver to call a stored procedure in which one parameter has been defined as an input/output parameter, and how to retrieve the results. When retrieving an output or input/output parameter, all results returned by the stored procedure must be consumed before the returned parameter value is accessible.

Variables that are initialized or updated to null, DateTime, or stream types cannot be used as output parameters.

Example 1

The following example calls a stored procedure that subtracts used vacation hours from the available vacation hours of a specified employee. The variable that represents used vacation hours, $vacationHrs, is passed to the stored procedure as an input parameter. After updating the available vacation hours, the stored procedure uses the same parameter to return the number of remaining vacation hours.

Initializing $vacationHrs to 4 sets the returned PHPTYPE to integer. To ensure data type integrity, input/output parameters should be initialized before calling the stored procedure, or the desired PHPTYPE should be specified. For information about specifying the PHPTYPE, see How to: Specify PHP Data Types.

Because the stored procedure returns two results, sqlsrv_next_result must be called after the stored procedure has been executed to make the value of the output parameter available. After calling sqlsrv_next_result, $vacationHrs contains the value of the output parameter returned by the stored procedure.

Calling stored procedures using canonical syntax is the recommended practice. For more information about canonical syntax, see Calling a Stored Procedure.

The example assumes that SQL Server and the AdventureWorks database are installed on the local computer. All output is written to the console when the example is run from the command line.

"AdventureWorks"); $conn = sqlsrv_connect( $serverName, $connectionInfo); if( $conn === false ) < echo "Could not connect.\n"; die( print_r( sqlsrv_errors(), true)); >/* Drop the stored procedure if it already exists. */ $tsql_dropSP = "IF OBJECT_ID('SubtractVacationHours', 'P') IS NOT NULL DROP PROCEDURE SubtractVacationHours"; $stmt1 = sqlsrv_query( $conn, $tsql_dropSP); if( $stmt1 === false ) < echo "Error in executing statement 1.\n"; die( print_r( sqlsrv_errors(), true)); >/* Create the stored procedure. */ $tsql_createSP = "CREATE PROCEDURE SubtractVacationHours @EmployeeID int, @VacationHrs smallint OUTPUT AS UPDATE HumanResources.Employee SET VacationHours = VacationHours - @VacationHrs WHERE EmployeeID = @EmployeeID; SET @VacationHrs = (SELECT VacationHours FROM HumanResources.Employee WHERE EmployeeID = @EmployeeID)"; $stmt2 = sqlsrv_query( $conn, $tsql_createSP); if( $stmt2 === false ) < echo "Error in executing statement 2.\n"; die( print_r( sqlsrv_errors(), true)); >/*--------- The next few steps call the stored procedure. ---------*/ /* Define the Transact-SQL query. Use question marks (?) in place of the parameters to be passed to the stored procedure */ $tsql_callSP = ""; /* Define the parameter array. By default, the first parameter is an INPUT parameter. The second parameter is specified as an INOUT parameter. Initializing $vacationHrs to 8 sets the returned PHPTYPE to integer. To ensure data type integrity, output parameters should be initialized before calling the stored procedure, or the desired PHPTYPE should be specified in the $params array.*/ $employeeId = 4; $vacationHrs = 8; $params = array( array($employeeId, SQLSRV_PARAM_IN), array(&$vacationHrs, SQLSRV_PARAM_INOUT) ); /* Execute the query. */ $stmt3 = sqlsrv_query( $conn, $tsql_callSP, $params); if( $stmt3 === false ) < echo "Error in executing statement 3.\n"; die( print_r( sqlsrv_errors(), true)); >/* Display the value of the output parameter $vacationHrs. */ sqlsrv_next_result($stmt3); echo "Remaining vacation hours: ".$vacationHrs; /*Free the statement and connection resources. */ sqlsrv_free_stmt( $stmt1); sqlsrv_free_stmt( $stmt2); sqlsrv_free_stmt( $stmt3); sqlsrv_close( $conn); ?> 

When binding an input/output parameter to a bigint type, if the value may end up outside the range of an integer, you will need to specify its SQL field type as SQLSRV_SQLTYPE_BIGINT. Otherwise, it may result in a «value out of range» exception.

Example 2

This code sample shows how to bind a large bigint value as an input/output parameter.

"testDB"); $conn = sqlsrv_connect($serverName, $connectionInfo); if ($conn === false) < echo "Could not connect.\n"; die(print_r(sqlsrv_errors(), true)); >// Assume the stored procedure spTestProcedure exists, which retrieves a bigint value of some large number // e.g. 9223372036854 $bigintOut = 0; $outSql = ""; $stmt = sqlsrv_prepare($conn, $outSql, array(array(&$bigintOut, SQLSRV_PARAM_INOUT, null, SQLSRV_SQLTYPE_BIGINT))); sqlsrv_execute($stmt); echo "$bigintOut\n"; // Expect 9223372036854 sqlsrv_free_stmt($stmt); sqlsrv_close($conn); ?> 

Источник