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

I tried to ask this yesterday succinctly but it didn't seem to work. I have a Perl script with CGI in it but as part of that I want to query a SQL2000 database. This is my code: 
use strict;
use diagnostics;

use CGI qw(:standard);
use CGI::Carp qw/fatalsToBrowser/;

use Win32::ODBC;
use lib '/perlcgi/settings';
require 'settings.pl';  # Included configuration file which contains g
+lobal variables

my %labels = (
    MA    => 'Mortgage Advisers',
    CA    => 'Customer Advisers',
    BM    => 'Branch Management',
    HO    => 'Head Office',
    Acc    => 'Accord',
    MSa    => 'MCC Sales',
    MSe    => 'MCC Service',
);

my $cgi    = CGI->new;
print $cgi->header('text/html');
my $dept    = $cgi->param('department');
my $today = 'somedate';

if (defined $dept) {
    if (exists $labels{$dept}) {
        # FIXME
        # untaint $dept and put it into database
        print
            $cgi->start_html,
            $cgi->p("$dept was received."),
            $db = new Win32::ODBC("$DSN");
            if ($dept ='CA'){
            if (!($db=new Win32::ODBC($DSN))) {
            print "Error connecting to Database\n";
           print "Error: " . Win32::ODBC::Error() . "\n";
            }
       $SqlStatement = "SELECT * FROM Pipeline WHERE Publish<='$today'
+ AND Expiry>='$today' AND CA='Must Read'";
         if ($db->Sql($SqlStatement)) {
         print "SQL failed.\n <br>$SqlStatement\n";
            print "Error: " . $db->Error() . "\n";
           }
         else {
          while($db->FetchRow()) {
            %Data = $db->DataHash();
          print "<li><a href=\"http://wwwybs/ybsone/Pipeline/master/$D
+ata{'Ref'}\" target=\"main\"><strong><font face=\"Arial\">$Data{'Titl
+e'}</a></font>Theme: $Data{'Theme'}</li>";
           }
          }
        $db->Close();
        } #End of IF CA=statement
        $cgi->end_html;
    } else {
        print
            $cgi->start_html,
            $cgi->p("$dept was received, but is not a valid department
+ name."),
            $cgi->end_html;
    };
} else {
    print
        $cgi->start_html,
        $cgi->start_form(
            -action    => $cgi->script_name,
        ),
        $cgi->popup_menu(
            -name        => 'department',
            -values        => [keys %labels],
            -labels        => \%labels,
        ),
        $cgi->submit,
        $cgi->end_form,
        $cgi->end_html;
};

[download]
I'm getting this error message: 
Software error:
Execution of E:\Perlcgi\Pipeline\cgi.pl aborted due to compilation err
+ors. 
For help, please send mail to this site's webmaster, giving this error
+ message and the time and date of the error. [Wed Apr 19 08:51:44 200
+6] E:\Perlcgi\Pipeline\cgi.pl: Execution of E:\Perlcgi\Pipeline\cgi.p
+l aborted due to compilation errors.

[download]
It makes no sense to me and there's nothing extra in the Perl error log. I think I haven't phrased the whole section that is trying to connect to the database right - If I comment everything out and just leave in the databse connection and first IF statement, it still gives the same error. Help!

Replies are listed 'Best First'.
Re: Perl CGI and SQL statements
by wfsp (Abbot) on Apr 19, 2006 at 09:13 UTC
    Mr. Muskrat explained that: 
    $db = new Win32::ODBC("$DSN"),

    [download]
    needed to be: 
    $db = new Win32::ODBC("$DSN");

    [download]
    You've posted the code again with the same error. You could at least fix that bit.

    use diagnostics; can be very useful. But if you have many errors it can be a bit overwhelming! I would comment it out while you eliminate the obvious errors. I added use warnings; added my to a few variable declarations and was able to get the thing to compile. Which is a start. Difficult to help you much more because of your data source.

    Hope that helps.

    Update:

    $cgi->p("$dept was received."),

    [download]
    should be: 
    $cgi->p("$dept was received.");

    [download]
[reply]
[d/l]
[select]
      Um, looking above that is a ';' not a ',' so now I'm a bit perplexed.

      I don't have 'use warnings' in my perl installation :(

[reply]
        It was the line before it. See my updated post.

        If your perl is earlier than 5.6.x add  -w to the end of the shebang line.

[reply]
[d/l]
        Right I've commented everything out and found that the problem seems to hinge on my IF statement. This is my code now: 
        use strict;
use diagnostics;

use CGI qw(:standard);
use CGI::Carp qw/fatalsToBrowser/;

use Win32::ODBC;
use lib '/perlcgi/settings';
require 'settings.pl';  # Included configuration file which contains g
+lobal variables

my %labels = (
    MA    => 'Mortgage Advisers',
    CA    => 'Customer Advisers',
    BM    => 'Branch Management',
    HO    => 'Head Office',
    Acc    => 'Accord',
    MSa    => 'MCC Sales',
    MSe    => 'MCC Service',
);

my $cgi    = CGI->new;
my $dept    = $cgi->param('department');
my $folderday = '19Apr';
my $SqlStatement ='SELECT * FROM Pipeline WHERE Publish<="$today" AND 
+Expiry>="$today" AND CA="Must Read"';
my $today = '19 Apr 2006';

print $cgi->header('text/html');


if (defined $dept) {
    if (exists $labels{$dept}) {
        # FIXME
        # untaint $dept and put it into database
        print
            $cgi->start_html,
            $cgi->p("$dept was received."),
            if ($dept ='CA'){
          } # end of IF CA=
            $cgi->end_html;

    } else {
        print
            $cgi->start_html,
            $cgi->p("$dept was received, but is not a valid department
+ name."),
            $cgi->end_html;
    };
} else {
    print
        $cgi->start_html,
        $cgi->start_form(
            -action    => $cgi->script_name,
        ),
        $cgi->popup_menu(
            -name        => 'department',
            -values        => [keys %labels],
            -labels        => \%labels,
        ),
        $cgi->submit,
        $cgi->end_form,
        $cgi->end_html;
};

        [download]
        The error message says there are compilation errors and syntax errors at this bracket:

        } # end of IF CA=

        but I think it doesn't like:

        if ($dept ='CA'){

        What's wrong with that?

[reply]
[d/l]
Re: Perl CGI and SQL statements
by planetscape (Chancellor) on Apr 19, 2006 at 09:12 UTC
[reply]

Back to Seekers of Perl Wisdom