##########################################################################
#
# routine: db_get_associations.pl
#
# Purpose: Look up the associations for a particular member in the
#          asn_members relation.   Return a list of the asn names.
#
# Input:   $MEMBER - name of association member to look up ASNs for
#          ENV.OPUS_SERVER - name of DB server
#          ENV.OPUS_DB - name of database containing asn_members
#
# Return:  @asns - list of ASN names
#          or empty list if no ASNs or db error
#
# Calls:   PrintMsg
#
# modification history:
#
#   date    opr     who     reason
# -------- -----  --------  --------------------------------------
# 03/30/01 43165  MSwam     first version
# 03/25/10 64274  MSwam     Replace ST_DBlib with DBI
# 06/30/10 64432  MSwam     Use single quotes for SQLServer
# 09/18/12 72255  Sherbert  get rid of DSQUERY
# 
#########################################################################
sub DB_get_associations{
    my ($MEMBER) = @_;                           # ARGUMENTS from @_
    my ($db, $query, @asns);  # LOCAL VARS
    my ($program_id, $obset_id, $member_num);

    # initialize as if failed
    @asns = ();

    require 'do_dbi_pkg.pl';

    $server = $ENV{"OPUS_SERVER"};
    $database = lc($ENV{"OPUS_DB"});

    my $db = STScI::DBI->connect( "dbi:Sybase:server=$server");

    if (!defined($db)) {
        PrintMsg("E","Cannot connect to server: $DBI::errstr");
        return;
    }
    DoDBI($db,"use $database"); # open database

    # fields from the ipppssoot
    $program_id = substr($MEMBER, 1, 3);
    $obset_id = substr($MEMBER, 4, 2);
    $member_num = substr($MEMBER, 6, 2);

    $query = <<"EOQ";
SELECT association_id FROM asn_members
WHERE program_id = '$program_id' and
      obset_id = '$obset_id' and
      member_num = '$member_num'
EOQ

    PrintMsg("D","search for ASNs for member $MEMBER");

    $sth = DoDBIexecute( $db, $query);
    while ( ( $asn ) = DoDBIfetch( $db, $query, $sth) ) {
        PrintMsg("D","found $asn");
        push(@asns, $asn);
    }

    DoDBIclose($db);
    @asns;   # return value
}
1;