#  for each database with name starting with "d" found on the sybase server 
#    Add an ODBC DSNs to the win32 client 
#  ----------------------------------------
#  based on Example 7.34.
#  "Win32 Perl Programming: The Standard Extensions" by Dave Roth
#  Published by Macmillan Technical Publishing.
#  ISBN # 1-57870-067-1


$odbc1500  = "Adaptive Server Enterprise"; # ASE 15.0 driver
$odbc42  = "Sybase ASE ODBC Driver"; # ASE 12 driver - ODBC 4.x or ODBC 3.x

# make -w switch happy
$odbc42 =~ s/^\s+//;
$odbc1500 =~ s/^\s+//;

my $origdsn = "icdp_unified";
my $uid="my_login";
my $pwd="";
my $sql ="SELECT * FROM master..sysdatabases where name like 'd%' order by 1 ";
# also customize strings in sub CfgSysDSN !

use Win32::ODBC;
use Win32::TieRegistry ( Delimiter=>"/", ArrayValues=>1 );



my $iCount = 0;
my $d = $odbc1500; #$odbc1500 ); #does not work

if ($d =~ /Adaptive/){
    	  listDSNentries($origdsn);
}

print  "\n\nList of DSNs before\n";
#printDSNList($d);    
print  "\n\n\n";

$db = new Win32::ODBC("DSN=$origdsn;UID=$uid;PWD=$pwd") or die "Error: " . Win32::ODBC::Error();
if ( !$db->Sql($sql) ) {
    print "Adding new DSNs\n\n";
    while ( my $r = $db->FetchRow() ) {
        if ($r) {
            %Data = $db->DataHash();
            printf( "%d) %s \n", ++$iCount, $Data{name} );
            CfgSysDSN( $Data{name}, $d );
        } else {
            print "Fetch error: " . $db->Error();

        }
    }
} else {
    print "SQL Error: " . $db->Error();
}
$db->Close();

print  "\n\nList of DSNs after, filter: $d\n";
printDSNList($d);    

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

#system DSN
sub CfgSysDSN {
    my ( $n, $d ) = @_;
    my $Driver = $d;
    my $s = "MYSERVER ASE 12.5.3";
    my $o = "ASE_ODBC35";
    my $DSN="${n}_${o}";
    # Create a System DSN...
    #if ( Win32::ODBC::ConfigDSN( ODBC_ADD_SYS_DSN, $Driver, "DSN=$DSN","Database=$n", "NetworkAddress=my.hostname.com 4100",
    #"Description=DB $n on dc5 ASE 12.5.3 ODBC driver 4.2.", "LogonID=my_login", "PWD=", ) ) 
    #if ( Win32::ODBC::ConfigDSN( ODBC_ADD_SYS_DSN, $Driver, "DSN=$DSN", "ServerName=SYBASE_DC5", "Database=$n", 
    #"Description=DB $n on $s $o", "LogonID=my_login", "PWD=", "RaiseerrorPositionBehavior=0") ) 
    if ( Win32::ODBC::ConfigDSN( ODBC_ADD_SYS_DSN, $Driver, "dsn=$DSN","server=my.hostname.com", "database=$n", "port=4100") ) 
    {
	print "Added DSN    $DSN        Description=DB $n on $s $o\n";
	
	if ($d =~ /ODBC Driver/){
	  addWorkArounds2($DSN) 
	}
	

    } else {
      print "database $n \t:  can't add DSN. \n";
      print Win32::ODBC::Error() if Win32::ODBC::Error() ;
         
    }
    
}

sub printDSNList {
my $filter = shift;
$filter ||= ".";
my $c = 0;
if ( my %DSNList = Win32::ODBC::DataSources() ) {
        foreach my $Name ( sort keys(%DSNList) ) {
            #next unless $Name           =~ /somefilter/;
            #next unless $Name           =~ /^_/;
            next unless $DSNList{$Name} =~ /$filter/i;            
            printf  "%d) $Name = '%s'\n", ++$c, $DSNList{$Name};
  

        }
    }
}


sub addWorkArounds2 {
        my $DSN = shift;
        #    $Registry->Delimiter("/");                  # Set delimiter to "/".
       my $Key= $Registry->{"LMachine/Software/ODBC/ODBC.INI/$DSN"};
        #Setting values
       $Key->{"WorkArounds2"}= "8192";
       undef $Key;
}



sub listDSNentries {
        my $DSN = shift;
        #    $Registry->Delimiter("/");                  # Set delimiter to "/".
       my $Key= $Registry->{"LMachine/Software/ODBC/ODBC.INI/$DSN"};
        #Setting values
       
       foreach my $k (sort  keys( %{$Key} )){
          
          print substr($k, 1). "\t$Key->{$k}[0]\n";
        }
       
       undef $Key;
}