CGI to MYSQL on IIS6 not working

115 100+

Hello all. This script I have did work on linux/apache and my service provider has told me the cgi-handler has been properly set up. I am able to get hello world test to display to a web page. My problem comes in when I try to connect to a mysql DB on IIS 6. My script reads as follows:

Expand|Select|Wrap|Line Numbers

 
use DBI qw(:sql_types);
 
$title = 'EnergyWise Device Test - Reading List';

$start = 40;
 
print <<ENDSTART;

Content-Type: text/html; charset=iso-8859-1
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html lang="en-US">

<head>

<title>$title</title>

</head>

<body>

<h1 style="font-size:1.2em">$title</h1>

ENDSTART
 
$dbName = "DBI:mysql:database";

$dbUser = "myuserid";

$dbPass = "mypass";

$numrows = 5;

$dbh = DBI->connect($dbName, $dbUser, $dbPass) || die "Die: $DBI::errstr\n";
 
    $sql = "select max(raw_id) from data";

    $sth = $dbh->prepare($sql) || die "prepare: $sql: $DBI::errstr";

    $result = $sth->execute() || print "execute: $sql: $DBI::errstr";

    @record = $sth->fetchrow();

    $maxid = @record[0];

    $sth->finish();

    print $maxid," rows found.  Last ", $numrows, " shown below:";

    $minid = $maxid-$numrows;

    $sql = "select * from data where raw_id > \"$minid\"";

    $sth = $dbh->prepare($sql) || die "prepare: $sql: $DBI::errstr";

    $result = $sth->execute() || print "execute: $sql: $DBI::errstr";

    while (@results = $sth->fetchrow() ) {

        print "<p>Row ";

        print @results[0];

        print ":<p>";

        print show(@results[1]);

        }

    $sth->finish();

    $dbh->disconnect();

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = gmtime(time);

$now = sprintf "%4d-%02d-%02dT%02d:%02dZ", 1900+$year,$mon+1,$mday,$hour,$min;

print p('Processed '.$now."\n");

print end_html;
 
sub show {

    $_[0] = escapeHTML($_[0]);

    $_[0] =~ s/\r\n/<br>/g;

    $_[0] =~ s/\n/<br>/g;

    return $_[0]; }

Logically to me the script should function. What I get in return from the web page is:

CGI Error
The specified CGI application misbehaved by not returning a complete set of HTTP headers.

Dec 10 '09 #1

Subscribe Post Reply

✓ answered by RonB

Here's an adjusted version, which still has room for improvment.

Expand|Select|Wrap|Line Numbers

 #!/usr/bin/perl
 
use strict;

use warnings;

use CGI qw(:standard);

use CGI::Carp qw(fatalsToBrowser warningsToBrowser);

use DBI qw(:sql_types);
 
print header();

warningsToBrowser(1);
 
my $dbName = "egwtest";

my $dbHost = "10.30.125.80";

my $dbUser = "************";

my $dbPass = "************";
 
my $dbh = DBI->connect("DBI:mysql:$dbName:$dbHost", $dbUser, $dbPass,

                       { RaiseError => 1 })

               or die "Die: $DBI::errstr\n";
 
# Prepare the SQL statement

my $sth= $dbh->prepare("SELECT VERSION()") or die $DBI::errstr;

# Send the statement to the server

$sth->execute();
 
my $numRows = $sth->rows; # This is not always accurate.

print "Rows returned: $numRows\n";
 
while ( my @row = $sth->fetchrow_array )

{

    print "@row\n";

}
 
$dbh->disconnect();

3485

RonB

589

Expert Mod 512MB

Add this line just above the use DBI statement.

Expand|Select|Wrap|Line Numbers

use CGI::Carp qw(fatalsToBrowser);

Then test the script and post back with the exact error message.

Dec 10 '09 #2

dbrewerton

115

100+

Expand|Select|Wrap|Line Numbers

 syntax error at \cgi-bin\newtest.cgi line 13, near "$dbHost "

syntax error at \cgi-bin\newtest.cgi line 18, near "DBI:"

Execution of \cgi-bin\newtest.cgi aborted due to compilation errors.

For help, please send mail to this site's webmaster, giving this error message and the time and date of the error.

I dumbed down the script to only use a small section of code. The code I'm using reads:

Expand|Select|Wrap|Line Numbers

 #!/usr/bin/perl -w

use CGI qw(:standard);

use CGI::Carp qw(fatalsToBrowser);

use DBI qw(:sql_types);
 
print <<ENDSTART;

Content-Type: text/html
 
<html>

ENDSTART
 
$dbName = "egwtest"

$dbHost = "10.30.125.80";

$dbUser = "************";

$dbPass = "************";

$numrows = 5;
 
$dbh = DBI->connect(DBI:mysql:database=$dbName:host=$dbHost,$dbUser,$dbPass) || die "Die: $DBI::errstr\n";
 
# Prepare the SQL statement

my $sth= $dbh->prepare("SELECT VERSION()") or die $DBI::errstr;

# Send the statement to the server

$sth->execute();

my $numRows = $sth->rows;

print "Rows returned: $numRows\n";

my @row;

while ( @row = $sth->fetchrow_array )

{

print "@row\n";

}
 
$dbh->disconnect();

Dec 10 '09 #3

dbrewerton

115

100+

I think it is the syntax of my connection string.

$dbh = DBI->connect(DBI:mysql:database="egwtest":host="10.30. 125.80",$dbUser,$dbPass) || die "Die: $DBI::errstr\n";

Dec 10 '09 #4

RonB

589

Expert Mod 512MB

That version also has compilation errors.

C:\TEMP>perl -c dbrewerton.pl
[Thu Dec 10 08:26:01 2009] dbrewerton.pl: Scalar found where operator expected at dbrewerton.pl line 13, near "$dbHost"
[Thu Dec 10 08:26:01 2009] dbrewerton.pl: (Missing semicolon on previous line?)
[Thu Dec 10 08:26:01 2009] dbrewerton.pl: Unquoted string "mysql" may clash with future reserved word at dbrewerton.pl line 18.
[Thu Dec 10 08:26:01 2009] dbrewerton.pl: Unquoted string "database" may clash with future reserved word at dbrewerton.pl line 18.

[Thu Dec 10 08:26:01 2009] dbrewerton.pl: Unquoted string "host" may clash with future reserved word at dbrewerton.pl line 18.
Status: 500
Content-type: text/html

<h1>Software error:</h1>
<pre>syntax error at dbrewerton.pl line 13, near "$dbHost "
syntax error at dbrewerton.pl line 18, near "DBI:"
dbrewerton.pl had compilation errors.
</pre>
<p>
For help, please send mail to this site's webmaster, giving this error message
and the time and date of the error.

</p>
[Thu Dec 10 08:26:01 2009] dbrewerton.pl: syntax error at dbrewerton.pl line 13, near "$dbHost "
[Thu Dec 10 08:26:01 2009] dbrewerton.pl: syntax error at dbrewerton.pl line 18, near "DBI:"
[Thu Dec 10 08:26:01 2009] dbrewerton.pl: dbrewerton.pl had compilation errors.

Take out the -w switch and add these to lins (pragmas).

Expand|Select|Wrap|Line Numbers

 use strict;

use warnings;

Those pragmas should be in every Perl script you write. They will help point out lots of mistakes/errors that can be difficult to track down otherwise.

Dec 10 '09 #5

RonB

589

Expert Mod 512MB

Here's an adjusted version, which still has room for improvment.

Expand|Select|Wrap|Line Numbers

 #!/usr/bin/perl
 
use strict;

use warnings;

use CGI qw(:standard);

use CGI::Carp qw(fatalsToBrowser warningsToBrowser);

use DBI qw(:sql_types);
 
print header();

warningsToBrowser(1);
 
my $dbName = "egwtest";

my $dbHost = "10.30.125.80";

my $dbUser = "************";

my $dbPass = "************";
 
my $dbh = DBI->connect("DBI:mysql:$dbName:$dbHost", $dbUser, $dbPass,

                       { RaiseError => 1 })

               or die "Die: $DBI::errstr\n";
 
# Prepare the SQL statement

my $sth= $dbh->prepare("SELECT VERSION()") or die $DBI::errstr;

# Send the statement to the server

$sth->execute();
 
my $numRows = $sth->rows; # This is not always accurate.

print "Rows returned: $numRows\n";
 
while ( my @row = $sth->fetchrow_array )

{

    print "@row\n";

}
 
$dbh->disconnect();

Dec 10 '09 #6

dbrewerton

115

100+

Ok, now I tried using what you show above and bringing it into my existing list.cgi script but I got tons of errors about Global symbol "$title" requires explicit package name. Do my variables need to be preceeded by my to make this work? Here is the script I'm trying to use:

Expand|Select|Wrap|Line Numbers

 #!/usr/bin/perl 

use strict; 

use warnings; 

use CGI qw(:standard); 

use CGI::Carp qw(fatalsToBrowser warningsToBrowser); 

use DBI qw(:sql_types); 
 
$title = Reading List';

$start = 40;
 
print <<ENDSTART;

Content-Type: text/html; charset=iso-8859-1
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html lang="en-US">

<head>

<title>$title</title>

</head>

<body>

<h1 style="font-size:1.2em">$title</h1>

ENDSTART
 
my $dbName = "egwtest"; 

my $dbHost = "10.30.125.80"; 

my $dbUser = "**********"; 

my $dbPass = "********"; 

my $numrows = 5;

my $dbh = DBI->connect("DBI:mysql:$dbName:$dbHost", $dbUser, $dbPass, { RaiseError => 1 }) or die "Die: $DBI::errstr\n"; 
 
    $sql = "select max(raw_id) from ewise_raw_data_in";

    $sth = $dbh->prepare($sql) || die "prepare: $sql: $DBI::errstr";

    $result = $sth->execute() || print "execute: $sql: $DBI::errstr";

    @record = $sth->fetchrow();

    $maxid = @record[0];

    $sth->finish();

    print $maxid," rows found.  Last ", $numrows, " shown below:";

    $minid = $maxid-$numrows;

    $sql = "select * from ewise_raw_data_in where raw_id > \"$minid\"";

    $sth = $dbh->prepare($sql) || die "prepare: $sql: $DBI::errstr";

    $result = $sth->execute() || print "execute: $sql: $DBI::errstr";

    while (@results = $sth->fetchrow() ) {

        print "<p>Row ";

        print @results[0];

        print ":<p>";

        print show(@results[1]);

        }

    $sth->finish();

    $dbh->disconnect();

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = gmtime(time);

$now = sprintf "%4d-%02d-%02dT%02d:%02dZ", 1900+$year,$mon+1,$mday,$hour,$min;

print p('Processed '.$now."\n");

print end_html;
 
sub show {

    $_[0] = escapeHTML($_[0]);

    $_[0] =~ s/\r\n/<br>/g;

    $_[0] =~ s/\n/<br>/g;

    return $_[0]; }

Dec 10 '09 #7

RonB

589

Expert Mod 512MB

Yes, you need to declare your vars with the my keyword just like you did for the db vars.

Also, when posting code, please use the code tags, like I did. It will retain the code indentation and makes it easier for us to help you. If you don't know where the code tag button is located, click on the "Go Advanced" button.

Dec 10 '09 #8

dbrewerton

115

100+

That one is fixed, now the other one I'm having a heck of a time with is this one called reading.cgi which allows me to import data using CGI into the MySQL DB. I don't have any undeclared vars but it is giving me that misbehaved error. Take a look please...thank you for all your help thus far :)

Expand|Select|Wrap|Line Numbers

 #!/usr/bin/perl 

use strict; 

use warnings; 

use CGI qw(:standard); 

use CGI::Carp qw(fatalsToBrowser warningsToBrowser); 

use DBI qw(:sql_types); 
 
my $dbName = "egwtest"; 

my $dbHost = "10.30.125.80"; 

my $dbUser = "************"; 

my $dbPass = "***********"; 

my $dbh = DBI->connect("DBI:mysql:$dbName:$dbHost", $dbUser, $dbPass, { RaiseError => 1 }) or die "Die: $DBI::errstr\n";

my @fields = param();

my @values = param(my $variable);

my @filehandles = upload(my $variable);
 
if(param()) {

  for $variable(@fields) {

    for my $value(@values) {

      if(@filehandles) {

        }

      else {

        $value =~ s/ / /g;

    if($value eq '') {

      }

    else {

      my $sql = "INSERT INTO data (raw_id, raw_data_xml) VALUES (default,\"$value\")";

      my $sth = $dbh->prepare($sql) || die "prepare: $sql: $DBI::errstr";

      $sth->execute() || print "execute: $sql: $DBI::errstr";

      $sth->fetchrow();

      $sth->finish();

      $dbh->disconnect();

      print "12.3";    # content

      }

    }

      }

    }

  }

else {

  print "empty";

  }

print "</html>";
 
sub show {

    $_[0] = escapeHTML($_[0]);

    $_[0] =~ s/\r\n/<br>/g;

    $_[0] =~ s/\n/<br>/g;

    return $_[0]; }

It returns this:

CGI Error
The specified CGI application misbehaved by not returning a complete set of HTTP headers.

If nothing is being sent, it should just say empty.

Dec 10 '09 #9

RonB

589

Expert Mod 512MB

You didn't print the html header.

Dec 10 '09 #10

dbrewerton

115

100+

I tried it with header printing but no luck... It returns the following error:

Expand|Select|Wrap|Line Numbers

 Software error:

Can't find string terminator "ENDSTART" anywhere before EOF at \cgi-bin\reading.cgi line 11.

Here's what I have now:

Expand|Select|Wrap|Line Numbers

 #!/usr/bin/perl 

use strict; 

use warnings; 

use CGI qw(:standard); 

use CGI::Carp qw(fatalsToBrowser warningsToBrowser); 

use DBI qw(:sql_types); 
 
my $title = Reading List'; 

my $start = 40; 
 
print <<ENDSTART; 

Content-Type: text/html; charset=iso-8859-1 
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 

<html lang="en-US"> 

<head> 

<title>$title</title> 

</head> 

<body> 

<h1 style="font-size:1.2em">$title</h1> 

ENDSTART 
 
my $dbName = "egwtest"; 

my $dbHost = "10.30.125.80"; 

my $dbUser = "**********"; 

my $dbPass = "**********"; 

my $dbh = DBI->connect("DBI:mysql:$dbName:$dbHost", $dbUser, $dbPass, { RaiseError => 1 }) or die "Die: $DBI::errstr\n";

my @fields = param();

my @values = param(my $variable);

my @filehandles = upload(my $variable);
 
if(param()) {

  for $variable(@fields) {

    for my $value(@values) {

      if(@filehandles) {

        }

      else {

        $value =~ s/ / /g;

    if($value eq '') {

      }

    else {

      my $sql = "INSERT INTO ewise_raw_data_in (raw_id, raw_data_xml) VALUES (default,\"$value\")";

      my $sth = $dbh->prepare($sql) || die "prepare: $sql: $DBI::errstr";

      $sth->execute() || print "execute: $sql: $DBI::errstr";

      $sth->fetchrow();

      $sth->finish();

      $dbh->disconnect();

      print "12.3";    # content

      }

    }

      }

    }

  }

else {

  print "empty";

  }

print "</html>";
 
sub show {

    $_[0] = escapeHTML($_[0]);

    $_[0] =~ s/\r\n/<br>/g;

    $_[0] =~ s/\n/<br>/g;

    return $_[0]; }

Dec 10 '09 #11

RonB

589

Expert Mod 512MB

Your first problem is with this line; it's missing the opening quote

Expand|Select|Wrap|Line Numbers

my $title = Reading List';

Your next problem is with the here document that prints the header. We could fix it, but since it's a bad way of doing that, I won't. You're already loading the CGI module, so use it.

I've fixed those 2 issues, but there are others that you'll need to look at.

Expand|Select|Wrap|Line Numbers

 #!/usr/bin/perl 

use strict; 

use warnings; 

use CGI qw(:standard); 

use CGI::Carp qw(fatalsToBrowser warningsToBrowser); 

use DBI qw(:sql_types); 
 
my $title = 'Reading List'; 

my $start = 40; 
 
print header(), start_html($title),

      h1({-style=>"font-size:1.2em"}, $title);
 
my $dbName = "egwtest"; 

my $dbHost = "10.30.125.80"; 

my $dbUser = "**********"; 

my $dbPass = "**********"; 

my $dbh = DBI->connect("DBI:mysql:$dbName:$dbHost", $dbUser, $dbPass, { RaiseError => 1 }) or die "Die: $DBI::errstr\n";

my @fields = param();

my @values = param(my $variable);

my @filehandles = upload(my $variable);
 
if(param()) {

  for $variable(@fields) {

    for my $value(@values) {

      if(@filehandles) {

        }

      else {

        $value =~ s/ / /g;

    if($value eq '') {

      }

    else {

      my $sql = "INSERT INTO ewise_raw_data_in (raw_id, raw_data_xml) VALUES (default,\"$value\")";

      my $sth = $dbh->prepare($sql) || die "prepare: $sql: $DBI::errstr";

      $sth->execute() || print "execute: $sql: $DBI::errstr";

      $sth->fetchrow();

      $sth->finish();

      $dbh->disconnect();

      print "12.3";    # content

      }

    }

      }

    }

  }

else {

  print "empty";

  }

print "</html>";
 
sub show {

    $_[0] = escapeHTML($_[0]);

    $_[0] =~ s/\r\n/<br>/g;

    $_[0] =~ s/\n/<br>/g;

    return $_[0]; }

Dec 10 '09 #12

dbrewerton

115

100+

Ok, I just tried the script. Apparently, it isn't making any posts into the DB as I'm using my test app to ensure records are being written. I'll continue to work with this but I do appreciate all the help you've been giving me thus far.

Dec 11 '09 #13

dbrewerton

115

100+

To manually test this, am I using the right syntax for the variable?

/cgi-bin/reading.cgi?value="<energywise><device>71827008778 4420</device><slice><time>20000103165315</time><t1>57.537</t1><t2>63.275</t2><c1>0</c1></slice></energywise>"

Dec 11 '09 #14

RonB

589

Expert Mod 512MB

Have you checked to see if your script is seeing the input data the way you think it should?

Add this just after printing the header.

Expand|Select|Wrap|Line Numbers

 my $value = param('value');

print "'value' param = $value";

Why are you declaring $variable inside the param() and upload() calls?

That var should be declared and assigned prior to using it in those cgi function calls.

Dec 11 '09 #15

dbrewerton

115

100+

Ok, that makes sense...I'll refine the script.

What I got back from the input was this:

Expand|Select|Wrap|Line Numbers

'value' param = '7182700877844202000010316531557.53763.2750'

What I expected to see was XML formatted data like what is going into it. Like this:

Expand|Select|Wrap|Line Numbers

 <energywise><device>71827008778 4420</device><slice><time>20000103165315</time><t1>57.537</t1><t2>63.275</t2><c1>0</c1></slice></energywise>
 

Dec 11 '09 #16

dbrewerton

115

100+

I'm totally frustrated! I don't know why the script won't post to the database...Also, why is 'value' being formatted as a number instead of a string. Any help here would be most appreciated. I'll post my script again for help...

Expand|Select|Wrap|Line Numbers

 #!/usr/bin/perl  

use strict;  

use warnings;  

use CGI qw(:standard);  

use CGI::Carp qw(fatalsToBrowser warningsToBrowser);  

use DBI qw(:sql_types);  
 
my $title = 'Reading List';  

my $start = 40;  
 
print header(), start_html($title), 

h1({-style=>"font-size:1.2em"}, $title); 

my $value = param("value");

print "'value' param = $value";  
 
my $dbName = "MyDatabase";  

my $dbHost = "192.168.1.160";  

my $dbUser = "MyUser";  

my $dbPass = "MyPass";  

my $dbh = DBI->connect("DBI:mysql:$dbName:$dbHost", $dbUser, $dbPass, { RaiseError => 1 }) or die "Die: $DBI::errstr\n"; 

my @fields = param(); 

my @values = param(my $variable); 

my @filehandles = upload(my $variable); 
 
if(param()) { 

  for $variable(@fields) { 

    for my $value(@values) { 

      if(@filehandles) { 

        } 

      else { 

        $value =~ s/ / /g; 

    if($value eq '') { 

      } 

    else { 

      my $sql = "INSERT INTO ewise_raw_data_in (raw_id, raw_data_xml) VALUES (default,\"$value\")"; 

      my $sth = $dbh->prepare($sql) || die "prepare: $sql: $DBI::errstr"; 

      $sth->execute() || print "execute: $sql: $DBI::errstr"; 

      $sth->fetchrow(); 

      $sth->finish(); 

      $dbh->disconnect(); 

      print "12.3";    # content 

      } 

    } 

      } 

    } 

  } 

else { 

  print "empty"; 

  } 

print "</html>"; 
 
sub show { 

    $_[0] = escapeHTML($_[0]); 

    $_[0] =~ s/\r\n/<br>/g; 

    $_[0] =~ s/\n/<br>/g; 

    return $_[0]; }

Dec 16 '09 #17

RonB

589

Expert Mod 512MB

Please use proper indentation on your code blocks. It will make it much easier for you and use.

Read: perldoc perlstyle
http://perldoc.perl.org/perlstyle.html

Part of your problem is with these 2 lines.

Expand|Select|Wrap|Line Numbers

 my @values = param(my $variable); 

my @filehandles = upload(my $variable);

You need to read the CGI documentation for the param() and upload() functions. Apparently you don't fully understand how to use them.
http://search.cpan.org/~lds/CGI.pm-3.48/lib/CGI.pm

Why do you have these 2 if blocks that do absolutely nothing?

Expand|Select|Wrap|Line Numbers

 if(@filehandles) { 

}

# and this one

if($value eq '') { 

}

And why this regex?

Expand|Select|Wrap|Line Numbers

$value =~ s/ / /g;

It substitutes a 'space' with a 'space'.

Dec 16 '09 #18

CGI to MYSQL on IIS6 not working

✓ answered by RonB

Similar topics