Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

Answer: How do I create/remove ODBC DSN entries under Windows (Win32)?

( #667763=categorized answer: print w/ replies, xml ) Need Help??

Q&A > database programming > How do I create/remove ODBC DSN entries under Windows (Win32)? contributed by knbknb

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; }

Comment on Answer: How do I create/remove ODBC DSN entries under Windows (Win32)?
Download Code
Log In?
Username:
Password:

What's my password?
Create A New User
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (7)
As of 2014-10-25 16:52 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (146 votes), past polls