#!/usr/bin/perl use DBI; use CGI::Carp qw(fatalsToBrowser); use CGI; use CGI qw(:standard);; ($sid) = "sohocat"; ($user) = "soho"; ($pass) = ""; $ENV{'ORACLE_HOME'} ="/dbsw/app/oracle/product/8.1.7"; ($start_date, $end_date); ### Pick form input $startyear=param("startyear"); $startmonth=param("startmonth"); $startday=param("startday"); $starthour=param("starthour"); $startminute=param("startminute"); $endyear=param("endyear"); $endmonth=param("endmonth"); $endday=param("endday"); $endhour=param("endhour"); $endminute=param("endminute"); $objname=param("objname"); ### Create Oracle-friendly variables ### SOHO date variables are in the format: DD-MON-YYYY HH:MM:SS ### Convert search form month to a three-letter abbreviation %months = ("1" => "JAN", "2" => "FEB", "3" => "MAR", "4" => "APR", "5" => "MAY", "6" => "JUN", "7" => "JUL", "8" => "AUG", "9" => "SEP", "10" => "OCT", "11" => "NOV", "12" => "DEC"); $s_month=$months{$startmonth}; $e_month=$months{$endmonth}; ### Generate the start and end observation parameters $date_obs = "$startday-$s_month-$startyear $starthour:$startminute"; $date_end = "$endday-$e_month-$endyear $endhour:$endminute"; ### Check if free-form field has a wildcard ### If yes, change * to Oracle -wildcard % if($objname =~/\*/) { $fixwild = ($objname =~ s/\*/\%/); } ### Connect to the database $dbh = DBI->connect("DBI:Oracle:$sid", $user, $pass) || die "Cannot connect to database: $DBI::errstr\n"; ### Build the query. (Thsi shoudlbe more modular) $select = "SELECT rownum as id, telescop.telescop_name, instrume.instrume_name, obs_type.obs_type_name, observation.date_obs, observation.date_end "; $tablesource = " FROM telescop, instrume, obs_type, observation "; $predicatetime = " WHERE telescop.telescop_name='SOHO' AND instrume.id_instrume=2 AND observation.id_instrume=2 AND obs_type.id_instrume=2 AND date_obs >= \'$date_obs\' AND date_end <= \'$date_end\' "; $predicatematch = "AND obs_type.obs_type_name=\'$objname\' "; $predicatewild = "AND obs_type.obs_type_name LIKE \'$objname\' "; $limit = "and rownum < 10"; ### Check if the free-form field has any contents if($objname eq "") { $sth = $dbh->prepare(qq{ $select $tablesource $predicatetime $limit }); } elsif ($objname =~/\%/) { $sth = $dbh->prepare(qq{ $select $tablesource $predicatetime $predicatewild $limit }); } else { $sth = $dbh->prepare(qq{ $select $tablesource $predicatetime $predicatematch $limit }); } $sth->execute(); ### Display the output (HTML) print "Content-type: text/html\n\n"; print ""; print "

Searching for observations between: $date_obs and $date_end
"; print "
Date end: $date_end\n"; print "\n"; while (@ary = $sth->fetchrow_array()) { print "\n"; foreach $field (@ary){ chomp($field); print " \n"; } print "\n"; } print "

$field

"; $dbh->disconnect();