case issue in fetchrow_hashref

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • dcruncher4@aim.com

    #1

    case issue in fetchrow_hashref

    Version Info
    DBI 1.48
    DBD Driver for DB2 UDB: 0.78
    DB2 8.2.3
    Perl 5.6.1

    8 my $sql = "select start_time , max(seqnum) as num_tapes
    9 from table(admin_lis t_hist()) as lh
    10 where operation = 'B'
    11 group by start_time " ;
    12
    13 my $sth = $DBH->prepare($sql );
    14 $sth->execute() or die "can not execute" ;
    15 while (my $v_data = $sth->fetchrow_hashr ef() ) {
    16 my $startime = $v_data->{'START_TIME '} ;
    17 my $num_tapes = $v_data->{'NUM_TAPES' } ;
    18 print "|$startime|$nu m_tapes|\n" ;
    19 }

    It seems fetchrow_hashre f converts column name into upper case even
    though
    the select statement uses the column name in lower case. When I wrote
    START_TIME
    as start_time in line 16 , I could not get any data.

    Is this an accepted and documented behaviour of DBI with DB2?

    Thanks.

  • Serge Rielau

    #2
    Re: case issue in fetchrow_hashre f

    dcruncher4@aim. com wrote:
    Version Info
    DBI 1.48
    DBD Driver for DB2 UDB: 0.78
    DB2 8.2.3
    Perl 5.6.1
    >
    8 my $sql = "select start_time , max(seqnum) as num_tapes
    9 from table(admin_lis t_hist()) as lh
    10 where operation = 'B'
    11 group by start_time " ;
    12
    13 my $sth = $DBH->prepare($sql );
    14 $sth->execute() or die "can not execute" ;
    15 while (my $v_data = $sth->fetchrow_hashr ef() ) {
    16 my $startime = $v_data->{'START_TIME '} ;
    17 my $num_tapes = $v_data->{'NUM_TAPES' } ;
    18 print "|$startime|$nu m_tapes|\n" ;
    19 }
    >
    It seems fetchrow_hashre f converts column name into upper case even
    though
    the select statement uses the column name in lower case. When I wrote
    START_TIME
    as start_time in line 16 , I could not get any data.
    >
    Is this an accepted and documented behaviour of DBI with DB2?
    This is documented behavior of ANSI SQL. If you want to preserve case
    you need to double-quote when you create and use the column.

    Cheers
    Serge
    --
    Serge Rielau
    DB2 Solutions Development
    IBM Toronto Lab

    IOD Conference

    Comment

    • Ian

      #3
      Re: case issue in fetchrow_hashre f

      dcruncher4@aim. com wrote:
      Version Info
      DBI 1.48
      DBD Driver for DB2 UDB: 0.78
      DB2 8.2.3
      Perl 5.6.1
      >
      8 my $sql = "select start_time , max(seqnum) as num_tapes
      9 from table(admin_lis t_hist()) as lh
      10 where operation = 'B'
      11 group by start_time " ;
      12
      13 my $sth = $DBH->prepare($sql );
      14 $sth->execute() or die "can not execute" ;
      15 while (my $v_data = $sth->fetchrow_hashr ef() ) {
      16 my $startime = $v_data->{'START_TIME '} ;
      17 my $num_tapes = $v_data->{'NUM_TAPES' } ;
      18 print "|$startime|$nu m_tapes|\n" ;
      19 }
      >
      It seems fetchrow_hashre f converts column name into upper case even
      though
      the select statement uses the column name in lower case. When I wrote
      START_TIME
      as start_time in line 16 , I could not get any data.
      >
      Is this an accepted and documented behaviour of DBI with DB2?
      Yes. See the FetchHashKeyNam e attribute for your handle. The default
      for DBI is to give you what the database returns, but you can force
      DBI to convert to either upper or lower case.

      DB2 folds all names to upper case, unless you are explicit:

      select max_seqnum as num_tapes -- NUM_TAPES
      select max_seqnum as "Num_Tapes" -- Num_Tapes

      Beware of using mixed case in your database, it quickly becomes a PITA.




      Comment

      Working...