Solved

HTTP Error 502.2 - Bad Gateway in Perl CGI script when accessing SQL Stored Proc with Parameters

Posted on 2009-05-20
3
1,087 Views
Last Modified: 2013-12-07
Hello,

I am currently involved in a project that requires me to send some data from a SQL database in a Perl script file.

Having never delt with Perl this is proving slightly problematic as I don't really know what I am doing but I am figuring it out slowly.

So far I have managed to get the connection to work correctly and can run basic SELECT commands using the URL parameters. I have also managed to get a SQL Stored Proc to run fine with out any problems that contained a simple SELECT * FROM Table statement.

The problem I am having now is adding the Parameters to the file as I keep getting a 502.2 error and I can't work out why.

I have attached a code snippet below and hopefully it is something glaringly obvious but as I have no Perl experience I have no idea. I have tried using some example in tutorials but I keep getting the error.

Lee
#!/usr/bin/perl
use CGI qw(param);
my $Model = param("Model");
 
print "Content-type: text/html\n\n";
 
print "<html><body>";
 
print "<br><p><center><h2>Data Return (CGI)</h2></center></p>";
 
print "<p>Start Of Data for Model: $Model</p>";
 
print "<p>";
 
use strict;
use DBI;
my $db_user = "user";
my $db_pass = "password";
if (my $dbh = DBI-> connect('dbi:ODBC:perltest',$db_user,$db_pass)) {
 
 
   my $sql = "declare @Model char(6) exec mtkSP_PerlTest @Model =123456";
 
   if (my $sth = $dbh->prepare($sql)) {
 
       if ($sth->execute()) {
 
           while (my @row = $sth->fetchrow_array) {
 
               print join(", ", @row), "!";
           }
       }
       else {
 
           print "Execute failed : ".$dbh->errstr;
       }
   }
   else {
 
       print "Prepare failed : ".$dbh->errstr;
   }
}
else {
 
   print "Failed to login";
 
}
 
print "</p>";
 
print "<p>End Of Data</p>";
print "</body></html>\n";
 
exit;

Open in new window

0
Comment
Question by:Lee Redhead
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 39

Assisted Solution

by:Adam314
Adam314 earned 20 total points
ID: 24436182
What web server are you using?  Is there anything in your error log?
Try adding this after your line 2... what is the output:
use CGI::Carp 'fatalsToBrowser';

Open in new window

0
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 480 total points
ID: 24436557
I assume the "declare @Model " is meant to be parsed by SQL Server, however, @Model will be interpolated by Perl as a list variable. You need to either escape it, or use single quotes in the SQL string.

# Try single quotes

my $sql = 'declare @Model char(6) exec mtkSP_PerlTest @Model =123456';

#OR escape the @

my $sql = "declare \@Model char(6) exec mtkSP_PerlTest \@Model =123456";
0
 

Author Closing Comment

by:Lee Redhead
ID: 31583658
mrjoltcola - That worked perfectly, brought back the data as expected.

adam314 -Even though it didn't answer the question being able to see what has gone wrong is really useful.
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question