Easysoft

Easysoft.com Documentation Knowledge Base
Products  |  Solutions  |  Support  |  Applications  |  Developer  |  Company   
Easysoft

Access MS SQL Server 7.0, 2000 and 2005 from PHP on UNIX and Linux.

Accessing Microsoft SQL Server (mssql) from PHP under Apache on UNIX or Linux

Contents

Introduction

This tutorial shows you how to access MS SQL Server databases from PHP running under Apache on UNIX and Linux by using the Easysoft ODBC-ODBC Bridge (OOB). The OOB accesses MS SQL Server by using the Windows MS SQL Server ODBC driver. If you have not already got the OOB, you can download a fully functional free trial version.

The components that you need to install on your client and server machines to provide ODBC access from PHP to Microsoft SQL Server data.

Pre-requisites

Before you start this tutorial you need to ensure you have satisfied all the pre-requisites:

  1. PHP

    We used PHP 4.2 but you need at least 3.0.6 as unified ODBC was not introduced until then. From the unified ODBC documentation:

    "the Unified ODBC functions in PHP allow you to access several databases that have borrowed the semantics of the ODBC API to implement their own API. Instead of maintaining multiple database drivers that were all nearly identical, these drivers have been unified into a single set of ODBC functions."

    To check which version of PHP you are running use the php -v command from the shell prompt.

    php -v

    If you get a "command not found" error then there is no PHP on your PATH (perhaps because you have not installed it).

    If you do have it installed then you should see something like 4.2.2

    If you find you have an older version than the required one then go to http://www.php.net to get an up to date distribution.

    The documentation and installation instructions can be found, both on the README that is included in the distribution and on the php.net web site, http://www.php.net/docs.php.

  2. Apache

    We used Apache 2.0 when developing this tutorial as we ran PHP as a CGI module under Apache. You can find out what version of Apache you are running by entering the following command at the shell prompt:

    httpd_bin_dir/httpd -V

    where httpd_bin_dir is the directory where the http daemon is installed. For example, on Linux the default location is normally /usr/sbin. The -V displays information about the Apache installation, you should receive information like this:

    Server version: Apache/2.0.40
Server built:   Feb 25 2003 05:01:56
Server's Module Magic Number: 200xxxxx:0
Architecture:   32-bit
Server compiled with....
 -D APACHE_MPM_DIR="server/mpm/prefork"
 -D APR_HAS_SENDFILE

    NOTE: All of the examples in this tutorial will work if you do NOT have a web server installed, instead, you can run the PHP script in command mode i.e. run them from the shell prompt.

    The first PHP script you create should be a phpinfo program, as the phpinfo script allows you to test that your PHP distribution is working correctly.

    The phpinfo function is a PHP function that can display extensive information about your PHP configurations and further machine level configurations. To create this script, you need to do something like this:

    Create a file called, for example, phpinfo.php in this file put:

    #!/bin/bash
# This is a simple one liner that amongst other things returns relevant PHP information
<?
    phpinfo()
?>

    Save this file then, from a command line, type:

    php phpinfo.php > newhtmlfile.html

    This will redirect the output from phpinfo and place in into a HTML file called newhtmlfile.html from here you can then choose to view it statically or if you have a web server running view it remotely through a web browser.

    NOTE: The phpinfo.php script can be viewed directly if you have a web server in place and you have saved the file in the HTML root directory. An example path to the HTML root is /var/www/html/.

  3. The Easysoft ODBC-ODBC Bridge and ODBC driver manager

    The Easysoft ODBC-ODBC Bridge is an ODBC driver for UNIX and Linux that can access MS SQL Server. We recommend you use the unixODBC driver manager. The OOB includes the unixODBC driver manager.

    You probably have the unixODBC driver manager installed if you have the odbcinst command (for the OOB, the unixODBC driver manager is located in /usr/local/easysoft/unixODBC and the odbcinst command in the bin sub directory of that path).

    We used unixODBC 2.2.7 in this tutorial. You can find out the unixODBC version you are using with:

      odbcinst --version

Access MS SQL Server 7.0, 2000 and 2005 from PHP on UNIX and Linux.

Assumptions

Operating System

This tutorial was written on UNIX and Linux and we have assumed you are using UNIX or Linux too. However, all the PHP examples should work equally well on Windows so long as minor alterations for the command line are made.

NOTE: The minor changes that need to be made to the command lines are syntax changes due to the difference in operating system, the Windows equivalent of a UNIX command can be found on many web sites.

ODBC Driver Manager

We have assumed you are using the driver manager. All discussion in this document relating to the location and definition of ODBC data sources is for unixODBC.

Accessing Microsoft SQL Server from PHP under Apache on UNIX or Linux

What you need to install

The Easysoft ODBC-ODBC Bridge allows ODBC applications on one machine to access ODBC drivers on a remote machine.

OOB consists of a client ODBC driver (which you install on the machine where your ODBC application is running) and a server (which you install on a machine where you have an ODBC driver for the database you want to access). OOB distributions contain both the client and the server but you generally only install one or the other.

The following scenario is an example which helps to illustrate what you need.

  • Client Machine

    This is the machine where you want to run your PHP. You need to have installed:

    • PHP. The minimum version required.
    • The OOB client. The OOB client is an ODBC driver which talks to the OOB Server and hence your remote ODBC data source. You need to install the OOB Client and the unixODBC Driver Manager. OOB distributions contain the unixODBC Driver Manager. There is no need to install the OOB Server on this machine. You can find details of the OOB Client installation in the OOB Manual available here

    For this example we assume your client machine is running Linux and is called linuxclient.

  • Server machine

    This is the machine where you have (or can install) an ODBC driver for the database you want to access. You need to have installed:

    • ODBC Driver. For this tutorial the ODBC driver you need is for Microsoft SQL Server and it is available in Microsoft Data Access Components (MDAC) from Microsoft. You do not need the MS SQL Server ODBC driver to reside on the same machine as your SQL Server database.
    • The OOB Server. The OOB Server is an ODBC application accepting connections from the OOB client and relaying them to your desired ODBC data source. You will need to license the OOB Server but trial licenses may be obtained during the installation. You can find details of the OOB Server installation in the OOB Manual available here

    For this example we assume your server machine is running Windows and is called windows_server.

Access MS SQL Server 7.0, 2000 and 2005 from PHP on UNIX and Linux.

Data Sources

An ODBC data source is a named resource the application passes to the ODBC driver manager which tells it which ODBC driver to use and any connection attributes it should use. You do not have to create data sources as there are DSN-less connections available. You will find detail on DSN setup here.

With OOB there are two data sources to consider. The first data source is an OOB client one on your client machine. This data source tells the driver manager to use the OOB Client ODBC data source and contains a number of attributes for the OOB Client which tell it which server to connect to and which data source on the remote machine to use.

A typical OOB Client data source looks like this:

[mydatasource]
# Driver tells the driver manager which ODBC driver to use
Driver = OOB
# Description is a description of this data source
Description = connect to SQL Server on windows
# ServerPort is the name of the machine where the OOB Server is running
# and the port on which it is listening. The default port is 8888.
ServerPort = windows_server:8888

# LogonUser is a valid user account on the windows_server machine
# LogonAuth is LogonUser's password
LogonUser = my_windows_username
LogonAuth = LogonUsers_password

# TargetDSN is the name of the data source on windows_server to connect to
TargetDSN = windows_server_dsn
# TargetUser and TargetAuth are synonomous with the ODBC connection
# attributes UID and PWD and specify the user and password to use for
# the database login - if required.
#
TargetUser = db_username
TargetAuth = TargetUsers_password

We will describe these attributes in the following sections.

NOTE: When using PHP, you do not need to specify TargetUser and TargetAuth in the data source as these will be passed in to the odbc_connect function.

Authentication

There are two levels of authentication you need to be aware of:

  1. OOB Server authentication

    By default the OOB Server needs to authenticate the client and become the specified user on the windows_server machine. The OOB Client needs to have specified the data source attributes LogonUser and LogonAuth to do this. LogonUser is the name of a user on the windows_server machine who has permission to logon locally to that machine and LogonAuth is that user's password. A user that has logon locally permissions is defined as a user that is capable of physically walking up to the windows_server machine and logging on to it. When the OOB Server has checked the authentication with the Windows operating system it will become the LogonUser.

    NOTE You can turn off OOB Server authentication but it has various implications (see the OOB manual).

  2. MS SQL Server authentication

    When you create a MS SQL Server data source in the ODBC Administrator on Windows you have the choice of:

    • With Windows NT authentication using the network login ID

      This is sometimes called trusted connections. In this situation, MS SQL Server examines the user on the Windows machine which is communicating with it and no SQL Server username or password are required.

    • With SQL Server authentication using a login ID and password entered by the user

      In this scenario, MS SQL Server will require a database username and password which your database administrator will have set up. If this is your situation then the ODBC connection attributes UID and PWD must be specified at the client end (you do this with the second and third arguments to the odbc_connect.

This may be better explained with the aid of an example. Suppose you have an account on the windows_server machine and your username is Fred Bloggs and your password is mypassword. Your database administrator has set up the OOB server with SQL Server authentication active and has set you up an account for the database with username dbuser and password dbpassword. In order for you to connect to this data source you have to set the OOB data source attribute LogonUser to your windows_server username and the attribute LogonAuth to your windows_server password. Your OOB Client data source would look like this:

[mydatasource]
# Driver tells the driver manager which ODBC driver to use
Driver = OOB
# Description is a description of this data source
Description = connect to SQL Server on windows

# ServerPort is the name of the machine where the OOB Server is running
# and the port on which it is listening. The default port is 8888.
ServerPort = windows_server:8888

# LogonUser is a valid user account on the windows_server machine
# LogonAuth is LogonUser's password
LogonUser = Fred Bloggs
LogonAuth = mypassword

# TargetDSN is the name of the data source on the windows_server to connect to
TargetDSN = windows_server_dsn
# TargetUser and TargetAuth are synonymous with the ODBC connection
# attributes UID and PWD and specify the user and password to use for
# the database login - if required.
#

NOTE: When using PHP you do not need to specify TargetUser and TargetAuth in the data source as these will be passed in to the odbc_connect function.

In addition to setting up the OOB data source, your database username and password have to be passed into the odbc_connect function within your PHP script. This is a typical odbc_connect connection string:

odbc_connect ( string dsn, string user, string password [, int cursor_type] )

When you use this function within your script you have to set string user to the username you were given for the database and you will have to set string password to the password for your account in the database. Using the scenario as before, this is what your odbc_connect call would look like:

odbc_connect ( "mydatasource", "dbuser", "dbpassword" )

ServerPort

ServerPort is an OOB client DSN attribute that tells the OOB Client ODBC driver which server to connect to. ServerPort is the name or IP address of the server machine where the OOB Server Service is installed and running and the port the OOB Server is listening on. The port defaults to 8888 in the OOB Server configuration. This is not the port your database engine is listening on. Separate the server machine from the port with a colon (:) in the ServerPort attribute value. For example, windows_server:8888.

The OOB Server is configurable through a HTTP interface. Supposing your OOB Server is installed on windows.mycompany.local you can access the OOB Server administration interface using the URL http://windows.mycompany.local:8890.

NOTE: The OOB server installation on the windows machine (windows_server) sets the HTTP interface to port 8890 by default, this can be changed during the installation of the OOB server and it can also be changed through the HTTP interface in the Configuration section.

Target Data Source

The Target data source (TargetDSN) tells the OOB client which data source on the remote server you want to access. This must be the name of a SYSTEM data source as the OOB Server can only access System data sources (i.e. when you create the data source in the MS ODBC Administrator select the SYSTEM tab before clicking on Add).

NOTE: Information on creating a DSN on the Windows platform can be found in the OOB User Guide or you can go to it directly here.

Testing your data source

The following script selects and fetches some test data. Use it to check that you can successfully access your data source from a PHP script. Remember to replace the data source name and database username and password placeholders with appropriate values for your database.

In order to create this script:

  1. Copy the script below into a new file.
  2. Edit the datasource_name, database_username and database_password.
  3. Save the file.
  4. Run the script from the command line or view it directly through a web server.

NOTE: If you would like to view the file via a web server, you must save the file in the HTML root of the web server, an example path to the HTML root of Apache is /var/www/html/

<?
# Replace datasource_name with the name of your data source.
# Replace database_username and database_password
# with the SQL Server database username and password.
$data_source='datasource_name';
$user='database_username';
$password='database_password';

# Connect to the data source and get a handle for that connection.
$conn=odbc_connect($data_source,$user,$password);
if (!$conn){
    if (phpversion() < '4.0'){
        exit("Connection Failed: . $php_errormsg" );
    }
    else{
        exit("Connection Failed:" . odbc_errormsg() );
    }
}

# This query generates a result set with one record in it.
$sql="SELECT 1 AS test_col";

# Execute the statement.
$rs=odbc_exec($conn,$sql);

# Fetch and display the result set value.
if (!$rs){
    exit("Error in SQL");
}
while (odbc_fetch_row($rs)){
    $col1=odbc_result($rs, "test_col");
    echo "$col1";
}

# Disconnect the database from the database handle.
odbc_close($conn);
?>

This next script can be used to list all of the tables you have available within a data source, it uses the odbc_tables function to create the list.

In order to create this script:

  1. Copy the script below into a new file.
  2. Edit the datasource_name, database_username and database_password.
  3. Save the file.
  4. View this version of the script directly through a web server.
<html>
<head>
<title>ODBC test script - Table List</title>
</head>
<body>
<?
# This script will list all the tables in the specified data source.
# Replace datasource_name with the name of your data source.
# Replace database_username and database_password
# with the SQL Server database username and password.
$data_source='datasource_name';
$user='database_username';
$password='database_password';

# Connect to the data source and get a handle for that connection.
$conn=odbc_connect($data_source,$user,$password);
if (!$conn){
    if (phpversion() < '4.0'){
        exit("Connection Failed: . $php_errormsg" );
    }
    else{
        exit("Connection Failed:" . odbc_errormsg() );
    }
}

        exit("Connection Failed:" . odbc_errormsg() );
    }
}

# Retrieves table list.
$result = odbc_tables($conn);

   $tables = array();
   while (odbc_fetch_row($result))
       array_push($tables, odbc_result($result, "TABLE_NAME") );
# Begin table of names.
       echo "<center> <table border = 1>";
       echo "<tr><td><b>Table Count</b></td><td><b>Table Name</b></td></tr>";
# Create table rows with data.
   foreach( $tables as $tablename ) {
     $tablecount = $tablecount+1;
     echo "<tr><td>$tablecount</td><td>$tablename</td></tr>";
   }

# End table.
echo "</table></center>";
# Disconnect the database from the database handle.
odbc_close($conn);
?>
</body>
</html>

NOTE: The above script is wrapped in HTML; this enables you to view a formatted table of table names within the data source specified, if you are running the PHP script from the shell prompt use the version of the script below:

In order to create this script:

  1. Copy the script below into a new file.
  2. Edit the datasource_name, database_username and database_password.
  3. Save the file.
  4. View this version of the script by running it from the command line.
<?
# This script will list all the tables in the specified data source.
# Replace datasource_name with the name of your data source.
# Replace database_username and database_password
# with the SQL Server database username and password.
$data_source='datasource_name';
$user='database_username';
$password='database_password';

# Connect to the data source and get a handle for that connection.
$conn=odbc_connect($data_source,$user,$password);
if (!$conn){
    if (phpversion() < '4.0'){
        exit("Connection Failed: . $php_errormsg" );
    }
    else{
        exit("Connection Failed:" . odbc_errormsg() );
    }
}

# Retrieves table list.
$result = odbc_tables($conn);

   $tables = array();
   while (odbc_fetch_row($result))
       array_push($tables, odbc_result($result, "TABLE_NAME") );
# Begin table of names.
       echo "Table Count  Table Name\n";
# Create table rows with data.
   foreach( $tables as $tablename ) {
     $tablecount = $tablecount+1;
     echo "$tablecount            $tablename\n";
   }

# Disconnect the database from the database handle.
odbc_close($conn);
?>

Where to go now

Once you've tested your data source (see Testing your data source) you are ready to start fetching and manipulating the data in your database from PHP

Access MS SQL Server 7.0, 2000 and 2005 from PHP on UNIX and Linux.

Calling Procedures

The following section shows you how to create and call SQL Server procedures

To use the sample scripts in this section you need a SQL Server database in which you can create and delete tables and procedures. The data source that you specify in the scripts needs to connect to this database.

Here is a simple procedure creating from the example select statement taken from above.

<?
# Replace datasource_name with the name of your data source.
# Replace database_username and database_password
# with the SQL Server database username and password.
  $data_source ='datasource_name';
  $user = 'database_username';
  $password = 'database_password';

# Connect to the data source and get a handle for that connection.
  $conn = odbc_connect($data_source,$user,$password,SQL_CUR_DEFAULT);
  if (!$conn){
    if (phpversion() < '4.0'){
      exit("Connection Failed: . $php_errormsg" )
    }
    else{
      exit("Connection Failed:" . odbc_errormsg())
    }
  }

# This procedure simply calls a select statement used in the initial code sample
  $sp = "CREATE PROCEDURE PROCEDURE_TEST AS
         SELECT 1 AS test_col";
# Prepare the statement
  $stmt = odbc_prepare($conn,$sp);
# Execute the statement.
  odbc_execute ($stmt);

  $callsp = "{call PROCEDURE_TEST}";
  $stmt = odbc_prepare($conn,$callsp);
  $parms = array();

  @odbc_execute ($stmt, $parms);

# Output the procedure's return value.
  odbc_result_all($stmt);
# Drop the precedure
  $dropsp = "DROP PROCEDURE PROCEDURE_TEST";
  $stmt = odbc_prepare($conn,$dropsp);

  odbc_execute ($stmt);
# Disconnect the database from the database handle.
  odbc_close($conn);
?>

Common Problems

How do I use Apache/PHP with OOB?

You need to obtain the source distributions of Apache and PHP from http://www.apache.org and http://www.php.net. Instructions for building Apache and PHP with OOB can be found in the Apache_PHP file contained in the docs directory of the OOB distribution.

Please make sure you carefully read the INSTALL files that come with Apache and PHP as they contain valuable information that can save you a lot of headaches.

Apache/PHP can't seem to find my odbc.ini file or Datasource not found and no default driver error. Why?

When you start Apache with PHP built with ODBC support, PHP immediately allocates an ODBC environment handle. Unfortunately, the OOB searches for the odbc.ini file when an environment handle is created and so your odbc.ini file needs to found at server startup. This can be a nuisance since most people start Apache at machine boot time from an rc script but locate their odbc.ini files in the same directory as their PHP scripts. The best way around this is to put all your DSN definitions in the /etc/odbc.ini (or SYS$ROOT:odbc.ini for VMS) file.

Why don't my PHP scripts appear to run on the web server?

Your first port of call should probably be the various PHP or Apache (or whatever web server you are using) news groups, mailing lists and FAQs. However, here are a few pointers specific to Apache/PHP.

  1. Did you build Apache with PHP?
  2. Make sure Apache knows what to do with a PHP script. You need to tell Apache in the httpd.conf (or one of the other config files) that files with the extension php3 or phtml should be passed to PHP. You do this with lines like this: 
    AddType application/x-httpd-php3
AddType application/x-httpd-php3 .phtml

    These tell Apache to pass files ending in .php3 and .phtml to PHP for processing.

  3. Check you can run a simple PHP script. Create a file called test.php3 in the htdocs directory of your web server containing: 
    <html>
<head>
<title>Test</title>
</head>
<body>
<p>
You should see PHP info below:
<?
phpinfo();
?>
</body>
</html>

    Then use the URL http://my_web_server/test.php3 (replace my_webserver with the appropriate machine name and domain) from your browser to see if you get a page of PHP information back. If you don't something far more serious is wrong with your Apache/PHP configuration than not being able to make ODBC connections from PHP through the OOB.

If all the above OK you might try:

  1. Examine your Apache error log. This file is named in your Apache configuration file(s) (e.g. httpd.conf) with the "ErrorLog" setting.
  2. Make sure you followed the PHP INSTALL file and copied the sample php ini file (php3.ini-dist) to the correct place. Some options in this file can prove useful for debugging:
    1. "track_errors = On" stores the last error in the php_errormsg variable.
    2. "error_reporting = 7" outputs Normal errors, Normal warnings, Parser errors.
    3. "error_log = filename" defines the file errors/warnings will be sent to.

If PHP appears to be working but you do not get data back through OOB then see the questions on turning logging in the OOB on.

Why do I get parse errors when compiling PHP with OOB?

There are a number of reasons why you may get parse errors whilst building PHP and OOB many of which are nothing to do with OOB. However some that we have seen are:

In file included from /usr/local/easysoft/oob/client/include/odbc.h:2, from functions/php3_unified_odbc.h:199, from internal_functions.c:90:

/usr/local/easysoft/oob/client/include/sqlext.h:1652: parse error before '0'

/usr/local/easysoft/oob/client/include/sqlext.h:1756: parse error before '0'

In this case a beta tester was building PHP with informix (--with-informix) and custom ODBC support. The problem with doing this is that the both drivers define many of the same symbols. In this case SQLCHAR was defined as '0' by Informix. If you want multiple ODBC drivers available from PHP, Easysoft recommend that you install a driver manager (e.g. http://www.unixodbc.org ) and then tell the driver manager about your multiple drivers.

Why do I get corrupted text columns back from MS SQL Server

We have had reported to us a problem accessing text fields in MS SQL Server. The reporter was attempting to retrieve multiple columns defined as SQL Server "text" in PHP and always retrieved garbage in the second text column. This problem only appears to happen when retrieving multiple text fields from a table with SQLGetData and occurs for the second text column retrieved. PHP sees that text fields in SQL Server can be very long so instead of binding the column (as usual) it uses SQLGetData. This problem could occur in any other ODBC interface if SQLGetData is used for multiple text fields.

The specific report involved a table created as follows:

create table BENCH_TEXT (f1 integer, f2 text, f3 text)
    insert into BENCH_TEXT values(1, 'some text', 'some text')
    select f1, f2, f3 from BENCH_TEXT

The f1 and f2 columns are retrieved OK with SQLGetData but when the f2 column is requested with SQLGetData the returned StrLen_or_IndPtr value is usually too short and the data is garbage.

After a search of the net we discovered a similar report by Chad Slater-Scott which read:

Subject: BUG: Service Pack 1 on SQL7.0 (SQLExtendedFetch Returns metadata on SQLGetData)
Date: 09/27/1999
Author: Chad Slater-Scott <avacado@usa.net>

Just wanted to let you all know that SP1 For SQL Server has a new bug. After using SQLExtendedFetch or SQLFetchScroll to retrieve records using SQL_ABSOLUTE or SQL_RELATIVE positioning, a SQLGetData call to a text field will return garbage. This is not reproducible if the text field is the only field selected or if you the text field is the first field called. In order to reproduce it, another field value must be retrieved using SQLGetData. I reported this to Microsoft and a Bug has been filed. If any of you are using this sequence of calls and have installed SP1, check your stuff for garbage on the text fields. The Bug ID is 56509 is want to track its status.

We have been told that the problem is in the SQL Server driver included in SP1 for SQL Server and SP6 for NT. The version with the problem appears to be C:\winnt\system32\sqlsvr32.dll (3.70.06.90) and the working version is 3.70.0623.

Our machines running SQL Server 7 and NT 4 sp4 appear OK.

Why is my NT OOB Server using a lot of CPU, my ODBC connections go to MS SQL Server?

Obviously, there could be many reasons for this. It should be remembered that all the CPU used and showing in Task Manager for esoobserver includes the CPU used by the driver manager and any ODBC drivers you are using. If the OOB Server is running threaded (the default) then CPU usage SEEN in Task Manager will be greater than if the OOB Server is running MultiProcess. This is simply due to the fact that when in MultiProcess mode a new process is created to handle each incoming connection, when that connection is closed the OOB Server process handling it will die and you cannot see the CPU usage for a dead process.

The most common scenario where the OOB Server appears to be using a lot of CPU is when it is handling a connection to MS SQL Server, a result-set has been generated but not consumed/closed and an attempt to create a new result-set is made. In this case, the MS SQL Server ODBC driver appears to spin-wait, consuming a lot of CPU. We have seen this ourselves on our web site and a few customers have hit the same problem - it is always down to not closing the result-set. The example below is a real example which occurred in a PHP script, it is provided in the hope it will help others avoid this problem.

The table in the MS SQL Server database contained a column defined as TEXT. One row in the table contained one of these text columns with over 7K in it but PHP's odbc.defaultlrl (default long read length) was set to the default of 4K. A query was issued which retrieved all of the columns from this table. PHP bound columns for most of the columns in the table but used SQLGetData to retrieve the TEXT column as it was defined as SQL_LONGVARCHAR. The problem arises because when PHP's odbc_result() was called to retrieve the result-set data, PHP passed a 4K buffer which was insufficient, MS SQL Server returned 4K and SQL_SUCCESS_WITH_INFO and hence the result-set is not consumed (ODBC says you can call SQLGetData multiple times on the same column to retrieve the data in chunks). The PHP script then goes on to issue another query which hangs. At this stage the OOB Server on the NT machine showed a lot of CPU usage which was tracked down to spin-waiting in the ODBC driver. This problem can be avoided very simply by closing any result-set you generate, in PHP that means calling odbc_free_result(), in ODBC terms it is a call to SQLFreeStmt(SQL_CLOSE).

In every case high CPU usage in the OOB Server has been reported to us, the connection was always to MS SQL Server and always resulted from the result-set not being closed.

Appendix A: Resources

Access MS SQL Server 7.0, 2000 and 2005 from PHP on UNIX and Linux.



© 1993 - 2008 Easysoft Limited. All rights reserved.