#!/usr/bin/perl
#
# sy_load - inspired by Informix' load/unload
#
# sy_load [options] databasename tablename filename
#
# options: -q: quoted strings, comma delimited input
#          -b: bar delimited input (default)
#          -d: optional delimiter,  replaces vertical bar
#          -s: optional skip first record (which might contain field names
#          -U: optional user name
#          -P: optional password
#
# Jeff Hecker / Apogee 

use DBI;

$U = "sa";
$P = "";
$delim = "|";
$skip = 0;

while (( $#ARGV > 0 ) && ( $ARGV[0] =~ m/^-/ )) {
  $_ = shift ( @ARGV );
  print "arg=$_ \n";
  m/-U/ && do { $U = shift ( @ARGV ); };
  m/-P/ && do { $P = shift ( @ARGV ); };
  m/-d/ && do { $delim = shift ( @ARGV ); };
  m/-s/ && do { $skip = 1; };
}

&usage unless ( $#ARGV == 2 );

$DB = shift ( @ARGV );
$TABLE = shift ( @ARGV );
$F = shift ( @ARGV );

open ( F, $F ) || die ( "Error opening $F\n" );

# $conn = DBI->connect ( "dbi:Sybase:database=" . $DB . ":scriptname=unload", $U, $P );
$conn = DBI->connect ( "dbi:Sybase:scriptname=load", $U, $P );
$conn->do ( "use $DB" );
$conn->do ( "checkpoint" );
$conn->do ( "set identity_insert " . $TABLE . " on" );

$curr = $conn->prepare (        'select c.name,c.type,c.length,i.name,c.colid ' .
                                'from sysindexes i, syscolumns c ' .
                                'where indid=0 and i.name=\'' . $TABLE . '\' and i.id=c.id ' .
                                'order by c.colid' ) || die ( $conn->errstr );
$rc = $curr-> execute || die ( $conn->errstr );

@F_NAME = ();
@F_TYP = ();
@F_LEN = ();
while ( @V = $curr->fetchrow_array ) {
  push ( @F_NAME, $V [ 0 ] );
  push ( @F_TYP, $V [ 1 ] );
  push ( @F_LEN, $V [ 2 ] );
}
$rc = $curr->finish;

$query = "insert into " . $TABLE . " (";
$F = "";
for $n ( 0 .. $#F_NAME ) {
  $query .= $F . $F_NAME [ $n ];
  $F = ",";
}

$query .= ") values (";
$F = "";
for $n ( 0 .. $#F_NAME ) {
  $query .= $F . "?";
  $F = ",";
}
$query .= ")";

print STDERR "query=", $query, "\n";
$curr = $conn->prepare ( $query ) || die ( $conn->errstr );

$n = 0;
while ( <F> ) {
  next if ( $skip-- > 0 );
  chop;
  @V = ();
  @V = split ( "\\" . $delim, $_ );
  $curr->execute ( @V );
  $conn->do ( "checkpoint" );
  $n++
}

close ( F );
$conn->disconnect;

print STDERR $n, " records processed.\n";
exit;

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #

sub usage {

print STDERR <<EOF;
 sy_load [options] databasename tablename filename

 options: -q: quoted strings, comma delimited input
          -b: bar delimited input (default)
          -d: optional delimiter,  replaces vertical bar
          -U: optional user name
          -P: optional password

 Jeff Hecker / Apogee 

EOF

exit;

}