471,066 Members | 1,191 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 software developers and data experts.

DBI Problem

3
Hi all
I am working in Perl from last three months;but using DBI module first time.
While accessing Oracle DB through Perl I got a problem given below.....
when give SQL statement in mysql it is working well ,it is giving all info about that required data e.g if in a table data e.g das has five rows then it is working well and displaying all five required information, but when I passing same statement through perl it is displayin only one information i.e first row.
how to get all five rows?
if anyone can help me
here is my code............

Expand|Select|Wrap|Line Numbers
  1. #!user/bin/perl
  2. use strict;
  3. use DBI;
  4. use DBD::Oracle;
  5.  
  6. my $part_number = $ARGV[0];
  7. my $dbh = DBI->connect('dbi:Oracle:IDEP', 'cis', 'ci2') || die("Database connection not made: $DBI::errstr");
  8.  
  9. my $sql = "SELECT * FROM haz_inactive_table WHERE PART_NBR = '$part_number' ";
  10. my $sql_count = "SELECT count(*) from haz_inactive_table where PART_NBR='$part_number' ";
  11.  
  12. my $sth = $dbh->prepare($sql);
  13. $sth->execute();
  14. my @data = $sth->fetchrow_array();
  15. print"printing @data\n";
  16.  
  17. my $sth2 = $dbh->prepare($sql_count);
  18. $sth2->execute();
  19. my $count = $sth2->fetchrow_array();
  20.  
  21. foreach (@data) {
  22.     $_ = "\t" if !defined($_);
  23. }
  24.  
  25. if($data[0] =~m/$part_number/g) {
  26.     print "Total count exist in database = $count\n";
  27.     print "Part Number exists in database\n";
  28.     print "Printing information about Part Number you entered.........\n";
  29.     print "Part Number = $data[0]\n";
  30.     print "MFR_CODE = $data[1]\n";
  31.     print "PRODUCT_ID = $data[2]\n";
  32.     print "FILE_NAME = $data[3]\n";
  33.     print "flag = $data[4]\n";
  34.     print "TMP_VAL = $data[5]\n";
  35.     print "Maint_Date = $data[6]\n";
  36. } else {
  37.     print"Either Part Number entered $part_number is invalid \n";
  38.     print"or does not exist in database\n";
  39. }
  40.  
  41. $sth->finish();
  42. $sth2->finish();
  43. $dbh->disconnect();
  44.  
Feb 27 '07 #1
3 1835
KevinADC
4,059 Expert 2GB
Expand|Select|Wrap|Line Numbers
  1. while(my @data = $sth->fetchrow_array()) {
  2.    print"printing @data\n";
  3.    .....
  4. }
Feb 27 '07 #2
das
3
hi all
I got the ans
not need to waste ur time
Feb 28 '07 #3
miller
1,089 Expert 1GB
hi all
I got the ans
not need to waste ur time
Call it wasted effort, but I felt like cleaning up your code a little bit anyway. There's still a lot of wierd and probably useless logic in it that I predict was used for debugging purposes, but this is more or less what you were aiming for:

Expand|Select|Wrap|Line Numbers
  1. #!user/bin/perl
  2. use strict;
  3. use DBI;
  4. use DBD::Oracle;
  5.  
  6. my $part_number = $ARGV[0];
  7.  
  8. my $dbh = DBI->connect('dbi:Oracle:IDEP', 'cis', 'ci2') or die("Database connection not made: $DBI::errstr");
  9.  
  10. my $sql_count = "SELECT count(*) from haz_inactive_table where PART_NBR='$part_number' ";
  11. my $sth = $dbh->prepare($sql_count);
  12. $sth->execute() or die $dbh->errstr;
  13. my $count = $sth->fetchrow_array();
  14. $sth->finish();
  15.  
  16. my $sql = "SELECT * FROM haz_inactive_table WHERE PART_NBR = '$part_number' ";
  17. my $sth = $dbh->prepare($sql);
  18. $sth->execute() or die $dbh->errstr;
  19.  
  20. while (my @data = $sth->fetchrow_array()) {
  21.     print"printing @data\n";
  22.  
  23.     foreach (@data) {
  24.         $_ = "\t" if !defined($_);
  25.     }
  26.  
  27.     if($data[0] =~m/$part_number/g) {
  28.         print "Total count exist in database = $count\n";
  29.         print "Part Number exists in database\n";
  30.         print "Printing information about Part Number you entered.........\n";
  31.         print "Part Number = $data[0]\n";
  32.         print "MFR_CODE = $data[1]\n";
  33.         print "PRODUCT_ID = $data[2]\n";
  34.         print "FILE_NAME = $data[3]\n";
  35.         print "flag = $data[4]\n";
  36.         print "TMP_VAL = $data[5]\n";
  37.         print "Maint_Date = $data[6]\n";
  38.     } else {
  39.         print"Either Part Number entered $part_number is invalid \n";
  40.         print"or does not exist in database\n";
  41.     }
  42. }
  43.  
  44. $sth->finish();
  45. $dbh->disconnect();
  46.  
It's the stuff inside the while loop that you probably could cut.

- M
Mar 1 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

11 posts views Thread by Kostatus | last post: by
117 posts views Thread by Peter Olcott | last post: by
18 posts views Thread by Ian Stanley | last post: by
28 posts views Thread by Jon Davis | last post: by
6 posts views Thread by Ammar | last post: by
2 posts views Thread by Mike Collins | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.