#!/usr/bin/perl

# #################################################################
#
# Project:     WEB-Publishing with Perl-DBI-Oracle
#
# Filename:    oradbi.cgi
#
# Version:     1.0
#
# Autor:       Akadia AG, Martin Zahn 26.12.1999
#
# Purpose:     This perl scripts connects to Oracle-8 using
#              the perl module DBI::ORACLE, selects all rows
#              from table EMP, shows this rows in the browser
#              and disconnects from the database.
#
# Environment: SQL*NET must be configured
#
# #################################################################
#
# We need the perl module DBI and CGI

use strict;
use DBI;
use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser carpout);  # Error Messages to Browser

my $dbname   = 'RAB1';
my $user     = 'scott';
my $password = 'tiger';
my $dbd      = 'Oracle';

my $dbh      = '';
my $empno    = 0;
my $ename    = '';
my $job      = '';
my $mgr      = 0;
my $hiredate = '';
my $sal      = 0;
my $deptno   = 0;
my $row;

$ENV{ORACLE_HOME} = "/opt/oracle/product/9.2.0";
$ENV{TNS_ADMIN} = "/home/oracle/config/9.2.0";
$ENV{NLS_LANG} = "AMERICAN_AMERICA.WE8ISO8859P1";

# Print HTML header

print header(), start_html("Access to Oracle Database");
print ("<link rel='stylesheet' href='http://www.akadia.com/docroot/css/website.css'>\n");

# Set the server specific variables

if ( $ENV{SERVER_NAME} =~ /asus/i ) {
  $dbname = 'ASU1';
  $dbname = 'ASU1';
} elsif ( $ENV{SERVER_NAME} =~ /cellar/i ) {
  $dbname = 'CEL2';
} elsif ( $ENV{SERVER_NAME} =~ /www\.akadia\.ch/i ) {
  $dbname = 'RAB1';
} elsif ( $ENV{SERVER_NAME} =~ /www\.akadia\.com/i ) {
  $dbname = 'RAB1';
} else {
  print "Not supported HTTP Server: $ENV{SERVER_NAME}\n";
  exit;
}

# Print header

print h1("Select from table EMP");

# Check for input parameter ...

if (param()) {

  my $ename = param('ename');

  # Print Headline
  print hr();
  print ("<p class='header'>Output from table EMP on Oracle Database: $dbname on: $ENV{SERVER_NAME}</p>");
  print ("<p class='normal'>We used Perl/CGI and DBI:DBD to connect the ORACLE database to the WEB.</p>");

  # Open a connection to the database

  if (!($dbh = DBI->connect ($dbname,$user,$password,$dbd))) {
      print "Error connecting to $dbd\n";
      print "Error: " . $DBI::errstr . "\n";
      exit;
  }

  # Prepare SQL statement

  my $selStmt = 'SELECT empno,ename,job,
                 NVL(TO_CHAR(mgr),\'----\'),
                 TO_CHAR(hiredate,\'DD.MM.YYYY\'),
                 RPAD(TO_CHAR(deptno),6,\' \'),
                 NVL(TO_CHAR(sal),\'0\')
                 FROM emp';
  my $wheStmt = ' WHERE UPPER(ename) LIKE ';
  my $ordStmt = ' ORDER BY ename';
  my $cur = '';
  my $found = 0;

  if ($ename) {
    $cur = $dbh->prepare ($selStmt . $wheStmt . "UPPER(\'$ename\')" . $ordStmt);
  } else {
    $cur = $dbh->prepare ($selStmt . $ordStmt);
  }
  if ($DBI::err) {
      print "$DBI::errstr\n";
      exit;
  }

  # Execute SQL statement

  $cur->execute();
  if ($DBI::err) {
      print "$DBI::errstr\n";
      exit;
  }

  # Fetch the rows and print them to STDOUT

  print "<TABLE BORDER=1 CELLPADDING=2 CELLSPACING=0 WIDTH=500>\n";
  while ($row = $cur->fetchrow_arrayref) {
    print '<TR><TD>', join('</TD><TD>',@$row), '</TD></TR>';
    $found += 1;
  }
  print "</TABLE>\n";
  print p("$found row(s) found.");

  # Close connection
  $cur->finish();
}

# ... or create the form

else {
  print hr(), start_form();
  print p("Enter Employee Name:", textfield("ename"), " (% and _ as Wildcards allowed)");
  print p(submit("Search"), reset("Clear"));
  print end_form();
}

print hr();
print ("<p><a href=\"../src/oradbi.txt\">Show source</a></p>");
print end_html();