This example works for Sybase servers. With few modifications, may also work for MSSQL Servers.
This code uses the old Win32::ODBC Module, not the DBI Module. Porting to DBI might be relatively easy, though.
You must edit the credentials in the CfgSysDSN function.
# for each database with name starting with "d" found on the sybase s
+erver
# 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 ODB
+C 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%' orde
+r 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=", "Raisee
+rrorPositionBehavior=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 delimit
+er 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 delimit
+er 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;
}
| 