TEMP column created via set_sql and MySQL DATE_FORMAT function returns 0 in Class::DBI but valid data from command-line

[prev] [thread] [next] [Date index for 2005/02/14]

I am using mysql's DATE_FORMAT function to make a datetime column
human-readable. Cutting and pasting the SQL in a set_sql statement from
a Class::DBI package works fine. However, when I attempt to access this
column as a TEMP column, the date comes back as double zeros. The
transcript below is realtime: I ran my sample script, cat'ed the sample
script and the module that it uses. And then I pasted the SQL from
set_sql into the MySQL command line and it gives different results.

Any feedback on this is appreciated.


terry@Abulafia:/var/www/terry/gimblerus.com/Gimble/Model$ perl -I../..
battle_t.pl=20
0 0 at battle_t.pl line 7.=20
0 0 at battle_t.pl line 7.=20
0 0 at battle_t.pl line 7.=20
0 0 at battle_t.pl line 7.=20
0 0 at battle_t.pl line 7.=20
0 0 at battle_t.pl line 7.=20
0 0 at battle_t.pl line 7.=20
0 0 at battle_t.pl line 7.=20
0 0 at battle_t.pl line 7.=20
0 0 at battle_t.pl line 7.=20
0 0 at battle_t.pl line 7.=20
0 0 at battle_t.pl line 7.=20
terry@Abulafia:/var/www/terry/gimblerus.com/Gimble/Model$ cat
battle_t.pl=20
use Gimble::Model::battle_t;=20
=20
=20
my $s =3D Gimble::Model::battle_t->search_recent_results;=20
=20
while (my $result =3D $s->next) {=20
  warn $result->battle_date;=20
}=20
terry@Abulafia:/var/www/terry/gimblerus.com/Gimble/Model$ cat
battle_t.pm=20
package Gimble::Model::battle_t;=20
=20
use base qw(Gimble::Model);=20
=20
=20
=20
__PACKAGE__->table('battle_t');=20
__PACKAGE__->columns( Primary =3D> 'battle_id' );=20
=20
__PACKAGE__->columns(=20
  Essential =3D> qw/winning_player_id losing_player_id creation_datetime
battle_result/=20
 );=20
=20
__PACKAGE__->columns(=20
  TEMP =3D> qw/battle_date/=20
 );=20
=20
__PACKAGE__->set_sql(recent_results =3D> qq{=20
   SELECT DATE_FORMAT(creation_datetime,'%b %d') as battle_date,
battle_t.*, wp.screen_name as winning_player,lp.screen_name as
losing_player =20
     FROM battle_t INNER JOIN player_t wp ON
(winning_player_id=3Dwp.player_id) =20
          INNER JOIN player_t lp ON (losing_player_id=3Dlp.player_id)=20
 ORDER BY creation_datetime DESC=20
    LIMIT 40=20
});=20
=20

terry@Abulafia:/var/www/terry/gimblerus.com/Gimble/Model$ mysql db_terry

Reading table information for completion of table and column names=20
You can turn off this feature to get a quicker startup with -A=20
=20
Welcome to the MySQL monitor.  Commands end with ; or \g.=20
Your MySQL connection id is 18329 to server version: 4.0.23_Debian-4-log

=20
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.=20
=20
mysql>    SELECT DATE_FORMAT(creation_datetime,'%b %d') as battle_date,
battle_t.*, wp.screen_name as winning_player,lp.screen_name as
losing_player =20
     FROM battle_t INNER JOIN player_t wp ON
(winning_player_id=3Dwp.player_id) =20
          INNER JOIN player_t lp ON (losing_player_id=3Dlp.player_id)=20
 ORDER BY creation_datetime DESC=20
    LIMIT 40;=20
    ->     ->     ->     ->
+-------------+-----------+-------------------+-------------------+-----
-------------+---------------+-----------------+-----------------+=20
| battle_date | battle_id | creation_datetime | winning_player_id |
losing_player_id | battle_result | winning_player  | losing_player   |=20
+-------------+-----------+-------------------+-------------------+-----
-------------+---------------+-----------------+-----------------+=20
| Feb 14      |        65 |    20050214004836 |                20 |
15 |             1 | Sam The Butcher | Princepawn      |=20
| Feb 14      |        66 |    20050214004836 |                20 |
15 |             1 | Sam The Butcher | Princepawn      |=20
| Feb 14      |        67 |    20050214004836 |                20 |
15 |             1 | Sam The Butcher | Princepawn      |=20
| Feb 12      |        62 |    20050212221959 |                20 |
15 |             1 | Sam The Butcher | Princepawn      |=20
| Feb 12      |        63 |    20050212221959 |                15 |
20 |             0 | Princepawn      | Sam The Butcher |=20
| Feb 12      |        64 |    20050212221959 |                15 |
20 |             1 | Princepawn      | Sam The Butcher |=20
| Feb 12      |        59 |    20050212031924 |                15 |
25 |             1 | Princepawn      | URzooked        |=20
| Feb 12      |        60 |    20050212031924 |                15 |
25 |             1 | Princepawn      | URzooked        |=20
| Feb 12      |        61 |    20050212031924 |                15 |
25 |             1 | Princepawn      | URzooked        |=20
| Feb 12      |        56 |    20050212025638 |                25 |
15 |             1 | URzooked        | Princepawn      |=20
| Feb 12      |        57 |    20050212025638 |                15 |
25 |             1 | Princepawn      | URzooked        |=20
| Feb 12      |        58 |    20050212025638 |                15 |
25 |             1 | Princepawn      | URzooked        |=20
+-------------+-----------+-------------------+-------------------+-----
-------------+---------------+-----------------+-----------------+=20
12 rows in set (0.00 sec)=20