Perl for System Administration

Using the ODBC Framework

The basic steps for using ODBC are similar to the DBI steps we just discussed.

Step 1: Load the necessary Perl module

                     
                     use Win32::ODBC;

Step 2: Connect to the database and receive a connection handle

ODBC requires one preliminary step before making a connection. We need to create a Data Source Name. A DSN is a named reference that stores the configuration information (e.g., server and database name) needed to reach an information source like an SQL server. DSNs come in two flavors, user and system, distinguishing between connections available to a single user on a machine and connections available to any user or service.^[26]

DSNs can be created either through the ODBC control panel under Windows NT/2000, or programmatically via Perl. We’ll take the latter route, if just to keep the snickering down among the Unix folks. Here’s some code to create a user DSN to our database on an MS-SQL server:

# creates a user DSN to a Microsoft SQL Server
# note: to create a system DSN, substitute ODBC_ADD_SYS_DSN 
# for ODBC_ADD_DSN
if (Win32::ODBC::ConfigDSN(
                   ODBC_ADD_DSN, 
                   "SQL Server", 
                   ("DSN=PerlSysAdm", 
                    "DESCRIPTION=DSN for PerlSysAdm", 
                    "SERVER=mssql.happy.edu",  # server name
                    "ADDRESS=192.168.1.4",     # server IP addr
                    "DATABASE=sysadm",         # our database
                    "NETWORK=DBMSSOCN",        # TCP/IP Socket Lib
                   ))){
                               print "DSN created\n";
                             }
else {
   die "Unable to create DSN:" . Win32::ODBC::Error(  ) . "\n";
}

Once we have a DSN in place, we can use it to open a connection to our database: ...