Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1423
  • Last Modified:

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.
0
sanmal
Asked:
sanmal
2 Solutions
 
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
 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now