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

Dear Monks,

while porting an application from MySQL to Oracle I discovered two horrible problems in my code:

my $pos = 1;
while ($hash = $sth->fetchrow_hashref()) {
  if ($pos > $skip)
  {
     push(@data, $hash);
     last if ($pos == ($skip+$slice));
  }

  $pos++;
}

[download]

First I recreated the LIMIT clause missing in Oracle; after that I found that keys of hashrefs returned are all in UPPERCASE instead of lowercase like in MySQL. Do you know how to tell Oracle to give me lowercase field names? I could lc all keys, but it seems too stupid to me...

Any help is welcome! Thank you very much, Valerio

update: fixed typo

Replies are listed 'Best First'.
Re: selectrow_hashref upper/lower case
by runrig (Abbot) on Oct 02, 2002 at 00:36 UTC
    First, see the FetchHashKeyName attribute in the DBI docs, and set it to 'NAME_lc' or 'NAME_uc' if you are going to use fetchrow_hashref.

    Both fetchrow_arrayref and fetchrow_hashref return the same arrayref (I think the docs say that the behavior on the hashref may change to do this, so you may want to change for forward compatibility). Ovid's answer above is okay since he is making a copy of the array every time. Another possiblility is to use one of the $sth->{NAME} attributes. E.g.:

    $sth->execute(...);
my $names = $sth->{NAME_lc};
while (my $row = $sth->fetch) {
  my %hash;
  $hash{@$names} = @$row;
  push @data, \%hash;
}

    [download]
    I think Oracle does have some sort of LIMIT clause (update: oh yeah, see rownum below...), though its called something else I believe...check the Oracle docs...besides, selectall_arrayref and fetchall_arrayref have a $max_rows parameter...see those also.
[reply]
[d/l]
[select]

      That is what I was looking for! I read the DBI man, but didn't see that option... shame on me! Thank you very much runrig and panix!

      Ciao, Valerio

[reply]
Re: selectrow_hashref upper/lower case
by Ovid (Cardinal) on Oct 02, 2002 at 00:22 UTC

    I like to explicitly state my keys and use DBI::fetchrow_arrayref and a hash slice for better control (and a nice performance boost).

    my @keys = qw/ id first_name last_name felony_convictions /;

while ( my $values = $sth->fetchrow_arrayref ) {
    my %results;
    @results{ @keys } = @$values;
    push @data => \%results;
}

    [download]

    With this idiom, I never have to worry about what how the DBD will handle the hash keys. Further, if you change the field names in the database, you merely update your SQL and don't have to worry about the rest of your code breaking.

    If you use this, be careful that you declare your hash inside of the while loop. If you declare it outside of the loop, you'll be putting a reference to the same hash in every element of data.

    Cheers,
    Ovid

    Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

[reply]
[d/l]

      Thanks Ovid, I totally agree with you, but actually I must use the code already running.

      Thanks, Valerio

[reply]
Re: selectrow_hashref upper/lower case
by panix (Monk) on Oct 02, 2002 at 00:43 UTC
    From DBI.pm:

           "FetchHashKeyName" (string, inherited)
           This attribute is used to specify which attribute name
           the fetchrow_hashref() method should use to get the
           field names for the hash keys. For historical reasons
           it defaults to '"NAME"' but it is recommended to set
           it to '"NAME_lc"' or '"NAME_uc"' according to your
           ...

    So something like this is probably what you want:

        $dbh->{FetchHashKeyName} = 'NAME_lc';

    [download]
    Also, if you implemented LIMIT in code, you could try rownum, eg:
        select *
    from   foo
    where  rownum < 6;

    [download]
[reply]
[d/l]
[select]
      ..., you could try rownum...

      I forgot about that. I do remember though, that rownum is useless if you have an ORDER BY clause, because it limits the number of rows selected (regardless of order), and THEN it orders what got selected.

[reply]

        Yes, I knew this method, but I discarded it for the same reason. Thank you very much again runrig!

        Ciao, Valerio

[reply]

Back to Seekers of Perl Wisdom