A piece of advice though, don't use backticks to call the date command - for one, it's not portable, and two, it's not as efficient as calling localtime (and use strict!):

my $month = (localtime)[4] + 1;
my $day   = (localtime)[3];
[download]

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
F--F--F--F--F--F--F--F--
(the triplet paradiddle)

Additionally, to get the zero-padding functionality of date(1)'s %m and %d, use sprintf().


my $month = sprintf ("%02d", (localtime)[4]+1);
my $day   = sprintf ("%02d", (localtime)[3]);
[download]


Two other options for formatting your date/time are: POSIX's strftime, and Date::Format.

Or even more similar to using date:

use POSIX qw( strftime );

# ...

my $month = strftime('%m');  # same as `date '+%m'`
my $day   = strftime('%d');  # same as `date '+%d'`
[download]

Any date/time formatting you could get with date is possible with <CDOE>strftime()</CODE> (since under the hood, date calls it too!).

Perhaps this particular use is best accomplished with localtime, but it could be helpful to know about the concordance between date and <CDOE>POSIX::strftime()</CODE>.

dmm

If you GIVE a man a fish you feed him for a day
But, TEACH him to fish and you feed him for a lifetime

Ha, I guess an error message would help, but I don't have one, if I run the SQL command by hand it returns the results I expected, but as soon as I put it in the .pl script...no luck! TA

What data types are your Month and Day columns? Presumably int from your example, but you never know...

Problems with prepared statements are notoriously difficult to simulate at the prompt, since command-line SQL utilities (isql, sqlplus, et al) don't support the concept.

First, I have to ask why you are using backticks. That just slows things down:

    my ( $day, $month ) = (localtime(time))[3,4];
    $month++;
[download]

I don't see enough from this snippet to see the problem. Further, I'm not sure what the problem is as you haven't actually described it. I am assuming that you mean your fetchrow_array statement isn't working. If so, did you set RaiseError => 1 when you instantiated your DBI object? Have you tried DBI->trace? There are a variety of things that could go wrong here.

Cheers,
Ovid

Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

--t. alex

"Excellent. Release the hounds." -- Monty Burns.

I don't see any problems with your code, but you could make it a bit more efficient with fetchrow_hashref().

$UniqueDay = "SELECT count(*) as COUNT, max(time) as MAX, min(time) as
+ MIN, attacksignature from np_data where month=? and day=? group by a
+ttacksignature as ATTACKSIG order by COUNT desc";

##Unique Events
$unique_events_sth = $db->prepare( $UniqueDay );
$unique_events_sth->execute($Month, $Day);

while ( my $unique = $unique_events_sth->fetchrow_hashref ) {
    push( @unique_array, $unique );
}
[download]

How exactly is fetchrow_hashref more efficient? From the DBI docs:

Because of the extra work "fetchrow_hashref" and Perl
have to perform, it is not as efficient as
"fetchrow_arrayref" or "fetchrow_array".
[download]

Also, you should not save the reference in an array, you may get bitten later. Also from the DBI docs:

Currently, a new hash reference is returned for each
row.  This will change in the future to return the same
hash ref each time, so don't rely on the current
behaviour.
[download]

If you really need to save all the results in an array of hashes, use selectall_arrayref (this changed recently, selectall_hashref use to be the solution to this, but as of DBI 1.20 it now returns a hashref, not an array). If you want to prepare the statement, that's fine, the selectall_* methods will take a sql string or a statement handle as an argument, or you can use one of the new fetchall_* methods.

How exactly is fetchrow_hashref more efficient?

I didn't mean to slight anybody, or say that the posted way was bad. Please accept my appologies if it came out that way.

You are correct about fetchrow_hashref being less efficient than fetchrow_array. But, the original code is taking the elements of the returned array, copying them into its own array, then creating a hash and copying the array into the hash element by element. I can't imagine that is faster than fetchrow_hashref (though I haven't benchmarked it, so maybe it is).

It also is less maintainable later if the requirements from the database change since it requires modifying code in more locations. But I suppose that really doesn't have to much to do with execution efficency.

Also, you should not save the reference in an array, you may get bitten later.

Thanks. That's why I like this place, you learn something new every day.

Thou shalt use strict - and warnings too - if thou coverts thy sanity.

cLive ;-)

ps - IANAL - I am Not A Lammergeyer

$UniqueDay = "SELECT count(*) as COUNT, max(time) as MAX, min(time) as
+ MIN, attacksignature from np_data where month=\'?\' and day=\'?\' gr
+oup by attacksignature order by COUNT desc";
[download]

Scott

DBI always auto quotes placeholders. Which can be annoying if you want to use a placeholder to represent what you are selecting and not what you are comparing.

## You can do this, and not worry about quoting:
$query = "SELECT x, y, z from table where a = ?";
## This doesn't work the way you think it does:
$query = "SELECT ? from table where a = 'blah'";
[download]

In the case of the second example above, if you provided "field1" to be bound to the placeholder, you would get: SELECT "field1" from table where a = 'blah', which means you would get a bunch of results that all say "field1".

I'd probably re-write the query like this as I suspect the order by COUNT is what is failing.

SELECT count(*) as cnt
     , max(time) as MAX
     , min(time) as MIN
     , attacksignature 
  from np_data 
 where month=? and day=? 
 group by attacksignature 
 order by cnt desc
[download]

Michael

-mark

$unique_events_sth = $db->prepare( $UniqueDay ) or die "Unable to prep
+are query : $DBI::errstr";
$unique_events_sth->execute($Month, $Day) or die "Unable to execute qu
+ery : $DBI::errstr";
[download]