data67 has asked for the wisdom of the Perl Monks concerning the following question:

Hi all, This thing has been bothering me for a little while so here it is..

BACKGROUND
What i have is a simple search interface for an Oracle 8.1 database. The script takes an input value (ss#) from the user and process it (returns employee record) through the DBI.

PROBLEM
My problem is on the data handling portion of this program.
Here i am simply searching for let say an employee with his ss#. If that employee with the supplied ss# does not exist print "employee does not exist". Simple right!
Some people suggested that i rely on error codes returned through $DBI::errstr and use that in my code to handle conditions, but this i think wont work in oracle because it will ALWAYS return 0 for things like "not found". And i don't think that using eval{..}and if ($@){..} will be a good idea also. Not to confuse not found with an error. Remeber we are just wanting to handle a not found condition.
look at my while statement. I know the "EMPLOYEE DOES NOT EXIST" part should be outside the while loop.

Searching with this:

my $quotedSearch = $dbh->quote( $search_for_id );
$SQL = "SELECT * FROM EMPLOYEE WHERE employee_number = $quotedSearch";
eval{
    $sth = $dbh->prepare($SQL);
}

[download]
Here is my problem:
while (@row = $sth->fetchrow_array) { 

# Handle null or "undef's"
$employee_number                = (defined $row[0]) ? $row[0] : '';
$first_name                     = (defined $row[1]) ? $row[1] : '';
$last_name                      = (defined $row[2]) ? $row[2] : '';
$email                          = (defined $row[3]) ? $row[3] : '';

if ($#row > 0) {
print "HERE IS ALL YOUR DATA\n";
} else {
print "NO DATA FOR RECECORD SEARCHED. DO you WANT TO ADD NEW DATA\n";
}


}

[download]

As always, any suggestions will go a long ways....

Replies are listed 'Best First'.
(jeffa) Re: Handling conditions DBI in Oracle 8.1
by jeffa (Bishop) on Jan 15, 2002 at 22:02 UTC
    How about this: 

    my $found = $dbh->selectrow_arrayref(q{
   select 1 from employee
   where employee_number = ?
},undef, $search_for_id);

<do something> unless $found->[0];

# now get the record

    [download]

    The idea is to first see if the the row with that id number is indeed there. If not - exit, die, next, last - whatever will keep the flow going for your code. Also, i am using placeholders in the example - this keeps you from having to call the DBI::quote method, but it is not portable for ALL database vendors (just most).

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
F--F--F--F--F--F--F--F--
(the triplet paradiddle)
[reply]
[d/l]
      If this code is called more than once, its worthwhile preparing the statement:
      my $emp_exists_sth = $dbh->prepare_cached(q{
 select 1 from employee
 where employee_number = ?
});
...
my $found = $dbh->selectrow_array($emp_exists_sth, undef, $search_for_
+id);
print "Not found\n" unless $found;

      [download]
[reply]
[d/l]
Re: Handling conditions DBI in Oracle 8.1
by screamingeagle (Curate) on Jan 15, 2002 at 23:06 UTC
    Another way of doing this is getting the resultset count before firing the required query i.e.
    firing a select count(*) ROWCOUNT from table where id = 1 If ROWCOUNT is 0 , then u dont need to fire the second SQL (which is  select * from table where id = 1) u need to fire the second SQL only if the first SQL returns a value greater than 0 )
    For example, 
    my $sql = <<EOF;
    select count(*) ROWCOUNT from EMPLOYEE where employee_id = 1
EOF
    ;
my $sth = $dbh->prepare($sql);
$sth->execute;
my $employee_count_row = $sth->fetchrow_hashref;
$sth->finish;

print "NO RECORDS FOUND" unless $auth_row->{'ROWCOUNT'} > 0;
 ...fire the second SQL if ROWCOUNT > 0

    [download]
    Hope this helps :)
[reply]
[d/l]
[select]
      You can't use SELECT COUNT(*) in Oracle because it will always give you the wrong count. For you to get an accurate count you will have to ANALYZE. Oracle is little different in this than let say MySQL.
      And then there is a matter where Oracle recommends that you use the DBMS_STATS package rather than ANALYZE for most statistics collection.
[reply]
[d/l]
[select]
        You can't use SELECT COUNT(*) in Oracle because it will always give you the wrong count.

        If I get the wrong count, I'll submit a bug report to Oracle (is there any documentation on this? I don't see anything to that effect under the docs for 'COUNT'). ANALYZE generates statistics for query plans, and shouldn't affect the results of a statement like 'select count(*) ...'.

[reply]
        You can't use SELECT COUNT(*) in Oracle because it will always give you the wrong count. For you to get an accurate count you will have to ANALYZE.

        I am 100% sure this is not true.

        ANALYZE collect statistics for the cost based optimizer and is used to improve (hopefully) the speed at which a query completes. It in no way impacts the results of the query... only the speed.

        Update:

        Just to confirm the above, I threw together a sample SQL script. Its output is below.

        Update #2:

        Now I think I know about what you are thinking -- the column 'num_rows' in the 'dba_tables' view is not populated until you analyze the table. This is not the same thing as doing a COUNT(*), but is similar. 

        ---------------------------
----- Test SQL Script -----
---------------------------

  1  SELECT version
  2*   FROM v$instance

VERSION
-----------------
8.1.7.2.0


  1* DROP TABLE oracle.bobtest
DROP TABLE oracle.bobtest
                  *
ERROR at line 1:
ORA-00942: table or view does not exist 


  1  CREATE TABLE oracle.bobtest
  2*   ( junk VARCHAR(1) )

Table created.


  1  INSERT INTO oracle.bobtest
  2*   VALUES ( 'A' )

1 row created.

1 row created.

1 row created.


  1* COMMIT

Commit complete.


  1  SELECT COUNT(*)
  2*   FROM oracle.bobtest

  COUNT(*)             
---------- 
         3           


  1  ANALYZE TABLE oracle.bobtest
  2*   COMPUTE STATISTICS

Table analyzed.


  1  SELECT COUNT(*)
  2*   FROM oracle.bobtest

  COUNT(*)             
----------
         3           


  1  ANALYZE TABLE oracle.bobtest
  2*   DELETE STATISTICS

Table analyzed.


  1  SELECT COUNT(*)
  2*   FROM oracle.bobtest

  COUNT(*)             
----------
         3           


  1  ANALYZE TABLE oracle.bobtest
  2*   ESTIMATE STATISTICS SAMPLE 20 PERCENT

Table analyzed.


  1  SELECT COUNT(*)
  2*   FROM oracle.bobtest

  COUNT(*)             
----------
         3           


  1* DROP TABLE oracle.bobtest

Table dropped.

        [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom