469,929 Members | 1,771 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,929 developers. It's quick & easy.

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_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.

Oct 26 '06 #1
2 1949
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/
Oct 26 '06 #2
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.


Oct 26 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

32 posts views Thread by Elliot Temple | last post: by
761 posts views Thread by Neo-LISPer | last post: by
7 posts views Thread by Lauren Quantrell | last post: by
16 posts views Thread by ME | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.