Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

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

by legato (Monk)
on Feb 17, 2005 at 19:49 UTC ( #432060=perlquestion: print w/replies, xml ) Need Help??

legato has asked for the wisdom of the Perl Monks concerning the following question:

When deploying a Perl database application, it is occasionally necessary to create ODBC DSN entries in Windows — users can not always be asked to do this, how can it be done programmatically?

Originally posted as a Categorized Question.

  • Comment on How do I create/remove ODBC DSN entries under Windows (Win32)?

Replies are listed 'Best First'.
Re: How do I create/remove ODBC DSN entries under Windows (Win32)?
by Discipulus (Abbot) on Feb 18, 2005 at 09:05 UTC
    hallo monks, like always there is more then one way..I used directly the registry and it works fine. this way permit to create easely system DSN on remote machines. The follow code is only the sub which create the DSN but with Tie::Registry is easier destroy then create.
    #!perl #(...) use Win32::TieRegistry ( Delimiter=>"/" ); #(...) sub Crea_dsn ################################################## { my $ip = shift; my $dbq= shift; my $nuovo_dsn= shift; my $descrizione="DB utente "; unless( $registry_remoto = $Registry->Connect( "$ip", "LMachine" )){r +eturn "Errore connessione registry remoto: $^E"} my $odbc_ini = $registry_remoto->{"Software/ODBC/ODBC.INI"}||return " +$^E"; my $odbc_data_sources= $odbc_ini->{"ODBC Data Sources"}||return "$^E" +; foreach my $dsn(sort keys %{$odbc_data_sources}) { $dsn=~s/\///; $dsn eq $nuovo_dsn ? return "$nuovo_dsn gia\' in uso\n" : +next; } $odbc_data_sources->{$nuovo_dsn} = "Microsoft Access Driver (*.mdb)"| +|return "$^E"; $odbc_ini->{$nuovo_dsn}={'DBQ'=>$dbq, 'Description'=>$descrizione, 'Driver'=>'C:\WINNT\System32\odbcjt32.dll', 'DriverID'=>['0x00000019','REG_DWORD'], 'FIL'=>'MS Access;', 'SafeTransactions'=>['0x00000000','REG_DWORD' +], 'UID'=>'', 'Engines'=>{ 'Jet'=>{'ImplicitCommitSync'=>'' +, 'MaxBufferSize'=>['0x0000 +0800','REG_DWORD'], #(hex(800)) da 2048 'PageTimeout'=>['0x000000 +05','REG_DWORD'], 'Threads'=>['0x00000003', +'REG_DWORD'], 'UserCommitSync'=>'yes' } } }||return "$^E"; return ("creato DSN: $nuovo_dsn su $dbq"); }
    I hope this can help
    cheers from sunny roma Lor*
Re: How do I create/remove ODBC DSN entries under Windows (Win32)?
by legato (Monk) on Feb 17, 2005 at 19:53 UTC
    Sample code, tested on platforms Windows NT through XP.
    use Win32::ODBC; print "Add DSN successful" if add_dsn('TestDSN'); print "Del DSN successful" if del_dsn('TestDSN'); sub add_dsn { my $dsn = shift; my $result = Win32::ODBC::ConfigDSN( ODBC_ADD_DSN, "SQL Server", ## this last is the driver name ( ##options "DSN=$dsn", ## DSN identifier "NETWORK=DBMSSOCN", ## use TCP/IP library "SERVER=test_server", ## server name "DATABASE=test_db", ## database name "DESCRIPTION=My test DSN" ) ); ## end of ConfigDSN call unless ($result) { print STDERR "Couldn't create $dsn because of error: " .Win32::ODBC::Error()."\n"; return undef; } return 1; } #^^ add_dsn sub del_dsn { my $dsn = shift; my $result = Win32::ODBC::ConfigDSN( ODBC_REMOVE_DSN, "SQL Server", ("DSN=$dsn") ); ## see add_dsn comments for more detail. unless ($result) { print STDERR "I failed to remove $dsn : " .Win32::ODBC::Error()."\n"; return undef; } return 1; } #^^ del_dsn
Re: How do I create/remove ODBC DSN entries under Windows (Win32)?
by knbknb (Acolyte) on Feb 13, 2008 at 12:31 UTC
    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; }
Re: How do I create/remove ODBC DSN entries under Windows (Win32)?
by Discipulus (Abbot) on Feb 14, 2008 at 11:53 UTC
    hello there !! in the spirt of the "there is more ways to do this" I offer my registry hacking solution to create an odbc connection to an Access db (it be can changed very easily in this way: create your new custom DSN and check the registry at LocalMachine/Software/ODBC/ODBC.INI and change the script in accord with your needs)
    ##at the beginning of the scripts use Win32::TieRegistry ( Delimiter=>"/" ); #then &create_dsn(10.0.0.55,'c:/your/path/aaa.mdb','myCustomDSN'); #here the good stuff!! sub create_dsn ##################################################### +####### { my $ip = shift; my $dbq= shift; my $nuovo_dsn= shift; my $descrizione="your description "; unless( $registry_remoto = $Registry->Connect( "$ip", "LMachine" )){r +eturn "Error connecting to remote registry: $^E"} my $odbc_ini = $registry_remoto->{"Software/ODBC/ODBC.INI"}||return " +$^E"; my $odbc_data_sources= $odbc_ini->{"ODBC Data Sources"}||return "$^E" +; #be carefull if there are no dsn on that machine then no "ODBC D +ata Sources" exists!! foreach my $dsn(sort keys %{$odbc_data_sources}) { $dsn=~s/\///; $dsn eq $nuovo_dsn ? return "$nuovo_dsn yet exists!! plz +try other name!!\n" :next; } $odbc_data_sources->{$nuovo_dsn} = "Microsoft Access Driver (*.mdb)"| +|return "$^E"; $odbc_ini->{$nuovo_dsn}={'DBQ'=>$dbq, 'Description'=>$descrizione, 'Driver'=>'C:\WINNT\System32\odbcjt32.dll', 'DriverID'=>['0x00000019','REG_DWORD'], 'FIL'=>'MS Access;', 'SafeTransactions'=>['0x00000000','REG_DWORD' +], 'UID'=>'', 'Engines'=>{ 'Jet'=>{'ImplicitCommitSync'=>'' +, 'MaxBufferSize'=>['0x0000 +0800','REG_DWORD'], #(hex(800)) da 2048 'PageTimeout'=>['0x000000 +05','REG_DWORD'], 'Threads'=>['0x00000003', +'REG_DWORD'], 'UserCommitSync'=>'yes' } } }||return "$^E"; return ("created DSN: $nuovo_dsn on $dbq"); }
    Your remaining task is the sub to delete it but is quite easy.

    hope this help
    Lor*

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://432060]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (2)
As of 2022-05-21 06:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (76 votes). Check out past polls.

    Notices?