case issue in fetchrow_hashref

[dcruncher4]

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_list_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_hashref() ) {
16 my $startime = $v_data->{'START_TIME'} ;
17 my $num_tapes = $v_data->{'NUM_TAPES'} ;
18 print "|$startime|$num_tapes|\n" ;
19 }

It seems fetchrow_hashref 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]

dc********@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_list_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_hashref() ) {
16 my $startime = $v_data->{'START_TIME'} ;
17 my $num_tapes = $v_data->{'NUM_TAPES'} ;
18 print "|$startime|$num_tapes|\n" ;
19 }

It seems fetchrow_hashref 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
http://www.ibm.com/software/data/ond...ness/conf2006/

[Ian]

dc********@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_list_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_hashref() ) {
16 my $startime = $v_data->{'START_TIME'} ;
17 my $num_tapes = $v_data->{'NUM_TAPES'} ;
18 print "|$startime|$num_tapes|\n" ;
19 }

It seems fetchrow_hashref 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 FetchHashKeyName 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.