Help me ! I am stuck ! PERL DBI

raja.b4u has asked for the wisdom of the Perl Monks concerning the following question:

Hi All,
I am new to perl ,I am stuck with below issues....
I am querying to database to fetch a field for a user through a subroutine. Check the below code....
1. if user is found in Database then subroutine returns the result. The print statement after the soubroutine call print the result. (expected)
2. but if user is not found or the the column vlaue is empty for that user , then my program is getting terminated before return the result to the subroutine ....so the print statement after the subroutine call is not being exceuted...

Please help me...
Here is the code

 
#!/usr/bin/perl</br>

use DBI;
$database = "idm";
$username = "idm";
$password = "idm";
$hostname = "localhost";

$output = &connectCBMS(900010);   --> calling a subroutine 
print $output; -- printing the result

sub connectCBMS {
$db = DBI->connect("DBI:Oracle:host=$hostname;sid=$database",$username
+,$password);

unless($db) {
    #LogResult("(ERROR) Failed to connect to $DIRHOST.");
    #ReportFailure("(ERROR) Failed to connect to $DIRHOST.");
    print "(ERROR) Failed to connect to $hostname."; 
    return 0;
  }

my ($staffcode) = @_;
# Execute a Query
my $sql = qq{ SELECT REGISTRATIONDAY FROM CBMS WHERE STAFFCODE = ? };
my $sth = $db->prepare( $sql );

$sth->bind_param( 1, $staffcode );
$sth->execute();

my $row;
 
   while ( $row = $sth->fetchrow_array(  ) ) {
           return $row;

      }
  

$sth->finish();
$db->disconnect();
exit(0);
}
[download]

Thanks Suriya

Comment on Help me ! I am stuck ! PERL DBI Download Code

Replies are listed 'Best First'.
Re: Help me ! I am stuck ! PERL DBI by kyle (Abbot) on Oct 13, 2007 at 02:04 UTC
If there are no rows returned, the sub you have never does a return at all. Instead, it goes on to call `$sth->finish()` and `$db->disconnect()` (both of which are unnecessary) and finally exit, which is what terminates the program. If you want to print or do anything after calling `connectCBMS`, change `exit(0)` to `return`. Note also: Your code is not consistently indented. This makes it harder to read. There isn't any point in wrapping `fetchrow_array` in a `while` loop. The loop ends on the first iteration because of the `return`. `my ($staffcode) = @_;` would be much better placed at the very top of the `sub`. It's a good idea to use warnings. It's also a good idea to use strict, but your code will require some more adjustments to work that way. I hope this helps.	[reply] [d/l] [select]
Re^2: Help me ! I am stuck ! PERL DBI by Anonymous Monk on Oct 13, 2007 at 03:35 UTC
Hi Kyle , Exactly ! you are 100 % correct . Now it works with "return" instead of exit. Thanks a lot . Also Thanks for you review on my coding standard. 1. Actually I will write all warning/error mesg into a log file. 2.ofcourse i will put my ($staffcode) = @_; to the top most in the sub. 3. You mean to say that use " sth->fetchrow_array" instead of ($sth->fetchrow_array()) ??? 4.I will learn abt use strict and let me... Thanks a lot. Suriya	[reply]
Re^3: Help me ! I am stuck ! PERL DBI by spectre9 (Beadle) on Oct 13, 2007 at 03:56 UTC
You might wish to get in a habit of checking for $err as well. In some situations, if you $sth->execute fails, and you $sth->fetchrow Perl may (will) exit entirely. Checking for errors is a good habit to get into -- it will prepare you for the 'next database' spectre#9 -- more dangerous than acme	[reply]
Re: Help me ! I am stuck ! PERL DBI by runrig (Abbot) on Oct 13, 2007 at 16:09 UTC
If you just want the first row, you might want to use the selectrow_array method. Also, you might want to set PrintError on the connect so that you'll have better error checking, and not just on the connect.	[reply]