Perl, MS Access and searching with dates?

[Masereth]

Hi. I've looked everywhere and tried an enourmous amount of different syntaxes for this, but I keep getting an error when trying to search my database table using a date.

use DBI;

my $dbh;

sub initialise {
	#open connection to Access database
	$dbh = DBI->connect("dbi:ODBC:TheatreWol", {AutoCommit => 1}) || die "Couldn't connect to DB\n";
}

initialise();
print "Enter command (search, quit): ";
$opt = <STDIN>;
chomp($opt);

while ($opt ne "quit") {
	if ($opt ne "search") {
		print "Invalid option - (search, quit):";
		$opt = <STDIN>;
		chomp($opt);
		next;
	}

	#prepare and execute SQL statement
	$sqlstatement="SELECT * FROM TheatreWol2007Presentations WHERE startdate=2007-02-01";
	$sth = $dbh->prepare($sqlstatement);
	$sth->execute || die "Could not execute SQL statement ... maybe invalid?";

	#output database results
	while (@row=$sth->fetchrow_array)
	{ print "@row\n" }

	print "Enter command (search, quit): ";
	$opt = <STDIN>;
	chomp($opt);
}

I've tried using ' ' around the date, I've tried using # # around the date, I've tried change the format (dd-mm-yyyy, mm-dd-yyyy). I've tried using / instead of - and nothing seems to work. I get this error message when running:

DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. (SQL-07002?)(DBD: st_execute/SQLExecute err=-1) .

Any help would be greatly appreciated.

[Masereth]

Apologies for not having a code. I'm a bit flustered and in a rush to get this done...

Never mind my question though, it was my own tiredness that caught up with me.. me fields is startseason and not startdate.

Time to be shot...

[miller]

Hi Masereth,

I'm glad you figured out your own problem. Take this as a lesson that you should always try out your queries at a sql prompt before assuming that they are working right within perl code.

Also, I took a quick moment to reformat your code and add better error messages for your database operations.

use DBI;

our $dbh;

sub initialise {
	#open connection to Access database
	$dbh = DBI->connect("dbi:ODBC:TheatreWol", {AutoCommit => 1}) or die "Couldn't connect to DB\n";
}

initialise();

for (;;) {
	print "Enter command (search, quit): ";
	my $opt = <STDIN>;
	chomp($opt);

	if (! grep {$opt eq $_} qw(search quit)) {
		print "Invalid option - $opt\n";
		next;
	}
	
	last if $opt eq 'quit';
	
	if ($opt eq 'search') {
		my $sql = "SELECT * FROM TheatreWol2007Presentations WHERE startseason=2007-02-01";
		$sth = $dbh->prepare($sql);
		$sth->execute or die $dbh->errstr;

		#output database results
		while (my @row = $sth->fetchrow_array) {
			print "@row\n"
		}
		
		$sth->finish;
	}
}

- Miller