Stored procedure in perl script

I want to write and call stored procedure in perl script and also whatever data has been extracted using that SP, I want to have an output in the form of excel sheet.
sanmalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sanmalAuthor Commented:
please let me know the answer as soon as possible
0
amitpagarwalCommented:
i would suggest the following approach:

1. create a temp table in the perl script

2. call the sp using "exec spname" - in the sp try to fill the temp table with the results

3. now extract data from the temp table and do anything with it
0
HamdyHassanCommented:
To call storeproc from perl, you need system();

system("isql -Saaa -Ufff -Pkkk -i myscript.sql");



and you should put the following at myscript.sql
use database_name
go
exec storeproc_name
go
0
mansoor_a_khanCommented:

Here is the solution.

Cheers
Mak

use Sybase::DBlib;
my $dbh = Sybase::DBlib->dblogin($user, $pass , $server);
die "Login failed!\n" if($DB_ERROR);

$dbh->dbuse($dbname);
die "Cannot change to $dbname.\n" if($DB_ERROR);

$dbh->dbcmd($sql); #SQL Can be anything in case of proc use exec procname
$dbh->dbsqlexec;
die "Cannot execute $sql.\n" if($DB_ERROR);

$dbh->dbresults;
my $status = $dbh->DBROWS;
my $cols = $dbh->dbnumcols ;

die "Cannot retrieve result set.\n" if($DB_ERROR);
print "No Data for the query .\n" if ($status != SUCCEED);

#Print column names
for (my $i = 1; $i <= $cols; $i++) {
    my $colname = $dbh->dbcolname($i);
    print RESULT "$colname,";
}
print RESULT "\n";
# print Resultset
my $row = 0;
while (my @db = $dbh->dbnextrow) {
    print RESULT "\t\t\t<tr>";
    for (my $i = 0; $i< $cols; $i++) {
        print RESULT "$db[$i],";
    }
    $row = $row + 1;
    print RESULT "\n";
}
print RESULT "Total Rows $row\n";

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DmitriyCommented:
mansoor_a_khan,

there is a slight error in your code.  You didn't declare RESULT as file output.  Thus the result goes into oblivion.


this can be done as follows:

open (RESULT, ">file_name_here")


the file must already exist in this case.  Or if another solution is preferred, you can make the file in a separate command and then do the "open" statement.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.