Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

Handling Null or Undef values in DBI (and things of those nature)...

by data67 (Monk)
on Jan 08, 2002 at 04:28 UTC ( #137005=perlquestion: print w/replies, xml ) Need Help??

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

Hi all,

Here is the deal guys. I am using DBI module with Oracle 8.1.7. (first timer) What i have is just an average web based interface for an oracle backend.
Two qestions:

1. How to handle my problem fields (empty fields?).
2. How to check if a record exists.

Question 1:
The problem is that when i try to pull in my data from the table my program crashes on the first field that is not defined.( i don't know if its a prblem with (NULL => undef ) or somthing else.

I have seen people use DBI::neat(undef) to handle similar thing and i tried that also with no luck, i still get "use of uninitialized value" message.
Here is what I have till now : (which by the way works good on records that have "all" fields filled.)

## connect to DB ## Run my SQL statements while (@row = $sth->fetchrow_array) { $employee_id = $row[0]; $last_name = $row[1]; $first_name = $row[2]; $cell_phone_number = $row[3]; $email = $row[15]; $information_date = $row[17]; ### clean up for web## eq ""); $last_name = " " if ($last_name eq ""); $first_name = " " if ($first_name eq ""); $cell_phone_number = " " if ($cell_phone_number eq " +"); $email = " " if ($email eq ""); $information_date = " " if ($information_date eq "" +); ## then print "$PRINT the "STUFF" retrieved"; ## disconnect


As you can see at the moment, this is really plain. Question 2:
I also want to find out "how?" i can check a the table before i add new records to it.

##somthing like so

check if record exists { if not present{ &add_new; } else { print " RECORD EXISTS"; } }

Replies are listed 'Best First'.
Re: Handling Null or Undef values in DBI (and things of those nature)...
by mpeppler (Vicar) on Jan 08, 2002 at 05:26 UTC
    For Q.2: In order to avoid race conditions (where you check fo r the record existence in the db, but someone else inserts it before you have a chance of inserting it) you should let the database do the checking.
    This is normally done with a constraint (usually a unique index of some sort). You then trap that error in the insert logic. Something like this:
    # Assumes RaiseError is set eval { $dbh->do("insert ...."); }; if($@) { # check for the "duplicate insert" error here, and # handle accordingly... }

    Michael

Re: Handling Null or Undef values in DBI (and things of those nature)...
by kwoff (Friar) on Jan 08, 2002 at 04:36 UTC
    Question 1: grep `perldoc DBI` for "undef":

    undef NULL values are represented by undefined values in Perl

    You might check like:
    $employee_id = (defined $row[0]) ? $row[0] : '';

    Question 2: do a select for that row. Using fetchrow_array(), it will return an empty list if there wasn't a row, so

    @foo = $sth->fetchrow_array(); if (@foo) { ... } else { ... }
    I use MySQL, so maybe Oracle has fancier features, for which read the manual.
Re: Handling Null or Undef values in DBI (and things of those nature)...
by gav^ (Curate) on Jan 08, 2002 at 09:05 UTC
    Or a slightly different syntax:
    my ($field1, $field2, $field3) = (undef, undef, undef); my $sth = $dbh->prepare('select * from table'); $sth->execute; if ($sth->rows > 0) { $sth->bind_columns(\($field1, $field2, $field3)); $sth->fetch; } $sth->finish; for ($field1, $field2, $field3) { $_ ||= '' }
    When dealing with failures caused by duplicate values (due to a constraint) I just check like this:
    $dbh->do('insert into blah (?, ?, ?)', undef, $x, $y, $z); if ($dbh->err) { if ($dbh->err == 1062) { # duplicate } else { # some other problem } }
    1062 happens to be the error code for MySQL.

    Look at $DBI::lasth->{Statement} which has the last SQL statement you tried to execute which is quite handy in a debug subroutine.

Re: Handling Null or Undef values in DBI (and things of those nature)...
by rbc (Curate) on Jan 08, 2002 at 04:49 UTC
    I hope this info can help you with your 2nd
    question.

    Off the top of my head there are aleast 3 ways
    to check the table before you insert a new record.

    way one) would be to define a unique constraint
    on this table in the DBMS. This will prevent any
    insertion of a record that is a duplicate.

    way two) would be to define a before insert trigger
    and have this trigger check for a duplicate.

    way three) execute the query select count(*) into n from ...
    and if n > 0 then the record exist.

    --
    Its like a dog that can sing and dance.
    It's remarkable because it can do it.
    Not that it can do it well.
      Can you please give me an example or two on your seggestions.

      As far as my first question i got it done by doing this

      while (@row = $sth->fetchrow_array) { foreach (@$row) { $_ = '' unless defined; ### HANDLE NULL FIELDS } }
      Find an SQL manual or on-line doc. and look up
      the ALTER TABLE and CREATE TRIGGER commands.
      They do a much better job than I could :)

      --
      Its like a dog that can sing and dance.
      It's remarkable because it can do it.
      Not that it can do it well.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://137005]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (5)
As of 2022-06-26 14:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My most frequent journeys are powered by:









    Results (86 votes). Check out past polls.

    Notices?