serving the solutions day and night

Pages

Sunday, May 2, 2010

Executing SQL Server Stored Procedure from PHP

Difference between PHP on Windows and PHP on Linux is:

On windows, the MS SQL Server support module is running as a DLL file. In order to enable the extension, must uncomment the line from the php.ini file : extension=php_mssql.dll

On Linux, the MS SQL Server support module is compiled into libphp5.so, there is no need to load it from extensions.


Execute Microsoft SQL Server stored procedures from PHP by calling the following functions: mssql_connect(), mssql_bind(), mssql_select_db(), mssql_execute(), mssql_init(), mssql_free_statement and mssql_close().

Syntax
mssql_connect (string $servername, string $username, string $password, bool $new_link)

mssql_connect() establishes a connection to a MS SQL server. The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mssql_close().

$new_link - If a second call is made to mssql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. This parameter modifies this behavior and makes mssql_connect() always open a new link, even if mssql_connect() was called before with the same parameters.

Returns a MS SQL link identifier on success, or FALSE on error.

"Warning: mssql_connect() [function.mssql-connect]: message: Cannot open user default database. Using master database instead. (severity 11) in "

It means the default database for the user you are connecting with doesn't exist or you don't have permission to access it. To ignore the warning
replace mssql_connect($server, $username, $password); code to @mssql_connect($server, $username, $password);.

Accessing SQL Query and Stored Procedure from the pubs database:

Example 1
CREATE PROCEDURE [dbo].[spEmployee] AS
SELECT fname FROM employee;
GO

<?php
$server = "localhost,1433";
$username = "sa";
$password = "pwd";
$databasename = "pubs";

$sqlconnect = @mssql_connect($server, $username, $password) or die("Couldn't connect to SQL Server");
$sqldb = mssql_select_db($databasename,$sqlconnect) or die("Couldn't open database");

// Using SQL Query
$sqlquery = "SELECT fname FROM employee;";
$results = mssql_query($sqlquery);
while ($row=mssql_fetch_array($results))
{
echo $row['fname']."
\n";
}

//using stored procedure
$sp = mssql_init("spEmployee", $sqlconnect) or die("Couldn't call stored procedure");
$results= mssql_execute($sp) or die(mssql_get_last_message());
while ($row=mssql_fetch_array($results))
{
echo $row['fname']."
\n";
}
mssql_close($sqlconnect);
?>

Example 2
CREATE PROCEDURE [dbo].[spGetEmployee]
@sTitleID varchar(10),
@sBookTitle varchar(100) OUTPUT
AS
DECLARE @iTotalEmployee int
SELECT @sBookTitle =title FROM titles WHERE title_id = @sTitleID
SELECT @iTotalEmployee = COUNT(emp_id) FROM employee
RETURN @iTotalEmployee
GO
<?php
$sp = mssql_init("spGetTitle_TotalEmployee", $sqlconnect) or die("Couldn't call stored procedure");

$sTitleID = 'bu1032';
$iTotalEmployee = 0;
$sBookTitle = '';

// Bind the parameters
mssql_bind($sp, "@sTitleID", $sTitleID, SQLVARCHAR);
mssql_bind($sp, "@sBookTitle", $sBookTitle, SQLVARCHAR, TRUE, FALSE,100);

// Bind the return value
mssql_bind($sp, "RETVAL", $iTotalEmployee, SQLINT2);

mssql_execute ($sp);
mssql_free_statement ($sp);
mssql_close($sqlconnect);

echo "There were $iTotalEmployee Employees returned.";
echo "The book title was: $sBookTitle.";
?>

8 comments:

Steve Holdoway said...

As your example talks about php4, which was eol'ed years ago, I don't think I'll bother reading any further.

Daynight said...

thanks for your comment, Yes that's correct, but most of them still using old libphp4.so in unix side, in windows it is for latest version.

Steve Holdoway said...

@Mohldeen

Most of who? Nobody in their right mind uses php4 unless forced - kicking and screaming - to do so.

Even if this were not the case, recommending software end-of-lifed almost 2 and a half years ago is not the way forward!

Daynight said...

Yes i understand it, but stored procedure concept is new in mysql and for php programmer, that is the reason i wrote blog for famous of sql server stored procedure. I also changed to php4 to php5, because i am not linux or unix based programmer. Thanks for your valuable comments. If you comments in my blog, it will helpful for other visitors.

Seth May said...

If your on a windows machine, using the SQL Server driver provided by MS offers many, many advantages over the old PHP driver. This is especially true when using stored procedures.

http://www.microsoft.com/downloads/details.aspx?FamilyID=df4d9cc9-459c-4d75-a503-ae3fceb85860&displaylang=en

Andy Stevenson said...

Hi everybody,

Thanks very much for your help. You know Alexei, some people have to take lessons to become a champion idiot. I was born with that skill.

Why I put i for the parameter identifier is a mystery only the heavens will now the answer to. Changed to to s for a string (which should be obvious to people working upright) and fixed the issue.

thanks again to you and everyone else.

If I had a sister, you could marry her with my blessing :o)

Alexei Shulga said...

@Mohideen:

I believe the link won't be helpful for Andy (no PDO and using MSSQL). Moreover I'd give the link to the corresponding PHP manual page ( http://www.php.net/manual/en/pdo.prepared-statements.php ), but this is not the question here. As you can see Andy is able to call stored procedure, the issue is that the procedure fails to render multiple user IDs to be deleted.

@Andy:

Btw, what is 'i' here?

$stmt->bind_param('i', $delUser);

According to the manual:

--------------

Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.

--------------

However, if you're still experiencing the issue, please answer my questions above.

99 Developer said...

hi, i have done this through Adodb Library in very simple manner...

$addProduct = $obj->ExecuteQuery("Begin;DECLARE @ProductCode as varchar (100) ;EXEC CREATEPRODUCT'$pname', '$price', @ProductCode OUTPUT, '$merchantId';select @ProductCode;End;");

$productCode = $addProduct[0][0];

for more explanation you can visit this site..
http://developer99.blogspot.com/2011/07/calling-ms-sql-sp-from-php.html