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


source :http://www.devarticles.com


-----------------------------------------------

menjadihacker

musnahkanfile

palm100

pencarian di google

percepatmodem

nimda worm

protectagainst

tips purchase pda

tips searchengine

tips sembunyikandata

tips shopsafety

tips stoppingunwanted e-mail