CGI to MYSQL on IIS6 not working

**RonB** · Dec 10 '09, 01:13 PM

Add this line just above the use DBI statement.

Code:

use CGI::Carp qw(fatalsToBrowser);

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

**dbrewerton** · Dec 10 '09, 04:15 PM

Code:

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:

Code:

#!/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();

**dbrewerton** · Dec 10 '09, 04:26 PM

I think it is the syntax of my connection string.

$dbh = DBI->connect(DBI:my sql:database="e gwtest":host="1 0.30.125.80",$d bUser,$dbPass) || die "Die: $DBI::errstr\n" ;

**RonB** · Dec 10 '09, 04:33 PM

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 "$dbHos t "
syntax error at dbrewerton.pl line 18, near "DBI:&quot ;
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).

Code:

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.

**RonB** · Dec 10 '09, 04:42 PM

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

Code:

#!/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();

**dbrewerton** · Dec 10 '09, 04:58 PM

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:

Code:

#!/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]; }

**RonB** · Dec 10 '09, 05:36 PM

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.

**dbrewerton** · Dec 10 '09, 05:36 PM

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 :)

Code:

#!/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.

**RonB** · Dec 10 '09, 05:49 PM

You didn't print the html header.

**dbrewerton** · Dec 10 '09, 06:56 PM

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

Code:

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

Here's what I have now:

Code:

#!/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]; }

**RonB** · Dec 10 '09, 10:06 PM

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

Code:

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.

Code:

#!/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]; }

**dbrewerton** · Dec 11 '09, 02:32 PM

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.

**dbrewerton** · Dec 11 '09, 02:56 PM

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

/cgi-bin/reading.cgi?val ue="<energywise ><device>718270 087784420</device><slice>< time>2000010316 5315</time><t1>57.537 </t1><t2>63.275</t2><c1>0</c1></slice></energywise>"

**RonB** · Dec 11 '09, 03:17 PM

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.

Code:

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.

CGI to MYSQL on IIS6 not working

CGI to MYSQL on IIS6 not working

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment