Menu Horisontal

Jumat, 05 Oktober 2012

PHP RSS Executing Microsoft SQL Server Stored Procedure from PHP on Linux Read more at http://www.devarticles.com/c/a/PHP/Executing-Microsoft-SQL-Server-Stored-Procedure-from-PHP-on-Linux

Sumber : http://www.devarticles.com/c/a/PHP/Executing-Microsoft-SQL-Server-Stored-Procedure-from-PHP-on-Linux/7/

On my Windows box (home2k), I use Microsoft SQL Server query analyzer to create the following stored procedure on pubs database (I will use this stored procedure on Sybase SQL Server with pubs2 database in my next article):
CREATE PROC sp_GetBooksByPrice
@minPrice money,
@maxPrice money,
@lowestPricedBook varchar(100) OUTPUT,
@highestPricedBook varchar(100) OUTPUT
AS
DECLARE @realminPrice money,  @realmaxPrice money, @totalBooks int
SELECT @realminPrice = min(price) FROM titles WHERE price >=@minPrice
SELECT @realmaxPrice = max(price) FROM titles WHERE price <
=@maxPrice
SELECT @lowestPricedBook =title FROM titles WHERE price = @realminPrice
SELECT @highestPricedBook =title  FROM titles WHERE price = @realmaxPrice
SELECT @totalBooks = COUNT(title)  FROM titles WHERE price >= @minPrice AND price <= @maxPrice
RETURN  @totalBooks
GO

On the Red Hat side, use an editor to create the following file called sp_test.php:
$myServer = "home2k";
$myUser = "sa";
$myPass = "";
$myDB = "pubs";

$s = mssql_connect($myServer, $myUser, $myPass)
or die("Couldn't connect to SQL Server on $myServer");

mssql_select_db($myDB, $s)
or die("Couldn't open database $myDB");

$proc = mssql_init("sp_GetBooksByPrice", $s);
$minPrice = 2.00;
$maxPrice = 20.00;
$lowestPricedBook = "";
$highestPricedBook = "";
$numBooks = 0;

// Bind the parameters
mssql_bind($proc, "@minPrice", $minPrice, SQLFLT8);
mssql_bind($proc, "@maxPrice", $maxPrice, SQLFLT8);
mssql_bind($proc, "@lowestPricedBook", $lowestPricedBook, SQLVARCHAR, TRUE, FALSE,100);
mssql_bind($proc, "@highestPricedBook", $highestPricedBook, SQLVARCHAR, TRUE, FALSE,100);

// Bind the return value
mssql_bind($proc, "RETVAL", $numBooks, SQLINT2);
mssql_execute($proc);
mssql_free_statement ($proc);
mssql_close($s);

echo "

There were $numBooks Books returned.

";
echo "The lowest price book was: $lowestPricedBook.
";
echo "The highest price book was: $highestPricedBook.";
?>

Save the file in /usr/local/Apache2/htdocs, open your browser, and input http://localhost/sp_test.php in address bar. It is useful when you debug your PHP code with MS SQL Server.
Read more at http://www.devarticles.com/c/a/PHP/Executing-Microsoft-SQL-Server-Stored-Procedure-from-PHP-on-Linux/7/#GMapUYYU2SbkICRo.99

Cara Lain Ada di : http://www.daniweb.com/web-development/php/threads/112159/php-mssql-stored-procedure-with-parameters-in-and-out

gave up trying to do the output code version of the Stored Procedure call, instead I changed my Stored Procedure to return a single row which would contain the information I needed.
Then, to call the MS SQL Server 2000 Stored Procedure from PHP, I did the following:
  
// Connect to SQL Server and check for errors
  1. $conn = mssql_connect($db_host,$db_user,$db_password);
  2. if ($conn===false)
  3. {
  4. echo 'Cannot connect to SQL Server Database. Please try again later. ';
  5. exit;
  6. }
  7. if (mssql_select_db("MyDatabase",$conn) === false)
  8. {
  9. echo 'Cannot connect to MyDatabase. Please try again later. ';
  10. exit;
  11. }
  12. $proc = mssql_init('MyStoredProcedure',$conn);
  13. mssql_bind($proc,'@ParameterOne',$ParameterOne,SQLVARCHAR);
  14. mssql_bind($proc,'@ParameterTwo',$ParameterTwo,SQLVARCHAR);
  15. mssql_bind($proc,'@ParameterThree',$ParameterThree,SQLVARCHAR);
  16. if ($result = mssql_execute($proc))
  17. {
  18. if ($row = mssql_fetch_row($result))
  19. {
  20. // now you can deal with the $row array to check out your results
  21. }
  22. }
 

Tidak ada komentar: