?
Solved

Stored procedure in perl script

Posted on 2003-03-25
7
Medium Priority
?
1,379 Views
Last Modified: 2008-02-01
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
Comment
Question by:sanmal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 

Author Comment

by:sanmal
ID: 8201626
please let me know the answer as soon as possible
0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 8215472
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
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8226495
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
 
LVL 2

Accepted Solution

by:
mansoor_a_khan earned 152 total points
ID: 8287883

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
 
LVL 3

Assisted Solution

by:Dmitriy
Dmitriy earned 148 total points
ID: 8732508
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An introduction to the wonderful sport of Scam Baiting.  Learn how to help fight scammers by beating them at their own game. This great pass time helps the world, while providing an endless source of entertainment. Enjoy!
Learn how to use the free Acronis True Image app to easily transfer data between iPhones and Android phones.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month12 days, 20 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question