Export (0) Print
Expand All

Chapter 13 - Developing: Applications - Migrating PHP

Published: April 27, 2005
On This Page

Introduction and Goals Introduction and Goals
PHP Modules PHP Modules
Scenario 1: Interoperating PHP on UNIX with SQL Server Scenario 1: Interoperating PHP on UNIX with SQL Server
Case 2: Interoperating a PHP Application Using OCI8 Functions Case 2: Interoperating a PHP Application Using OCI8 Functions
Case 3: Interoperating a PHP Application Using ODBC Functions Case 3: Interoperating a PHP Application Using ODBC Functions
Common Function Translation Issues Common Function Translation Issues
Scenario 2: Porting the Application to Win32 Scenario 2: Porting the Application to Win32

Introduction and Goals

This chapter explores the options available to maintain connectivity of PHP: Hypertext Preprocessor (PHP) applications as part of the Oracle to Microsoft® SQL Server™ migration project. In PHP, the logic to interact with databases is provided by database-specific modules (or drivers). These modules have specialized functions to perform database-related tasks and can be replaced to accommodate a SQL Server backend with relative ease.

PHP is an open source, server-side, cross-platform scripting language used to create dynamic Web pages. Like any other Common Gateway Interface (CGI), PHP can provide dynamic, data-driven characteristics to static HTML forms or pages.

As discussed in Chapter 2, "Envisioning Phase," there are four different strategies available for transitioning applications in an Oracle to SQL Server migration project. The strategies are:

  • Interoperate the application with the UNIX environment.

  • Port or rewrite the application to the Microsoft .NET platform.

  • Port or rewrite the application to the Microsoft Win32® platform.

  • Quick port using the Microsoft Windows® Services for UNIX 3.5 platform.

Because of the unique characteristics of PHP, some of these strategies are more feasible than others.

For example, because PHP can be ported to Windows, there is no need to rewrite the application for .NET or Win32 unless the existing solution's source code is not available. Because the available drivers for .NET are still in beta, the porting has to be done to a Win32 environment, Also, because the application can run within the Windows environment, a quick port using Windows Services for UNIX is not necessary in most cases.

Based on the available migration strategies, two scenarios can be developed to migrate PHP applications. These scenarios include:

  • Scenario 1: Interoperating PHP on UNIX with SQL Server

    If the business requirements do not include eliminating the UNIX environment, an interoperation strategy can be implemented quickly. Few changes need to be made to the source code and installing a new driver allows the PHP application to connect to a SQL Server database. If the migration is performed in phases, interoperation can be used as an interim step.

  • Scenario 2: Porting the Application to Win32

    PHP applications can also be ported to run natively on the Windows platform. As with interoperation, few changes need to be made to the source code.

Available options are discussed for each scenario in separate cases. Each case lists steps required to connect the PHP application to the SQL Server data source. Also included are discussions on the differences between the various Oracle and SQL Server functions. When applicable, sample source code is provided to illustrate changes that need to be made.

Note If your PHP scripts use UNIX system calls extensively (such as frequent use of

exec
,
passthru
,
popen
, or the backtick (`)), porting them to Windows Services for UNIX/Interix may be a suitable option because Interix has more support for the desired system calls. Chapter 2, "Envisioning Phase," provides a more detailed discussion of when choosing Windows Services for UNIX would be more appropriate when moving to a Windows-based platform.

ors13_00.gif

You will need the following to implement this option (porting PHP applications to Windows Services for UNIX/SQL Server):

  • A port of PHP for Interix downloadable from Interop Systems at http://www.interopsystems.com/tools/warehouse.htm, which includes the capability of using DBLib, CTLib and ODBC. The PHP distribution will have to be compiled for using a specific database connectivity library.

  • A connectivity driver to the SQL Server database. This is provided by the port of FreeTDS on Interix and is downloadable from Interop Systems at http://www.interopsystems.com/tools/db.htm. FreeTDS provides three connectivity options for the PHP application to connect to the SQL Server database. Two options go through the libraries DBLib and CTLib, which provide connectivity to both Sybase and SQL Server. The third is the ODBC driver, which can be used to access any ODBC database (including SQL Server).

This technology option, however, has not been fully tested as a part of development of this solution and therefore has not been detailed further.

PHP Modules

PHP contains a rich set of options for interfacing with common proprietary databases, including Oracle and SQL Server. These interfaces are available through PHP as internal functions or extensions. The database-related functions are grouped into modules which must be compiled into the PHP installation before use. These modules are enabled by specifying them in the configuration file (php.ini).

Figure 13.1 PHP modules (functions) that can access Oracle

Figure 13.1 PHP modules (functions) that can access Oracle

Figure 13.2 PHP modules (functions) that can access SQL Server

Figure 13.2 PHP modules (functions) that can access SQL Server

The following modules are commonly used with Oracle databases. Each is discussed in detail in this chapter:

  • Oracle (ORA) functions

    ORA is a set of functions used to access Oracle databases.

    The php_oracle value in the php.ini file (extension=php_oracle.dll) is used to enable functions. The functions in this module can be identified by the ora_ prefix, such as ora_logon().

  • Oracle 8 (OCI8) functions

    OCI8 is a set of functions based on Oracle Call Interface (OCI) used to access Oracle databases, including 8i and 9i releases. This module is more flexible than the standard Oracle functions.

    The php_oci8 value in the php.ini file (extension=php_oci8.dll) is used to enable the functions. The set of functions that are in this module can be identified by the oci_ prefix, such as oci_logon(). Before PHP 5.0, every oci_ function was named with an oci prefix. Although the functions with the oci prefix are still available, they are now deprecated.

  • ODBC functions

    A modified version of ODBC that provides a unified driver for native support for several databases is available for PHP. ODBC support is integrated with PHP and, unlike the modules needed for ORA and OCI8, no extensions need to be specified.

    ODBC has the advantage of allowing applications to be written which, theoretically, are independent from the RDBMS. If the RDBMS changes, only the driver will need to be changed, not the code.

    ODBC function calls are handled by a driver manager that passes the calls to a specific driver for the target database for execution. The iODBC driver manager is commonly used with PHP to connect to databases on Windows. iODBC (http://www.iodbc.org) is an open source ODBC driver manager and the SDK is maintained by OpenLink Software (http://www.openlinksw.com). iODBC calls use an odbc_ prefix. For example, odbc_connect() utilizes the ODBC driver manager.

    Another open source project, named after the unixODBC project, has developed a driver manager called unixODBC (http://www.unixodbc.org). The unixODBC calls use the three-letter SQL prefix. For example, SQLconnect() uses the unixODBC driver manager.

    Note The function phpinfo() can be called to reveal the extensions that have been loaded into PHP. A simple script containing the following line is sufficient to implement this call:

    <php phpinfo(); ?>

Scenario 1: Interoperating PHP on UNIX with SQL Server

It is possible to interoperate between PHP applications in a UNIX environment and SQL Server databases using Microsoft® SQL Server™ functions or through an ODBC interface. Each API has its own function calls to connect to the database and execute statements. These APIs vary in support for common functions such as those related to cursors and statements.

Case 1: Interoperating a PHP Application Using ORA Functions

If the existing PHP solution uses ORA functions, then the best alternative is to replace them with Microsoft SQL Server (MSSQL) functions. This modification will require replacing the current driver with one that supports the MSSQL functions. One of the drivers that can be used is FreeTDS (http://www.freetds.org).

FreeTDS is a set of libraries for UNIX and Linux that allows programs to interact with Microsoft SQL Server. Using the FreeTDS libraries, PHP's mssql_xxx() functions can be used to directly access a SQL Server database from a UNIX or Linux computer. FreeTDS is an open source implementation of the Tabular Data Stream protocol used by SQL Server for its clients. The FreeTDS C libraries are freely available under the terms of the GNU GPL license.

The protocol version also affects how database servers interpret commands. Microsoft SQL Server 2000 is known to behave differently with versions 4.2 and 8.0 of FreeTDS. version 8.0 is recommended for compatibility with SQL Server tools.

To interoperate a PHP application using ORA functionality, follow these common steps:

  1. Install the FreeTDS SQL Server libraries.

    Download the source code from http://www.freetds.org/software.html. The installation process is very similar to that detailed in Appendix D, "Installing Common Drivers and Applications," except that the installation is occurring in UNIX instead of Windows Services for UNIX.

    Note FreeTDS should be compiled with the --enable-msdblib option.

  2. Recompile PHP to use MSSQL functions.

    Refer to http://www.freetds.org/userguide/php.htm for more information on compiling PHP.

    Note PHP should be configured with the --with-mssql[=DIR] switch, where DIR is the location of the FreeTDS libraries. For example, from the PHP source directory, type:

    ./configure --with-mssql=/usr/local/freetds

  3. Enable MSSQL functions.

    Before MSSQL functions can be used, they must be enabled and initialized for use by uncommenting or adding the following line in the initialization file php.ini.

    extension=php_mssql.dll
  4. Verify the installation.

    Verify that the module is installed by executing the phpinfo() function. MSSQL should appear in the list of modules.

  5. Change all embedded SQL statements to T-SQL.

    Refer to chapter 11, "Developing: Applications — Oracle SQL and PL/SQL," for a detailed discussion on modifying Oracle SQL to be SQL Server-compliant.

  6. Modify the PHP application code.

    The following sample source code is used to illustrate the changes required when migrating PHP code from ORA functions to MSSQL functions. Pay special attention to the differences between these code sections.

    Note MSSQL does not have functions to create and drop cursors. Implicit cursors are used to handle results returned.

    The following example is sample ORA-based PHP code that executes a simple query and retrieves results using a cursor operation:

    <?php
    // Connection parameters
    $tns = "hrdb";
    $username = "scott@$tns";
    $password = "tiger";
    // Create Statement
    $sql = "SELECT last_name, salary FROM hr.employee ORDER BY salary";
    // Make connection
    $conn = ora_logon($username, $password);
    // Open cursor
    $mycursor = ora_open($conn);
    ora_parse($mycursor, $sql, 0);
    // Execute query
    ora_exec($mycursor);
    // Display the information
    while (ora_fetch($mycursor)) {
    echo "RESULT: " . ora_getcolumn ($mycursor, 0) . ", " . ora_getcolumn ($mycursor, 1) . "<br>\ ";
    }
    // Close cursor
    ora_close($mycursor);
    // Close connection
    ora_logoff($conn);
    ?>

    The following example is the same code modified to MSSQL-based PHP code:

    <?php
    // Connection parameters
    $sqlserver = "win2kss1";
    $username = "scott";
    $password = "tiger";
    // Create Statement
    $sql = "SELECT last_name, salary FROM hr..employee ORDER BY salary";
    // Make connection
    $conn = mssql_connect($sqlserver, $username, $password);
    // Execute query
    $sqldb = mssql_select_db("hr",$conn);
    $mycursor = mssql_query($sql);
    // Display information
    while ($row = mssql_fetch_array($mycursor)){
    echo "RESULT: " . $row['last_name'] . ", " . $row['salary'] . "<br>\n";
    }
    // Close connection
    mssql_close($conn);
    ?>

    Database server information can also be referenced from freetds.conf, as shown in the following configuration information.

    /* freetds.conf
       [SQLServer]
       host = win2kss1
       port = 1433
       tds version = 8.0
    */

    To use the freetds.conf entry, the connection code will change as follows:

    // specify path to file
    putenv(FREETDSCONF=/usr/local/freetds.conf’)
    $conn=mssql_connect(‘SQLServer’, $username, $password);

The following table provides a mapping between key ORA functions and the respective MSSQL functions. ORA functions that do not have a matching MSSQL function are denoted with a hyphen. A discussion of handling these disparate functions appears in the "Common Function Translation Issues" section later in this chapter.

Table 13.1: Mapping Between ORA and MSSQL Functions

Operation

Task

ORA function

MSSQL function

Connection

Open connection

ora_logon()

mssql_connect()

 

Close connection

ora_logoff()

mssql_close()

 

Persistent connection

ora_plogon()

mssql_pconnect()

Cursor

Open cursor

ora_open()

-

 

Close cursor

ora_close()

-

Parsing

Parse statement

ora_parse()

-

Binding

Bind variable

ora_bind()

mssql_bind()

Execution

Execute statement

ora_exec()

mssql_execute()

mssql_query()

 

Prepare and execute

ora_do()

mssql_execute()

mssql_query()

Fetching

Fetch row

ora_fetch()

ora_fetch_into

mssql_fetch_row()

mssql_fetch_array()

 

Fetch column

ora_getcolumn()

mssql_fetch_field

Transaction Management

Commit

ora_commiton()

ora_commitoff()

ora_commit()

-

 

Rollback

ora_rollback()

-

Error Handling

Error checking

ora_error()

ora_errorcode()

-

Others

Name of result column

ora_columnname()

mssql_field_name()

 

Size of result column

ora_columnsize()

mssql_field_length()

 

Datatype of result column

ora_columntype()

mssql_field_type()

 

Number of rows effected

ora_numrows()

mssqp_num_rows()

 

Number of columns returned

ora_numcols()

mssql_num_fields()

Case 2: Interoperating a PHP Application Using OCI8 Functions

To interoperate using OCI8, follow these common steps:

As with Case 1 "Interoperating a PHP Application Using ORA Functions", the best alternative for OCI8 functions is to replace them with MSSQL calls. Most of the steps are the same between this procedure and the procedure in the "Case 1: Interoperating a PHP Application Using ORA Functions" section of this scenario. As a result, these steps are not described in detail here.

  1. Install the FreeTDS SQL Server libraries.

  2. Compile PHP to use MSSQL APIs.

  3. Enable MSSQL functions within php.ini.

  4. Verify the installation using phpinfo().

  5. Change all embedded SQL statements to T-SQL.

    Refer to Chapter 11, "Developing: Applications — Oracle SQL and PL/SQL," for a detailed discussion on modifying Oracle SQL to be SQL Server-compliant.

  6. Modify application code to reflect the new drivers.

    The following sample source code is used to illustrate the changes required in PHP code when replacing OCI8 functions with MSSQL functions.

    The following example is the original OCI8-based PHP code:

    <?php
    // Create connection
    $conn = OCILogon("scott","tiger","hrdb");
    if ( !$conn ) {
       $err = OCIError();
       echo "Unable to connect – " . $err[text];
       die();
    }
    // Insert SQL statement without binding
    $sql1 = OCIParse($conn, "INSERT INTO countries VALUES (‘US‘,‘United States of America’, 2)");
    OCIExecute($sql1);
    // Insert SQL statement with binding
    $var1 = ‘MX’;
    $var2 = ‘Mexico”;
    $var3 = 3;
    $sql2 = OCIParse($conn, "INSERT INTO countries VALUES (:bind1, :bind2, :bind3); ");
    OCIBindByName($sql2, ":bind1", $var1);
    OCIBindByName($sql2, ":bind2", $var2);
    OCIBindByName($sql2, ":bind3", $var3);
    OCIExecute($sql2);
    echo OCIRowCount($sql2) . " rows inserted. <br />";
    // Commit
    OCICommit($conn);
    // Close connection
    OCILogoff($conn);
    ?>

    The following example is the same PHP source code modified to use the MSSQL drivers. Note the difference in the commit function.

    <?php
    // Create connection
    $conn = mssql_connect("win2kss1", "scott", "tiger");
    if ( !$conn ) {
       echo "Unable to connect - $conn";
       die();
    }
    //begin
    $sql = "begin tran";
    $result = mssql_query($sql);
    // Insert SQL statement without binding
    $sql = "INSERT INTO hr.dbo.countries VALUES ('US','United States of America',2)";
    mssql_query($sql);
    $result = mssql_query("SELECT @@ROWCOUNT");
    echo mssql_rows_affected($conn) . " rows inserted. <br />";
    $sql = "INSERT INTO hr.dbo.countries VALUES ('MX','Mexico', 3)";
    mssql_query($sql);
    //commit
    $sql = "commit_tran";
    $result = mssql_query($sql);
    // Close connection
    mssql_close($conn);
    ?>

Table 13.2 maps the equivalent calls between OCI8 and MSSQL functions. A hyphen is used when a similar function does not exist in MSSQL. A discussion on handling these disparate functions is available in the "Common Function Translation Issues" section later in this chapter.

Table 13.2: Mapping Between OCI8 and MSSQL Functions

Operation

Task

OCI8 Functions

MSSQL Functions

Connection

Open connection

ociLogon()

mssql_connect()

 

Close connection

ociLogoff()

mssql_close()

 

Persistent connection

ociPLogon()

mssql_pconnect()

Cursor

Open cursor

ociNewCursor()

-

 

Close cursor

ociFreeCursor()

-

Statement

Free statement

oci_free_statement()

mssql_free_statement()

Parsing

Parse statement

ociParse()

-

Execution

Execute statement

ociExecute()

mssql_execute()

mssql_query()

Fetching

Fetch row

ociFetch()

mssql_fetch_row()

Transaction Management

Commit

ociCommit()

-

 

Rollback

ociRollback()

-

Error Handling

Error checking

ociError()

-

Others

Name of result column

ociColumnName()

mssql_field_name()

 

Size of result column

ociColumnSize()

mssql_field_length()

 

Datatype of result column

ociColumnType()

mssql_field_type()

 

Number of rows effected

ociRowCount()

mssql_num_rows()

 

Number of columns returned

ociNumCols()

mssql_num_fields()

Case 3: Interoperating a PHP Application Using ODBC Functions

While migrating from Oracle to SQL Server, no changes will be needed to the ODBC calls themselves; the availability of SQL Server-specific drivers for the UNIX environment and the capabilities of the driver manager to work with these drivers define the changes that must be made. The best choice for interoperating with SQL Server is offered by the

FreeTDS driver, which is supported by the iODBC driver manager of the source environment.

Other options available include Easysoft's ODBC-ODBC Bridge (OOB) and OpenLink's Universal Data Access drivers.

To interoperate using ODBC functions, follow these common steps:

The following steps are required to interoperate PHP applications with SQL Server.

  1. If needed, install ODBC software.

    It is assumed that the iODBC driver manager is already installed and is being used to connect to Oracle. If this is not true, then either the iODBC or the unixODBC driver manager must be installed.

    The iODBC driver manager can be downloaded from http://www.openlinksw.com/iodbc/. Installation instructions are available in Appendix D, "Installing Common Drivers and Applications."

    The unixODBC driver manager can be downloaded from http://www.unixodbc.org/. Installation instructions are also available in Appendix D, "Installing Common Drivers and Applications."

  2. Install the SQL Server driver.

    The most popular ODBC driver to connect to SQL Server from a UNIX operating system is FreeTDS. FreeTDS is an open source implementation of TDS that allows UNIX-based programs native access to SQL Server. OpenLink also offers drivers for SQL Server that use the FreeTDS implementation of TDS. Installation instructions are located in Appendix D, "Installing Common Drivers and Applications."

    An ODBC-ODBC bridge to SQL Server is available from Easysoft. For detailed instructions on enabling ODBC support under PHP, refer to http://www.easysoft.com/products/2002/apache.phtml.

    OpenLink (http://www.openlinksw.com) also offers single-tier and multitier Universal Data Access drivers.

  3. Recompile PHP to use ODBC functions.

    To be capable to use the SQL Server driver, PHP must be recompiled with ODBC functions. This procedure will vary based on the ODBC driver that is installed. Refer the following resources for more information.

  4. Configure the Data Source Name (DSN).

    ODBC needs an ODBC data source to connect to the database. The DSN is generally defined in an odbc.ini file that the driver manager uses to load the ODBC driver.

    When using FreeTDS, the DSN can also be configured by modifying the odbc.ini file. A SQL Server DSN should be similarly configured to the following example.

    [ODBC Data Sources]
    SS_HR_DB=Sample MS SQLServer 
    [SS_HR_DB]
    Driver=/usr/local/freetds/lib/libtdsodbc.so
    Description=SQL Server 2000
    Server=win2kdbp1
    Port=1433
    Database=hrapp
    LogonID=daveb
    Password=cougar
  5. Check the connection.

    In iODBC, the command line query program odbctest can be used to test the DSN connectivity. If using unixODBC, the isql utility can be used to do this.

  6. Modify the application source code to reflect the new data source.

    Because ODBC is a generic interface to any database, no changes are required in the application code except to connect to the SQL Server database. The proper DSN has already been set up in an earlier step.

    The following PHP code snippet uses the ODBC module to connect to an Oracle data source, and it should be similar to code in the existing solution.

    <?php
    // create connection    
    $conn = odbc_connect('ORA_HR_DB', '', ''); 
    if (!$conn){ 
    exit("Unable to connect to database: $conn"); 
    } 
    ?>

    When migrated to connect to SQL Server, the connection string will reflect the changes that follow.

    <?php
    // create connection    
    $conn = odbc_connect('SS_HR_DB', '', ''); 
    if (!$conn){ 
    exit("Unable to connect to database: $conn"); 
    } 
    ?>
  7. Change all embedded SQL statements to T-SQL.

    Refer to Chapter 11, "Developing: Applications — Oracle SQL and PL/SQL," for a detailed discussion on modifying Oracle SQL to be SQL Server-compliant.

Table 13.3 is a listing of the ODBC function calls that are used in PHP code to work with databases.

Table 13.3: ODBC Function Calls in PHP

Operation

Task

ODBC Function

Connection

Open connection

odbc_connect()

 

Open persistent connection

odbc_pconnect()

 

Close connection

odbc_close()

 

Get connection information

odbc_data_source()

Parsing

Prepare statement

odbc_prepare()

Cursor

Get cursor name

odbc_cursor()

Execution

Execute statement

odbc_execute()

 

Prepare and execute

odbc_exec()

odbc_do()

Fetching

Fetch row

odbc_fetch_row()

odbc_fetch_array()

odbc_fetch_into()

odbc_fetch_object()

 

Fetch column

-

Transaction Management

Commit

odbc_commit()

 

Rollback

odbc_rollback()

Error Handling

Error Checking

odbc_error()

 

Get last error

odbc_errormsq()

Others

Name of result column

odbc_field_name()

 

Name of table column

odbc_columns()

 

Size of result column

odbc_field_len()

 

Datatype of result column

odbc_field_type()

 

Number of rows effected

odbc_num_rows()

 

Number of columns returned

odbc_num_fields()

Common Function Translation Issues

Though SQL Server and Oracle share a great deal of functionality, some database operations differ. This section discusses some of the differences between coding with PHP extensions for Oracle and SQL Server.

Handling Transactions

The Oracle PHP extension module supports transactions using the following function calls:

  • ora_commit()

  • ora_commitoff()

  • ora_commiton()

  • ora_rollback()

The OCI PHP extension module supports transactions using the following function calls.

  • OCICommit()

  • OCIRollBack()

MSSQL API does not provide commit or rollback functions. These functions can be achieved using the following example code:

// Here $db is a PHP variable for connection object
//To begin transaction
$sql = "begin tran";
$result = mssql_query($sql,$db);
//To commit transaction
$sql = "commit tran";
$result = mssql_query($sql,$db);
//To rollback transaction
$sql = "rollback tran";
$result = mssql_query($sql,$db);

Migrating Cursors

Oracle requires using cursors with SELECT statements, regardless of the number of rows requested from the database. In SQL Server, a SELECT statement that is not disclosed on a cursor returns rows to the client as a default result set. This is an efficient way to return data to a client application.

When porting a PL/SQL procedure from Oracle, first determine whether cursors are needed to perform the same function in Transact-SQL. If the cursor is used only to return a set of rows to the client application, use a non-cursor SELECT statement in Transact-SQL to return a default result set. If the cursor is used to load data a row at a time into local procedure variables, use cursors in Transact-SQL.

Unlike Oracle and OCI8 (which have methods for explicit use of cursors), MSSQL uses implicit cursors. Functions such as mssql_query() and mssql_execute() associate a handle to the returned result, which can be traversed similar to an explicit cursor. The following code shows the use of result set to achieve the same functionality as that of a cursor.

$conn=mssql_connect("myhost","user","pwd");
mssql_select_db("hr",$conn);
// create the select statement  
$sqlquery="SELECT companyName FROM Customers;";
// activate the query and retrieve the result set
$results= mssql_query($sqlquery);
// Do a row fetch from the result set for display
while ($row=mssql_fetch_array($results)){
    echo $row['companyName']."<br>\n";
}

SQL Server cursors may also be used for one of the following reasons:

  • Updateable cursors

  • Scrollable, read-only cursors

    Note ODBC Cursor Library cursors build on top of default result sets. Be sure to fetch to the end of the result set quickly to free up locks.
    When you use server cursors, use SQLExtendedFetch to fetch in blocks of rows instead of a single row at a time. This is the same thing as array-type fetching in Oracle. This saves on round-trip fetches to the server. For more information on SQLExtendedFetch, refer to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlextendedfetch.asp.
    Use result sets when you think it will fetch all rows at one time, but use server cursors when you know a result set would not fetch all rows at once.

Connection Pooling

While PHP does not offer connection pooling, persistent Oracle connections can be opened with the following API function calls.

  • Oracle API: ora_plogon()

  • OCI8 API: ociPLogon()

To interoperate with SQL Server, these functions should be replaced by one of the following functions, depending upon the API used:

  • ODBC API: odbc_pconnect()

  • MSSQL API: mssql_pconnect()

Unlike connection pooling, a persistent connection is local to a process and cannot be shared between different processes.

Stored Procedures

Stored procedures can be handled by the MSSQL API as well as the ORA and OCI8 APIs. The following example shows sample stored procedures using MSSQL calls.

// Create Query for Stored Procedure In Code
// Stored procedure name: CustOrdersDetail from Northwind database
$stmt = mssql_init("CustOrdersDetail",$conn);
mssql_select_db("Northwind", $conn);
// Parameter values
$orderid = 1;
// Binding parameter value to stored procedure
mssql_bind($stmt, "@OrderID", $orderid, SQLINT4);
// Execute SQL Statement & Capture Results
$result = mssql_execute($stmt);
// Fetch return data and place into array for access
while($row = mssql_fetch_array($result)){
// Assign Variables
$unitprice = $row["UnitPrice"];
echo "Unit Price" . $unitprice;
$quantity = $row["Quantity"];
echo "Quantity" . $quantity;
$discount = $row["Discount"];
echo "Discount" . $discount;
$price = $row["ExtendedPrice"];
echo "Price" . $price;
}

Scenario 2: Porting the Application to Win32

Because PHP is a cross-platform language, porting applications from a UNIX environment to Windows is not much more complex than the steps required for interoperation. The strategy, steps, and actual changes required are very similar to those discussed for interoperation. The only additional step is installing PHP and the application code in the Windows environment.

Case 1: Porting a PHP Application using ORA Functions

Most of the following steps are covered in more detail in the "Case 1: Interoperating a PHP Application Using ORA Functions" section of Scenario 1.

To port the application using ORA functions, follow these common steps:

  1. Install PHP on target Windows server.

    For more information on installing PHP, refer to http://uk2.php.net/manual/en/install.php.

  2. Install SQL Server libraries.

    Instead of the FreeTDS libraries, the Windows-based libraries offered by Microsoft in SQL Client Tools can be installed on the system where PHP is installed. The Client Tools can be installed from the MS SQL Server CD or by copying ntwdblib.dll from \winnt\system32 on the server to \winnt\system32 on the target Windows server. However, copying ntwdblib.dll will only provide access. Configuration of the client will require installation of all the tools.

  3. Compile PHP to use the MSSQL module.

  4. Enable MSSQL functions in php.ini.

  5. Verify the installation using phpinfo().

  6. Transport source code to target server.

  7. Modify the application's source code to reflect the MSSQL API instead of ORA functions.

Case 2: Porting a PHP Application Using OCI8 Functions

Most of the steps needed to migrate this application are discussed in more detail in the "Case 2: Interoperating a PHP Application Using OC18 Functions" section of Scenario 1. The following steps are required to migrate PHP applications to Windows using OC18 functions.

To port the application using OCI8 functions, follow these common steps:

  1. Install PHP on target Windows server.

  2. Install SQL Server libraries from SQL Client Tools.

  3. Compile PHP to use the MSSQL module.

  4. Enable MSSQL functions in php.ini.

  5. Verify the installation using phpinfo().

  6. Transport the source code to the target Windows server.

  7. Modify the application code to reflect the MSSQL API instead of OCI functions.

Case 3: Porting a PHP Application Using ODBC Functions

Most of the steps listed as follows are discussed in more detail in the "Case 3: Interoperating a PHP Application Using ODBC Functions" section of scenario 1. The following steps are required to migrate PHP applications to Windows using ODBC functions.

To port the application using ODBC functions, follow these common steps:

  1. Install PHP on target Windows server.

  2. Install ODBC software.

    For installation instructions, refer to http://support.microsoft.com/default.aspx?scid=kb;en-us;313008.

  3. Install the SQL Server driver.

    In addition to the drivers mentioned for interoperation, the Microsoft ODBC driver for SQL Server may also be used. This driver is available along with the SQL Server client CD. Installation instructions are available at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_install_029l.asp.

  4. Compile PHP to use ODBC in the php.ini file.

  5. Configure the DSN to connect with the SQL Server database.

  6. Check the connection using command line utilities.

  7. Transport the source code to the target Windows environment.

  8. Modify the application source code to reflect the MSSQL API.

Download

Get the Solution Guide for Migrating Oracle on UNIX to SQL Server on Windows

Update Notifications

Sign up to learn about updates and new releases

Feedback

Send us your comments or suggestions

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft