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,079 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
3 Comments
 
LVL 39

Assisted Solution

by:Adam314
Adam314 earned 20 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sql server lock cursor 13 39
ORA-12560: TNS:protocol adapter error 8 41
SQL Help - 12 34
SQL Transaction logs 8 6
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now