Strict Standards: Redefining already defined constructor for class wpdb in /var/www/www.sweeting.org/html/mark/blog/wp-includes/wp-db.php on line 52

Deprecated: Assigning the return value of new by reference is deprecated in /var/www/www.sweeting.org/html/mark/blog/wp-includes/cache.php on line 36

Strict Standards: Redefining already defined constructor for class WP_Object_Cache in /var/www/www.sweeting.org/html/mark/blog/wp-includes/cache.php on line 389

Strict Standards: Declaration of Walker_Page::start_lvl() should be compatible with Walker::start_lvl($output) in /var/www/www.sweeting.org/html/mark/blog/wp-includes/classes.php on line 537

Strict Standards: Declaration of Walker_Page::end_lvl() should be compatible with Walker::end_lvl($output) in /var/www/www.sweeting.org/html/mark/blog/wp-includes/classes.php on line 537

Strict Standards: Declaration of Walker_Page::start_el() should be compatible with Walker::start_el($output) in /var/www/www.sweeting.org/html/mark/blog/wp-includes/classes.php on line 537

Strict Standards: Declaration of Walker_Page::end_el() should be compatible with Walker::end_el($output) in /var/www/www.sweeting.org/html/mark/blog/wp-includes/classes.php on line 537

Strict Standards: Declaration of Walker_PageDropdown::start_el() should be compatible with Walker::start_el($output) in /var/www/www.sweeting.org/html/mark/blog/wp-includes/classes.php on line 556

Strict Standards: Declaration of Walker_Category::start_lvl() should be compatible with Walker::start_lvl($output) in /var/www/www.sweeting.org/html/mark/blog/wp-includes/classes.php on line 653

Strict Standards: Declaration of Walker_Category::end_lvl() should be compatible with Walker::end_lvl($output) in /var/www/www.sweeting.org/html/mark/blog/wp-includes/classes.php on line 653

Strict Standards: Declaration of Walker_Category::start_el() should be compatible with Walker::start_el($output) in /var/www/www.sweeting.org/html/mark/blog/wp-includes/classes.php on line 653

Strict Standards: Declaration of Walker_Category::end_el() should be compatible with Walker::end_el($output) in /var/www/www.sweeting.org/html/mark/blog/wp-includes/classes.php on line 653

Strict Standards: Declaration of Walker_CategoryDropdown::start_el() should be compatible with Walker::start_el($output) in /var/www/www.sweeting.org/html/mark/blog/wp-includes/classes.php on line 678

Deprecated: Assigning the return value of new by reference is deprecated in /var/www/www.sweeting.org/html/mark/blog/wp-includes/query.php on line 21

Deprecated: Assigning the return value of new by reference is deprecated in /var/www/www.sweeting.org/html/mark/blog/wp-includes/theme.php on line 507
PHP, Stored Procedures, and SQL Server
Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method GeoURL::tags() should not be called statically in /var/www/www.sweeting.org/html/mark/blog/wp-includes/plugin.php on line 160

PHP, Stored Procedures, and SQL Server

While working on ezmp3, I had occasion to use MS SQL Server stored procedures from PHP. A couple of people I know always moan about blogs filling up the internet with useless junk, so here, for the benefit of all mankind, is something I hope some of you may find useful!

The MS SQL/PHP documentation on this was a little sparse at the time, so I’ve decided to put together an example here for other people to see/use.

Getting a database connection is very similar to what you might do with PostgreSQL or MySQL:

<?php
function &get_conn()
{
   global $conn;
   $myServer = "localhost";
   $myUser   = "markuser";
   $myPass   = "markpass";
   $myDB     = "markdb"; 

   if(!$conn)
   {
      $conn = mssql_connect($myServer, $myUser, $myPass)
         or die("Couldn't connect to SQL Server on $myServer");
      mssql_select_db($myDB, $conn)
         or die("Couldn't select database $myDB");
   }
   return $conn;
}

/* get a connection */
$conn =& get_conn();
?>

(Some of you may frown at the use of global $conn. I don’t normally use this, but it was a quick and dirty hack, not for production use….)

So far, all pretty standard stuff.

When executing a stored procedure, you have two choices.

1. (BAD) Execute it as a standard SQL statement:

EXEC myprocedure param1, param2, param3

or

EXEC myprocedure @id=param1, @name=param2, @email=param3

This is great from the command line when using isql, or when using SQL query analyzer (damn the Americans and their Z’s!).

2. (GOOD) A far more robust solution is to use the mssql_init, mssql_bind and mssql_execute methods, and bind named (and typed) parameters to your stored procedure object. The following snippet example gives you an idea of how to do this:

<?php
/* prepare the statement resource */
$stmt=mssql_init("myprocedure", $conn);

/* now bind the parameters to it */
mssql_bind($stmt, "@id",    $id,    SQLINT4,    FALSE);
mssql_bind($stmt, "@name",  $name,  SQLVARCHAR, FALSE);
mssql_bind($stmt, "@email", $email, SQLVARCHAR, FALSE);    

/* now execute the procedure */
$result = mssql_execute($stmt);
?>

There are two great benefits to using this approach:

  1. Security - You don’t have to worry about escaping characters (like apostrophes) in your variable names. This makes nasty things like SQL injection impossible.
  2. Typed variables - If you try and put text in a field that should be an integer you will get an error. While nobody likes errors, it’s much better to get them here than at the database end.

So that’s it in a nutshell! I needed my stored procedure to return stuff to me, but I was having problems getting my output parameters to work. I’ve just seen the following in the online PHP documentation, posted by a chap called Matt, that may help here:

1) you need to pass your php variable by reference. mssql_bind($stmt, "@outParam", &$outParam, true)

2) php-4.3.1-win32 had a binding bug and output parameters do not bind. 4.1 and 4.3.2rc1 do not have the bug. I have not tested other versions.

I was running PHP on Windows, so it may have been this. I may not have passed the output variable by reference either, though I have a feeling I did try this… Anyway, I got around the problem by returning a normal result/record set from my procedure, and getting it in the normal way:

<?php
/* execute the procedure */
$result = mssql_execute($stmt);

/* get the row that is returned */
$row = mssql_fetch_assoc($result);

/* get my value out */
$my_id = $row['album_id'];
?>

Right, that’s about it. I hope some of you find this useful. I’m no guru on this sort of stuff, but it seemed to do the job for me. When I have MS SQL server available I try to use stored procedures whenever possible as they are so quick, they are far more robust, and they are extremely secure. In fact, I normally use the permissions manager to prevent my web user from running SELECT, INSERT, UPDATE and DELETE statements, and just give them EXEC permissions on specified stored procedures. I love SQL server… but that’s another story :-)

Sociable:These icons link to social bookmarking sites where readers can share and discover new web pages.
  • del.icio.us
  • digg
  • Furl
  • NewsVine
  • Reddit
  • YahooMyWeb

7 Responses to “PHP, Stored Procedures, and SQL Server

  • CunningLinguist
    November 17th, 2006 12:28
    1

    If you are going to “damn the Americans and there Z’s!” (to which I agree), I think you should at least use “their” instead of “there”.

  • Mark
    November 17th, 2006 15:49
    2

    Oops - thanks CunningLinguist! Spelling was never my strong point…

  • c4
    December 4th, 2008 04:10
    3

    Thanks for the post Mark, I’m new to this as well and your instructions are as clear as can be. Thanks for helping make sense of a new subject.

    (CunningLinguist must have googled “damn the americans” and found this entry…)

  • Ian
    September 24th, 2011 17:46
    4

    Since your post came up when I was searching to figure out output parameters here’s what you need to do:

    Assuming we follow your mssql_bind, and if your stored procedure doesn’t return any results you specify:

    If you do have a result set returned:

  • Ian
    September 24th, 2011 17:48
    5

    (submitted again as the comments don’t like php tags in them)
    Since your post came up when I was searching to figure out output parameters here’s what you need to do:

    Assuming we follow your mssql_bind, and if your stored procedure doesn’t return any results you specify:

    mssql_bind($stmt, “@outParam”, $outParam, true) // you don’t specify the & for byref variables in PHP 5
    mssql_execute($stmt, true); // the true means no results are returned
    echo $outParam; // this should work now

    If you do have a result set returned:

    mssql_bind($stmt, “@outParam”, $outParam, true) // you don’t specify the & for byref variables in PHP 5
    mssql_execute($stmt, true); // the true means no results are returned
    mssql_next_result($result); // this is cryptically explained in the note on doc page (http://php.net/manual/en/function.mssql-execute.php)
    echo $outParam; // this should work now

  • Ian
    September 24th, 2011 18:54
    6

    Apologies the second example should be:

    mssql_bind($stmt, “@outParam”, $outParam, true) // you don’t specify the & for byref variables in PHP 5
    $results = mssql_execute($stmt);
    mssql_next_result($result); // this is cryptically explained in the note on doc page (http://php.net/manual/en/function.mssql-execute.php)
    echo $outParam; // this should work now

  • Mark
    November 26th, 2011 12:39
    7

    Thanks for the input Ian. This used to work with older versions of PHP, but things have changed a lot since 2005!

Leave a Reply