Use sample programs to view the basic usage of stored procedure and retrieve rows from the pubs2 authors table.
Use the following sample program to view the basic usage of stored procedures in Perl. This program connects to a server, creates two stored procedures, calls prepare, binds, or executes the procedures, prints the results to STDOUT, disconnects, and exits the program.
use strict;
use DBI qw(:sql_types);
use DBD::SybaseASE;
require_version DBI 1.51;
my $uid = "sa";
my $pwd = "";
my $srv = $ENV{"DSQUERY"} || die 'DSQUERY appears not set';
my $dbase = "tempdb";
my $dbh;
my $sth;
my $rc;
my $col1;
my $col2;
my $col3;
my $col4;
# Connect to the target server.
#
$dbh = DBI->connect("dbi:SybaseASE:server=$srv;database=$dbase",
$uid, $pwd, {PrintError => 1});
# One way to exit if things fail.
#
if(!$dbh) {
warn "Connection failed, check if your credentials are set correctly?\n";
exit(0);
}
# Ignore errors on scale for numeric. There is one marked call below
# that will trigger a scale error in ASE. Current settings suppress
# this.
#
$dbh->do("set arithabort off")
|| die "ASE response not as expected";
# Drop the stored procedures in case they linger in ASE.
#
$dbh->do("if object_id('my_test_proc') != NULL drop proc my_test_proc")
|| die "Error processing dropping of an object";
$dbh->do("if object_id('my_test_proc_2') != NULL drop proc my_test_proc_2")
|| die "Error processing dropping of an object";
# Create a stored procedure on the fly for this example. This one
# takes input args and echo's them back.
#
$dbh->do(qq{
create proc my_test_proc \@col_one varchar(25), \@col_two int,
\@col_three numeric(5,2), \@col_four date
as
select \@col_one, \@col_two, \@col_three, \@col_four
}) || die "Could not create proc";
# Create another stored procedure on the fly for this example.
# This one takes dumps the pubs2..authors table. Note that the
# format used for printing is defined such that only four columns
#appear in the output list.
#
$dbh->do(qq{
create proc my_test_proc_2
as
select * from pubs2..authors
}) || die "Could not create proc_2";
# Call a prepare stmt on the first proc.
#
$sth = $dbh->prepare("exec my_test_proc \@col_one = ?, \@col_two = ?,
\@col_three = ?, \@col_four = ?")
|| die "Prepare exec my_test_proc failed";
# Bind values to the columns. If SQL type is not given the default
# is SQL_CHAR. Param 3 gives scale errors if arithabort is disabled.
#
$sth->bind_param(1, "a_string");
$sth->bind_param(2, 2, SQL_INTEGER);
$sth->bind_param(3, 1.5411111, SQL_DECIMAL);
$sth->bind_param(4, "jan 12 2012", SQL_DATETIME);
# Execute the first proc.
#
$rc = $sth->execute || die "Could not execute my_test_proc";
# Print the bound args
#
dump_info($sth);
# Execute again, using different params.
#
$rc = $sth->execute("one_string", 25, 333.2, "jan 1 2012")
|| die "Could not execute my_test_proc";
dump_info($sth);
# Enable retrieving the proc status.
$sth->{syb_do_proc_status} = 1;
$rc = $sth->execute(undef, 0, 3.12345, "jan 2 2012")
|| die "Could not execute my_test_proc";
dump_info($sth);
$rc = $sth->execute("raisin", 1, 1.78, "jan 3 2012")
|| die "Could not execute my_test_proc";
dump_info($sth);
$rc = $sth->execute(undef, 0, 3.2233, "jan 4 2012")
|| die "Could not execute my_test_proc";
dump_info($sth);
$rc = $sth->execute(undef, 0, 3.2234, "jan 5 2012")
|| die "Could not execute my_test_proc";
dump_info($sth);
$rc = $sth->execute("raisin_2", 1, 3.2235, "jan 6 2012")
|| die "Could not execute my_test_proc";
dump_info($sth);
$rc = $sth->execute(undef, 0, 3.2236, "jan 7 2012")
|| die "Could not execute my_test_proc";
dump_info($sth);
# End of part one, generate blank line.
#
print "\n";
# Undef the handles (not really needed but...).
#
undef $sth;
undef $rc;
# Prepare the second stored proc.
#
$sth = $dbh->prepare("exec my_test_proc_2")
|| die "Prepare exec my_test_proc_2 failed";
# Execute and print
#
$rc = $sth->execute || die "Could not execute my_test_proc_2";
dump_info($sth);
#
# An example of a display/print function.
#
sub dump_info {
my $sth = shift;
my @display;
do {
while(@display = $sth->fetchrow) {
foreach (@display) {
$_ = '' unless defined $_;
}
$col1 = $display[0];
$col2 = $display[1];
$col3 = $display[2];
$col4 = $display[3];
# Proc status is suppressed, assume proc
# execution was always successful. Enable
# by changing the write statement.
#
#write;
write unless $col1 eq 0;
}
} while($sth->{syb_more_results});
}
#
# The FORMAT template for this example.
#
format STDOUT_TOP =
Column1 Column2 Column3 Column4
------ ------ ------ ------
.
# Treat all data as left-justified strings
#
format STDOUT =
@<<<<<<<<<<<< @<<<<<<<<<<<< @<<<<<<<<<<<<
@<<<<<<<<<<<<
$col1, $col2, $col3, $col4
.
# The End.....
#
$dbh->do("drop proc my_test_proc");
$dbh->do("drop proc my_test_proc_2");
$dbh->disconnect;
Use the following sample program to retrieve rows from the pubs2 authors table, insert them into tempdb, and append new rows for batch insert. The program then prints the updated authors table to STDOUT, disconnects, and exits.
use strict;
use DBI ();
use DBD::SybaseASE ();
require_version DBI 1.51;
# trace(n) where n ranges from 0 - 15.
# use 2 for sufficient detail.
#DBI->trace(2); # 0 - 15, use 2 for sufficient detail
# Login credentials, handles and other variables.
#
my $uid = "sa";
my $pwd = "";
my $srv = $ENV{"DSQUERY"} || die 'DSQUERY appears not set';
my $dbase = "tempdb";
my $temp_table = "$dbase..authors";
my $rows;
my $col1;
my $col2;
my $dbh;
my $sth;
my $rc;
# Connect to the target server:
#
$dbh = DBI->connect("dbi:SybaseASE:server=$srv;database=$dbase",
$uid, $pwd, {PrintError => 0, AutoCommit => 0})
|| die "Connect failed, did you set correct credentials?";
# Switch to the pubs2 database.
#
$rc = $dbh->do("use pubs2") || die "Could not change to pubs2";
# Retrieve 2 columns from pubs2..authors table.
#
$sth = $dbh->prepare(
"select au_lname, city from authors where state = 'CA'")
|| die "Prepare select on authors table failed";
$rc = $sth->execute
|| die "Execution of first select statement failed";
# We may have rows now, present them.
#
$rows = dump_info($sth);
print "\nTotal # rows: $rows\n\n";
# Switch back to tempdb, we take a copy of pubs2..authors
# and insert some rows and present these.
#
$rc = $dbh->do("use $dbase") || die "Could not change to $dbase";
# Drop the authors table in tempdb if present
#
$rc = $dbh->do("if object_id('$temp_table') != NULL drop table
$temp_table")
|| die "Could not drop $temp_table";
# No need to create a tempdb..authors table as the select into will
# do that.
$rc = $dbh->do("select * into $temp_table from pubs2..authors")
|| die "Could not select into table $temp_table";
# Example of a batch insert...
#
$sth = $dbh->prepare("
insert into $temp_table
(au_id, au_lname, au_fname, phone, address, city, state,
country, postalcode) values
('172-39-1177', 'Simpson', 'John', '408 496-7223',
'10936 Bigger Rd.', 'Menlo Park', 'CA', 'USA', '94025')
insert into $temp_table
(au_id, au_lname, au_fname, phone, address, city, state,
country, postalcode) values
('212-49-4921', 'Greener', 'Morgen', '510 986-7020',
'309 63rd St. #411', 'Oakland', 'CA', 'USA', '94618')
insert into $temp_table
(au_id, au_lname, au_fname, phone, address, city, state,
country, postalcode ) values
('238-95-4766', 'Karson', 'Chernobyl', '510 548-7723',
'589 Darwin Ln.', 'Berkeley', 'CA', 'USA', '94705')
insert into $temp_table
(au_id, au_lname, au_fname, phone, address, city, state,
country, postalcode ) values
('267-41-4394', 'OLeary', 'Mich', '408 286-2428',
'22 Cleveland Av. #14', 'San Jose', 'CA', 'USA', '95128')
insert into $temp_table
(au_id, au_lname, au_fname, phone, address, city, state,
country, postalcode ) values
('274-80-4396', 'Straight', 'Shooter', '510 834-2919',
'5420 College Av.', 'Oakland', 'CA', 'USA', '94609')
insert into $temp_table
(au_id, au_lname, au_fname, phone, address, city, state,
country, postalcode ) values
('345-22-1785', 'Smiths', 'Neanderthaler', '913 843-0462',
'15 Mississippi Dr.', 'Lawrence', 'KS', 'USA', '66044')
insert into $temp_table
(au_id, au_lname, au_fname, phone, address, city, state,
country, postalcode ) values
('405-56-7012', 'Bennetson', 'Abra', '510 658-9932',
'6223 Bateman St.', 'Berkeley', 'CA', 'USA', '94705')
insert into $temp_table
(au_id, au_lname, au_fname, phone, address, city, state,
country, postalcode ) values
('427-17-2567', 'Dullest', 'Annie', '620 836-7128',
'3410 Blonde St.', 'Palo Alto', 'CA', 'USA', '94301')
insert into $temp_table
(au_id, au_lname, au_fname, phone, address, city, state,
country, postalcode ) values
('527-72-3246', 'Greene', 'Mstar', '615 297-2723',
'22 Graybar House Rd.', 'Nashville', 'TN', 'USA', '37215')
insert into $temp_table
(au_id, au_lname, au_fname, phone, address, city, state,
country, postalcode ) values
('672-91-3249', 'Yapan', 'Okiko', '925 935-4228',
'3305 Silver Ct.', 'Walnut Creek', 'CA', 'USA', '94595')
");
$rc = $sth->execute || die "Could not insert row";
# Retrieve 2 columns from tempdb..authors table and present these
#
$sth = $dbh->prepare(
"select au_lname, city from $temp_table where state = 'CA'")
|| die "Prepare select on $temp_table table failed";
$rc = $sth->execute
|| die "Execution of second select statement failed";
# Output
#
$rows = dump_info($sth);
print "\nTotal # rows: $rows";
print "\n";
sub dump_info {
my $sth = shift;
my @display;
my $rows = 0;
while(@display = $sth->fetchrow) {
$rows++;
foreach (@display) {
$_ = '' unless defined $_;
}
$col1 = $display[0];
$col2 = $display[1];
write;
}
$rows;
}
# The FORMAT template for this example.
#
format STDOUT_TOP =
Lastname City
-------- -------
.
format STDOUT =
@<<<<<<<<<<<< @<<<<<<<<<<<<
$col1, $col2
.
$dbh->disconnect;
