CGI executing a SQL statement

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

I've been searching for days and can't figure out how to do this.

I've got a script producing a form using CGI:

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',
);
$today = 'somedate'

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

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;
    } 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]

This is the SQL statement I want it to execute:

 #     $SqlStatement = "SELECT * FROM Pipeline WHERE Publish<='$today'
+ AND Expiry>='$today' AND $dept='Must Read'";
[download]

My problem is connecting to the database and then executing the SQL statement from within the CGI. I can't use DBI or anything wonderful like that, so I'm stuck with Win32::0DBC. I just can't figure out how to build this into the CGI way of laying stuff out. My usual way of connecting like this:

  if (!($db=new Win32::ODBC($DSN))) {
   print "Error connecting to Database\n";
   print "Error: " . Win32::ODBC::Error() . "\n";
  }
  $SqlStatement = "SELECT * FROM BulletinGroups";
  if ($db->Sql($SqlStatement)) {
    print "SQL failed.\n";
    print "Error: " . $db->Error() . "\n";
  } else {
    while($db->FetchRow()) {
    Do something here
}
[download]

just won't work. ($DSN is defined in settings.pl)

I'm totally confused.

Any help would be gratefully appreciated.

Comment on CGI executing a SQL statement Select or Download Code

Replies are listed 'Best First'.
Re: CGI executing a SQL statement by Mr. Muskrat (Canon) on Apr 18, 2006 at 15:00 UTC
"just don't work" isn't going to help us help you. You've told us why your doing things this way and what code you used. You haven't told us what exactly is wrong though (error messages for example). Start out by reading How (Not) To Ask A Question then come back and give us more information. I suspect that you already have all the information you need to figure this out for yourself though.	[reply]
Re^2: CGI executing a SQL statement by kjg (Sexton) on Apr 18, 2006 at 15:11 UTC
OK. If I try and put this code: $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<='$tod +ay' 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/ +$folderday/$Data{'Ref'}\" target=\"main\"><strong><font face=\"Arial\ +">$Data{'Title'}</a></font>Theme: $Data{'Theme'}</li>"; } } $db->Close(); } [download] In after this statement in the CGI that I described above: `$cgi->p("$dept was received."),` [download] I just get compilation errors and random messages about syntax errors. Why?	[reply] [d/l] [select]
Re^3: CGI executing a SQL statement by Mr. Muskrat (Canon) on Apr 18, 2006 at 15:33 UTC
"I just get compilation errors and random messages about syntax errors. Why?" They aren't random at all. Read them. They will tell you where the problem lies. You probably want to add code after a statement instead of in the middle as you said you did: "If I try and put this code... In after this statement in the CGI that I described above... I just get compilation errors and random messages about syntax errors." You probably also want to terminate your statements with semicolons instead of not terminating them at all or doing so with commas. `$today = 'somedate'; # was: $today = 'somedate' # snip 8< $db = new Win32::ODBC("$DSN"); # was: $db = new Win32::ODBC("$DSN"),` [download] updated a few times within the first several minutes after posting since i seemed to miss a few things.	[reply] [d/l]
Re^3: CGI executing a SQL statement by CountZero (Bishop) on Apr 18, 2006 at 19:58 UTC
Your "database" code has all the looks of a badly executed "cut-and-paste" job. It might have worked in another script, but it is obviously broken in this script. Unless you have predeclared (using "`my`") all variables in this script somewhere else, Perl will keep complaining since you have asked it to do so by using "`use strict;`". CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply] [d/l] [select]
Re^3: CGI executing a SQL statement by Anonymous Monk on Apr 18, 2006 at 15:47 UTC
Hi, it's me again, Anonymonk. I gave you this CGI program a couple of days ago: Re: Drop down and display. You just said: I just get compilation errors and random messages about syntax errors. These errors are not random! They were generated for a reason! Why? You are supposed to read them up in your favourite Perl manual and act accordingly. We can't help as we don't know which one you got. If you haven't bought a book yet, perhaps perldiag helps. If you can't make heads or tails out of them, come back here with the exact wording ('cause The Error Message Is God™) and say so; but don't be so terribly lazy, mon.	[reply]