In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Sam Roberts) wrote:
> I'm sorry its taken me so long to get back to you. I lost your
> patch, hacked something that worked for my particular problem,
> and then got pulled into other things.
>
> However, I'm back. I'm having the problem with DBD:CSV, wherein things
> like DISTINCT and SORT don't work:
>
[snip]
> ~/w/svv/tools/dbtools $ perl db-tool export "select distinct date from
> svv_bug_count"
> 2001/09/18 11:25
I know that in MySQL you can do something along the lines of
SELECT date FROM svv_bug_count ORDER BY date DESC LIMIT 1
which will return a descending list of dates from the most recent on
down, and limit the return to a single value :)
I don't know if DBD::CSV will do it, but you *could* try Jeff Zucker's
DBD::AnyData.pm and AnyData.pm modules, which are the replacement for
the old DBD::CSV/DBD::RAM modules. I DO know that there were some things
that DBD::CSV was choking on that DBD::AnyData was able to do admirably
on MacPerl (setting a different csv_eol for example :)
> Can you send me what I need (a newer SQL::Statement, at least) to try
> your newest stuff, and I'll do so Monday, and get back to you right away
> (this time) on how it works.
>
> Thanks a lot, sorry I appeared to drop off the earth.
>
> Sam
>
> p.s. Totally offtopic: I'm an SQL newbie, do you happen to know an SQL
> query that will get me the latest date? I was going to do a
> sort|uniq|head on "select date" (the perl equiv, that is) to get the
> latest date for which data exists, but was wondering if there was a
> purely SQL way. The sort and uniq is supported by SQL, but getting only
> the single greates valued item?
see above :)
> p.p.s. Is there any kind of special date handling in SQL::Statement?
> I've chosen "yyyy/mm/dd hh:mm" (with 24 hour hh) because it a textual
> sort is equivalent to a date sort, but it would be nice if there was
> a way to treat a field as a date, and do things like:
this is *very* close to the formatting used by MySQL for it's DATETIME
field, which is "yyyy-mm-dd HH:MM:SS"...
> select distinct year-month-day(date) from a_table
>
> so that the SQL engine new that the data field was date data, and just
> returned the day-month-year part, stripping out the timeof day part.
>
> Not SQLs problem, or just not supported by DBD::CSV?
with the proper formatting (and yours should suffice even if you didn't
want to convert it to the standard MySQL DATETIME format) you should be
able to extract it with a simple regex, since you KNOW the format that
you can expect to be returned.
With DBD::mysql I'd do: (I don't offhand know whether it supports an
extraction but I suppose it does.. for the sake of argument lets assume
it doesn't (cuz I'm too tired to check, and it's likely that CSV or
AnyData don't anyway) and that we want a regex to extract the return
value (and that RaiseError => 1 is set))
my $sth = $dbh->prepare("SELECT date FROM svv_bug_count ORDER BY date
DESC LIMIT 1");
$sth->execute();
$sth->bind_columns( \($date) );
while ( $sth->fetch() )
{
my($yyymmdd, $hhmmss) = split / /, $date;
my($yyyy, $mm, $dd) = split m|/|, $yyyymmdd; # or split /-/, etc.
print "Year: $yyyy, Month: $mm, Day, $dd\n";
}
$sth->finish if $sth;
$dbh->disconnect();
does that help?
--
Scott R. Godin | e-mail : [EMAIL PROTECTED]
Laughing Dragon Services | web : http://www.webdragon.net/
