Re^3: Connecting to MS SQL using ADODB and ActivePerl

Well, is it necessary to use a DSN connection? With ADO it's possible to use DSN-less connections. Here's a connection string I use regularly (needs MDAC 2.6+).

my $ConnStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Se
+curity Info=False;Data Source=hostname;Initial Catalog=Database;";

my $AdoConn = new Win32::OLE("ADODB.Connection");
   $AdoConn->Open($ConnStr);
[download]

If you need to specify a user, here is the syntax:

my $ConnStr = "Provider=SQLOLEDB.1;User Id=user; Password=password; Da
+ta Source=hostname; Initial Catalog=Database;";
[download]

--
The ability to destroy planets is insignificant next to the power of the Deacon Effect.

Comment on Re^3: Connecting to MS SQL using ADODB and ActivePerl Select or Download Code

Replies are listed 'Best First'.
Re: Re^3: Connecting to MS SQL using ADODB and ActivePerl by qadwjoh (Scribe) on Feb 07, 2003 at 09:35 UTC
Tired this... `my $connect_str = "Provider=SQLOLEDB.1;" . "User Id=$uid;Password=$pwd;" . "Data Source=$svr;" . "Initial Catalog=$db"; my $conn = new Win32::OLE('ADODB.Connection') or die "Cannot make ADODB connection : " . Win32::OLE->LastError; $conn->Open($connect_str) or die "Cannot connect to $db on $svr : " . Win32::OLE->LastError; $conn->Close;` [download] ...but I'm still getting the same problem... `Cannot connect to $db on $svr : 0 at adodb.pl line 22` [download] I tried using Win32::ODBC to connect, which I got working ok, but this method doesn't support stored procedures with output parameters, which is what I really need to do. I'm looking into DBI too. Anyone got any ideas how to do this?	[reply] [d/l] [select]
Re: Re^3: Connecting to MS SQL using ADODB and ActivePerl by qadwjoh (Scribe) on Feb 11, 2003 at 08:59 UTC
Thanks for your help, but I got it working a different way - using DBI. For anyone who's interested - it seems difficult to find info on accessing MS SQL Server from Perl. I used DBI and DBD::ODBC - I was able to get a connection going with Win32::ODBC and RDBAL but wasn't sure about running stored procedures with these methods. Here's the code...note you need to install DBI, DBD::ODBC and set up a system DNS to connect to database from your ODBC data sources control panel - there may be a way to get a DSN-less connection though. Also, you must initialise any parameter being bound to a placeholder and used as an output parameter to the stored procedure to some valid value of the expected datatype. If you're expecting a string then initalise to ' ' or other string value. If you're getting some datatype conversion error ("error converting data type ... to ..."), then this could be the problem. use DBI; use DBI qw(:sql_types); my $MSSQL_uid = 'myuid'; my $MSSQL_pwd = 'mypwd'; my $MSSQL_svr = 'mysqlsvr'; my $MSSQL_db = 'mydb'; my $MSSQL_dsn = "dbi:ODBC:$MSSQL_db"; my $sth; # statement handle # connect to DB my $dbh = DBI->connect($MSSQL_dsn, $MSSQL_uid, $MSSQL_pwd) or die "Couldn't connect to $MSSQL_svr : $DBI::errstr\n"; print "\n\nConnected to $MSSQL_dsn DS\n\n"; # execute stored procedures and get output parameter value my $param1_int = 0; # note you must initalise this to something my $exe_string = "EXECUTE my_stored_procedure" . "?, " . "'$param2_str', " . "'$param3_str', " . "$param4_int"; $sth = $dbh->prepare($exe_string) or die "\nCouldn't prepare statement : $DBI::errstr\n"; $sth->bind_param_inout(1, \$param1_int, 50) or die "\nCouldn't bind param inout : $DBI::errstr\n"; $sth->execute or die "\nCouldn't execute stored procedure : $DBI::errstr\n"; print "Output parameter value is now $param1_int\n"; # disconnect from DB $dbh->disconnect or die "\nError disconnecting : $DBI::errstr\n"; [download] Andrew	[reply] [d/l]