Chapter 12 - Developing: Applications - Migrating Perl

Article
12/05/2007

Introduction and Goals

This chapter contains a detailed discussion of changes that must be made to Perl applications to work with Microsoft® SQL Server™. At the conclusion of this chapter, the Perl application should be capable of successfully connecting to the SQL Server database that was migrated from Oracle and the solution can be tested.

As discussed in the "Define the Solution Concept" section of 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 UNIX.
Port or rewrite the application to the Microsoft .NET platform.
Port or rewrite the application to the Microsoft Win32® platform.
Quick port using the Windows® Services for UNIX 3.5 platform.

Perl is an interpreted scripting language that runs on both UNIX and Windows. Because of the cross-platform capabilities of Perl, some of these strategies are more logical than others.

For example, because Perl can be ported to Windows, there is no need to rewrite the application in the .NET framework or for the Win32® environment. Also, because the application can run within the Windows environment, a quick port using Windows Services for UNIX is not necessary and would only prevent the application from tightly integrating with Windows.

Based on available migration strategies, two scenarios can be developed to migrate Perl applications. The solution scenarios are:

Scenario 1: Interoperation of Perl 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 Perl application to connect to a SQL Server database. Interoperation can also be used as an interim step if the migration is performed in phases.
Scenario 2: Port the Perl Application to Win32

Perl 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.

Note If your Perl applications use UNIX system calls extensively (such as frequent use of
```
syscall
```

and FakePre-806136d742e54d289aa34facf027f0a2-d491083e641c47baaa02cad0f4cc8789), 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 Services for UNIX would be more appropriate when moving to a Windows-based platform.

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

A port of Perl for Interix (downloadable from Interop Systems at https://www.interopsystems.com/tools/warehouse.htm), which ships with the necessary DBI, DBD::ODBC and DBD::Sybase modules.
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 https://www.interopsystems.com/tools/db.htm. FreeTDS provides two connectivity options for the Perl application to connect to the SQL Server database. One is a library called CTlib, which can be accessed through the DBD::Sybase module. The other is an odbc driver, which can be accessed though the DBD::ODBC module.
If you use the ODBC driver, you will also need an ODBC driver manager. Two different ODBC driver managers iODBC and unixODBC are available for Windows Services for UNIX from https://www.interopsystems.com/tools/warehouse.htm.

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

Introduction to the Perl DBI Architecture

There are several different modules available for use with Perl that extend the capabilities of the language. One of these modules, the Database Independent Interface (DBI), provides a common programming interface for several different proprietary databases, including Oracle and SQL Server. DBI offers a single set of functions or methods that can interact with the databases. These functions form an abstraction layer that allows the programmer to write code using generic DBI calls without needing specific API knowledge for the particular database. This layer makes the Perl code portable across databases.

These generic DBI calls are then interpreted into RDBMS-specific functions using specialized Database Driver (DBD) modules. A DBD is a database-specific driver that translates DBI calls to the target database. The DBI API forwards the call to the appropriate database driver module, which then provides support, such as the dynamic loading of drivers, error-checking, and management. Figure 12.1 provides a visual representation of path from a Perl script to the backend database using DBI-DBD.

Figure 12.1 Perl communication with databases using DBI API and DBD drivers

DBI also supports the Open Database Connectivity (ODBC) interface. While DBI itself is an abstraction layer, ODBC forms another additional abstraction layer. This layer, consisting of an ODBC driver and ODBC driver manager mirrors the functions of the DBI and DBD within Perl. If the current solution utilizes ODBC, then a port using ODBC may provide the most direct solution. In other instances, ODBC may simply duplicate other layers and add additional processing overhead. In these situations, ODBC is not recommended. Figure 12.2 shows the path from Perl script to backend database involving ODBC drivers.

Figure 12.2 ODBC adds additional layers of connectivity between the application and database

Scenario 1: Interoperation of Perl on UNIX with SQL Server

Interoperation between a UNIX-based Perl application and a SQL Server database is possible because of the availability of a driver that interfaces between these technologies. Because of the DBI API, this type of migration is usually a relatively simple process. Only minimal changes may need to be made to the Perl application's source code. The cases described in this section discuss options available for Perl applications using ODBC or Oracle drivers.

The following cases assume that the Perl application currently uses either the ODBC DBD or the Oracle DBD within the existing solution.

Case 1: Interoperating an ODBC DBD Application

If the original application was written using ODBC DBD, then the best migration option is to continue to use this driver. Because ODBC is not database-specific, no changes should need to be made to the application's source code. The only changes that need to be made concern connectivity with the SQL Server database.

To interoperate the Perl application using ODBC, follow these common steps:

Install the ODBC driver.

The DBD::ODBC module requires a driver manager and a driver to interact with SQL Server. DBD::ODBC is packaged with the Independent ODBC (iODBC) driver manager from OpenLink Software (https://www.openlinksw.com/). For more information about this driver, refer to https://www.freetds.org/userguide/perl.htm#DBD.ODBC. Another driver manager, unixODBC, that could be used in an interoperation scenario is available from https://www.freetds.org/userguide/prepodbc.htm.

FreeTDS is a popular ODBC driver used to connect to SQL Server from a UNIX-based application. FreeTDS is an open source implementation of Tabular Data Stream (TDS) protocol that allows the application native access to SQL Server. OpenLink also offers drivers for SQL Server that use the FreeTDS implementation of TDS. Detailed instructions for building and configuring this driver are available at https://www.freetds.org/userguide/perl.htm.

For detailed steps on installing the unixODBC or FreeTDS drivers, see Appendix D: "Installing Common Drivers and Applications." Even though the discussion is related to installing FreeTDS in a Windows Services for UNIX environment, it applies equally to UNIX as well.
Create a SQL Server data source.

To function, ODBC needs a data source to connect to the database. The Data Source Name (DSN) is generally defined in an odbc.ini file that is used by the driver manager. The DSN is used by the driver manager to load the ODBC driver.

iODBC offers a graphical user interface to set up the DSN. Complete instructions are available from https://www.iodbc.org/index.php?page=docs/odbcstory.

The DSN can also be configured by manually modifying the odbc.ini file. The following example file uses an Oracle DSN.
```
[ODBC Data Sources]
```

ORA_HR_DB=Sample Oracle8 dsn [ORA_HR_DB] Driver=/opt/odbc/lib/ivor8x01.so Description=Oracle8 ServerName=uxdbp1 LogonID=daveb Password=cougar

This configuration file can be modified to use the SQL Server DSN, as shown in the following example. Note that the \[ORA\_HR\_DB\] section is replaced with \[SS\_HR\_DB\]. Though most of the keys remain, the values are modified to allow for the SQL Server data source.

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">[ODBC Data Sources]

SS_HR_DB=Sample MS SQLServer [SS_HR_DB] Driver=/usr/local/freetds/lib/libtdsodbc.so Description=SQL Server 2000 Database=hrapp UID=daveb PWD=cougar Address=win2kdbp1,1433

**Note** The **isql** command line utility can be used to check the validity of the DSN entry when using unixODBC. This allows you to ensure that the entries in the odbc.ini are correct. The syntax for use is:  

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">isql -v DSN username password</pre>

Test the connectivity.

iODBC contains a utility named odbctest which can be used to test the DSN entries and interact with the database by connecting and issuing queries directly without any code. The syntax for performing this test is:
```
odbctest "DSN=SS_HR_DB;UID=daveb;PWD=cougar"
```
Change the existing application to use SQL Server as data source.

The following sample source Perl program from an Oracle environment contains functions to connect, prepare, execute, bind, and disconnect. Compare these functions with the Perl code in the following example. Minor adjustments are made to allow for the data source change.
```
#Load the DBI module
```

use DBI; my $connectstring_oracle = 'ora_hr_db'; my $username = 'daveb'; my $password = 'cougar'; my $tablename = 'Employee_Info'; #Connecting to Oracle DB using ODBC my $dbh = DBI->connect("dbi:ODBC:$connectstring_oracle", "$username", "$password"); #Preparing the SQL statement $sth = $dbh->prepare("insert into $tablename (last_name) values (?)"); #Binding the value to the parameter at run time. $sth->bind_param( 1,"LName); #Executing the query $sth->execute() or warn $sth->errstr(); # check for error $sth->finish(); #Disconnect from Oracle database $dbh->disconnect or warn "Can't disconnect from the Oracle $connectstring_oracle database: $DBI::errstr\n";

The same functions are modified to use a SQL Server data source in the following example:

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">my $connectstring_oracle = 'ora_hr_db';

my $username = 'daveb'; my $password = 'cougar'; my $tablename = 'Employee_Info'; #Connecting to Oracle DB using ODBC my $dbh = DBI->connect("dbi:ODBC:$connectstring_oracle", "$username", "$password") to my $connectstring_ss = 'ss_hr_db'; my $username = 'daveb'; my $password = 'cougar'; my $tablename = 'Employee_Info'; #Connecting to SQL Server DB using ODBC my $dbh = DBI->connect("dbi:ODBC:$connectstring_ss", "$username", "$password")

Change all embedded SQL statements to T-SQL.

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

Case 2: Interoperating an Oracle DBD Application

If the original application was written using the Oracle DBD, there are two possible options that can be evaluated. The recommended option is to replace the Oracle DBD with a Sybase DBD. The second option is to migrate to an ODBC framework similar to the one seen in "Case 1: Interoperating an ODBC DBD Application." Sybase DBD offers better performance than ODBC.

Migrate to SQL Server Using Sybase DBD

Because SQL Server was originally based on the Sybase data structure, it is possible to use the Sybase DBD to connect to SQL Server using a SQL Server driver.

To use the Sybase DBD to connect to SQL Server using a SQL Server driver, follow these steps:

Install SQL Server library support.

The FreeTDS driver is needed for this method. Instructions for installing and configuring the driver are located in Appendix D, "Installing Common Drivers and Applications." Even though the discussion is related to installing FreeTDS in a Windows Services for UNIX environment, it applies equally to UNIX as well.

Because the driver is not being used with ODBC, the -disable-odbc switch can be used with the configure command while installing FreeTDS. Details on the use of this command are available at https://www.freetds.org/userguide/config.htm.
Configure the data source.

After FreeTDS is installed, the freetds.conf file should be modified to include the SQL Server database information. A sample entry is shown in the following example:
```
[SS_HR_DB]
```

host = win2kdbp1 # or IP address port = 1433 tds version = 8.0

Install the Sybase DBD module.

For more information on installing this Perl module, refer to https://search.cpan.org/~mewp/DBD-Sybase/Sybase.pm.

In addition, the SYBASE environment variable should be set to the location of the FreeTDS installation. If using bash or ksh, the following commands can be used:
```
export SYBASE=/usr/local/freetds
```
Modify the application's source code.

Minor changes will need to be made to the source code to allow for the data source change. The following sample code shows some different implementation options of the Perl language that may appear in your existing source code. Compare these to the second code sample that has been modified to use the Sybase DBD.
```
#  load the DBI module
```

use DBI;

Connect to the Oracle Database

my $dbh = DBI->connect("dbi:Oracle: ora_hr_db ", 'userName', 'password', { RaiseError => 1, AutoCommit => 0 } ) || die "Database connection not made: $DBI::errstr"; #Prepare a SQL statement my $sql = qq{ SELECT id, name, title, phone FROM employees }; my $sth = $dbh->prepare( $sql ); #Execute the statement $sth->execute(); my( $id, $name, $title, $phone );

Bind the results to the local variables

$sth->bind_columns( undef, $id, $name, $title, $phone ); #Retrieve values from the result set while( $sth->fetch() ) { print "$name, $title, $phone\n"; } #Close the connection $sth->finish(); $dbh->disconnect();

Change the connection to the Sybase DBI and SQL Server DSN.

The script in Step 4 has been rewritten to use DBD::Sybase. Note that the changes have been made in the header for the DBD and in the connection string for SQL Server. The rest of the Perl code is untouched by this change. This modified code is shown in the following example.
```
#  load the DBI module
```

use DBI; use DBD::Sybase; BEGIN { $ENV{SYBASE} = "/usr/local"; }

Connect to the SQL Server Database

my $dbh = DBI->connect( “dbi:Sybase:server=ss_hr_db;database=hrapp”, 'userName', 'password', { RaiseError => 1, AutoCommit => 0 } ) || die "Database connection not made: $DBI::errstr"; #Prepare a SQL statement my $sql = qq{ SELECT id, name, title, phone FROM employees }; my $sth = $dbh->prepare( $sql ); #Execute the statement $sth->execute(); my( $id, $name, $title, $phone );

Bind the results to the local variables

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.

Migrate to SQL Server Using ODBC

To migrate an Oracle DBD connection to ODBC, follow these steps:

Install the ODBC driver.

FreeTDS should be installed. For more information see Appendix D, "Installing Common Drivers and Applications." Even though the discussion is related to installing FreeTDS in a Windows Services for UNIX environment, it applies equally to UNIX as well.
Configure the DSN settings.

The steps for creating an ODBC DSN have been shown in "Case 1: Interoperating an ODBC DBD Application."
Install the ODBC DBD module.

Installation instructions are available at https://search.cpan.org/dist/perl/INSTALL and https://www.easysoft.com/products/2002/perl_dbi_dbd_odbc.phtml#3_0

In addition, the following environment variables need to be set:
- ODBCHOME. The directory where ODBC driver manager is installed.
- DBI_DSN. The dbi data source.
- DBI_USER. The username to connect to the database.
- DBI_PASS. The password for the database username.
Modify the source code.

The original script written for use with DBD::Oracle has to be changed to connect to SQL Server through the ODBC DSN. Changes are limited to the connect() method as shown in the following code.
```
#  load the DBI module
```

use DBI;

Connect to the Oracle Database

my $dbh = DBI->connect("dbi:ODBC:ss_hr_db", 'userName', 'password', { RaiseError => 1, AutoCommit => 0 } ) || die "Database connection not made: $DBI::errstr"; #Prepare a SQL statement my $sql = qq{ SELECT id, name, title, phone FROM employees }; my $sth = $dbh->prepare( $sql ); #Execute the statement $sth->execute(); my( $id, $name, $title, $phone );

Bind the results to the local variables

**Note** ODBC connections can also be made without using DSN sources. The difference from a DSN is that the required configuration information is embedded in the Perl code instead of residing in a separate initialization file. The following example shows a non-DSN connection:

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">my $dsn = 'DBI:ODBC:Driver={SQL Server}';

my $host = 'hostname'; my $database = 'dbname'; my $user = 'username'; my $auth = 'password'; my($dbh) = DBI->connect("$dsn;$host;$database",$user,$auth, { RaiseError => 1, AutoCommit => 1});

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.

Scenario 2: Porting the Perl Application to Windows

The migration of Perl applications in a UNIX environment to Windows and moving the backend to a SQL Server database is not much more effort than that needed under the interoperation scenario. It is possible to port the Perl application to both the .NET and the Win32 environment. The port to .NET is made possible by the availability of the utility PerlNET, which can wrap Perl programs into a .NET component. However, the application has to be ported from the UNIX environment to Windows environment before the conversion to .NET can be performed. Details about PerlNET are available at https://aspn.activestate.com/ASPN/docs/PDK/6.0/PerlNET.html#perlnet_top.

There are a few additional steps that should be followed to successfully move the Perl application to Win32. These steps are discussed in the following two cases.

Case 1: Porting a Perl Application using ODBC DBD

To port the Perl application to the Windows environment, follow these steps:

Install Perl in the target Windows environment.

ActiveState Perl is available from https://www.activestate.com. For more information on installing ActivePerl, see Appendix D, "Installing Common Drivers and Applications."
Install DBI and DBD::ODBC.

The DBI and DBD::ODBC modules can be installed using Perl Package Manager (PPM), which is installed with ActivePerl. PPM is an interactive command line utility which can be launched from the command line by typing ppm.

Download the DBI and DBD::ODBC files to a local directory—for example, c:\perlppd. These files can be downloaded from https://ppm.activestate.com/PPMPackages/zips/.

The modules can be installed by typing the following commands:
```
PPM> rep add new c:\perlppdPPM> install DBIPPM> install DBD-ODBCPPM> quit
```
Install the ODBC driver.

There are several available options for acquiring and installing an ODBC driver for SQL Server. These options include:
- An ODBC driver for SQL Server is included as part of the SQL Server distribution CD and is installed as part of the SQL Server client install. Refer to https://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_install_029l.asp for information on installing the SQL Server client.
- OpenLink software offers an ODBC solution that eliminates the need to install SQL Server client software on the server on which the Perl application will run. Details on this driver are available at https://uda.openlinksw.com/odbc/st/odbc-sqlserver-st/.
- DataDirect offers Connect for ODBC drivers that also provide clientless access to SQL Server. Details are available at https://www.datadirect.com/products/odbc/index.ssp.
Configure the ODBC data source.

An ODBC data source can be configured by modifying odbc.ini as shown in the "Case 1: Interoperating an ODBC DBD Application" section of Scenario 1. The data source can also be configured using the ODBC Data Source Administrator utility that is bundled with Windows. This utility can be accessed by accessing from the Control Panel by choosing Administrative Tools, then Data Sources (ODBC). Some database vendors also provide utilities to configure DSNs.

Figure 12.3 Configuring the ODBC System DSN

Note In Windows, DSNs should be created as a System DSN and not as a User DSN because of permissions restrictions associated with the latter.

Further steps for creating a data source are available at https://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_6_odbcht_1oqc.asp.
Change the application to use SQL Server as the data source.

The move to Windows does not add any overhead in terms of modifications to the application. The changes are driven by the change in DSN and are similar to changes discussed in the "Case 1: Interoperating an ODBC DBD Application" section of Scenario 1.
Change all embedded SQL statements to T-SQL.

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

Case 2: Porting a Perl Application Using Oracle DBD

When migrating from a UNIX platform to a Windows platform, there are two options to replace the DBD::Oracle module. One option is to use the DBD::ODBC module. The other is to use ActiveX Data Objects (ADO) through the DBD::ADO module.

Using DBD::ODBC is recommended in most instances and is discussed in greater detail in the "Case 1: Interoperating an ODBC DBD Application" section of Scenario 1. DBD::ADO is a DBI driver that acts as an interface to other lower-level database drivers within Windows. DBD::ADO requires the DBD::ODBC module to function. Using DBD::ADO will add an additional connectivity layer to the solution and should only be implemented if the application needs to take advantage of the OLE DB APIs. This driver can then be used to connect to and access data from any ADO data source.

Migrate to DBD::ADO

To migrate to DBD::ADO, follow these steps:

Install ActivePerl.

ActivePerl is a Windows-based Perl engine available from https://www.activestate.com. For detailed installation instructions, see Appendix D, "Installing Common Drivers and Applications."
Install DBI.

The DBI module can be installed using Perl Package Manager (PPM), which is installed with ActivePerl.
Install DBD::ODBC using PPM.
Install the ODBC driver.
Configure the ODBC data source.
Install ADO.

The DBD::ADO module requires Microsoft ADO version 2.1 or later to work reliably. ADO drivers are available from Microsoft as part of the Microsoft Data Access Component (MDAC) available at https://msdn.microsoft.com/data/technologyinfo/mdac.

Download the DBD::ADO module files to a local directory—for example, c:\perlppd. The ADO module can be downloaded from https://ppm.activestate.com/PPMPackages/zips/.

This module can be installed from Perl Package Manager (PPM).

Type ppm at the command line to start the package manager, and then type the following commands:
```
PPM. Rep add new c:\perlppdPPM> install DBIPPM> install DBD-ADOPPM> quit
```
Modify the connection string to use ADO. No other changes should need to be made to the source code. A sample ADO connection string is shown in the following example:
```
use DBI;
```

my $dsn ="Provider=sqloledb;Trusted_Connection=Yes;Server= win2kdbp1;Database=hrapps"; $dbh = DBI->connect("dbi:ADO:$dsn", $user, $passwd);

Calling stored procedures is supported by DBD::ADO using the ODBC style call procedure\_name(). An example for calling a procedure is

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">my $sth = $dbh-&gt;prepare("{call procedure name (?)}");</pre>


Parameters can be either input or output parameters. Parameters are bound as seen in earlier examples.

Change all embedded SQL statements to T-SQL.

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

Chapter 12 - Developing: Applications - Migrating Perl

On This Page

Introduction and Goals

Introduction to the Perl DBI Architecture

Scenario 1: Interoperation of Perl on UNIX with SQL Server

Case 1: Interoperating an ODBC DBD Application

Case 2: Interoperating an Oracle DBD Application

Migrate to SQL Server Using Sybase DBD

Connect to the Oracle Database

Bind the results to the local variables

Connect to the SQL Server Database

Bind the results to the local variables

Migrate to SQL Server Using ODBC

Connect to the Oracle Database

Bind the results to the local variables

Scenario 2: Porting the Perl Application to Windows

Case 1: Porting a Perl Application using ODBC DBD

Case 2: Porting a Perl Application Using Oracle DBD

Migrate to DBD::ADO

Additional resources