use strict;
use DBI qw(:sql_types) ;
use DBD::Oracle qw( :ora_types  ) ;
use Getopt::Long;

use vars qw(%map);

# globals
my %options =();	# Commandline arguments

my $hlp;
my $usr;
my $pwd;
my $dbs;

# ------------------------------------------------------------------------------
# Get commandline arguments
# ------------------------------------------------------------------------------

GetOptions(
	"h!"  => \$hlp,
	"u=s" => \$usr,
	"p=s" => \$pwd,
	"d=s" => \$dbs,
) or usage();	

# get options
usage() if $hlp;
usage() if not $usr;
usage() if not $pwd;
usage() if not $dbs;

my $ifile = $ARGV[0] || usage();

# ------------------------------------------------------------------------------
# SQL statements
# ------------------------------------------------------------------------------

my $selsql = qq{
 	select cmp_id from cmp_num_idx where num_idx_tx = ?
};

my $selsqlTOCHAR = qq{
 	select cmp_id from cmp_num_idx where num_idx_tx = TO_CHAR(?)
};

# ------------------------------------------------------------------------------
# Main
# ------------------------------------------------------------------------------

logger("INFO: Start.");

# connect to database
my $dbh = DBI->connect(
	"dbi:Oracle:$dbs",
	"$usr",
	"$pwd",
	{
		RaiseError => 1,
		AutoCommit => 0
	}
) || die "Database connection not made: $DBI::errstr";

$dbh->{AutoCommit} = 0;  # enable transactions, if possible
$dbh->{RaiseError} = 1;

# open inputfile
open(IN, "$ifile") || die("open: $!");
	
eval {
	# prepare statements
	my $sth = $dbh->prepare( $selsql );

	# read inputfile
	while (defined (my $line = <IN>)) {
		chomp($line);
		
		# trim (remove any whitespace)
		$line = trim($line);
		# skip empty lines	
		next if ($line =~ /^\s*$/);

		# bind line which holds the SIRET number
		$sth->bind_param( 1, $line );
		
		# execute statement
		$sth->execute($line);
		$dbh->commit() ;
		
		# print result		
		logger("INFO: SIRET '$line' '$row_count'.");
	}
	
	# cleanup 
	$sth->finish();
	$dbh->commit() ;
	$dbh->disconnect;
	
};
if ($@) {
      warn "Transaction aborted because $@";
      # now rollback to undo the incomplete changes
      # but do it in an eval{} as it may also fail
      eval { $dbh->rollback };
      # add other application on-error-clean-up code here
}

close(IN ) || die("close: $!");

logger("INFO: End.");

exit (0);

# ------------------------------------------------------------------------------
# Functions
# ------------------------------------------------------------------------------

sub trim(){
	my $string = shift;
	$string =~ s/^\s+//;
	$string =~ s/\s+$//;
	return $string;
}

# ------------------------------------------------------------------------------

sub logger {
	my ($msg) = @_;

	my ($second, $minute, $hour, $dayOfMonth, $month, $yearOffset, $dayOfWeek, $dayOfYear, $daylightSavings) = localtime();
	my $year = 1900 + $yearOffset;
	my $time = sprintf("%02d:%02d:%02d", $hour,$minute,$second);
	
	print "[".$time."] " . $msg . "\n";
}

# ------------------------------------------------------------------------------

sub usage {
	print STDERR "usage: $0 -u user -p passwd -d database input_file output_file \n";
	print STDERR "\n";
	print STDERR "-h              : this (help) message\n";
	print STDERR "-u user         : database user\n";
	print STDERR "-p passwd       : password for database user\n";
	print STDERR "-d db           : database to connect to\n";
	exit;
}

# EOF