?
Solved

PHP Calling MS SQL Stored Procedures

Posted on 2006-04-28
12
Medium Priority
?
1,915 Views
Last Modified: 2012-06-27
Hi All,

I'm new to PHP and am banging my head against the wall in an effort to call an MS SQL stored procedure.  Any help would be appreciated!  I'm using currently the ADOdb abstraction layer.

This simple code should call a store procedure called procCountry_Upd.  This stored proc has two input paramters (@i32CountryID, and @strCountry).  If a value of @i32CountryID = 0 is passed, a record is added, however if an existing value is passed, then the record is updated.  The return value is always the record identity.

My current code is:

<?php
echo "about to connect<br>";
include('adodb/adodb.inc.php');
$db = &ADONewConnection("ado_mssql");      
print "<h1>Connecting DSN-less $db->databaseType...</h1>";                  
$myDSN="PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=myserver;DATABASE=mydatabase;UID=sa;PWD=;";
$db->Connect($myDSN);

$stmt = $db->PrepareSP("procCountries_Upd");
$db->InParameter($stmt,$CID,"i32CountryID");
$db->InParameter($stmt,$C,"strCountry");
$db->OutParameter($stmt,$NewID,"i32CountryIDNew");
$db->OutParameter($stmt,$ret,"RETVAL");

$CID = 0;
$C = "New Zealand";
$rs = $db->Execute($stmt);
if( !$rs ) {
print $db->ErrorMsg();
die;
}
echo "String = $ret\n";
echo $NewID

?>

The procedure works fine in Query Analyser, but when I execute the PHP page, all that happens is a blank record is inserted!!!!

The stored procedure code is:

ALTER PROCEDURE [dbo].[procCountries_Upd]
@i32CountryID AS INT = 0
, @strCountry AS NVARCHAR(50) = ''
, @i32CountryIDNew AS INT = 0 OUTPUT

AS
/**********************************************************************
Purpose :      Update/Insert for Countries tbl.
Notes   :      -
History  :      DJA      21-Apr-2006      Initial Creation.
**********************************************************************/
SET NOCOUNT ON
DECLARE @i32Error AS INT
DECLARE @i32TranCount AS INT

SET @i32Error = 0
SET @i32TranCount = @@TRANCOUNT

-- Update OR Insert ?
IF EXISTS (SELECT CountryID
            FROM Countries
            WHERE CountryID = @i32CountryID)
BEGIN
      UPDATE Countries
      SET Country = @strCountry
      WHERE CountryID = @i32CountryID
END
ELSE
BEGIN
      -- Insert.      
      
      INSERT INTO Countries
            (Country)
      VALUES (@strCountry)
END

SET @i32CountryIDNew = @@IDENTITY
RETURN @@IDENTITY
0
Comment
Question by:darcher23
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16569399
Hi darcher23,

I am not goo at all PHP, anyway you can read the folowing article to check how to call an SP in php

http://www.sitepoint.com/print/php-microsoft-sql-server

Also, before doing  any modifications, to your existing code, run the sql profiler, then run your application and chck what statement is executing at the sql server end

Aneesh
0
 

Author Comment

by:darcher23
ID: 16570231
Hi Aneesh,

Unfortunately the above link requires a PEAR database connection and this is a hosted server solution where I have no control over having PEAR installed.  Any other ideas?

Cheers,
David.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16571117
This is more of a PHP syntax question, why don't you try posting in the PHP section?

A blank record - do you mean the identity value is created but the country value is blank?

That would imply that this line:

     INSERT INTO Countries
          (Country)
     VALUES (@strCountry)


Somehow has a blank value in @strCountry. Which seems to imply that the calling code is putting a blank value in, or the calling code isn't working quite right.

If you can connect with Profiler as previously suggested then you can check if this is the case.

You don't need PEAR libraries to run Profiler, you just need system admin priveliges in the database, a connection to the database, and the Profiler tool on your client.


I always had a lot of trouble with parameters in using ADO in VB, so I'm guessing the issue is in the PHP code. Have you been able to find an example of PHP code that does this?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:darcher23
ID: 16571204
Hi nmcdermaid,

I realised this was probably more a PHP syntax question after posting so have posted into that section (no responses yet - I think the PHP guys like using MySQL rather than MS SQL ;-) )

When I run the stored proc in Query Analyzer there is no problem, so I'm guessing it is more a problem with my PHP calling code.  I have actually managed to make this work by creating a string I can execute ...

// Database Operation
// Update a record in the countries table - return is updated record
$id = 11;
$C = "Aussie Land";

$result = $DbConn->Execute(" EXEC procCountries_Upd ".$id.", '".$C."' ");

$CountryID = $result->fields['CountryID'];
$Country = $result->fields['Country'];

                          while (!$result->EOF)
                          {
        echo $CountryID->value.' - '.$Country->value.'<BR>';
                          $result->MoveNext();
                          }
                         
$DbConn->Close();

However this approach isn't the neatest.

Cheers,
David.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16571277
I think better you move this question under the php section,

http://www.experts-exchange.com/Web/Web_Languages/PHP/PHP_Databases/
0
 
LVL 10

Expert Comment

by:PSSUser
ID: 16572869
I'm afraid I don't know PHP at all, but just scanning the code it looks to me the order of some ofyour lines is the cause of the problem.

Try moving the lines where you set the values of the variables:

$CID = 0;
$C = "New Zealand";

$db->InParameter($stmt,$CID,"i32CountryID");
$db->InParameter($stmt,$C,"strCountry");
$db->OutParameter($stmt,$NewID,"i32CountryIDNew");
$db->OutParameter($stmt,$ret,"RETVAL");

$rs = $db->Execute($stmt);
0
 
LVL 13

Accepted Solution

by:
kamermans earned 2000 total points
ID: 16588762
I'm not an MSSQL SP expert, but I am a PHP expert.  From the PHP side, if you look at the first example here: http://www.sitepoint.com/print/php-microsoft-sql-server they use PEAR to connect to the DB (like you already said) but you don't need it for that example, the first part constructs the SQL query, then it is executed.  You can borrow the code and modify it for plain PHP -> DB connections:

$sql = "EXEC GetCustomerList @StoreId=";
$sql .= intval($_GET['StoreId']);
$sql .= ', @CustomerType=';
if ($_GET['CustomerType'] == '') {
 $sql .= 'NULL';
}
else {
 $sql .= "'" . $_GET['CustomerType'] . "'" ;
}
$DbConn->Execute($sql);

This is similar to what you posted earlier and unless you make your own class to interface with stored procedures on MSSQL you will have to deal with it's messy-ness.  Im sure you could come up with a simple function to make it more modular.

Let me know if that helps,

Steve Kamerman
0
 

Author Comment

by:darcher23
ID: 16604359
Hi Steve,

Thanks for your help.  I've used a variation of your suggestions and now have a working solutions.  I changed the stored procedure to return the newly inserted (or updated) record.

$DbConn->Open($strConnString);

// Database Operation
//insert a new record into the countries table - return is a new record
$id = 0;
$C = "New Country";

$result = $DbConn->Execute(" EXEC procCountries_Upd ".$id.", '".$C."' ");

$CountryID = $result->fields['CountryID'];
$Country = $result->fields['Country'];

                          while (!$result->EOF)
                          {
        echo $CountryID->value.' - '.$Country->value.'<BR>';
                          $result->MoveNext();
                          }
                         
$DbConn->Close();

I'm not sure that this the best way to integrate PHP and MS SQL Stored Procedures, but it seems to work well :-).

Cheers,
David.
0
 
LVL 13

Expert Comment

by:kamermans
ID: 16604911
GranMod - Sorry about the link thing - you may want to cleanup some of my recent posts too then.  I will make sure I don't do that in the future!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question