Feb 24, 2009

perl ado connection for sql server , oracle, MySQL by Win32::OLE

A easy way to connect database server and get data by using perl Win32::OLE.
The connection string is same with asp or vbscript.

use Win32::OLE;
#for sqlserver with user/password
#my $ConnStr="driver={SQL Server};server=;AutoTranslate=No;uid=sa;pwd=xxx;Network=Dbmssocn;Database=master;";

#for oracle , must install oracle client first to get connction str
#my $ConnStr="driver={Microsoft ODBC for Oracle};server=;uid=system;pwd=xxx;";

#for sqlserver with Trusted_Connection
my $ConnStr="driver={SQL Server};server=;AutoTranslate=No;Trusted_Connection=yes;Network=dbnmpntw;Database=master;";

#For MySQL
my $connCMDBstr="DRIVER={MySQL ODBC 3.51 Driver};SERVER=192.168.2.1; PORT=3306;DATABASE=Test; USER=root; PASSWORD=; OPTION=3;";


my $Conn = Win32::OLE-> new('ADODB.Connection');
$Conn-> Open($ConnStr);
my $err = Win32::OLE::LastError();
if (not $err eq "0")
{
print"FATAL: no connection, OLE error 0x%08x: $err\n";
exit;
}

my $Statement = "select * from sysdatabases";
if(! ($RS = $Conn->Execute($Statement)))
{
print Win32::OLE->LastError();
exit;
}
while (! $RS->EOF)
{
$servername= $RS->Fields(0)->value;
print"servername is: $servername\n";
$RS->MoveNext;
}
$RS->Close;
$Conn->Close;

0 意見: