#!./perl
#
# cntInfo -- Return information about a country, given a country code
#
#	Written by Clint Goss <clint@goss.com>, November 1998
#

BEGIN {
	# Access Perl modules

	#push (@INC, ...your lib directory here...);

	# Point to our preferred installation of Oracle

	#$ENV{'ORACLE_HOME'} = ...your Oracle Home here...);
}

# Perl Modules

use OraConnection;	# Connection to Oracle, incl DBI and DBD::Oracle
use strict;		# Restrict unsafe variables, refs, barewords

# Process command-line arguments and options

sub usage {
	print <<END_USAGE

usage: $0 COUNTRY_ID

COUNTRY_ID is a two-character or three-character country code.

END_USAGE
}

# Command line processing

my ($cntID) = $ARGV[0];
if ((length ($cntID) < 2) || (length ($cntID) > 3)) { &usage(); exit; }

# Set up the connection to Oracle

my ($database, $username, $password) = qw (ora9 demo demo);
my ($dbc) = new OraConnection;
$dbc->connect ($database, $username, $password);

# Form a query, based on a 2- or 3-character code

$cntID = uc ($cntID);

my ($stmt) = "SELECT " .
	"cnt_code_a2, cnt_code_a3, cnt_code_number, " .
	"cnt_name_upper_case, cnt_name_mixed_case " .
"FROM COUNTRIES ";

if (length ($cntID) == 2) {
	$stmt .= "WHERE cnt_code_a2 = " . $dbc->quote ($cntID);
} else {
	$stmt .= "WHERE cnt_code_a3 = " . $dbc->quote ($cntID);
}

# Look up the single record.
# Don't complain on a missing record, as we handle the error ourselves.

my ($cntInfo) = $dbc->fetchSingle ($stmt, "Y");
if (! $cntInfo) {
	print STDERR "There is no country with country code '$cntID'\n";
	exit;
} 

# Exract the fields, and display the result.
# Country number is always shown as 3 digits.

my ($a2, $a3, $number, $upper, $mixed) = @$cntInfo;

my ($number3) = sprintf ("%03d", $number);

print <<END_OUTPUT ;
       A2 code: $a2
       A3 code: $a3
Country number: $number3
  Name (UPPER): $upper
  Name (Mixed): $mixed
END_OUTPUT

exit;