Rachel has asked for the wisdom of the Perl Monks concerning the following question: 

#!c:/perl/bin/perl.exe -w

    use strict;
    use DBI;
    use CGI;
  
 
    #open connection to Access database
    my $dbh = DBI->connect("dbi:ODBC:directory",
    { 'AutoCommit' =>1, 'RaiseError' =>1}) ||
           
    die "Error connecting: '$DBI::errstr'";

    # setup CGI handle
    my $cgi = new CGI;

    # start HTML

    print $cgi->header . $cgi->start_html('Drive');
      
    my $hash ={ Initiative=>'DriveInitiative', 
            ResOrientation => 'DriveResOrient',
            Creativity => 'DriveCreativity',
            ChangeOrientation => 'DriveChangeOrient',
            DecisionMaking => 'DriveDecisionMake',
            SelectLevel => 'ResLevel',
            ManagesPeople => 'ResManage'};

    my @clauses = ();
       foreach my $checkbox (keys %$hash) {
       push @clauses, $hash->{$checkbox} if validate($cgi->param($chec
+kbox));
       }

      
    my $where_clause = join(' and ',map($_.= ' = "on"', @clauses));

    my $sql = "SELECT ResType, ResLevel, ResManage, Details, Length, S
+ource, Cost, FurtherDetails 
          FROM ResourceSettings, Resources
          WHERE ((ResourceSettings.ResID = Resources.ResID) and
                 ($where_clause))
          if     ($where_clause)
          ORDER BY order by ResType, ResLevel, ResManage, 
                Details, Length, Source, Cost, FurtherDetails";

    my $sth = $dbh->prepare($sql); 
       $sth->execute || 
    die "Could not execute SQL statement ... " . $dbh->errstr;

        
        my $rows = $dbh->selectall_arrayref($sql) || die $dbh->errstr;
        if (@$rows) {
          print "<table border=1 cellspacing=0 cellpadding=3><tr>" .
                "<th>Type</th><th>Level</th><th>Manage</th><th>Details
+</th><th>Length</th><th>Source</th><th>Cost</th><th>Further Details</
+th></tr>";
          foreach my $row (@$rows) {
            print "<tr><td>" . join ("</td><td>", @$row) . "</td></tr>
+\n";
          }
          print "</table>\n";
          }
          else {
          print "<p><i>No matches found</i></p>\n";
         }
        
 
      
    # disconnect from database
    $dbh->disconnect();
    exit(0);

    # validate user input 
      sub validate {
        my $string = shift;
        # get rid of all non-letter, non-numerical characters and perc
+ents
        $string =~ s/[^A-Za-z0-9%]//g;
        return $string;
      }

[download]

update (broquaint): title change (was Update on checkbox queries- revamped code and STILL getting blank screen..........Can anyone help?)

Replies are listed 'Best First'.
Re: Update on checkbox queries- revamped code and STILL getting blank screen..........Can anyone help?
by Abigail-II (Bishop) on Nov 21, 2002 at 10:53 UTC
    So, what's does the error log say?

    Abigail

[reply]
      This is what the error log said. Any help would be greatly appreciated:)

      Software error:

      Can't call method "execute" on an undefined value at line 51.

      For help, please send mail to this site's webmaster, giving this error message and the time and date of the error.

[reply]

        The only occurance of execute in the script is 

        $sth->execute ||
   die "Could not execute SQL statement ... " . $dbh->errstr;

        [download]
        I hope this is line 51.

        This seems a bit odd to me, as you ought to be finding that error string in the log file. But the implication is clearly that $sth is undefined. The only way this can be happening is if the prepare on the previous line has failed. And you're not error trapping that.

        (I no nothing about odbc, but the line if     ($where_clause) in the middle of the sql statement looks odd to me)

        And I'm confused about the error trapping anyway: you've set RaiseError to 1, so every statement ought to be error trapped by DBI anyway. Which would explain why you don't see the "Could not execute SQL statement" in the log: DBI has died before your code gets a chance to. But it doesn't explain why the prepare doesn't result in death. Odd.

        --
        Tommy
        Too stupid to live.
        Too stubborn to die.

[reply]
[d/l]
[select]

        That says that $sth is undefined. In the line:     my $sth = $dbh->prepare($sql); the right hand side is evaluating to undef. Either prepare() is failing, or $dbh itself is undefined. Try adding a die clause: 

            my $sth = $dbh->prepare($sql)
        or die "No sth: '$DBI::ErrStr'", $!;

        [download]
        With luck, you'll get something telling what the database doesn't like.

        Do you see a pattern forming here? Always check for errors when you go to the system!

        After Compline,
        Zaxo

[reply]
[d/l]
[select]
Re: Checkbox Query Revisited
by dingus (Friar) on Nov 21, 2002 at 11:34 UTC
    A bit more info might help...
    However here are some comments on what I see

    In general a cgi script has 3 separate phases. You need to debug each and they can be developed sort of separately.

    Phase 1 is the form layout - in other words the request screen where you ask the user to enter what he is looking for. This can be completely separate script (or even static HTML) or it can be a subroutine in the same script as phases 2 and 3. This is where you create forms, add checkboxes etc etc. and if you don't have a phase 1 then you can rarely do anything useful.

    Phase 2 is validating and processing the input received from the user, this includes cookies, extended path info and the query_string as well as any POSTed parameters. Phase 2 means making sure that you have got sane defaults, that the defaults get replaced only with valid alternatives and that any invalid alternatives get flagged as errors.

    Phase 3 then takes the output of phase 2 and runs whatever work you want to do on it and prints the results nicely for the user. During development it is quite easy to create a dummy phase 3 only script which prints results based on input you set in the script.

    When you say you get blank screens and have problems with checkboxes it is unclear whether you are referin to phase 1, phase 2 or phase 3. A blank screen in Phase 1 implies that you are not correctly doing your 
    print $cgi->header, $cgi->start_html, $cgi->start_form etc.

    [download]
    A blank screen for the results may be because of a processing/validating error in phase 2. i.e. checboxes are not beong recognised as being set or it could be a bug in your phase 3 code that results in no DB hits no matter what happens. If you make a debug version of the script that prints the output of phase 2 before entering phase 3 you can see which one is your problem (hint Data::Dumper and s/</&lt;/g are useful things for debugging)

    Finally I have noticed that if you have a checkbox parameter at phase 2 then CGI.pm returns 'on' if the checkbox is set but the parameter is completely omitted if the checkbox is clear. Thus you may need to have some defaul "off" lines that get replaced if a parameter exists and is set to 'on'.

    Dingus

    Enter any 47-digit prime number to continue.
[reply]
[d/l]
      Sorry if I appear ignorant but I only started learning Perl 2 weeks ago, and it has been a steep learning curve so my understanding of how all the modules relate to each other is not as good as it could be. I would go away and research it a lot more thoroughly if I wasn't under quite a lot of time pressure and I haven't just come on here looking for an easy solution either. I have tried everything I can think of and I can use a text box to query my database but I am having a lot of difficulty using checkboxes. I know the values are being passed to the script as they are in the url and I know there is nothing wrong with the html output because I tested it with a more simple query. This is the html form; 
      <HTML><HEAD>
<BODY>
<FORM action=
method=get>
<H3>Select Drive Elements</H3>
<P><INPUT type=checkbox value=Initiative name=drive>Initiative</P>
<P><INPUT type=checkbox value=ResOrientation name=drive>Results Orient
+ation</P>
<P><INPUT type=checkbox value=Creativity name=drive>Creativity</P>
<P><INPUT type=checkbox value=ChangeOrientation name=drive>Change Orie
+ntation 
</P>
<P><INPUT type=checkbox value=DecisionMaking name=drive>Decision 
Making</P>Select Level: <SELECT name=SelectLevel> <OPTION value=Experi
+enced 
  selected>Experienced<OPTION value=Introduction>Introduction</OPTION>
+</SELECT> 
Manages People: <SELECT name=ManagesPeople> <OPTION value=Yes 
  selected>Yes<OPTION value=No>No</OPTION></SELECT> 
<P><INPUT type=submit value=View name=Query></P></FORM></BODY></HTML>

      [download]
      This is the script which SHOULD process it; 
      #!c:/perl/bin/perl.exe -w

    use strict;
    use DBI;
    use CGI;
    #use CGI::Carp qw( fatalsToBrowser warningsToBrowser );
  
 
    #open connection to Access database
    my $dbh = DBI->connect("dbi:ODBC:directory",
    { 'AutoCommit' =>1, 'RaiseError' =>1}) ||
           
    die "Error connecting: '$DBI::errstr'";

    # setup CGI handle
    my $cgi = new CGI;

    # start HTML

    print $cgi->header . $cgi->start_html('Drive');
      
    my $hash ={ Initiative=>'DriveInitiative', 
            ResOrientation => 'DriveResOrient',
            Creativity => 'DriveCreativity',
            ChangeOrientation => 'DriveChangeOrient',
            DecisionMaking => 'DriveDecisionMake',
            SelectLevel => 'ResLevel',
            ManagesPeople => 'ResManage'};

    my @clauses = ();
       foreach my $checkbox (keys %$hash) {
       push @clauses, $hash->{$checkbox} if validate($cgi->param($chec
+kbox));
       }

      
    my $where_clause = join(' and ',map($_.= ' = "on"', @clauses));

    my $sql = "SELECT ResType, ResLevel, ResManage, Details, Length, S
+ource, Cost, FurtherDetails 
          FROM ResourceSettings, Resources
          WHERE ((ResourceSettings.ResID = Resources.ResID) and
                 ($where_clause))
          if     ($where_clause)
          ORDER BY order by ResType, ResLevel, ResManage, 
                Details, Length, Source, Cost, FurtherDetails";

    print $cgi->p($sql);

    my $sth = $dbh->prepare($sql); 
       $sth->execute || 
    die "Could not execute SQL statement ... " . $dbh->errstr;

        
        my $rows = $dbh->selectall_arrayref($sql) || die $dbh->errstr;
        if (@$rows) {
          print "<table border=1 cellspacing=0 cellpadding=3><tr>" .
                "<th>Type</th><th>Level</th><th>Manage</th><th>Details
+</th><th>Length</th><th>Source</th><th>Cost</th><th>Further Details</
+th></tr>";
          foreach my $row (@$rows) {
            print "<tr><td>" . join ("</td><td>", @$row) . "</td></tr>
+\n";
          }
          print "</table>\n";
          }
          else {
          print "<p><i>No matches found</i></p>\n";
         }
        
 
      
    # disconnect from database
    $dbh->disconnect();
    exit(0);

    # validate user input 
      sub validate {
        my $string = shift;
        # get rid of all non-letter, non-numerical characters and perc
+ents
        $string =~ s/[^A-Za-z0-9%]//g;
        return $string;
      }

      [download]
[reply]
[d/l]
[select]
        OK. I understand what is (or rather is not) happening. This DBI.pm documentation explains all the bits.

        Whats happening is that the call to my $sth = $dbh->prepare($sql); appears to be failing and hence you cannot run the immediate next command (sth->execute) because sth is not the correct object type.

        Why $dbh->prepare doesn't work is beyond me as I've never used DBI.pm but I suspect that is you add change the prepare line to my $sth = $dbh->prepare($sql) || die $dbh->errstr; you'll get a more helpful error message.

        PS I recommend uncommenting the use CGI:Carp line at the top of the script for debugging.

        Dingus

        Enter any 47-digit prime number to continue.
[reply]
[d/l]
[select]
Re: Checkbox Query Revisited
by shotgunefx (Parson) on Nov 21, 2002 at 11:10 UTC
    Ditto on Abigail-II's suggestion. Not looking at the error logs is like trying to divine the cause of an engine problem by looking at the outside of the car.

    Show some due diligence and you're apt to find you get much more help around here.
    Having said that, for testing, you can use CGI::Carp to see the errors in your browser. 
    use CGI::Carp qw(fatalsToBrowser warningsToBrowser);

    [download]
    Usually these types of problems are caused by something being printed (an error or warning) before the header is sent to the browser.

    -Lee

    "To be civilized is to deny one's nature."
[reply]
[d/l]

Back to Seekers of Perl Wisdom