Read data from SQL Server using Perl and ADODB

How to read data from a SQL Server using Perl and ADODB.  Maybe a bit off topic, but we've shown you quite a few examples of how to search Active Directory.  In fact, almost all of our scripting examples involve some sort of search.  In Perl and VBScript, we use ADO (ActiveX Data Objects) to search Active Directory like a database.  Well, we might as well show how to search a SQL database too. 

This script access the database "myDatabase" on the server "myServer", which has a table called "table1".  You can change these to point to a database in your environment.

We start by creating an ADODB connection object, give it a connection string, and then open it.  The connection string tells ADO to use the SQL OLEDB database provider, and to use our current Windows logon credentials (Integrated Security=SSPI).

Next, we create an ADODB recordset object, and call the open method, passing it our SQL query, and point it at our connection.  We can then walk through the records.

Rather than hard-code the field names (after all, I can only imagine what's in your crazy database), this script enumerates the fields with a for loop, then prints each field name and value for each record.

use Win32::OLE;

$server = "myServer";
$database = "myDatabase";
$table = "table1";

$conn = Win32::OLE->CreateObject("ADODB.Connection");
$conn->{ConnectionString} = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=$database;Data Source=$server";
$conn->open;

$rs = Win32::OLE->CreateObject("ADODB.RecordSet");
$rs->open("select * from $table",$conn);

print "$rs->{RecordCount} records found\n";

until ($rs->EOF){
 for($fieldNum = 0; $fieldNum < $rs->Fields->{Count}; $fieldNum++){
  $fieldName = $rs->Fields($fieldNum)->{Name};
  $fieldValue = $rs->Fields($fieldNum)->{Value};
  print "$fieldName: $fieldValue\n";
 }
 print "\n";
 $rs->MoveNext;
}

Stay Tuned for more SQL related Perl scripts...