Executing SQL Server Stored Procedure from Perl
Perl language is the most useful administration language on the Unix/Linux environment. Since Web technologies have risen in popularity, Perl also became a main development language for Internet-based development. Perl programming, such as CGI, Fast-CGI, mod_perl, Apache::ASP (very similar to Microsoft’s ASP 3.0), and Embedded-perl are used to create dynamic web pages and have become very successful web solutions.Today’s website cannot leave out database support. Combining Perl and SQL Server (Sybase or Microsoft) is a natural way for those companies who want to migrate from traditional client-server architectures to Internet-based architectures for expanding their business. New companies also benefit from low ownership and cost by choosing a Perl and SQL Server solution.
In this article, I will show you how to setup a development environment using Perl and SQL Server on the following platforms: Microsoft Windows, Red Hat Linux, and Sun Solaris. Step by step, you can learn, and may choose, the best way that fits into your own requirements.
SQL Server was a product that originated from Sybase Inc., and became a popular DBMS product on the market from the end of 1980s. In the early days Sybase SQL Server was running on the UNIX platform while PCs still used MS DOS (single task OS) – an operating system which was not suitable to develop and run a DBMS.
In 1987, Sybase and Microsoft signed a contract on joint development to port SQL Server to OS/2 (Multi-task OS) which became the replacement for MS DOS (OS/2 owned by both IBM and Microsoft). The OS/2 project did not continue for long, and Microsoft decided to develop its own multi-task operating system (Windows) and stop its contract with IBM. In 1990, with the successful release of Windows 3.0, Microsoft also released its Windows version of SQL Server – Microsoft SQL Server.
Milestones for MS SQL Server:
- MS SQL Server 4.2 (16-bits, Jan. 1992)
- MS SQL Server for Windows NT (32-bits, July, 1993)
- MS SQL Server 6.0 (June, 1995)
- MS SQL Server 6.5 (April, 1996)
- MS SQL Sever 7.0 (Nov. 1998)
- MS SQL Server 2000 (Aug. 2000)
At the same time, Sybase dominated the Unix DBMS market. From Sybase SQL Server 4.2 (released around 1989, introduced unions), Version 4.8 (1990, introduced multiprocessor architecture), Version 4.9 (1992, more stable product, support SMP) Version 10 (1993, introduced server-side cursor, auditing and backup server), Version 11, Sybase Adaptive Server 12, until today: Sybase Adaptive Server 12.5.
Although in 1994, Microsoft and Sybase announced the end of the contract on joint SQL Server Development, MS SQL Server 6.5 and Sybase SQL Server 10 and all their previous versions used the same core code and were easily able to access each other.
MS SQL Server 7.0 changed completely from its previous version and was no longer compatible with Sybase SQL Server. But, with the release of Service Pack 2 for SQL Server 7.0 (Microsoft Knowledge Base Article – 239883 “Fix: SYBASE CT-Library Clients Cannot Connect to Microsoft SQL Server 7.0”) we had one last chance to access MS SQL Server 7.0 from the Sybase Client (Sybase SQL Server was more compatible with MS SQL Server, as you could successfully access Sybase SQL Server from the MS SQL Server 2000 client, iSQL).
Executing SQL Server Stored Procedure from Perl - TDS protocol
SQL Server uses a typical Client-Server architecture. The SQL Server client is a set of C APIs plus management tools such as iSQL query tool and SQL Server Enterprise Manager. SQL Server Server-Side component is usually a system service, listening to specific TCP ports (Microsoft use 1433, Sybase use 4100 or 5000), and is ready to accept the signal from an SQL Server client at anytime.
The protocol used between an SQL Server client and server is traditionally called the TDS protocol (Tabular Data Stream). Microsoft SQL Server 4.2, 6.0, and 6.5 use the same TDS protocol as Sybase SQL Server 4.2 and 10, which is known as TDS4.2.
From Version 11, Sybase stopped further development of the old set of APIs (we call DB-Library) and started a new set of APIs, known as CT-library. They also redesigned the TDS protocol, which is now known, industry-wide, as TDS 5.0. At the same time, Microsoft chose to stick to DB-Library but enhanced the TDS protocol, which we call TDS 7.0. But, do not think that TDS 7.0 is more advanced than TDS 5.0 or compatible with TDS 5.0. In fact, TDS 7.0 belongs solely to Microsoft SQL Server (we’re better off to call it Microsoft TDS) and TDS 5.0 belongs specifically to Sybase (Sybase TDS).
So, we easily understand that the latest version of Microsoft SQL Server (2000) supports TDS 7.0 and TDS 4.2 (backwards compatibility) and Sybase SQL Server’s latest version 12.5 supports TDS 5.0 and TDS 4.2 (backwards compatibility).
Executing SQL Server Stored Procedure from Perl - Setup Perl and SQL Server on Windows
Perl’s running environment is integrated within the *nix system. For Microsoft Windows operating systems, however, you need to download and install it by yourself.
There are a few Windows-based distributions of Perl. ActivePerl from ActiveState.com may be the most popular one. You can download it from:
http://www.activestate.com/Products/ActivePerl/
Choose the latest version of ActivePerl (5.8) (most recent Unix/Linux also uses Perl 5.8).
Installing ActivePerl on Windows is fairly easy. After installation you can use the following command to verify that perl is working:
From command prompt, input:
Perl –v
It will show you something like:
This is perl, v5.8.0 built for MSWin32-x86-multi-thread
Copyright 1987-2002, Larry Wall
Download Sybase Adaptive Server 12.5 Developer Edition from the Sybase website for free: www.sybase.com/ase_125devel
Installing and configuring Sybase Adaptive Server is also as easy as MS SQL Server.
If you want to use MS SQL Server as a database, install MS SQL Server 7.0 with service pack 2.
Perl uses DBI/DBD architecture to access databases (DBI means Database Interface, DBD means Database Driver). With a specific DBD driver installed, Perl can use the same DBI API to support different database systems.
The DBI module is already installed with the ActivePerl distribution itself. In order to support SQL Server, we need to download the SQL Server DBD driver from cpan.org: www.cpan.org, search for the DBD-Sybase module, and download the latest version 1.01 source code: DBD-Sybase-1.01.tar.gz
Note: there are a few other DBD Drivers that support both Sybase and Microsoft SQL Server on Windows, such as DBD::ADO; however, they can only be used on a Windows platform.
In order to compile and install the DBD::Sybase source code, we need a C compiler installed on Windows (I use Microsoft Visual C++ 6.0), and make sure nmake.exe is available in the PATH.
Use a decompression utility (ie: Winzip) to extract DBD-Sybase-1.0.1.tar.gz to any available drive such as C:. You should now have the folder c:DBD-Sybase-1.01 that holds all the source code.
Change directory to the above folder, and issue the following command in the command prompt window:
Perl Makefile.PL
You may receive a warning message, such as missing the following library: libtcl.lib, libcomn.lib and libintl.lib.
Because we are using the Sybase CT-Library, libcs.lib and libct.lib are mandatory; others are optional, and we can ignore this warning. Sybase removes these static libraries from its version 12.5 of the Adaptive Server package. The run time libraries still do exist (ie: libtcl.dll, libcomn.dll, linintl.dll)
If you receive an error message other than the one above, please check the following environment variable: SYBASE and SYBASE_OCS
%SYBASE% should equal the directory root where Sybase Adaptive Server is installed (ie: C:sybase) and %SYBASE_OCS% should equal OCS-12_5 for Sybase Adaptive Server 12.5
Issue the following command to compile the source code:
Nmake
Then install the package by issuing this command:
Nmake install
Using the following code, we can list all the DBD drivers we’ve installed with Perl:
#!/usr/bin/perl
use strict;
use DBI;
my @drivers=DBI->available_drivers();
print join(”n”,@drivers);
print “n”;
Copy and paste the above code in notepad, and save the file as dbi_drivers.pl.
Issue the following command in the command prompt window:
perl dbi_drivers.pl
Executing SQL Server Stored Procedure from Perl - Create A Stored Procedure on Sybase Adaptive Server
Create the following Stored Procedure on Sybase Adaptive Server (using the pubs2 database):
CREATE PROCEDURE dbo.sp_GetBooksByPrice
@minPrice money,
@maxPrice money,
@lowestPricedBook varchar(100) OUTPUT,
@highestPricedBook varchar(100) OUTPUTAS
BEGIN
DECLARE @realminPrice money, @realmaxPrice money, @totalBooks int
SELECT * FROM titles WHERE price >=@minPrice AND price <=@maxPrice
SELECT @realminPrice = min(price) FROM titles WHERE price >=@minPrice
SELECT @realmaxPrice = max(price) FROM titles WHERE price <=@maxPrice
SELECT @lowestPricedBook =title FROM titles WHERE price = @realminPrice
SELECT @highestPricedBook =title FROM titles WHERE price = @realmaxPrice
SELECT @totalBooks = COUNT(title) FROM titles WHERE price >= @minPrice AND price <= @maxPrice
RETURN @totalBooks
END
The above Stored Procedure is a very typical one; it takes 2 input parameters and 2 output parameters. Running it, we will get one resultset, one return value and 2 output parameters values. Save the above SQL code as sp.sql. We will use it later on our Unix/Linux platform.
Open the PerlIDE application and input the following Perl code:
#!/usr/bin/perl
use strict;
use DBI;
my $server = “ibmxp”;
my $db = “pubs2″;
my $username = “sa”;
my $password = “”;my $dbh = DBI->connect(”dbi:Sybase:$server”, $username,$password);
$dbh->do(”use $db”);
my $query = “declare @minPriceBook varchar(100), @maxPriceBook varchar(100)
exec sp_GetBooksByPrice @minPrice =2.00 , @maxPrice = 20.00, @lowestPricedBook = @minPriceBook OUTPUT, @highestPricedBook = @maxPriceBook OUTPUT”;
my $sth = $dbh->prepare($query);
$sth->execute();
do {
while(my $d = $sth->fetchrow_arrayref) {
if ($sth->{syb_result_type}==4040){
print join(”t”, @$d),”n”;
}
if ($sth->{syb_result_type}==4042){
print “The lowest price book is: “, $d->[0], “n”;
print “The highest price book is: “, $d->[1], “n”;
}
if ($sth->{syb_result_type}==4043){
print “There are total: “, $d->[0], ” books returnedn”;
}
}
} while($sth->{syb_more_results});
$sth=undef;
$dbh->disconnect;Save the Perl code as sybase_sp.pl and from the command prompt window, issue the following command:
Perl sybase_sp.pl
We get the same result!
Please notice that the constant value used to identify $sth->{syb_result_type} comes from the Sybase CT-Library
API header file cspublic.h:#define CS_ROW_RESULT (CS_INT)4040
#define CS_CURSOR_RESULT (CS_INT)4041
#define CS_PARAM_RESULT (CS_INT)4042
#define CS_STATUS_RESULT (CS_INT)4043
#define CS_MSG_RESULT (CS_INT)4044
#define CS_COMPUTE_RESULT (CS_INT)4045We can use the same Perl code to execute the stored procedure on MS SQL Server 7.0 with service pack 2.
Open the SQL Server Query Analyzer, select the pubs database and run the above Stored Procedure code. You can check the Stored Procedure from SQL Server Enterprise Manager;
I use the Sybase dsedit utility to edit the Sybase interface file, and add my Microsoft SQL Server entry into the interface file. My machine name where MS SQL Server is installed is home2k; we can ping this server from the dsedit menu button, and make sure it is connected. See the following screenshots:
(You can also manually edit %SYBASE%inisql.ini. On Unix/Linux Sybase uses the file called interface in the $SYBASE root directory, same functionality as sql.ini)
Modify our Perl code as follows:
my $server = “home2k”;
my $db = “pubs”;Other parts of the code remain unchanged. Run the code again, this time, we get the stored procedure running on MS SQL Server, and get the same result.
Save the above Perl code as mssql_sp.pl. We will use it on Unix/Linux platform later on.
See the source.
RSS/XML