Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    1

    Unanswered: Perl with Access Database

    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");


    }

  2. #2
    Join Date
    Sep 2002
    Location
    Germany, near Aachen
    Posts
    120
    It seems that your statement is prepared but not executed.
    This may be because DBD::ODBC delays the prepare until the execute.

    You should check $sth->err and $sth->errstr after the execute.

    For more information on errorhandling refer to the DBI documentation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •