Hi again

I decided to give the database approach a go and see how fast it is.

I'm using DBI::AnyData and creating an in-memory table, which should be pretty quick.

However, I'm having trouble with my SQL. This works (i.e. it returns the time of the latest pressure data at the time of the event:

SELECT max(weather_time) FROM pressure where (weather_time < '$event_t
+ime')
[download]

I'd really like to do this:

SELECT max(weather_time), baro_pressure FROM pressure where (weather_time < '$event_time') But of course this is not valid SQL. Does the DBI interface support nested SQL? I've tried this:

SELECT weather_time, baro_pressure from pressure where weather_time = (select max(weather_time) FROM pressure where (weather_time < '$radar_time')) This gives the following error:

DBD::AnyData::db prepare failed: SQL ERROR: Bad table or column name '^0^' has chars not alphanumeric or underscore!

Any suggestions?

R.

Update

As a work around, I'm using two SQL queries to get the information. The following code snippet is from a function:

my $sth = $dbh->prepare("SELECT max(weather_time) FROM pressure where 
+(weather_time < '$data_time')")
  or die "Cannot prepare: " . $dbh->errstr();
$sth->execute()
  or die "Cannot execute: " . $sth->errstr();
my $row = $sth->fetchrow_hashref;
my $press_time = $row->{'weather_time'};
if (!defined $press_time) {
  die "No pressure data earlier than the time of the data point ($data
+_time)"
};
$sth = $dbh->prepare("SELECT baro_pressure from pressure where (weathe
+r_time = '$press_time')")
  or die "Cannot prepare: " . $dbh->errstr();
$sth->execute()
  or die "Cannot execute: " . $sth->errstr();
$row = $sth->fetchrow_hashref;
$sth->finish();
return $row->{'baro_pressure'};
[download]

Now, this works, but is not exactly speedy.

The data set I am testing with has data points from 2003-10-12, and I initially created a CSV file containing pressure data for all of October (it has 1331 pressure lines) but this proved to be painfully slow. I improved things by creating a file containing just pressure data from 2003-10-12 (it has only 46 pressure lines).

This solution is functional, but I'd like to improve it so I can not mess about with subsets of pressure data.

Perhaps it would be an idea to create some sort of indexed database file from the CSV file and use a DBI interface to that?

Thoughts?

R.