Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

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

( #432060=categorized question: print w/ replies, xml ) Need Help??
Contributed by legato on Feb 17, 2005 at 19:49 UTC
Q&A  > database programming


Description:

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?

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

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*
Answer: How do I create/remove ODBC DSN entries under Windows (Win32)?
contributed by legato

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
Answer: 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; }
Answer: How do I create/remove ODBC DSN entries under Windows (Win32)?
contributed by Discipulus

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*

Please (register and) log in if you wish to add an answer



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • 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 chilling in the Monastery: (11)
    As of 2014-08-22 10:47 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      The best computer themed movie is:











      Results (153 votes), past polls