#! /home/mitravij/local/perl/bin/perl

# Name : defncp.pl
# Usage : defncp <dbname> <procname>
# Description :  Creates output file of defn copy of the passed in procedure.
# Programmer : Vijay Mitra
# Date : Aug 20 2002
#
#

use DBI;

#**************************
#  Start script
#**************************

### Get the $SYBASE environment variable
$ENV{SYBASE} || die "Environment variable $SYBASE not defined\n";

### Set variables
$defncopy="$ENV{SYBASE}/bin/defncopy";

### Header line
$OUT_HEADER = "
& Automated script generated by $0
&
& NAME        : $outFile
&
&
& DESCRIPTION : This macro creates database data.
&
";

#===========================================================================
#                     START OF MAIN PROGRAM
#===========================================================================


######################## PARAMETERS CHECK ##################################
### Check the no. of parameters
($progname = $0)        =~ s!^.*/!!;
$progname =~ s/\.pl// ;
die "USAGE: $progname  <dbprefix T/H/C> <procname> \n" unless ($#ARGV == 1 ) ;

### Get the parameters
$dbPreFix = `echo $ARGV[0] | tr '[a-z]' '[A-Z]'| cut -c1 `;  ## $ARGV[0] is dbPrefix
$filePreFix = `echo $ARGV[0] | tr '[A-Z]' '[a-z]'| cut -c1 `; ## also get the output file prefix
chomp($dbPreFix);
chomp($filePreFix);
$procName = $ARGV[1];   ## $ARGV[1] is proc name

##################### GET DBI PARAMS AND OPEN CHANNEL #####################
### Check that we have all the environment variables we require
$ENV{DB_USER} and $ENV{DSQUERY} and $ENV{DB_PASW} or
     die ("ERROR : $0 requires environment variables DB_USER, DSQUERY, DB_PASW\n");

### Set database connection parameters
$DB_USER=$ENV{DB_USER};
$DB_SERVER=$ENV{DSQUERY};
$DB_PWD=$ENV{DB_PASW};
$DB_PFIX=$ENV{DB_PFIX};

### Work out our database post-fix (if any)
if ( length $DB_PFIX ) {
     $DB_PFIX = "_$DB_PFIX";
} else {
     die ("ERROR : No database postfix\n");
}

### Our DBI parameters are:
$dsn="dbi:Sybase:server=$DB_SERVER";

### Connect DB
$dbh = DBI->connect ($dsn, $DB_USER, $DB_PWD) || die "ERROR : Database connection not made: $DBI::errstr\n";

########################## DATABASE CHECK #################################
### Select DBs from master..sysdatabases
$sql = "SELECT name FROM master..sysdatabases WHERE name LIKE '\%$DB_PFIX' AND name LIKE '${dbPreFix}\%'" ;
$sth = $dbh->prepare($sql);
$sth->execute();

$db_count = 0;
my @databases;
### Get each row and get the DB
while (@row = $sth->fetchrow_array) {
   $_ = $row[0] ;
   s/\s//g;
   $databases[$db_count++] = $_;
}

### If more than one or no database found then error
die "ERROR : No database found for the DB Prefix '$dbPreFix' entered\n" if ($#databases < 0);
die "ERROR : More than one databases found for the DB Prefix '$dbPreFix' entered\n" if ($#databases > 0);

### Get the database to use
$database = $databases[0];

############################## PROCEDURE CHECK #################################
### Use the database selected
$dbh->do("USE $database") || die "ERROR : Database error $dbh->errstr\n" ;

### Prepare sql to check if table exists
$sql = "SELECT  name FROM sysobjects WHERE name like '\%${procName}\%'" ;
$sth = $dbh->prepare($sql);
$sth->execute();

$rowcount =0;
my @objects;
### Get each row and get the object
while (@row = $sth->fetchrow_array) {
   $_ = $row[0] ;
   s/\s//g;
   $objects[$rowcount++] = $_;
}

### If no object found then error
die "ERROR : No '$procName' object found in '$database' database\n" if ($rowcount == 0);

$rowcount =0;
### If More than 1 objects found then list them. 
if ($#objects > 0) {
   foreach $x (@objects) {
      print "$rowcount  $x \n";
      $rowcount ++;
   }
   print "Enter Choice : ";
   $choice = <STDIN>;
   chomp($choice);

   die "ERROR : Incorrect choice. Try again...\n" if (($choice < 0) || ($choice > $rowcount));

   ### Get the selected choice
   $object = $objects[$choice];
} 
else {
   ### Get the passed proc name
   $object = $procName
}

############################## OPEN OUTPUT FILE #################################
### Formulate & open output file name
$outFile = "${object}.dat";
if ( -e $outFile ) {
    print "INFO : Output file '$outFile' already exists .Overwriting ... \n";
}

open (OUT , "> $outFile") || die "ERROR : Can't open output file $outFile\n" ;

############################## EXECUTE DEFNCOPY #################################
$defncp_command = "$defncopy -S$DB_SERVER -U$DB_USER -P$DB_PWD out $outFile $database $object";
system $defncp_command || die "Error while running defncopy \n";
print "Defncopy done. Please check '$outFile' file.\n" ;

#*************************
# End Script
#*************************