Perl with Access Database [Archive]

dmarie

03-06-03, 16:45

I'm very new to Perl and am trying to set up Perl with an Access database. I get 2 errors. I am using a NT server. Any help is greatly appreciated...

1.DBD::ODBC::st fetchrow failed: (DBD: no select statement currently executing err=-1) at c:\inetpub\wwwroot\Mia\cgi-bin\jobs1.pl line 231.
2.Use of uninitialized value in print at c:\inetpub\wwwroot\Mia\cgi-bin\jobs1.pl line 146.

Here is my code

#!/usr/bin/perl -w
use strict;
use CGI qw/:standard/;
use DBI;
$|++;

# Config
my $TITLE = 'IPM Job Search';
my $APPLY_URL = 'http://localhost/cgi-bin/ipm/chap11/resume2.cgi';

print header,
start_html(-title=>$TITLE, -bgcolor=>'white');

if( !param() ) {
print_form();
} elsif ( param('JobID') ) {
get_job( param('JobID') );
} else {
print_results();
}

print end_html;

########################################

sub print_form {

# Connect to database
my $dbh = DBI->connect('dbi:ODBC:jobs') or
die "Cannot connect to database: $!\n";

# Get locations
my $sql = "SELECT * FROM locations";
my $sth = $dbh->prepare($sql);
$sth->execute;

my %location_labels;
my @locations;
while (my ($id, $location) = $sth->fetchrow) {
$location_labels{$id}=$location;
push(@locations, $id);
}

$sth->finish;

# Get categories
$sql = "SELECT * FROM categories";
$sth = $dbh->prepare($sql);
$sth->execute;

my %category_labels;
my @categories;
while( my ($id, $category) = $sth->fetchrow) {
$category_labels{$id}=$category;
push(@categories, $id);
}

$sth->finish;
$dbh->disconnect;

print h1($TITLE),
hr;

print start_form,
table({-border=>0},
Tr(td([b('Location')])),
Tr(td([scrolling_list(-name=>'location',
-value=>\@locations,
-size=>5,
-multiple=>1,
-labels=>\%location_labels)])),

Tr(td([b('Category')])),

Tr(td([scrolling_list(-name=>'category',
-value=>\@categories,
-size=>5,
-multiple=>1,
-labels=>\%category_labels)])),

Tr(td([b('Keywords')])),
Tr(td([textfield(-name=>'keywords',-size=>50)]))
);

print submit,
reset;

}

sub print_results {
my $dbh;
my $sql;
my $sth;

# Get form variables
my @locations = param('location');
my @categories = param('category');
my $keywords = param('keywords');

# Clear arrays if 'Any' is selected
@locations = () if grep /\b1\b/, @locations;
@categories = () if grep /\b1\b/, @categories;

# Connect to database
$dbh = DBI->connect('dbi:ODBC:jobs') or
die "Cannot connect to database: $!\n";

# Get location name
my @location_names;
my $location_names;
if (@locations) {
foreach my $location (@locations) {
$sql = "SELECT location FROM locations WHERE id=$location";
$sth = $dbh->prepare($sql);
$sth->execute;

my $location_name = $sth->fetchrow;
push (@location_names, $location_name);
}
$location_names = join(', ', @location_names);
$sth->finish;
} else {
$location_names = 'Any';
}

# Get category name
my @category_names;
my $category_names;
if (@categories) {
foreach my $category (@categories) {
$sql = "SELECT category FROM categories WHERE id=$category";
$sth = $dbh->prepare($sql);
$sth->execute;

my $category_name = $sth->fetchrow;
push (@category_names, $category_name);
}
$category_names = join(', ', @category_names);
$sth->finish;
} else {
$category_names = 'Any';
}

# Print output to user
print h1($TITLE),
hr,
h2('Search Criteria'),
br,
b('Location: '),
$location_names,
br,
b('Category: '),
$category_names,
br,
b('Keywords: ' ),
$keywords,
p,
hr,
h2('Search Results');

# Find jobs that match criteria
$sql = "SELECT * FROM jobs ";

# Check if we need a WHERE
if (@locations || @categories || $keywords) {
$sql .= "WHERE ";

# Location
if (@locations == 1) {
$sql .= "location=$locations[0]";
} elsif (@locations > 1) {
$sql .= '(';
for (my $i = 0; $i <= $#locations; $i++) {
if ($i < $#locations) {
$sql .= "location=$locations[$i] OR ";
} else {
$sql .= "location=$locations[$i])";
}
}
}

if (@categories == 1) {
if (@locations) {
$sql .= ' AND ';
}
$sql .= "category=$categories[0]";
} elsif (@categories > 1) {
if (@locations) {
$sql .= ' AND ';
}
$sql .= '(';
for (my $i = 0; $i <= $#categories; $i++) {
if ($i < $#categories) {
$sql .= "category=$categories[$i] OR ";
} else {
$sql .= "category=$categories[$i])";
}
}
}

if ($keywords) {
my @keywords = split(/\s+/, $keywords);
if (@locations or @categories) {
$sql .= ' AND ';
}
$sql .= '(' if @keywords > 1;
for (my $i = 0; $i <= $#keywords; $i++) {
if ($i < $#keywords) {
$sql .= "keywords LIKE '%$keywords[$i]%' OR ";
} else {
$sql .= "keywords LIKE '%$keywords[$i]%'";
$sql .= ')' if @keywords > 1;
}
}
}

}

$sth = $dbh->prepare($sql);
$sth->execute;

# Print results
print "<TABLE BORDER=0 CELLSPACING=15>";
print "<TH>Date</TH>";
print "<TH>Location</TH>";
print "<TH>Job Title</TH>";
while( my($id, $title, $cat, $date, $loc_id, $desc,
$sal) = $sth->fetchrow ) {
print "<TR>";
$sql = "SELECT location FROM locations WHERE id=$loc_id";
$sth = $dbh->prepare($sql);
$sth->execute;
my $loc = $sth->fetchrow;
$sth->finish;
print "<TD>$date</TD>";
print "<TD>$loc</TD>";
my $script = url();
$script .= "?JobID=$id";
print "<TD><A HREF=\"$script\">$title</A></TD>\n";
print "</TR>";
}
print "</TABLE>";
print hr;

$sth->finish;
$dbh->disconnect;

}

sub get_job {
my $job_id = shift;

my $dbh;
my $sql;
my $sth;

# Connect to database
$dbh = DBI->connect('dbi:ODBC:jobs') or
die "Cannot connect to database: $!\n";

# Get locations
$sql = "SELECT j.title, l.location, j.description, j.salary " .
"FROM jobs j, locations l " .
"WHERE j.id=$job_id AND j.location=l.id";

$sth = $dbh->prepare($sql);
$sth->execute;

my ($title,$location,$description,$salary) = $sth->fetchrow;

$sth->finish;

if (!$title) {
print "Invalid JobID<BR>\n";
$dbh->disconnect;
exit;
}

$dbh->disconnect;

# Print the job information
print h1($title),
h2($location),
hr,
p($description),
hr,
b('Salary: '),
$salary,
hr,
a({-href=>"$APPLY_URL?JobID=$job_id"},"Apply Online");

}