From: Shashank Date <sdate@...>
Date: 2005-01-03T00:31:38+09:00
Subject: Re: Finding out what row DBI chokes on?

Hi Francis,

Francis Hwang wrote:
> Perhaps I didn't explain my problem very clearly. None of my current 
> code is generating bad datetimes, but at work I deal with a database 
> with legacy data going back 7 years, and from time to time I come across 
> bad datetimes that were inserted years before. 

This much I had guessed from your earlier email, so I guess you had 
explained the problem clearly ;-)

Apologies if my answers did not make sense to you.

 > DBI can't handle these at  all. Here's test code to show it:

<snip>

> Now, it isn't surprising or necessarily a bug that DBI won't even 
> sensibly process this value, and catching the exception is easy enough. 
> But it slows me down a little that
> 
> 1) I don't know exactly what the bad value is. You'd be surprised how 
> many ways MySQL lets you insert bad datetime strings.

I deal with such data almost on a routine basis (although not using 
MySQL), and use standard "scrubbing techniques":

1. Try to limit the data in the SELECT, like borrowing from your example:

SELECT * FROM TEST WHERE DT > '1997-01-01';

or


SELECT DISTINCT DT FROM TEST;

to see what values show up

2. Use the datetime / string conversion functions to try to identify the 
   bad data.

3. If the data set is small (< 100,000 rows) use CURSOR logic (usually 
frowned upon) to identify the bad rows

4. Export the table to a CSV and then use the text filtering tools and 
then Import it back

Again, not knowing exactly what your situation is, I do not know if all 
these are even relevant. So I guess, what I am trying to say is, as far 
as possible try to handle it at the backend BEFORE it reaches DBI.


> 2) I don't know which row the bad value is in if I've done a select that 
> should return more than 1 row.
> So I'm wondering if, short of patching DBI, there is a way for a DBI 
> client (Lafcadio, in my case) to get more info to return a more 
> informative error message. I'm big into informative errors.

I am afraid there may be no way to do that. But I am by no means a DBI 
expert.

HTH,

> Francis Hwang
> http://fhwang.net/

-- shanko