MSSQL Driver for PHP5.3

Before using PHP5.3, I never had to worry about MSSQL support. PHP included a dll file to use with MSSQL Server 2000 and up. This is no longer true if you decide to upgrade to PHP5.3.x. For months I looked for ways to connect to our MSSQL Server using PHP5.3.x. I finally found the driver and it works. It’s called SQLSRV and you can download it from http://sqlsrvphp.codeplex.com. It’s easy to install, just read the CHM file.

  • Run the exe to unpack it
  • Rename the folder to Microsoft SQL Server Driver for PHP (optional)
  • Move this to your %Program Files% directory (optional)
  • Inside that directory you will find several dll files. It supports both NTS (Non Thread Safe) and TS (Thread Safe). You also need to choose between VC6 or VC9 compiler. This will determine which file will be used.
  • Copy that file to %Installation Directory%PHPext (assuming you are using the ext directory for your extensions.
  • Edit your php.ini. In the extensions section add the following extension=php_sqlsrv_53_ts_vc6.dll – or whatever file you copied to your ext directory.
  • Restart your web server and that should be it.

Here’s a quick sample code to get you started.

<?php

$servername = "SQLSERVER_NAME_OR_IP";
$uid = "db_username";
$pwd = "db_passwd";
$connectionInfo = array (
"UID"=>$uid,
"PWD"=>$pwd,
"Database"=>"database_name"
);
$conn = sqlsrv_connect($serverName, $connectionInfo);

if ($conn === false) {
echo "ERROR: DB Connection";
die(print_r(sqlsrv_errors(), true));

}
$stmt = sqlsrv_query($conn, "SELECT * FROM table");

if ($stmt) {
while ($row = sqlsrv_fetch_array($stmt)) {
echo $row[0].'<br />';
}
}

?>

That should do it. I have only tested this on Windows 7 Professional (32bit), MSSQL 2005 (32bit) Standard on a remote Windows 2008 Server Standard R2, PHP5.3.3, Apache2.2.15, and driver version 1.1 (version 2 is now available) . Make sure your MSSQL server can accept remote connections if it is on a different server than your web server. The above instructions shouldn’t be too different on other versions.

If you have a phpinfo() page, you will see the image below.

sqlsrv

Hope this helps you get started. Happy coding.

Windows 2008 and MSSQL Server 2005 not allowing remote connections

If you think you checked every setting in MSSQL Server that allows remote connections, you may have missed the Windows firewall setting. I have installed MSSQL 2005 many times on Windows 2000 Server, 2003, and 2008 and never ran into the remote connection problem. What’s happening is I’m unable to connection using the Management Studio on another machine into the server.

  • Checked the Configuration Tools/SQL Server Configuration Manager
  • Checked SQL Browser service is running
  • Pinged the IP address
  • Checked the Surface Area Configurations and made sure it allowed local and remote connections using TCP/IP only

It turns out that Windows firewall didn’t add MSSQL to the exceptions list. So here’s how you do it. Run firewall.cpl to bring up the Windows Firewall settings. On the left click on “Allow a program through Windows Firewall”. Make sure you are on the “Exceptions” tab and click on “Add program…”. If it’s not on the list, browse for the sqlservr.exe. You will find this in [INSTALL_DIR]MSSQL.1MSSQLBinnsqlservr.exe.

I never had this issue before. Not sure what caused it. Maybe Windows 2008 SP2. The versions I’m using are Windows Server 2008 RC2 and MSSQL Server 2005 Standard.

Connect to MSSQL 2005 using PHP

Here’s a couple of quick tips to connecting to a MSSQL 2005 server using PHP on a Windows webserver. You will need to enable the php_mssql.dll in your php.ini file.

Here’s the code to connect to the server.

<?php

$conn = mssql_connect('my_server', 'db_username', 'db_password');
if (!$conn) { die('ERROR: Unable to connect to the database.'); }
mssql_select_db('database_name');

?>

Here is a helpful function I found that will help escape strings. I found this at http://stackoverflow.com/questions/574805/how-to-escape-strings-in-mssql-using-php.

<?php

function ms_escape_string($data) {
//if ( !isset($data) or empty($data) ) return ";
if ( !isset($data) or empty($data) ) return """;         // modified to handle empty $data
if ( is_numeric($data) ) return $data;

$non_displayables = array(
  '/%0[0-8bcef]/', // url encoded 00-08, 11, 12, 14, 15
  '/%1[0-9a-f]/', // url encoded 16-31
  '/[x00-x08]/', // 00-08
  '/x0b/', // 11
  '/x0c/', // 12
  '/[x0e-x1f]/' // 14-31
);
foreach ( $non_displayables as $regex ) {
  $data = preg_replace( $regex, ", $data );
  $data = str_replace("'", """, $data );
  //return $data;
  return "'".$data."'";        // modified to handle empty $data
}

?>

Here is a sample query to the database.

<?php

$id = ms_escape_string($_GET['id']);    // escape variable
$sql = 'SELECT id FROM table_name WHERE id='.$id.";
$rs = mssql_query($sql);

if (!$rs)
{
  echo 'ERROR: Unable to get records. '.mssql_get_last_message();    // equivalent to mysql_error()
}else{
  while ($row = mssql_fetch_assoc($rs))
  {
    echo 'The id = '.$row['id'].'<br />';
  }
}

?>

Another thing I learned from the article is the equivalent of “mysql_insert_id()”. It is “SELECT @@IDENTITY”. To handle dates and make them friendly to PHP use CONVERT(VARCHAR, [date], 20). This will display it as if you are using date(‘Y-m-d H:i:s) – 24 hour format. So an example would be…

SELECT CONVERT(VARCHAR, getdate(), 20) AS now

I tried to use PDO but was having a hard time getting it to work. I kept getting errors that had something to do with functions. I searched for solutions and tried them but none worked for me. Also, the rowCount() will always return less than or equal to 0.

I had trouble on both sides PHP and Microsoft. This seems to be the best solution that works for me. Hope it helps others.

MSSQL Stored Procedures and Classic ASP

A client at work wanted us to create a way to duplicate existing records on their website with a click of a button. These records involve multiple tables in the database. Some of the tables contained a lot of columns. We tried to do most of work using ASP but sometimes the browser would time out and Dreamweaver would crash.

Then I thought, why don’t we have our SQL server do most of the heavy lifting. I created a stored procedure that query the database for the record(s) that need to be duplicated and have them duplicated. Below is a short example to get you started.

First you need to create a stored procedure in SQL Server.

CREATE PROCEDURE stored_procedure_sample
@record_id INT OUTPUT

AS
BEGIN

INSERT INTO table_name (column1, column2, column3)
SELECT column1, column2, column3
FROM table_name
WHERE record_id = @record_id

END;

GO

Next you need to create the ASP page that process the request. You can use form variable or URL query string. For the example, I will use a form variable. Let’s imagine on page1.asp there is a form with a hidden variable that contains the record_id you want to duplicate. You submit this to page2.asp and here’s a sample of what page2.asp should look like.

<%

Dim connString, rsCmd

connString = your connection string
Set rsCmd  = Server.CreateObject(ADODB.Command)
rsCmd.ActiveConnection = connString
rsCmd.CommandType = 4
rsCmd.CommandText = stored_procedure_sample

rsCmd.Parameters.Append rsCmd.CreateParameter("@record_id", 200, 1, 4, Request.Form("record_id"))

rsCmd.Execute

%>

What page2.asp does is it takes the form variable “record_id” from page1.asp and tells the stored procedure to get the values for this record and insert it as a new record. That’s pretty much it. Just a short, simple example on how you can create a stored procedure in MSSQL Server and use it with classic ASP.