Re^3: Perl CGI and SQL statements

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?

Comment on Re^3: Perl CGI and SQL statements Download Code

Replies are listed 'Best First'.
Re^4: Perl CGI and SQL statements by cdarke (Prior) on Apr 19, 2006 at 11:10 UTC
if ($dept='CA') is an assignment, not a comparisson. In addition you should use 'eq' for a texual comparison (== for numeric). So: if ($dept eq 'CA') is the way to go. HOWEVER, you have an imbedded 'if' inside a print staement, which is the reason for the syntax error. I suggest you break it up into two print statements.	[reply]
Re^5: Perl CGI and SQL statements by kjg (Sexton) on Apr 19, 2006 at 12:16 UTC
Thanks, The snippet now looks like this: my $db = new Win32::ODBC('$DSN'); if (defined $dept) { if (exists $labels{$dept}) { # FIXME # untaint $dept and put it into database print $cgi->start_html, $cgi->p("$dept was received."); $cgi->end_html; if ($dept eq "CA") { if (!($db=new Win32::ODBC('$DSN'))) { $cgi->p("Error connecting to Database"); $cgi->p("Error: " . Win32::ODBC::Error ) . " "); } my $SqlStatement ='SELECT * FROM Pipeline WHERE Publish<="19 Apr 2006" + AND Expiry>="19 Apr 2006" AND CA="Must Read"'; print $SqlStatement; $db->Sql('$SqlStatement'); [download] I'm still getting this error: `:\Perlcgi\Pipeline\cgi.pl: Can't call method "Sql" on an undefined val +ue at E:\Perlcgi\Pipeline\cgi.pl line 51.` [download] Line 51 is: `$db->Sql('$SqlStatement');` It's like it won't substitute the contents $SqlStatement or something? Why is that? Incidentally, if I drop in the SQL Statement in that Line 51 code it still gives the same error message. Does CGI need me to express the $db->Sql() differently?	[reply] [d/l] [select]
Re^6: Perl CGI and SQL statements by osunderdog (Deacon) on Apr 19, 2006 at 13:44 UTC
`my $db = new Win32::ODBC('$DSN');` Firstly, you need to either remove the tic marks or use double quotes. The way this is written, you're sending exactly `$DSN` as a string. If you want to send what `$DSN` is set to, then use: `my $db = new Win32::ODBC($DSN);` Secondly, this probably explains the next problem... `$db` is undefined because the instance creation failed. In other words, after you do this: `my $db = new Win32::ODBC($DSN);` You should check `$db` to verify that the object was created successfuly. If it could not connect, then `$db` will be undefined. The error you are seeing is because you can't call the function `Sql` on an undefined `$db` variable. Seriously, a little time with the perl debugger, now that you've got compilable code, would help you understand what's going on. Neat Debugger tricks Re: Linux vs. Windows for Learning Perl perl debugger Hazah! I'm Employed!	[reply] [d/l] [select]
Re^6: Perl CGI and SQL statements by ptum (Priest) on Apr 19, 2006 at 13:33 UTC
In order for your variables to be interpolated, you must used double-quotes instead of single quotes (or none at all in this case would do as well). `$db->Sql($SqlStatement);` [download] or possibly: `$db->Sql("$SqlStatement");` [download] Either of those ought to work. I take it there is no return value to the Sql method, or perhaps you should be capturing that return? No good deed goes unpunished. -- (attributed to) Oscar Wilde	[reply] [d/l] [select]
Re^4: Perl CGI and SQL statements by wfsp (Abbot) on Apr 19, 2006 at 10:43 UTC
`$cgi->p("$dept was received."),` [download] Change the comma to a `;` Also: `if ($dept eq'CA'){` [download] is better than: `if ($dept ='CA'){` [download] If you had warnings (-w) enabled that would have been flagged up too. Update: `==` corrected to `eq` as pointed out by cdarke below	[reply] [d/l] [select]
Re^5: Perl CGI and SQL statements by kjg (Sexton) on Apr 19, 2006 at 11:26 UTC
Right, I've had a good mess with this now and here's my code: use strict; use diagnostics; #use warnings; 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'); 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") { my $db = new Win32::ODBC('$DSN'); if (!($db=new Win32::ODBC('$DSN'))) { $cgi->p("Error connecting to Database"); $cgi->p("Error: " . Win32::ODBC::Error() . " "); } my $SqlStatement ='SELECT * FROM Pipeline WHERE Publish<="19 Apr 2006" + AND Expiry>="19 Apr 2006" AND CA="Must Read"'; print $SqlStatement; $db->Sql('$SqlStatement'); } $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; }; This is what prints out: <code> CA was received. SELECT * FROM Pipeline WHERE Publish<="19 Apr 2006" AND Expiry>="19 Ap +r 2006" AND CA="Must Read" Software error: Can't call method "Sql" on an undefined value at E:\Perlcgi\Pipeline\c +gi.pl line 50. [download] Now I really don't get it. Interestingly it seems to do the database connection no matter what the value of $dept.	[reply] [d/l]
Re^6: Perl CGI and SQL statements by osunderdog (Deacon) on Apr 19, 2006 at 11:56 UTC
`my $db = new Win32::ODBC('$DSN');` The declaration of `$db` is too low. The variable is being declared in an if check and it's being referenced in an outer scope. Declare `$db` higher up in a block where it's visible to both the case where you assign it and where you reference it. The outer scope would work, that's where you've defined the `$cgi` variable. Hazah! I'm Employed!	[reply] [d/l] [select]