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

I am having trouble with some code Ive produced to connect to a database, read in a row of data and assign each column to a variable. Sample code: 
$dbi = DBI->connect('Blah', 'Blah', 'Blah', 'ODBC');
$dbistmt = $dbi->prepare(" SELECT * FROM MyTable" );   
$dbistmt->execute;
while(@publication_record = $dbistmt->fetchrow()){
  last if $publication_record[0]=~/^$PublicationId$/
}
$dbistmt->finish;
$dbi->disconnect;

$Pu_strID = $publication_record[0];
$Pu_PuStOp_strID = $publication_record[1];
$Pu_PuTy_strID = $publication_record[2];
$Pu_strName = $publication_record[3];
etc..

[download]
The variables I am using ($Pu_strID, $Pu_PuStOp_strID, etc..) are taken from the column names themselves (Pu_strID, Pu_PuStOp_strID, etc..). This has been working well until recently. A coworker added a new feild betwen the 0 and 1st columns and now $publication_record[2] and $publication_record[3] are not what I was expecting. I would like to avoid this in the future. How can I say to sql/dbi/whatever to grab the data from the column named "Pu_strName" and assign it to the $Pu_strName variable, regardless of where the Pu_strName column is? (hope at least some of this makes any sense at all)

Replies are listed 'Best First'.
(jefffa) Re: Database Problems
by jeffa (Bishop) on Apr 16, 2003 at 15:00 UTC
    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
[reply]
      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]
Re: Database Problems
by jasonk (Parson) on Apr 16, 2003 at 14:58 UTC

    Either specify the field names, instead of saying *, so you always get them in the same order, or use fetchrow_hashref instead of fetchrow, this is exactly why fetchrow_hashref exists.

    We're not surrounded, we're in a target-rich environment!
[reply]
Re: Database Problems
by physi (Friar) on Apr 16, 2003 at 15:03 UTC
    Well
    make your select more specific : 
    $dbi = DBI->connect('Blah', 'Blah', 'Blah', 'ODBC');
$sth = $dbi->prepare('select a, b, e, s from MyTable');
$sth->execute;
while (my ($a, $b, $c, $d) = $sth->fetchrow_array) {
 ....
}

    [download]
    So if you used named columns in the select statement it does not matter, in which order these columns are stored by your database. So there can be done any change (additional columns) to the table, and your script still works.

    hope this helps 
    -----------------------------------
--the good, the bad and the physi--
-----------------------------------
[reply]
[d/l]

Back to Seekers of Perl Wisdom