#!/local/bin/perl5
# Chris Wilson

use DBI; 
use CGI;

#-- The line below will dump the errors to the browser.
use CGI::Carp qw/fatalsToBrowser/;

# --- begin parameters ---
 
$ENV{INFORMIXDIR}    = '/local/apps/informix';
$ENV{INFORMIXSERVER} = 'onlintli';

# "my" is a keyword for local variables.

my $data_source = "dbi:Informix:stores7";
my $username = "cwilson";
my $password = "********";  

# --- end parameters ---

my $myquery = new CGI();
print $myquery->header();

#this is a subroutine call. You may name your function whatever you want.
&getThemAll;

print $myquery->end_html();
#we are done. 
exit;

#----------------- a subroutine executing the query -------------
sub getThemAll{

#-- Here again we are declaring all the local variables.
# $sql is your SQL query.
# $dbh this is what is called database handle.
# $cursor same as embedded sql in c you need a cursor to get the stuff from the database.
# $row this is an array that you get back from the query.

my ( $sql,$dbh,$cursor,@row);

# The following CGI function returns the value of the parameter
# named "state" into the local variable $state.

my $state = $myquery->param("state");

$sql = " SELECT fname, lname, address1, city, c.state, zipcode ".
    "FROM customer c, state s ".
	"WHERE c.state=s.code AND s.sname MATCHES '$state' ".
	    "ORDER BY 2";

print $myquery->start_html(-title=>'stores7 sample query 2', -BGCOLOR=>'white' );
  
  print ("The following query is executed:\n");
   print("<P>\n", $sql, "\n\n");
  print '<HR>';
 
$dbh = DBI->connect($data_source, $username, $password);
    unless( $cursor = $dbh->prepare($sql)){
    die ("Hey, I could not prepare query $DBI::Errstr");
}
$cursor->execute;

print("<P>\n All customers living in ", $state, ":\n");
print("\n<OL>");
while( @row = $cursor->fetchrow){
    
    print("\n<P><LI>");
    print (@row[0], " ", @row[1], "<BR>\n");
    print(@row[2], "<BR>\n");
    print(@row[3], ", ", @row[4], " ", @row[5], "\n");
}
  $cursor->finish;
  $dbh->disconnect;
  undef $cursor;
  print  "\n</OL>\n";
}