[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I capture the db2 stored procedure values and put it directly to another table in Perl

Posted on 2011-05-06
8
Medium Priority
?
366 Views
Last Modified: 2012-05-11
I need to capture the stored procedure output in an array or hash and put it into another table.
The stored proc returns Name, Location and Address when an Id value is passed onto it and the results have to be inserted in another table INFO which has the same column names Name, Location and Address.

How can I do that? I am doing something like this..

my $id = 3;
my $sel_dbh1 = DBI->connect("$server", '', '', { 'RaiseError' => 1 }) or
       die "Can't connect to database: $DBI::errstr";

    my $sel_sql1 = "CALL EMPL_ID('$id')" ;

    $sel_sql1 =~ s/\$id/$id/;

    my $sel_sth1 = $sel_dbh1->prepare($sel_sql1);

    $sel_sth1->execute();
     while (my @row = $sel_sth1->fetchrow_array) {
         my $tr_sql = "insert into ID(Name, Location, Address) ($row[0], $row[1],$row[2])";
         my $tr_sth = $tr_dbh->do($tr_sql);
}

Is this correct?
0
Comment
Question by:sunny82
  • 4
  • 4
8 Comments
 

Author Comment

by:sunny82
ID: 35705896
In the above code, I am getting an uninitialized value or string error in the insert statement line.
0
 
LVL 27

Expert Comment

by:wilcoxon
ID: 35706099
I don't know much about DB2 so this may be barking up the wrong tree...

In Sybase (and, iirc, Oracle), in order to capture output from stored procedures, you have to explicitly use output parameters to the proc (or Oracle also supports user-defined functions which return a value but your syntax is wrong for that as well).

What does the definition "header" of the proc look like (eg "create proc EMPL_ID ...")?
0
 

Author Comment

by:sunny82
ID: 35706260
There are no output parameters. The definition  looks like create procedure EMPL_ID(IN IN_ID SMALLINT)

The stored proc returns values under column headings NAME, LOCATION, ADDRESS when Id is passed into it.


I can also write the all the rows returned from stored proc to an excel sheet by this
=======================================================
my $workbook  = Spreadsheet::WriteExcel::Big->new("Id_Info.xls");
my $worksheet = $workbook->addworksheet("Details");
my $format2 = $workbook->add_format();

my $col=0;
my $row=0;
foreach my $i(
  "NAME",
  "LOCATION",
  "ADDRESS",
)
 {
   $worksheet->write(0, $col++, $i, $format1);
  }
my $id = 3;
my $sel_dbh1 = DBI->connect("$server", '', '', { 'RaiseError' => 1 }) or
       die "Can't connect to database: $DBI::errstr";

    my $sel_sql1 = "CALL EMPL_ID('$id')" ;
    $sel_sql1 =~ s/\$id/$id/;
    my $sel_sth1 = $sel_dbh1->prepare($sel_sql1);
    $sel_sth1->execute();
    my $row = 1;
    while (my $a = $sel_sth1->fetchrow_hashref()) {
      $worksheet->write_string($row,0, (defined($a->{"NAME"})? $a->{"NAME"} : '[NULL]'),  $format2);
      $worksheet->write($row,1, (defined($a->{"LOCATION"})? $a->{"LOCATION"} : '[NULL]'),
      $worksheet->write($row,2, (defined($a->{"ADDRESS"})? $a->{"ADDRESS"} : '[NULL]'), $format2);
      $row++;
}
=================================

Its just that I do not want to write it to the excel sheet as it is creating an unnecessary overhead and want to directly put into a table as inserting it into a table is the ultimate goal. How can I do that?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 27

Accepted Solution

by:
wilcoxon earned 2000 total points
ID: 35706389
If the Excel code works, then this should work for inserting into a table...
my $id = 3;
my $sel_dbh1 = DBI->connect("$server", '', '', { 'RaiseError' => 1 }) or
    die "Can't connect to database: $DBI::errstr";

my $sel_sql1 = "CALL EMPL_ID('$id')";

my $sel_sth1 = $sel_dbh1->prepare($sel_sql1);

$sel_sth1->execute();
while (my $row = $sel_sth1->fetchrow_hashref()) {
    foreach my $key (keys %$row) {
        if (not defined $row->{$key}) {
            $row->{$key} = 'null';
        } else {
            $row->{$key} = $sel_dbh1->quote($row->{$key});
        }
    }
    my $tr_sql = "insert into ID (Name, Location, Address) values ($row->{NAME}, $row->{LOCATION}, $row->{ADDRESS})";
    $tr_dbh->do($tr_sql);
}

Open in new window

0
 
LVL 27

Expert Comment

by:wilcoxon
ID: 35706402
The original code had two main issues:
1) missing "values" in the insert statement (at least I assume DB2 follows SQL standards in that).
2) the insert sql was not quoting the values nor handling undef/null
0
 

Author Comment

by:sunny82
ID: 35707348
This is working just fine...except for the small change -- for insert statement, another database handler has to be defined. Now I am looping all the ids to be passed into the proc by selecting it from another table OLD_ID. In this way the stored proc can execute one id at a time and insert it into ID table This is what I am doing, just hope I am right....what do you think? or should I start the loop just before the procedure call after defining @Id array there?



 
my @id;
my $sel_dbh2 = DBI->connect("$server", '', '', { 'RaiseError' => 1 }) or
die "Can't connect to database: $DBI::errstr";

my $sel_sql2 = "select distinct ID from OLD_ID fc" ;
my $sel_sth2 = $sel_dbh2->prepare($sel_sql2);
$sel_sth2->execute();

while (my @row = $sel_sth2->fetchrow_array()) {

       push @id, @row;

    }
foreach my $id (@id) {

my $sel_dbh1 = DBI->connect("$server", '', '', { 'RaiseError' => 1 }) or
    die "Can't connect to database: $DBI::errstr";

my $sel_sql1 = "CALL EMPL_ID('$id')";

my $sel_sth1 = $sel_dbh1->prepare($sel_sql1);

$sel_sth1->execute();
while (my $row = $sel_sth1->fetchrow_hashref()) {
    foreach my $key (keys %$row) {
        if (not defined $row->{$key}) {
            $row->{$key} = 'null';
        } else {
            $row->{$key} = $sel_dbh1->quote($row->{$key});
        }
    }

       my $tr_dbh = DBI->connect("$server", '', '', { 'RaiseError' => 1 }) or
       die "Can't connect to database: $DBI::errstr";


    my $tr_sql = "insert into ID (Name, Location, Address) values ($row->{NAME}, $row->{LOCATION}, $row->{ADDRESS})";
    $tr_dbh->do($tr_sql);
}

Open in new window

0
 
LVL 27

Expert Comment

by:wilcoxon
ID: 35708449
Looks good except you should move lines 33-34 (setting up $tr_dbh) outside of the while loop.  Personally, I (almost) always group $dbh defines so I'd move 33-34 to line 18.

It should work fine being inside the loop but will slow things down considerably (it is likely to be opening a new database connection every time through the loop).
0
 

Author Comment

by:sunny82
ID: 35709753
sure ..thanks a ton for all your help. It was wonderful.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

I've just discovered very important differences between Windows an Unix formats in Perl,at least 5.xx.. MOST IMPORTANT: Use Unix file format while saving Your script. otherwise it will have ^M s or smth likely weird in the EOL, Then DO NOT use m…
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans
Suggested Courses

872 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