Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

mssqlserver dsn

by Anonymous Monk
on Mar 24, 2003 at 21:28 UTC ( [id://245548]=perlquestion: print w/replies, xml ) Need Help??

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

Hi monks, I'm trying to create a DSN using the Win32::ODBC package; however, it doesn't seem to be working. my code slice:
use Win32::ODBC; if (Win32::ODBC::ConfigDSN( ODBC_ADD_SYS_DSN, "SQL Server", ("DSN=MyDSN", "DESCRIPTION=none", "SERVER=Machine1", "DATABASE=db1", "UID=sa", "PWD=sa") )) { print "$dsn was created successfully.\n"; } else { print "$dsn could not be created.\n"; }
i cannot find any documentation on sqlserver and win32::ODBC. Can someone help me or point me in the right direction? thanks, Michael

Replies are listed 'Best First'.
Re: mssqlserver dsn
by Rhose (Priest) on Mar 24, 2003 at 21:58 UTC
    Not exactly the answer to your question, but here is some concept code I left in my temp directory which might help.
    #/usr/bin/perl -w use strict; use DBI; my $DSN = 'dbi:ODBC:driver={SQL Server};server={myserver};database={my +db}'; my $dbh = DBI->connect($DSN, undef, undef) or die "$DBI::errstr\n"; my $gSQLCmd = 'SELECT col1, col2, col3 FROM mytable'; my $gSQLHandle = $dbh->prepare ( $gSQLCmd ) || die 'Error with SQL statement ['.$DBI::errstr.' - '.$DBI::err.' +]'; $gSQLHandle->execute() || die 'Error with SQL statement ['.$DBI::errst +r.' - '.$DBI::err.']'; while (my @gFields = $gSQLHandle->fetchrow_array) { print 'Row: ',$gFields[0],"\t",$gFields[1],"\t",$gFields[2],"\n"; } $dbh->disconnect;

    The code will work a little better if you replace myserver, mydb, and mytable with ones from your actual system. *Smiles* Also, please note that this code connects to the database without a userid or password -- it is using my domain account for authorization.

    Update I forgot to give credit where credit is due -- my code is based on the information from DBD::ODBC Connecting without DSN from Win32 by sparkyichi

Re: mssqlserver dsn
by tachyon (Chancellor) on Mar 24, 2003 at 22:47 UTC

    LastError() is your friend. Use it as all is often revealed.

    cheers

    tachyon

    s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

      Hi tachyon. thanks so much for your time & interest... are you talking about this:
      print Win32::OLE->LastError(), "\n";
      because if you are, it unfortunately returns NOTHING! thanks, michael
        You can also use Win32::GetLastError() which is included in core perl. To get the text based version of that, Win32::FormatMessage(Win32::GetLastError()) or "$^E"
Re: mssqlserver dsn
by ibanix (Hermit) on Mar 24, 2003 at 21:56 UTC
    Hi Michael,

    Update: I forgot to mention -- this requires you to
    use Win32::TieRegistry

    I actually haven't tried Win32::ODBC yet; thanks for mentioning it! I'll have to look at it.

    I've been creating and editing DSNs by directly manipulating the registry. While this is a bit of a hack, it works ok for my means. Here's some code for you, which works with MS SQLServer:

    ## create_dsn($dsn, $pointer, @servers) ## Creates a DSN named $dsn pointing to $pointer on servers named @ser +vers sub create_dsn { my $dsn = shift(@_); my $pointer = shift(@_); my @servers = @_; foreach my $server (@servers) { my $ref = $Registry->{"//$server/LMachine/Software/ODBC/ODBC.I +NI/"}; if (not exists $ref->{"$dsn/server"}) { print "$server: Creating DSN $dsn, setting to $pointer... +"; $ref->{"$dsn/"} = { 'Description' => "", 'Server' => "$pointer", 'Database' => "$dsn", 'Driver' => "C:\\WINNT\\System32\\SQLSRV32.dll", 'OEMTOANSI' => "YES", 'UseProcForPrepare' => "YES", 'TrustedConnection' => "", }; print " done\n"; print "$server: Updating ODBC Data Sources list... "; $ref->{"ODBC Data Sources/$dsn"} = "SQL Server"; print " done\n"; } else { print "$server: DSN $dsn already exists!\n"; } } print "\n\tRemember to edit the new DSNs with username and passwor +d!\n"; }
    Cheers,
    ibanix

    P.S: I should note that this doesn't work with NT Server 4.0, just Win2k and beyond.

    $ echo '$0 & $0 &' > foo; chmod a+x foo; foo;
      Hi Ibanix!!!! Thanks for the code. I'm guessing if i can't get the Win32::ODBC::ConfigDSN to work, i'll probably do something similar (edit the registry). thanks again. michael

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others perusing the Monastery: (7)
As of 2024-03-28 21:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found