PHP와 MS-SQL에서 저장 프로시저 사용하기
컨텐츠 정보
- 52,696 조회
- 5 추천
- 목록
본문
PHP와 MS-SQL에서 저장 프로시저 사용하기
Stored Procedures on PHP and Microsoft SQL Server
번역자 김영진(cogolda@hanmail.net)
알아두기
이 문서는 제가 http://www.sitepoint.com/print/php-microsoft-sql-server/ 에 있는 기사를 번역, 추가, 생략한 내용입니다. 만약 질문이나 의견이 있으면, 이메일이나 코멘트를 이용해 주시면 감사하겠습니다. 글을 보면 반말과 존대말을 섞어 쓰는 경우가 있는데 반말은 원문이고, 존대말은 제가 작성한 부분입니다. 이 기사는 제 수준에서는 좀 어려운 내용이네요.
대상 독자
이 문서는 MS-SQL 서버에서 PHP를 사용하고자 하는 분들에게 도움이 될 만한 내용을 담고 있으며, 기본적인 PHP와 SQL을 알고 있어야 하며, MS-SQL 서버의 저장 프로시저를 알면 크게 도움이 됩니다. 원문에는 저장 프로시저에 대한 설명이 없는데 초보자 분들을 위해 제가 저장 프로시저에 관한 내용을 추가했습니다.
머리말
PHP와 MySQL과 함께 사용하는 것이 일반화 되어있지만, PHP와 MS-SQL Server은 강력한 팀이 될 수 있다.(유행어를 빌리자면, php는 mysql과 써야 한다는 편견을 버려) 여러분 PEAR 테이터베이스 추상 레이어를 사용하여 마치 mysql에 하는 것 처럼,쉽고 효과적으로 M$-SQL에 쿼리문을 날릴수 있다. 그러나 여러분은 mysql 이상의 ms-sql의 주요 장점(namely, stored procedures)의 하나를 사용하기로 시도하기를 시작할떄 약간의 문제가 보일 것이다.
저장 프로시저란 무엇인가?
저장 프로시저(stored procedure, 줄여서 sproc)는 일종의 스크립트, 좀더 정확히 말하면 일괄 처리이다. 그러나 개별적인 텍스트 파일이 아니라 데이터베이스에 저장된다. 또한 저장 프로시저는 입력 인자, 출력인자, 반환값 등 스크립트에는 없는 것을 가질 수 있다.
그럼 간단한 예제를 만들어 보자. 쿼리의 예제에서와 마찬가지로 한 테이블의 모든 것(모든 행들의 모든 열들)을 뽑아내는 작업이 적합할 것이다.
/* Northwind 테이터 베이스를 사용하겠다 */
USE Northwind
/* GO는 하나의 스크립트를 여러 개의 일괄처리들로 분리할 때 사용한다 오라클의 COMMIT와 비슷 */
GO
CREATE PROC spShippers
AS
SELECT * FROM Shippers
그냥 AS에 쿼리문을 집어넣기만 하면 된다. CREATE 앞에 GO를 넣은 이유는, 테이블을 생성하는 것이 아닌 CREATE 문은 다른 코드와 개별적인 하나의 일괄처리 안에 들어가야 하기 때문이다.
이제 저장 프로시저(함수)를 수행해 보자
EXEC spShippers
그러면 그냥 SELECT 문을 직접 수행한 것과 같은 결과를 얻는다.
ShipperID CompanyName Phone
1 Speedy Express (503) 444-4322
2 United Package (503) 425-3199
3 Federal Shipping (503) 555-9931
들어가기
처음, 여러분의 PHP code는 자주 마치 여러분이 실행하는 동안 SQL 문장을 작성하는 것처엄 자주 흐트러 진다. 다음과 같은 저장 프로시저 정의를 보자
GetCustomerList @StoreId int,
@CustomerType varchar(50)
그리고 몇 페이지 요청으로부터 이 프로시저를 실행 할 SQL 문장을 작성하기 위해 필요한 PHP 코드를 생각해 보자.
// 쿼리문을 변수에 저장
$sql = "EXEC GetCustomerList @StoreId=";
$sql .= intval($_GET['StoreId']);
$sql .= ', @CustomerType=';
if ($_GET['CustomerType'] == '') {
$sql .= 'NULL';
}
else {
$sql .= "'" . $_GET['CustomerType'] . "'" ;
}
// Assume you have an open PEAR database connection ($pearDB)
$arrResults = $pearDB->getAll($sql);
대부분 읽을 수 있거나 아주 깔끔한 코드가 아니다. 그렇지 않은가?
두번째, 결과목록을 위해 간단한 쿼리인 저장 프로시저를 호출하는 것 보다, 고급스런 것은 여러분이 원하는건 어떤가? 말해보아라, 예를들면, 여러분의 저장 프로시저에서 반환 변수를 저장하거나 출력 파라미터를 사용하고 싶을은 경우이다. 이 것을 위해 PEAR 데이터베이스 라이브러리로 직접 작성하는 것은 아니다.
(문장이 좀 있는데 좀 이상해서 생략합니다. 대강 내용은 위에 기술한 소스 코드는 않 좋은 예라는 애기입니다)
다음에 소개할 내용이 위의 문제점을 보완할 내용 입니다.
들어가기: The SqlCommand Class
SqlCommand 클래스는 이런 문제를 줄이기 위해 디자인된 객체이다. 그것은 우리가 보다 가독성 높고(디버깅도 편하고) 보안에 강한 코드의 작성을 도와준다. 기본적인 사용 예는 간결하고 6개의 public 메소드만 포함한다.([]는 파라미터를 보여준다.)
SqlCommand([$sCommandText], [$bGetReturnValue])
Class instantiation, 일반적으로 저장 프로시저 이름을 정의하기 위해 사용된다.
addParam($sParamName, [$oValue], [$sType], [$iLength], [$bForOutput])
저장 프로시저에 통과되는 파라미터를 설정한다. $sType 옵션은 변수 타입의 정확한 SQL 서버이름을 보여준다. 다음과 같은 데이터타입을 모두 지원한다. bit, int, smallint, tinyint, real, float, money, text, char, varchar, datetime, and smalldatetime .
execute($oDB)
결과들 (such as for insert/update/deletes)을 얻지 않아도 실행한다.
getAll($oDB, [$mode])
결과들(such as for select statements)을 얻고 실행한다.
getReturnValue()
Retrieve the return value of the stored procedure.
getOutputValue($sParamName)
저장 프로시저에서 정의된 모든 출력 파라미터의 변수를 얻는다.
SqlCommand 클래스를 정확히 사용하기 위해, 여러분은 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);
이것은 처음에 작성한 코드보다 가독성이 좋다.
변수를 반환하고 파라미터 출력은 어떻게 하는가?
나는 그것을 언급했다. 반환변수를 불러들이기 위한 능력은 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');
이게 전부다. 여러분은 모든 출력 파라미터를 설정할 수 있고, 저장 프로시저의 반환 변수 처럼 그 변수를 불러들있 수 있다.
이 장면에서 진짜 무엇이 진행되는가?
만약 MS 쿼리 분석기를 이용하여 직접 저장 프로시저를 실행한다면, 여러분들은 반환변수와 출력 파라미터는 자동적으로 반환되지 않는 것을 알 것이다. 예를 들면, 저장 프로시저에 대해 반환변수를 찾기 위하여, 여러분은 다음과 같이 쓰는 것이 필요하다.
DECLARE @res int
EXEC @res = GetCustomerList 1, ‘SomeType’
SELECT @res
DECLARE @res int에 대한 쿼리결과는 여러분의 저장 프로시저 호출의 반환변수를 포함한다. 출력 파라미터의 변수를 불러들이는 것은 다소 비슷하다, 다음과 같은 것이 일반적이다.
DECLARE @out_value int
EXEC GetStoreCustomerCount 1, @out_value OUTPUT
SELECT @out_value
변수 스스로 다음과 같은 실행으로 SELECT 문장을 사용하려 반환할 수 있다.SqlCommand 클래스는 자동적으로 각 기능을 제공하기 위해 문장의 타입으로 여러분의 데이터베이스 쿼리문을 감싼다.
결론
저장 프로시저는 여러분이 MS-SQL로 상호작용하는 소프트웨어를 개발할 때, 유용하게 사용될 수 있는 강력한 툴이다. 비록 여러분은 PHP로 그것은 직접 사용 할 수 있지만, 여러분의 코드는 보다 구조적인 접근으 사용함으로서 장점이 있을 것이다. SqlCommand 클래스는 구조를 제공하고, 갈결하며 여러분의 프로젝트이 고안도 관리 하는 것을 도와준다.
그리고 이 SqlCommand.zip 소스코드를 다운로드 하는 것을 잊지 말아라
다운로드 받을 수 있는 곳은 아래주소입니다.
http://www.sitepoint.com/examples/sqlcommand/SqlCommand.zip
번역자에 대해
집에서 놀고 있는 백수
아래는 여기서 설명한 SqlCommand Class 파일입니다. 이거는 내용이 너무 생소해서, 좀 엉망인데, 좀더 연구해서 제대로 올리겠습니다.
<?php
/**
DESCRIPTION:
이 클래스는 PHP로 MS-SQL에서 저장 프로시저의 사용을 간단하고 쉽게 하기위해 설계되었다.
This class is designed to simplify and enhance the use of stored procedures in a
MS SQLServer database through PHP. It allows you to create command object, and
add parameters to it, somewhat similar to the ADO.Net SqlCommand object. The
most important features are that it supports type-safety in parameter values (i.e.
if a parameter is meant to be an int then it's value will be forced to an integer
type, while if the parameter is meant to be a date value then it will be validated
as a date value - unsafe, or incorrect parameter values will trigger an error
from the object, as opposed to issuing an unsafe query), and that the class allows
you to easily retrieve return and output parameter values, along with the actual
results of the stored procedure.
The class does not require the use of a PEAR database connection until you
actually want to execute a query, and in fact allows you to retrieve the exact
SQL query that will be executed as a string value. However, if you intend to
actually execute a query from within the class (which is quite likely), you will
need to supply an (open) PEAR database connection to your data source.
AUTHOR(S):
David Clark (dclark@thecalico.com)
IMPORTANT NOTE:
There is no warranty, implied or otherwise with this software.
LICENSE:
This code has been placed in the Public Domain for all to enjoy.
NOTES:
The page that you use this class from should use: require_once('DB.php');
Uses the named parameter style of procedure calling, i.e.
EXEC @lngReturnValue = stpProcedure @Param1 = 'Value1', @Param2 = 'Value2'
Performs "type-safe" filtering of all values being passed to the database
Supports return values, output parameters.
Does NOT support input/output parameters.
Does NOT fully support the use of the GETDATE() function for date parameters.
Does NOT support passing of datetime values with an actual time portion for date parameters, i.e.
date values must be of the form "mm/dd/yyyy" and NOT "mm/dd/yyyy hh:mm:ss".
REVISIONS:
DGC 10-30-2003 Corrected support for "text" (and "blob") values in _filterValueByType()
to not restrict value by length.
DGC 9-26-2003 Commented out a section of code in getAll() related to some custom
processing when DB_FETCHMODE_ORDERED is specified. Apparently the
results are returned correctly without this code.
* @version 1.1
* @access public
*/
class SqlCommand
{
var $_bGetReturnValue;
var $_sCommandText;
var $_arrParams;
var $_arrErrors;
var $_arrOutputValues; // An array of the output parameter values (including return code) from the most recently executed query
var $_iOutputParamIndicator = 135; //A special flag value used for differentiating parameter output values from actual query results
/* --- Public Methods --- */
/**
* Constructor method for the SqlStoredProcedure_class
* @param string $sCommandText The string value of the command (ex: "stpGetClientList")
* @param boolean $bGetReturnValue Whether or not to check return values for this command; default is TRUE
* @return void
* @access public
*/
function SqlCommand($sCommandText = null, $bGetReturnValue = null)
{
$this->_sCommandText = ($sCommandText === null ? null : $sCommandText);
$this->_arrParams = array();
$this->_arrErrors = array();
$this->_bGetReturnValue = ($bGetReturnValue === null ? true : $bGetReturnValue);
return;
}
/**
* Sets the command text of the query to execute
* @param string $sCommandText The string value of the command (ex: "stpGetClientList")
* @return void
* @access public
*/
function setCommandText($sCommandText)
{
$this->_sCommandText = $sCommandText;
return;
}
/**
* Adds a parameter value to the query. Optionally sets any or all details
* about the parameter including value, field type, and direction.
* @param string $sParamName The name of the parameter (ex: "lngClientId")
* @param object $oParamValue The value to use for the parameter
* @param string $sParamType The field type of the parameter, currently using
* normal SQLServer syntax (ex: "int", "bit", "varchar", "datetime")
* @param integer $iParamLength For string/text parameters, this is the maximum
* number of characters that should be allowed
* @param boolean $bParamOutput True/false for whether or not the parameter
* is an output parameter
* @return void
* @access public
*/
function addParam($sParamName, $oParamValue = null, $sParamType = null, $iParamLength = null, $bParamOutput = false)
{
$arrCurParam = array( 'name' => $sParamName,
'value' => $oParamValue,
'type' => $sParamType,
'len' => $iParamLength,
'output' => $bParamOutput
);
$this->_arrParams[$sParamName] = $arrCurParam;
return;
}
/**
* Sets the field type for a given parameter that has already been added to the
* query command
* @param string $sParamName The name of the parameter being modified
* @param string $sParamType The field type of the parameter, currently using
* normal SQLServer syntax (ex: "int", "bit", "varchar", "datetime")
* @param integer $iParamLength For string/text parameters, this is the maximum
* number of characters that should be allowed
* @return void
* @access public
*/
function setParamType($sParamName, $sParamType, $iParamLength = null)
{
$this->_arrParams[$sParamName]['type'] = $sParamType;
$this->_arrParams[$sParamName]['len'] = $iParamLength;
return;
}
/**
* Sets the value for a given parameter that has already been added to the
* query command
* @param string $sParamName The name of the parameter being modified
* @param object $oParamValue The value to use for the parameter
* @return void
* @access public
*/
function setParamValue($sParamName, $oParamValue)
{
$this->_arrParams[$sParamName]['value'] = $oParamValue;
return;
}
/**
* Sets the direction for a given parameter that has already been added to the
* query command
* @param string $sParamName The name of the parameter being modified
* @param boolean $bParamOutput True/false for whether or not the parameter
* is an output parameter
* @return void
* @access public
*/
function setParamDirection($sParamName, $bParamOutput)
{
$this->_arrParams[$sParamName]['output'] = $bParamOutput;
return;
}
/**
* Executes the current command query and simply returns true/false on completion.
* Intended for use with action queries (INSERT, UPDATE, DELETE) where no result
* set is returned.
* @param object $oDB An instance of an open PEAR database connection, or
* object that implements a similar interface
* @return boolean
* @access public
*/
function execute($oDB)
{
$arrResult = $this->getAll($oDB);
if (DB::isError($arrResult)) {
return(false);
}
else { return(true); }
}
/**
* Executes the current command query
* @param object $oDB An instance of an open PEAR database connection, or
* object that implements a similar interface
* @param object $mode The PEAR mode to use when building the result array,
* i.e. either associative (DB_FETCHMODE_ASSOC, default) or ordinal
* (DB_FETCHMODE_ORDERED)
* @return object An array of results from the current command query
* @access public
*/
function getAll($oDB, $mode = DB_FETCHMODE_ASSOC)
{
$sSql = $this->getSqlText();
if ($sSql === '') {
array_push($this->_arrErrors, 'getAll: No SQL text returned by query construction.');
return(new DB_Error('getAll: No SQL text returned by query construction.'));
}
// Execute the Sql command
$oDB->setFetchmode($mode);
$oResult = $oDB->query($sSql);
if (DB::isError($oResult)) {
// An error occurred; no way to check return values or output parameters
array_push($this->_arrErrors, 'getAll: Error executing specified SQL text.');
return($oResult);
}
// Get an array of the results from the query
$arrResultOrig = array();
while (DB_OK === $oResult->fetchInto($row, $mode)) {
$arrResultOrig[] = $row;
}
/*
// The DB_FETCHMODE_ORDERED style doesn't currently work correctly
// with MSSQL, apparently, so if that was the result requested
// then reformat before returning
if ($mode === DB_FETCHMODE_ORDERED)
{
$arrResultNew = array();
for ($i = 0; $i < count($arrResultOrig); $i++)
{
$k = 0;
for ($j = 0; $j < count($arrResultOrig[$i]); $j+=1)
{
$arrResultNew[$i][$k] = $arrResultOrig[$i][$j];
$k++;
}
}
$arrResultOrig = $arrResultNew;
}
*/
// Look for return values and output parameters
$this->_getOutputParamValues($oResult, $mode, $arrResultOrig, $blnNoOutput);
if ($blnNoOutput) {
// Okay, this means there wasn't actually any output from the actual query,
// all result set(s) were for output parameters
$arrResultOrig = array();
}
$oResult->free();
return($arrResultOrig);
}
/**
* Returns the array of errors (if any) that have been generated during the
* use of this instance of the class
* @return string array
* @access public
*/
function getErrors()
{
return($this->_arrErrors);
}
/**
* Gets the return value (if any) of the previously executed command query.
* If return values are not being checked, or no return was found, the
* value should be null.
* @return integer
* @access public
*/
function getReturnValue()
{
return($this->_arrOutputValues['lngStoredProcedureReturnValue']);
}
/**
* Gets the output value (if any) of the specified parameter from the previously
* executed command query.
* @param string $sParamName The name of the output parameter to check
* @return object
* @access public
*/
function getOutputValue($sParamName)
{
// NOTE: Could use array_key_exists to decide whether to return
// null or an actual value here
return($this->_arrOutputValues[$sParamName]);
}
/**
* Builds and returns the full Sql text that must be executed for this
* query command. Any errors will trigger an empty string to
* be returned as the function value.
* @return string
* @access public
*/
function getSqlText()
{
// Initialize to success
$bErrors = false;
$sQuery = '';
$sQueryPrep = '';
$sQueryPost = '';
$iParamCount = 0;
// Make sure the user has specified a query to execute
if (trim($this->_sCommandText) === '') {
array_push($this->_arrErrors, 'getSqlText: No SQL command text specified.');
return('');
}
// Wipe out and prepare for any output parameters
$this->_arrOutputValues = array();
// Cycle through all the parameters and make sure they're type-safe
// Build most of the actual query command as we go
foreach ($this->_arrParams as $arrCurParam) {
$sParamName = $arrCurParam['name'];
$oParamValue = $arrCurParam['value'];
$sParamType = strtolower($arrCurParam['type'] === null ? 'varchar' : $arrCurParam['type']);
$iParamLength = ($arrCurParam['len'] === null ? 255 : $arrCurParam['len']);
$bParamOutput = $arrCurParam['output'];
// Allow NULL-valued parameters to be passed through
if ($oParamValue === null || $oParamValue === 'NULL') {
$oParamFilterValue = 'NULL';
$sParamFilterType = 'null';
}
else {
$oParamFilterValue = $oParamValue;
$sParamFilterType = $sParamType;
}
// Make sure the value is safe to be passed
if ($this->_filterValueByType($oParamFilterValue, $sParamFilterType, $iParamLength)) {
if ($iParamCount > 0) {
// Second (or later) parameter
$sQuery .= ', ';
}
if ($bParamOutput) {
// For output parameters, we have to do a little more work than usual
$sQueryPrep .= "DECLARE @$sParamName" . "_Output AS $sParamType";
if ($sParamType == 'char' || $sParamType == 'varchar') {
$sQueryPrep .= "($iParamLength)";
}
$sQueryPrep .= "; ";
$sQuery .= "@$sParamName = @$sParamName" . '_Output OUTPUT';
$sQueryPost .= $this->_getOutputParamSelect($sParamName . '_Output');
}
else {
// Input parameter, simple name = value pairing
$sQuery .= "@$sParamName = $oParamFilterValue";
}
}
else {
array_push($this->_arrErrors, "getSqlText: Invalid ($sParamType) value specified for $sParamName.");
$bErrors = true;
}
// Count how many parameters we're using
$iParamCount++;
}
// Are we allowed/supposed to look for return values?
if ($this->_bGetReturnValue) {
$sQueryPrep = 'DECLARE @lngStoredProcedureReturnValue int; ' . $sQueryPrep;
$sQueryPost = $this->_getOutputParamSelect('lngStoredProcedureReturnValue') . $sQueryPost;
$sQuery = 'EXEC @lngStoredProcedureReturnValue = ' .
$this->_sCommandText . ' ' . $sQuery . '; ';
}
else { $sQuery = 'EXEC ' . $this->_sCommandText . ' ' . $sQuery . '; ';}
// If anything went wrong, return an empty string as an error indicator
// Otherwise return the full set of Sql text that should be executed
if ($bErrors) {
return('');
}
else { return($sQueryPrep . $sQuery . $sQueryPost); }
}
/* --- Private Methods --- */
/**
* Builds and returns a SQL string for selecting the value of an output parameter; this
* particular construction is used to make it possible to differentiate results
* of output parameter selection from results of the actual query. There appears
* to be no other way in PEAR to differentiate among multiple result sets.
* @param string $sParamName The name of the output parameter to be used in the query
* @return string
* @access public
*/
function _getOutputParamSelect($sParamName)
{
return('SELECT ' . $this->_iOutputParamIndicator . " AS intOutputParamMode, '$sParamName' AS strOutputParamName, @" . $sParamName . " AS $sParamName; ");
}
/**
* Retrieves all output parameter values (including return values) from the query
* query results. Also performs a check to see if the first result set returned
* by the query was really the results of the actual command, or simply those
* returned by the output parameters for this query.
* @param object $oResult The PEAR DB_Result handle for that was returned on execution
* of the current command query
* @param object $mode The PEAR mode with which the query was executed
* @param object $arrFirstResult An array of the first set of results returned by the query
* @param boolean $blnNoOutput The parameter is used to show whether or not the first
* set of results from the query, where in fact for the command itself, or were a
* result of the output parameters being returned by the query. True indicates
* the first result set are actual results, while False indicates that there were
* no true results returned by the query.
* @return boolean
* @access public
*/
function _getOutputParamValues($oResult, $mode, $arrFirstResult, &$blnNoOutput)
{
// Set the flag that states that the first result set found
// was in fact output from the query itself, and not just the
// return/output parameters
$blnNoOutput = false;
// Build an array of all output parameters to check (including the return code, if necessary)
$arrOutputParams = array();
if ($this->_bGetReturnValue) {
$arrOutputParams['lngStoredProcedureReturnValue'] =
array( 'name' => 'lngStoredProcedureReturnValue',
'checkname' => 'lngStoredProcedureReturnValue'
);
}
foreach ($this->_arrParams as $arrCurParam) {
if ($arrCurParam['output']) {
$sParamName = $arrCurParam['name'];
$arrOutputParams[$sParamName] =
array ( 'name' => $sParamName,
'checkname' => $sParamName . '_Output'
);
}
}
// If there's nothing to do, then just leave (successfully!)
if (count($arrOutputParams) === 0) { return(true); }
// If this is an associative array, then check for flags by name
// Otherwise, use ordinal positions
if ($mode === DB_FETCHMODE_ASSOC) {
$oParamModeCol = 'intOutputParamMode';
$oParamNameCol = 'strOutputParamName';
}
else {
$oParamModeCol = 0;
$oParamNameCol = 1;
}
// Now look for each of these output values
$iResultSet = 0;
$bCheckResults = true;
$arrResult = $arrFirstResult;
do {
// Test some special flags that we've worked into our output selection
// queries to ensure that we're not looking at some results from the
// actual stored procedure query (see _getOutputParamSelect)
if (is_array($arrResult[0])) {
if (array_key_exists($oParamModeCol, $arrResult[0]) &&
array_key_exists($oParamNameCol, $arrResult[0])) {
if ($arrResult[0][$oParamModeCol] === $this->_iOutputParamIndicator)
{
foreach ($arrOutputParams as $arrCurParam) {
if ($arrResult[0][$oParamNameCol] == $arrCurParam['checkname']) {
if ($mode == DB_FETCHMODE_ASSOC) {
$oParamValueCol = $arrCurParam['checkname'];
}
else { $oParamValueCol = 2; }
$oParamValue = $arrResult[0][$oParamValueCol];
$this->_arrOutputValues[$arrCurParam['name']] = $oParamValue;
// If this is the first result set, then there couldn't
// have been any actual output from the query itself
if ($iResultSet === 0) { $blnNoOutput = true; }
}
}
}
}
}
// See if there are more result sets to check
if ($oResult->nextResult()) {
$arrResult = array();
if ($oResult->fetchInto($row, $mode) === DB_OK) {
array_push($arrResult, $row);
$iResultSet++;
}
else { return(false); }
}
else { $bCheckResults = false; }
}
while ($bCheckResults);
return(true);
}
/**
* Ensures that the specified value is safe for use in the specified data
* type within the database.
* @param string &$oValue The value to be filtered (and returned with database safe contents)
* @param string $sFieldType The abbreviated type description for this field
* @param integer $iFieldLength For string/text fields, the maximum length of data values
* @return boolean
* @access private
*/
function _filterValueByType(&$oValue, $sFieldType, $iFieldLength)
{
// Default to failure
$bResult = false;
switch ($sFieldType) {
case 'bit' :
$oValue = abs(intval($oValue));
if ($oValue > 1) { $oValue = 1; }
$bResult = true;
break;
case 'int' :
case 'integer' :
$oValue = intval($oValue);
$bResult = true;
break;
case 'smallint' :
$oValue = intval($oValue);
// The min and max cutoffs used here are based on SQLServer 2000
if (($oValue < -32768) || ($oValue > 32767)) {
$bResult = false;
}
else { $bResult = true; }
break;
case 'tinyint' :
$oValue = intval($oValue);
// The min and max cutoffs used here are based on SQLServer 2000
if (($oValue < 0) || ($oValue > 255)) {
$bResult = false;
}
else { $bResult = true; }
break;
case 'real' :
case 'float' :
case 'money' :
case 'currency' :
$oValue = doubleval($oValue);
$bResult = true;
break;
case 'blob' :
case 'text' :
case 'char' :
case 'string' :
case 'varchar' :
if (trim($oValue) == '') {
// Allow for NULL string values
$oValue = 'NULL';
}
else {
// Make sure we're within the limits of the field size
if ($sFieldType != 'text' && $sFieldType != 'blob') {
if ($iFieldLength > 0) {
if (strlen($oValue) > $iFieldLength) {
$oValue = substr($oValue, 0, $iFieldLength);
}
}
}
// *** Watch out for unfiltered single quotes
$oValue = str_replace("''", '&*?&*?', $oValue);
$oValue = str_replace("'", "''", $oValue);
$oValue = str_replace('&*?&*?', "''", $oValue);
// ***
$oValue = "'$oValue'";
}
$bResult = true;
break;
Stored Procedures on PHP and Microsoft SQL Server
번역자 김영진(cogolda@hanmail.net)
알아두기
이 문서는 제가 http://www.sitepoint.com/print/php-microsoft-sql-server/ 에 있는 기사를 번역, 추가, 생략한 내용입니다. 만약 질문이나 의견이 있으면, 이메일이나 코멘트를 이용해 주시면 감사하겠습니다. 글을 보면 반말과 존대말을 섞어 쓰는 경우가 있는데 반말은 원문이고, 존대말은 제가 작성한 부분입니다. 이 기사는 제 수준에서는 좀 어려운 내용이네요.
대상 독자
이 문서는 MS-SQL 서버에서 PHP를 사용하고자 하는 분들에게 도움이 될 만한 내용을 담고 있으며, 기본적인 PHP와 SQL을 알고 있어야 하며, MS-SQL 서버의 저장 프로시저를 알면 크게 도움이 됩니다. 원문에는 저장 프로시저에 대한 설명이 없는데 초보자 분들을 위해 제가 저장 프로시저에 관한 내용을 추가했습니다.
머리말
PHP와 MySQL과 함께 사용하는 것이 일반화 되어있지만, PHP와 MS-SQL Server은 강력한 팀이 될 수 있다.(유행어를 빌리자면, php는 mysql과 써야 한다는 편견을 버려) 여러분 PEAR 테이터베이스 추상 레이어를 사용하여 마치 mysql에 하는 것 처럼,쉽고 효과적으로 M$-SQL에 쿼리문을 날릴수 있다. 그러나 여러분은 mysql 이상의 ms-sql의 주요 장점(namely, stored procedures)의 하나를 사용하기로 시도하기를 시작할떄 약간의 문제가 보일 것이다.
저장 프로시저란 무엇인가?
저장 프로시저(stored procedure, 줄여서 sproc)는 일종의 스크립트, 좀더 정확히 말하면 일괄 처리이다. 그러나 개별적인 텍스트 파일이 아니라 데이터베이스에 저장된다. 또한 저장 프로시저는 입력 인자, 출력인자, 반환값 등 스크립트에는 없는 것을 가질 수 있다.
그럼 간단한 예제를 만들어 보자. 쿼리의 예제에서와 마찬가지로 한 테이블의 모든 것(모든 행들의 모든 열들)을 뽑아내는 작업이 적합할 것이다.
/* Northwind 테이터 베이스를 사용하겠다 */
USE Northwind
/* GO는 하나의 스크립트를 여러 개의 일괄처리들로 분리할 때 사용한다 오라클의 COMMIT와 비슷 */
GO
CREATE PROC spShippers
AS
SELECT * FROM Shippers
그냥 AS에 쿼리문을 집어넣기만 하면 된다. CREATE 앞에 GO를 넣은 이유는, 테이블을 생성하는 것이 아닌 CREATE 문은 다른 코드와 개별적인 하나의 일괄처리 안에 들어가야 하기 때문이다.
이제 저장 프로시저(함수)를 수행해 보자
EXEC spShippers
그러면 그냥 SELECT 문을 직접 수행한 것과 같은 결과를 얻는다.
ShipperID CompanyName Phone
1 Speedy Express (503) 444-4322
2 United Package (503) 425-3199
3 Federal Shipping (503) 555-9931
들어가기
처음, 여러분의 PHP code는 자주 마치 여러분이 실행하는 동안 SQL 문장을 작성하는 것처엄 자주 흐트러 진다. 다음과 같은 저장 프로시저 정의를 보자
GetCustomerList @StoreId int,
@CustomerType varchar(50)
그리고 몇 페이지 요청으로부터 이 프로시저를 실행 할 SQL 문장을 작성하기 위해 필요한 PHP 코드를 생각해 보자.
// 쿼리문을 변수에 저장
$sql = "EXEC GetCustomerList @StoreId=";
$sql .= intval($_GET['StoreId']);
$sql .= ', @CustomerType=';
if ($_GET['CustomerType'] == '') {
$sql .= 'NULL';
}
else {
$sql .= "'" . $_GET['CustomerType'] . "'" ;
}
// Assume you have an open PEAR database connection ($pearDB)
$arrResults = $pearDB->getAll($sql);
대부분 읽을 수 있거나 아주 깔끔한 코드가 아니다. 그렇지 않은가?
두번째, 결과목록을 위해 간단한 쿼리인 저장 프로시저를 호출하는 것 보다, 고급스런 것은 여러분이 원하는건 어떤가? 말해보아라, 예를들면, 여러분의 저장 프로시저에서 반환 변수를 저장하거나 출력 파라미터를 사용하고 싶을은 경우이다. 이 것을 위해 PEAR 데이터베이스 라이브러리로 직접 작성하는 것은 아니다.
(문장이 좀 있는데 좀 이상해서 생략합니다. 대강 내용은 위에 기술한 소스 코드는 않 좋은 예라는 애기입니다)
다음에 소개할 내용이 위의 문제점을 보완할 내용 입니다.
들어가기: The SqlCommand Class
SqlCommand 클래스는 이런 문제를 줄이기 위해 디자인된 객체이다. 그것은 우리가 보다 가독성 높고(디버깅도 편하고) 보안에 강한 코드의 작성을 도와준다. 기본적인 사용 예는 간결하고 6개의 public 메소드만 포함한다.([]는 파라미터를 보여준다.)
SqlCommand([$sCommandText], [$bGetReturnValue])
Class instantiation, 일반적으로 저장 프로시저 이름을 정의하기 위해 사용된다.
addParam($sParamName, [$oValue], [$sType], [$iLength], [$bForOutput])
저장 프로시저에 통과되는 파라미터를 설정한다. $sType 옵션은 변수 타입의 정확한 SQL 서버이름을 보여준다. 다음과 같은 데이터타입을 모두 지원한다. bit, int, smallint, tinyint, real, float, money, text, char, varchar, datetime, and smalldatetime .
execute($oDB)
결과들 (such as for insert/update/deletes)을 얻지 않아도 실행한다.
getAll($oDB, [$mode])
결과들(such as for select statements)을 얻고 실행한다.
getReturnValue()
Retrieve the return value of the stored procedure.
getOutputValue($sParamName)
저장 프로시저에서 정의된 모든 출력 파라미터의 변수를 얻는다.
SqlCommand 클래스를 정확히 사용하기 위해, 여러분은 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);
이것은 처음에 작성한 코드보다 가독성이 좋다.
변수를 반환하고 파라미터 출력은 어떻게 하는가?
나는 그것을 언급했다. 반환변수를 불러들이기 위한 능력은 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');
이게 전부다. 여러분은 모든 출력 파라미터를 설정할 수 있고, 저장 프로시저의 반환 변수 처럼 그 변수를 불러들있 수 있다.
이 장면에서 진짜 무엇이 진행되는가?
만약 MS 쿼리 분석기를 이용하여 직접 저장 프로시저를 실행한다면, 여러분들은 반환변수와 출력 파라미터는 자동적으로 반환되지 않는 것을 알 것이다. 예를 들면, 저장 프로시저에 대해 반환변수를 찾기 위하여, 여러분은 다음과 같이 쓰는 것이 필요하다.
DECLARE @res int
EXEC @res = GetCustomerList 1, ‘SomeType’
SELECT @res
DECLARE @res int에 대한 쿼리결과는 여러분의 저장 프로시저 호출의 반환변수를 포함한다. 출력 파라미터의 변수를 불러들이는 것은 다소 비슷하다, 다음과 같은 것이 일반적이다.
DECLARE @out_value int
EXEC GetStoreCustomerCount 1, @out_value OUTPUT
SELECT @out_value
변수 스스로 다음과 같은 실행으로 SELECT 문장을 사용하려 반환할 수 있다.SqlCommand 클래스는 자동적으로 각 기능을 제공하기 위해 문장의 타입으로 여러분의 데이터베이스 쿼리문을 감싼다.
결론
저장 프로시저는 여러분이 MS-SQL로 상호작용하는 소프트웨어를 개발할 때, 유용하게 사용될 수 있는 강력한 툴이다. 비록 여러분은 PHP로 그것은 직접 사용 할 수 있지만, 여러분의 코드는 보다 구조적인 접근으 사용함으로서 장점이 있을 것이다. SqlCommand 클래스는 구조를 제공하고, 갈결하며 여러분의 프로젝트이 고안도 관리 하는 것을 도와준다.
그리고 이 SqlCommand.zip 소스코드를 다운로드 하는 것을 잊지 말아라
다운로드 받을 수 있는 곳은 아래주소입니다.
http://www.sitepoint.com/examples/sqlcommand/SqlCommand.zip
번역자에 대해
집에서 놀고 있는 백수
아래는 여기서 설명한 SqlCommand Class 파일입니다. 이거는 내용이 너무 생소해서, 좀 엉망인데, 좀더 연구해서 제대로 올리겠습니다.
<?php
/**
DESCRIPTION:
이 클래스는 PHP로 MS-SQL에서 저장 프로시저의 사용을 간단하고 쉽게 하기위해 설계되었다.
This class is designed to simplify and enhance the use of stored procedures in a
MS SQLServer database through PHP. It allows you to create command object, and
add parameters to it, somewhat similar to the ADO.Net SqlCommand object. The
most important features are that it supports type-safety in parameter values (i.e.
if a parameter is meant to be an int then it's value will be forced to an integer
type, while if the parameter is meant to be a date value then it will be validated
as a date value - unsafe, or incorrect parameter values will trigger an error
from the object, as opposed to issuing an unsafe query), and that the class allows
you to easily retrieve return and output parameter values, along with the actual
results of the stored procedure.
The class does not require the use of a PEAR database connection until you
actually want to execute a query, and in fact allows you to retrieve the exact
SQL query that will be executed as a string value. However, if you intend to
actually execute a query from within the class (which is quite likely), you will
need to supply an (open) PEAR database connection to your data source.
AUTHOR(S):
David Clark (dclark@thecalico.com)
IMPORTANT NOTE:
There is no warranty, implied or otherwise with this software.
LICENSE:
This code has been placed in the Public Domain for all to enjoy.
NOTES:
The page that you use this class from should use: require_once('DB.php');
Uses the named parameter style of procedure calling, i.e.
EXEC @lngReturnValue = stpProcedure @Param1 = 'Value1', @Param2 = 'Value2'
Performs "type-safe" filtering of all values being passed to the database
Supports return values, output parameters.
Does NOT support input/output parameters.
Does NOT fully support the use of the GETDATE() function for date parameters.
Does NOT support passing of datetime values with an actual time portion for date parameters, i.e.
date values must be of the form "mm/dd/yyyy" and NOT "mm/dd/yyyy hh:mm:ss".
REVISIONS:
DGC 10-30-2003 Corrected support for "text" (and "blob") values in _filterValueByType()
to not restrict value by length.
DGC 9-26-2003 Commented out a section of code in getAll() related to some custom
processing when DB_FETCHMODE_ORDERED is specified. Apparently the
results are returned correctly without this code.
* @version 1.1
* @access public
*/
class SqlCommand
{
var $_bGetReturnValue;
var $_sCommandText;
var $_arrParams;
var $_arrErrors;
var $_arrOutputValues; // An array of the output parameter values (including return code) from the most recently executed query
var $_iOutputParamIndicator = 135; //A special flag value used for differentiating parameter output values from actual query results
/* --- Public Methods --- */
/**
* Constructor method for the SqlStoredProcedure_class
* @param string $sCommandText The string value of the command (ex: "stpGetClientList")
* @param boolean $bGetReturnValue Whether or not to check return values for this command; default is TRUE
* @return void
* @access public
*/
function SqlCommand($sCommandText = null, $bGetReturnValue = null)
{
$this->_sCommandText = ($sCommandText === null ? null : $sCommandText);
$this->_arrParams = array();
$this->_arrErrors = array();
$this->_bGetReturnValue = ($bGetReturnValue === null ? true : $bGetReturnValue);
return;
}
/**
* Sets the command text of the query to execute
* @param string $sCommandText The string value of the command (ex: "stpGetClientList")
* @return void
* @access public
*/
function setCommandText($sCommandText)
{
$this->_sCommandText = $sCommandText;
return;
}
/**
* Adds a parameter value to the query. Optionally sets any or all details
* about the parameter including value, field type, and direction.
* @param string $sParamName The name of the parameter (ex: "lngClientId")
* @param object $oParamValue The value to use for the parameter
* @param string $sParamType The field type of the parameter, currently using
* normal SQLServer syntax (ex: "int", "bit", "varchar", "datetime")
* @param integer $iParamLength For string/text parameters, this is the maximum
* number of characters that should be allowed
* @param boolean $bParamOutput True/false for whether or not the parameter
* is an output parameter
* @return void
* @access public
*/
function addParam($sParamName, $oParamValue = null, $sParamType = null, $iParamLength = null, $bParamOutput = false)
{
$arrCurParam = array( 'name' => $sParamName,
'value' => $oParamValue,
'type' => $sParamType,
'len' => $iParamLength,
'output' => $bParamOutput
);
$this->_arrParams[$sParamName] = $arrCurParam;
return;
}
/**
* Sets the field type for a given parameter that has already been added to the
* query command
* @param string $sParamName The name of the parameter being modified
* @param string $sParamType The field type of the parameter, currently using
* normal SQLServer syntax (ex: "int", "bit", "varchar", "datetime")
* @param integer $iParamLength For string/text parameters, this is the maximum
* number of characters that should be allowed
* @return void
* @access public
*/
function setParamType($sParamName, $sParamType, $iParamLength = null)
{
$this->_arrParams[$sParamName]['type'] = $sParamType;
$this->_arrParams[$sParamName]['len'] = $iParamLength;
return;
}
/**
* Sets the value for a given parameter that has already been added to the
* query command
* @param string $sParamName The name of the parameter being modified
* @param object $oParamValue The value to use for the parameter
* @return void
* @access public
*/
function setParamValue($sParamName, $oParamValue)
{
$this->_arrParams[$sParamName]['value'] = $oParamValue;
return;
}
/**
* Sets the direction for a given parameter that has already been added to the
* query command
* @param string $sParamName The name of the parameter being modified
* @param boolean $bParamOutput True/false for whether or not the parameter
* is an output parameter
* @return void
* @access public
*/
function setParamDirection($sParamName, $bParamOutput)
{
$this->_arrParams[$sParamName]['output'] = $bParamOutput;
return;
}
/**
* Executes the current command query and simply returns true/false on completion.
* Intended for use with action queries (INSERT, UPDATE, DELETE) where no result
* set is returned.
* @param object $oDB An instance of an open PEAR database connection, or
* object that implements a similar interface
* @return boolean
* @access public
*/
function execute($oDB)
{
$arrResult = $this->getAll($oDB);
if (DB::isError($arrResult)) {
return(false);
}
else { return(true); }
}
/**
* Executes the current command query
* @param object $oDB An instance of an open PEAR database connection, or
* object that implements a similar interface
* @param object $mode The PEAR mode to use when building the result array,
* i.e. either associative (DB_FETCHMODE_ASSOC, default) or ordinal
* (DB_FETCHMODE_ORDERED)
* @return object An array of results from the current command query
* @access public
*/
function getAll($oDB, $mode = DB_FETCHMODE_ASSOC)
{
$sSql = $this->getSqlText();
if ($sSql === '') {
array_push($this->_arrErrors, 'getAll: No SQL text returned by query construction.');
return(new DB_Error('getAll: No SQL text returned by query construction.'));
}
// Execute the Sql command
$oDB->setFetchmode($mode);
$oResult = $oDB->query($sSql);
if (DB::isError($oResult)) {
// An error occurred; no way to check return values or output parameters
array_push($this->_arrErrors, 'getAll: Error executing specified SQL text.');
return($oResult);
}
// Get an array of the results from the query
$arrResultOrig = array();
while (DB_OK === $oResult->fetchInto($row, $mode)) {
$arrResultOrig[] = $row;
}
/*
// The DB_FETCHMODE_ORDERED style doesn't currently work correctly
// with MSSQL, apparently, so if that was the result requested
// then reformat before returning
if ($mode === DB_FETCHMODE_ORDERED)
{
$arrResultNew = array();
for ($i = 0; $i < count($arrResultOrig); $i++)
{
$k = 0;
for ($j = 0; $j < count($arrResultOrig[$i]); $j+=1)
{
$arrResultNew[$i][$k] = $arrResultOrig[$i][$j];
$k++;
}
}
$arrResultOrig = $arrResultNew;
}
*/
// Look for return values and output parameters
$this->_getOutputParamValues($oResult, $mode, $arrResultOrig, $blnNoOutput);
if ($blnNoOutput) {
// Okay, this means there wasn't actually any output from the actual query,
// all result set(s) were for output parameters
$arrResultOrig = array();
}
$oResult->free();
return($arrResultOrig);
}
/**
* Returns the array of errors (if any) that have been generated during the
* use of this instance of the class
* @return string array
* @access public
*/
function getErrors()
{
return($this->_arrErrors);
}
/**
* Gets the return value (if any) of the previously executed command query.
* If return values are not being checked, or no return was found, the
* value should be null.
* @return integer
* @access public
*/
function getReturnValue()
{
return($this->_arrOutputValues['lngStoredProcedureReturnValue']);
}
/**
* Gets the output value (if any) of the specified parameter from the previously
* executed command query.
* @param string $sParamName The name of the output parameter to check
* @return object
* @access public
*/
function getOutputValue($sParamName)
{
// NOTE: Could use array_key_exists to decide whether to return
// null or an actual value here
return($this->_arrOutputValues[$sParamName]);
}
/**
* Builds and returns the full Sql text that must be executed for this
* query command. Any errors will trigger an empty string to
* be returned as the function value.
* @return string
* @access public
*/
function getSqlText()
{
// Initialize to success
$bErrors = false;
$sQuery = '';
$sQueryPrep = '';
$sQueryPost = '';
$iParamCount = 0;
// Make sure the user has specified a query to execute
if (trim($this->_sCommandText) === '') {
array_push($this->_arrErrors, 'getSqlText: No SQL command text specified.');
return('');
}
// Wipe out and prepare for any output parameters
$this->_arrOutputValues = array();
// Cycle through all the parameters and make sure they're type-safe
// Build most of the actual query command as we go
foreach ($this->_arrParams as $arrCurParam) {
$sParamName = $arrCurParam['name'];
$oParamValue = $arrCurParam['value'];
$sParamType = strtolower($arrCurParam['type'] === null ? 'varchar' : $arrCurParam['type']);
$iParamLength = ($arrCurParam['len'] === null ? 255 : $arrCurParam['len']);
$bParamOutput = $arrCurParam['output'];
// Allow NULL-valued parameters to be passed through
if ($oParamValue === null || $oParamValue === 'NULL') {
$oParamFilterValue = 'NULL';
$sParamFilterType = 'null';
}
else {
$oParamFilterValue = $oParamValue;
$sParamFilterType = $sParamType;
}
// Make sure the value is safe to be passed
if ($this->_filterValueByType($oParamFilterValue, $sParamFilterType, $iParamLength)) {
if ($iParamCount > 0) {
// Second (or later) parameter
$sQuery .= ', ';
}
if ($bParamOutput) {
// For output parameters, we have to do a little more work than usual
$sQueryPrep .= "DECLARE @$sParamName" . "_Output AS $sParamType";
if ($sParamType == 'char' || $sParamType == 'varchar') {
$sQueryPrep .= "($iParamLength)";
}
$sQueryPrep .= "; ";
$sQuery .= "@$sParamName = @$sParamName" . '_Output OUTPUT';
$sQueryPost .= $this->_getOutputParamSelect($sParamName . '_Output');
}
else {
// Input parameter, simple name = value pairing
$sQuery .= "@$sParamName = $oParamFilterValue";
}
}
else {
array_push($this->_arrErrors, "getSqlText: Invalid ($sParamType) value specified for $sParamName.");
$bErrors = true;
}
// Count how many parameters we're using
$iParamCount++;
}
// Are we allowed/supposed to look for return values?
if ($this->_bGetReturnValue) {
$sQueryPrep = 'DECLARE @lngStoredProcedureReturnValue int; ' . $sQueryPrep;
$sQueryPost = $this->_getOutputParamSelect('lngStoredProcedureReturnValue') . $sQueryPost;
$sQuery = 'EXEC @lngStoredProcedureReturnValue = ' .
$this->_sCommandText . ' ' . $sQuery . '; ';
}
else { $sQuery = 'EXEC ' . $this->_sCommandText . ' ' . $sQuery . '; ';}
// If anything went wrong, return an empty string as an error indicator
// Otherwise return the full set of Sql text that should be executed
if ($bErrors) {
return('');
}
else { return($sQueryPrep . $sQuery . $sQueryPost); }
}
/* --- Private Methods --- */
/**
* Builds and returns a SQL string for selecting the value of an output parameter; this
* particular construction is used to make it possible to differentiate results
* of output parameter selection from results of the actual query. There appears
* to be no other way in PEAR to differentiate among multiple result sets.
* @param string $sParamName The name of the output parameter to be used in the query
* @return string
* @access public
*/
function _getOutputParamSelect($sParamName)
{
return('SELECT ' . $this->_iOutputParamIndicator . " AS intOutputParamMode, '$sParamName' AS strOutputParamName, @" . $sParamName . " AS $sParamName; ");
}
/**
* Retrieves all output parameter values (including return values) from the query
* query results. Also performs a check to see if the first result set returned
* by the query was really the results of the actual command, or simply those
* returned by the output parameters for this query.
* @param object $oResult The PEAR DB_Result handle for that was returned on execution
* of the current command query
* @param object $mode The PEAR mode with which the query was executed
* @param object $arrFirstResult An array of the first set of results returned by the query
* @param boolean $blnNoOutput The parameter is used to show whether or not the first
* set of results from the query, where in fact for the command itself, or were a
* result of the output parameters being returned by the query. True indicates
* the first result set are actual results, while False indicates that there were
* no true results returned by the query.
* @return boolean
* @access public
*/
function _getOutputParamValues($oResult, $mode, $arrFirstResult, &$blnNoOutput)
{
// Set the flag that states that the first result set found
// was in fact output from the query itself, and not just the
// return/output parameters
$blnNoOutput = false;
// Build an array of all output parameters to check (including the return code, if necessary)
$arrOutputParams = array();
if ($this->_bGetReturnValue) {
$arrOutputParams['lngStoredProcedureReturnValue'] =
array( 'name' => 'lngStoredProcedureReturnValue',
'checkname' => 'lngStoredProcedureReturnValue'
);
}
foreach ($this->_arrParams as $arrCurParam) {
if ($arrCurParam['output']) {
$sParamName = $arrCurParam['name'];
$arrOutputParams[$sParamName] =
array ( 'name' => $sParamName,
'checkname' => $sParamName . '_Output'
);
}
}
// If there's nothing to do, then just leave (successfully!)
if (count($arrOutputParams) === 0) { return(true); }
// If this is an associative array, then check for flags by name
// Otherwise, use ordinal positions
if ($mode === DB_FETCHMODE_ASSOC) {
$oParamModeCol = 'intOutputParamMode';
$oParamNameCol = 'strOutputParamName';
}
else {
$oParamModeCol = 0;
$oParamNameCol = 1;
}
// Now look for each of these output values
$iResultSet = 0;
$bCheckResults = true;
$arrResult = $arrFirstResult;
do {
// Test some special flags that we've worked into our output selection
// queries to ensure that we're not looking at some results from the
// actual stored procedure query (see _getOutputParamSelect)
if (is_array($arrResult[0])) {
if (array_key_exists($oParamModeCol, $arrResult[0]) &&
array_key_exists($oParamNameCol, $arrResult[0])) {
if ($arrResult[0][$oParamModeCol] === $this->_iOutputParamIndicator)
{
foreach ($arrOutputParams as $arrCurParam) {
if ($arrResult[0][$oParamNameCol] == $arrCurParam['checkname']) {
if ($mode == DB_FETCHMODE_ASSOC) {
$oParamValueCol = $arrCurParam['checkname'];
}
else { $oParamValueCol = 2; }
$oParamValue = $arrResult[0][$oParamValueCol];
$this->_arrOutputValues[$arrCurParam['name']] = $oParamValue;
// If this is the first result set, then there couldn't
// have been any actual output from the query itself
if ($iResultSet === 0) { $blnNoOutput = true; }
}
}
}
}
}
// See if there are more result sets to check
if ($oResult->nextResult()) {
$arrResult = array();
if ($oResult->fetchInto($row, $mode) === DB_OK) {
array_push($arrResult, $row);
$iResultSet++;
}
else { return(false); }
}
else { $bCheckResults = false; }
}
while ($bCheckResults);
return(true);
}
/**
* Ensures that the specified value is safe for use in the specified data
* type within the database.
* @param string &$oValue The value to be filtered (and returned with database safe contents)
* @param string $sFieldType The abbreviated type description for this field
* @param integer $iFieldLength For string/text fields, the maximum length of data values
* @return boolean
* @access private
*/
function _filterValueByType(&$oValue, $sFieldType, $iFieldLength)
{
// Default to failure
$bResult = false;
switch ($sFieldType) {
case 'bit' :
$oValue = abs(intval($oValue));
if ($oValue > 1) { $oValue = 1; }
$bResult = true;
break;
case 'int' :
case 'integer' :
$oValue = intval($oValue);
$bResult = true;
break;
case 'smallint' :
$oValue = intval($oValue);
// The min and max cutoffs used here are based on SQLServer 2000
if (($oValue < -32768) || ($oValue > 32767)) {
$bResult = false;
}
else { $bResult = true; }
break;
case 'tinyint' :
$oValue = intval($oValue);
// The min and max cutoffs used here are based on SQLServer 2000
if (($oValue < 0) || ($oValue > 255)) {
$bResult = false;
}
else { $bResult = true; }
break;
case 'real' :
case 'float' :
case 'money' :
case 'currency' :
$oValue = doubleval($oValue);
$bResult = true;
break;
case 'blob' :
case 'text' :
case 'char' :
case 'string' :
case 'varchar' :
if (trim($oValue) == '') {
// Allow for NULL string values
$oValue = 'NULL';
}
else {
// Make sure we're within the limits of the field size
if ($sFieldType != 'text' && $sFieldType != 'blob') {
if ($iFieldLength > 0) {
if (strlen($oValue) > $iFieldLength) {
$oValue = substr($oValue, 0, $iFieldLength);
}
}
}
// *** Watch out for unfiltered single quotes
$oValue = str_replace("''", '&*?&*?', $oValue);
$oValue = str_replace("'", "''", $oValue);
$oValue = str_replace('&*?&*?', "''", $oValue);
// ***
$oValue = "'$oValue'";
}
$bResult = true;
break;
관련자료
-
링크
댓글 0
등록된 댓글이 없습니다.
