in reply to Database Problems

Try using fetchrow_hashref instead. It is not as fast as fetchrow_arrayref (which you should have been using instead of just fetchrow), but it allows you to 'fetch' data by name instead of index. WARNING: once you start using a hash, YOU have to ensure that you won't have key collisions. DO NOT USE select * in conjunction with fetchrow_hashref, spell out each field name explicitly instead. If fact, don't ever use select * in any production code. It just leads to trouble.

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

UPDATE:
tilly has a wonderful node on why he prefers fetchrow_hashref over fetchrow_arrayref, let me see if i can find it ... hmmm, not much luck. Here is a quote from another post that will do in the meantime. ;)

"It is a really bad idea to rely on knowing the order of columns in a database. You have a table with 20 columns. You have an insert with 20 values. If the order is off slightly, have fun debugging! As columns are added or dropped, this will be a constant source of bugs. (Just imagine if some future DBA has to do a database migration and happens to reorder the columns. Then your scripts all have to work against that..?)

This is typically an issue any time you use positional based logic. Associating things by name works much better."

-- tilly

Replies are listed 'Best First'.
Re: (jefffa) Re: Database Problems
by Coplan (Pilgrim) on Apr 16, 2003 at 16:02 UTC
    I agree with Jeffa absolutely 100%. I have one caveat though:

    I like use "?" to indicate where I'll be filling in data. I do this so that I can have one generic subroutine that I can call from almost anywhere. Here is such a subroutine that I can pull off the top of my head: 

    # assume strict, and all other typical good code practices
my $column    = "UserName";
my $condition = "UserID";
my $qualifier = "101";

my ($rowcount, $UserID) = SQL_select($column, $condition, $qualifier);

sub SQL_select {
  my $col  = shift;
  my $cond = shift;
  my $qual = shift;

  my $sth = $dbh->("SELECT ? FROM DB.Table WHERE ? = ?");
  $sth->execute($col, $cond, $qual);

  my $rowcount = $sth->rows;
  my $UserID   = $sth->fetchrow_hashref;

  return ($rowcount, $UserID);
}

    [download]

    Now, all you have to do is make sure you define the column, condition and qualifiers as needed. Of course, you'd probably want to get more complicated than this. You'd probably want some error checking and what-not (I'm on lunch...didn't get to stick it in there).

    As a side note, notice I counted the rows. I've always done that, and I think it's a good idea (even if you're using a LIMIT function in the Database query) as a form of error checking. Obviously, this is a generic subroutine...so who knows what the guy is looking for. Supposing he is doing a query to find a UserName for someone with a UserID of 101 (as in this case). Well, if there are no rows returned, you'll probably want to report that the user doesn't exist. Or, if more than one is returned, you'll want to maybe take some other measures to notify the system maintainer that there is more than one UserName registered to that UserID. A whole set of other subroutines might be called if this is the case. So, it's always good to count rows.

    --Coplan

[reply]
[d/l]
      my $sth = $dbh->("SELECT ? FROM DB.Table WHERE ? = ?");
      Just a portability warning: Using placeholders for column names is very specific to the DBI driver for MySQL - this will not work with Oracle, Sybase, MS-SQL, etc.

      Michael

[reply]
        Using placeholders for column names is very specific to the DBI driver for MySQL

        And it's specific to current versions of MySQL. When prepared queries are supported (soon, from the sound of it), that trick won't work.

[reply]

In Section Seekers of Perl Wisdom