Executing SQL Server Stored Procedure from Perl - Create A Stored Procedure on Sybase Adaptive Server

(Page 5 of 5 )

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) OUTPUT 

AS
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  

Tip: using the Sybase Central “Add Procedure (Template)”
is the easiest way to create the above procedure. When you
save the procedure, it will compile the procedure and tell
you if any syntax errors exist. See following screenshot:

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;

Run the above code in the PerlIDE windows and we get the following screenshot:

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)4045

We 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; see following screenshot:

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.

The above screenshot is the tracing result from MS SQL Profiler on the server side.

Save the above Perl code as mssql_sp.pl. We will use it on Unix/Linux platform later on.

Limitation on Windows: Because of incompatibilities between Sybase, MS SQL Server 2000 cannot be used as a development component in the above scenario. In next part of this article, we’ll move on to Red Hat Linux 9.0. We will see with the help from FreeTDS (only available for Unix/Linux) that we have no barrier to access MS SQL Server 2000 from our Perl code.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: Executing SQL Server Stored Procedure from Perl

>>> Be the FIRST to comment on this article!