Re: Strange selectall_hashref/fetchall_hashref problem with DBD::mysql
"Martin J. Evans" <[email protected]>
| Newsgroups | gmane.comp.db.mysql.perl |
|---|---|
| Organization | Easysoft Ltd |
| Message-ID | <[email protected]> |
Payne, Thanks for the suggestions. On 14-Feb-2006 Boysenberry Payne wrote: > It sounds to me like DATEDIFF and DATE might be messing things up a bit, > hence the problems go away after they're gone. Yes, that was my conclusion. > 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. I'm not saying I cannot do that but as I did say, this was my attempt to simplify the problem. The actual SQL in the real application involves procedures and cannot be changed. The procedures isolate the application from database differences. > 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'. I've done that. If you replace the datediff in the SQL where with an exact date it works. > 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] Just in case anyone else is following this thread it has been pointed out to me privately that I over-simplified the problem. I said I was doing: selectall_hashref($sql, 'race_id') and in fact race_id is not the unique key for the result-set. The unique key for the result-set is a combination of race_id and meeting_id. However: selectall_hashref($sql, ['race_id', 'meeting_id']) does not work either. Neither does inventing a different key using methods like adding the following to the select list and changing the key in the selectall_hashref to 'pkey': CONCAT_WS('$;',m.meeting_id,r.race_id) AS pkey (@id := @id + 1) AS pkey Martin -- Martin J. Evans Easysoft Ltd, UK http://www.easysoft.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]