Re: Strange selectall_hashref/fetchall_hashref problem with DBD::mysql

It sounds to me like DATEDIFF and DATE might be messing things up a bit,
hence the problems go away after they're gone.

I myself use perl to do the Date calculations then translate it for the 
database.

It avoids issues where database syntax changes as well as your problem.

Try it with perl doing the calculations, or just supply arbitrary date 
info to
get it working as a request.  It will isolate the issue a bit more, as 
well as
allow you to get what you want done until you figure out what's going 
on with
'DATEDIFF', and 'DATE'.

You might also want to try the mod_perl group they're really good about
knowing the internals of Packages like DBI and DBD::mysql:

[email protected]



Boysenberry

boysenberrys.com | habitatlife.com | selfgnosis.com

On Feb 14, 2006, at 5:22 AM, Martin J. Evans wrote:

> Hi,
>
> I posted the following email to dbi-users list but have so far 
> received no
> responses. I hoped I might have more luck here. I'd appreciate any 
> ideas on how
> to persue this.
>
> Thanks
>
> -----FW: <[email protected]>-----
>
> Date: Fri, 10 Feb 2006 12:04:33 -0000 (GMT)
> From: "Martin J. Evans" <[email protected]>
> To: [email protected]
> Subject: Strange selectall_hashref/fetchall_hashref problem with 
> DBD::mysql
>
> Hi,
>
> selectall_hashref and fetchall_hashref work fine for me most of the 
> time but
> I've hit a scenario where I get a result I just cannot fathom.
>
> I'm using DBI 1.50 and DBD::mysql 3.0002_4 (with a few minor patches I 
> posted
> on this list to make it compile and get rid of the FREE UNBIND problem 
> I had).
>
> I have tables:
>
> race
> race_id               primary key auto increment
> meeting_id            foreign key to meeting_id in meeting
>
> meeting
> meeting_id            primary key auto increment
> created_date_time_utc datetime
>
> (there are other columns but they are not referenced).
>
> I do:
>
> SELECT m.meeting_id,r.race_id FROM meeting m, race r where
> r.meeting_id = m.meeting_id and
> DATEDIFF('2006-02-10', DATE(m.created_date_time_utc)) <= 100
>
> and dump the reference returned by selectall_hashref(race_id) or
> fetchall_hashref(race_id) and get:
>
> $VAR1 = {
>           '' => {
>                   'race_id' => undef,
>                   'meeting_id' => undef
>                 }
>         };
>
> The query does return rows. I know because:
> 1) if I change to use prepare/execute/fetchrow_array I can the rows
>    back
> 2) if I leave it as fetchall_hashref/selectall_hashref and set
>    DBI_TRACE to 20 I can see my data in the trace.
>
> I can also change nothing other than switch to DBD::ODBC (and the 
> myodbc
> driver) and it works fine.
>
> The interesting bit is if all I do is take the
> "DATEDIFF('2006-02-10', DATE(m.created_date_time_utc)) <= 100" out of
> the where clause it works, even though this makes no difference
> I can see to either the number of rows returned, the column names or
> result-set content. Unfortunatly the trace at level 20 is 120K.
>
> I've tried reproducing with other tables which are more simple but
> failed - the above is as simple as I can get it and fail.
>
> Any ideas?
>
> Martin
> --
> Martin J. Evans
> Easysoft Ltd, UK
> http://www.easysoft.com
>
> --------------End of forwarded message-------------------------
>
> --
> Martin J. Evans
> Easysoft Ltd, UK
> http://www.easysoft.com
>
>
> -- 
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe:    
> http://lists.mysql.com/[email protected]
>
>
>


-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]