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.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.