fetchrow_hashref Not returning Dates

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

I recently needed to modify a loop that was reading the results of a SELECT statement from fetchrow_array format to fetchrow_hashref for clarity and to accomodate new fields to be processed. The SELECT statement requests several character, number, and date fields. The two date fields have always been requested with "to_char" formatting.

Previously, my while statement looked like this...

while ( ($granule_name, $path, $start_date, $stop_date, $byte_size) = 
+$sth->fetchrow_array) {
...
}
[download]

Now it looks like this...

while ( $lhref = $sth->fetchrow_hashref('NAME_lc') ) {
...
}
[download]

In addition to the granule_name (char), path (char), start_date (date->to_char), stop_date (date->to_char), and byte_size (number) fields, I've added two more fields to the end of the SELECT statement i.e. num_passes (number) and browse_avail (char).

The problem is that now all of the values are returned except for the two date fields. Remember that I have applied "to_char" formatting to them, so they are being returned as character values. Can anyone tell me why the fields before and after the date->to_char fields are being returned in the hash but not the date->to_char fields themselves? Many thanks!

Comment on fetchrow_hashref Not returning Dates Select or Download Code

Replies are listed 'Best First'.
Re: fetchrow_hashref Not returning Dates by astroboy (Chaplain) on Sep 03, 2009 at 02:00 UTC
It would be most useful if you provided Your SQL A dump of $lhref using Data::Dumper or similar I suspect that you'll find that your column names will include the to_char function: `SQL> select to_char(sysdate, 'DD-MON-YYYY') from dual; TO_CHAR(SYS ----------- 03-SEP-2009` [download] Although I don't know your code or SQL, I suspect you'll need to provide an alias for your column (most likely the original column name if that's what you're referring to in your code): `SQL> select to_char(sysdate, 'DD-MON-YYYY') "sysdate" from dual; sysdate ----------- 03-SEP-2009` [download]	[reply] [d/l] [select]
Re^2: fetchrow_hashref Not returning Dates by Anonymous Monk on Sep 03, 2009 at 16:52 UTC
"I suspect that you'll find that your column names will include the to_char function:" Thanks, astroboy! This is exactly what was happening and I don't know why I didn't think to just print out the hash to investigate. (I guess it had something to do with getting less than 6 hours of sleep the night before.) The SELECT statement looked like this: `SELECT granule_name, path, to_char(start_date, 'yyyymmddhh24miss'), to +_char(stop_date, 'yyyymmddhh24miss'), byte_size, num_passes, browse_a +vail FROM $invName WHERE $pclause $sclause $suffix` [download] The key/value pairs look like this: `granule_name f15_grid_2009.243_dayAD.eos path 2009/f15_grid_2009.243_dayAD.eos to_char(stop_date,'yyyymmddhh24miss') 20090901004308 to_char(start_date,'yyyymmddhh24miss') 20090831000749 byte_size 4888111 num_passes 29 browse_avail Y` [download]	[reply] [d/l] [select]
Re^3: fetchrow_hashref Not returning Dates by Anonymous Monk on Nov 01, 2010 at 19:17 UTC
I've got the same issue, but unfortunately, I don't have access to the stored procedure that is formatting the dates. Other than the alias, is there a way to get the data when the returned key is balled up with the to_char?	[reply]
Re: fetchrow_hashref Not returning Dates by roboticus (Chancellor) on Sep 03, 2009 at 01:53 UTC
Since you don't show the code, I can only assume that perhaps you didn't alias the columns to names. In MS SQL parlance, something like: `select convert(char(8),getdate(),112) as NamedDate` [download] Since the fetchrow_hashref uses column names as keys, missing column names could be the problem... ...roboticus	[reply] [d/l]