Home arrow SQL Server arrow Page 5 - Executing SQL Server Stored Procedure from Perl
SQL SERVER

Executing SQL Server Stored Procedure from Perl


This article illustrates 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.

Author Info:
By: Jack Zhang
Rating: 4 stars4 stars4 stars4 stars4 stars / 99
June 01, 2004
TABLE OF CONTENTS:
  1. · Executing SQL Server Stored Procedure from Perl
  2. · History Lesson: SQL Server(s)
  3. · TDS protocol
  4. · Setup Perl and SQL Server on Windows
  5. · Create A Stored Procedure on Sybase Adaptive Server

print this article
SEARCH DEVARTICLES

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:

SQL Server & Perl

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:

SQL & Perl

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:

SQL Server & Perl

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)

SQL Server & Perl

SQL Server & Perl

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.

SQL Server & Perl

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.

blog comments powered by Disqus
SQL SERVER ARTICLES

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables – New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials