sybperl-l Archive

From: "Connie Zhang" <czhang at fir dot fbc dot com>
Subject: SybPerl Question
Date: Jan 21 1998 3:59PM

#!/usr/local/bin/perl5 -w

$user_id      = $ARGV[0];
$password     = $ARGV[1];
$server       = $ARGV[2];
$database     = $ARGV[3];
$home         = $ARGV[4];
$list_codes   = $ARGV[5];
$filename     = $ARGV[6];
$base_country = $ARGV[7];
$page_count   = $ARGV[8];
$description  = $ARGV[9];

# invoke sybperl from DBlib
use Sybase::DBlib;
use Sybase::Sybperl;

# open the output file
$filename = "$filename.TXT";
$filepath = "$home/gml_work_space";

open (OUT, "> $filepath/$filename") || die "can not open $filename";

# specify db error message handles
&dbmsghandle ("message_handler"); # Sybase error messages
&dberrhandle ("error_handler");   # Errors in SQL queries

# create three db handles
$dbh1 = new Sybase::DBlib "$user_id", "$password", "$server";

$dbh1->dbuse("$database");

@list_codes = (($D1, $D2) = split(/~/, $list_codes));

if ($D2 eq "")
  {
    $DF = $D1;
  }
else
  {
    $DF = "$D1 OR $D2";
  }

$description = "$description" ." F-$filename$page_count";

# insert mail history
$dbh1->dbcmd (" BEGIN TRANSACTION");

&dbsqlexec ($dbh1);
&dbresults ($dbh1);
while (&dbnextrow ($dbh1))
  {
    # do nothing.
  }

$dbh1->dbcmd ("DECLARE \@list_id    int,     
                       \@history_id int,
                       \@own_id     char(10), 
                       \@cnt        int

		SELECT \@list_id = MAX(saved_list_id) + 1
                  FROM gml_saved_list 

	        SELECT \@history_id = MAX(mail_history_id) + 1
		  FROM gml_mail_history 

                SELECT \@own_id = owner_id 
	          FROM gml_user
		 WHERE unix_id = $user_id

	   INSERT INTO gml_saved_list VALUES 
		       (\@list_id, GETDATE(), '$description', 'L', '$DF')

           INSERT INTO gml_saved_list_contents (contact_id,
		             saved_list_id)
		    SELECT distinct A.contact_id, \@list_id
		      FROM gml_mlc_view A, gml_london_fax_cost B,
		           gml_country C
                     WHERE A.comp_country_cd = C.country_cd
		       AND A.ret_fax_dt      = 'Jan 1 1900 12:00AM'
		       AND A.comp_country_cd = B.country_cd");
&dbsqlexec ($dbh1);
&dbresults ($dbh1);

while (&dbnextrow ($dbh1))
  {
    # do nothing.
  }

if ($D2 eq "")
  {
    $dbh1->dbcmd (" AND A.list_cd = '$D1' ");
  }
else
  {
    $dbh1->dbcmd (" AND A.list_cd IN \('$D1', '$D2'\) ");
  }


$dbh1->dbcmd ("SELECT \@list_id = MAX(saved_list_id) + 1
              FROM gml_saved_list");

&dbsqlexec ($dbh1);
&dbresults ($dbh1);

while (&dbnextrow ($dbh1))
  {
    # do nothing.
  }

print "list_id = '\@list_id'\n";

#&dbsqlexec ($dbh1);
#&dbresults ($dbh1);
#while (&dbnextrow ($dbh1))
#  {
#    # do nothing.
#  }

$dbh1->dbcmd ("SELECT \@history_id = MAX(mail_history_id) + 1
		        from gml_mail_history");

print "history id = '\@history_id'\n";
#&dbsqlexec ($dbh1);
#&dbresults ($dbh1);
#while (&dbnextrow ($dbh1))
#  {
#    # do nothing.
#  }

$dbh1->dbcmd ("SELECT \@own_id = owner_id from gml_user
		       where unix_id = $user_id");

#print "own id = '\@own_id'\n");

#&dbsqlexec ($dbh1);
#&dbresults ($dbh1);
#while (&dbnextrow ($dbh1))
#  {
#    # do nothing.
#  }

$dbh1->dbcmd ("INSERT INTO gml_saved_list values 
		             (\@list_id, GETDATE(), '$description', 'L',
			      '$DF')");

#&dbsqlexec ($dbh1);
#&dbresults ($dbh1);
#while (&dbnextrow ($dbh1))
#  {
#    # do nothing.
#  }

print "list_id = '\@list_id'\n";

$dbh1->dbcmd ("INSERT INTO gml_saved_list_contents (contact_id,
		             saved_list_id)
		    SELECT distinct A.contact_id, \@list_id
		      FROM gml_mlc_view A, gml_london_fax_cost B,
		           gml_country C
                     WHERE A.comp_country_cd = C.country_cd
		       AND A.ret_fax_dt      = 'Jan 1 1900 12:00AM'
		       AND A.comp_country_cd = B.country_cd");

print "contact_id = :contact_id\n";

if ($D2 eq "")
  {
    $dbh1->dbcmd (" AND A.list_cd = '$D1'");
  }
else
  {
    $dbh1->dbcmd (" AND A.list_cd IN \('$D1', '$D2'\)");
  }

#&dbsqlexec ($dbh1);
#&dbresults ($dbh1);

#while (&dbnextrow ($dbh1))
#  {
#    # do nothing.
#  }
		      
$dbh1->dbcmd ("SELECT \@cnt = COUNT(*) 
		 FROM gml_saved_list_contents
	        WHERE saved_list_id = \@list_id ");

#&dbsqlexec ($dbh1);
#&dbresults ($dbh1);

print "count = \@cnt \n";

#while (&dbnextrow ($dbh1))
#  {
#    # do nothing.
#  }

print "owner_id = \@own_id \n";

$dbh1->dbcmd ("INSERT INTO gml_mail_history VALUES (\@history_id,
		           \@list_id, 'LNDFX', '$description',
			   ' ', GETDATE(), \@own_id, \@cnt)");

&dbsqlexec ($dbh1);
&dbresults ($dbh1);

while (&dbnextrow ($dbh1))
  {
    # do nothing.
  }

print "owner_id = \@own_id \n";

$dbh1->dbcmd ("COMMIT TRANSACTION");

&dbsqlexec ($dbh1);
&dbresults ($dbh1);

while (&dbnextrow ($dbh1))
  {
    # do nothing.
  }

&dbexit();
close(OUT);