Using the ODBC Framework (Perl for System Administration)

7.3. 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.[3]

[3]There's a third flavor, file, which writes the DSN configuration information out to a file so it can be shared among several computers, but it isn't created by the Win32::ODBC method call we're about to use.

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:

# connect to the named DSN, returns a database handle
$dbh=new Win32::ODBC("DSN=PerlSysAdm;UID=$username;PWD=$pw;");
die "Unable to connect to DSN PerlSysAdm:" . Win32::ODBC::Error(  ) . "\n" 
  unless (defined $dbh);

Step 3: Send SQL commands to the server

The ODBC equivalent of DBI's do(), prepare( ), and execute( ) is a little bit simpler because the Win32::ODBC module has a single method, Sql( ), for sending commands to a server. Though ODBC theoretically has a notion of prepared statements and placeholders, they are not implemented in the current Win32::ODBC module.[4]Win32::ODBC also does not use statement handles; all communication takes place through the initial database handle opened by the new method above. We're left with the simplest of command structures:

[4]At the time of this writing, Dave Roth was beta testing a new version of Win32::ODBC that allows for parameter binding. It uses a similar syntax to DBI (i.e., Prepare( )and then Sql( )) with a few ODBC twists thrown in. See http://www.roth.net for more information.

$rc = $dbh->Sql(q{SELECT * from hosts});

TIP

An important distinction between the ODBC and DBI methods: unlike DBI's do( ), the ODBC Sql( ) call returns undef if it succeeds, and some non-zero number if it fails.

If you need to know how many rows were affected by an INSERT, DELETE, or UPDATE query, you would use the RowCount( ) method. Win32::ODBC's documentation notes that not all ODBC drivers implement this call (or implement it for all SQL operations), so be sure to test your driver before relying on it. Like the return code for DBI's execute( ), RowCount( ) will return -1 if the number of rows returned is not available to the driver.

Here's the equivalent ODBC code for the DBI do( ) example in the previous section:

if (defined $dbh->Sql(q{UPDATE hosts 
                        SET bldg = 'Main' 
                        WHERE name = 'bendir'})){
  die "Unable to perform update: ".Win32::ODBC::Error(  )."\n"
}
else {
  $results = $dbh->RowCount(  );
}

Step 4: Retrieve SELECT results

Retrieving the results of a SELECT query under ODBC is performed in a fashion similar to DBI's method, with one twist. First, fetching the data from the server and accessing it are two separate steps under Win32::ODBC. FetchRow( )gets the next row, returning 1 if it succeeds, undef if it does not. Once we've got the row we can choose one of two methods to access it.

Data( ) returns a list of the returned columns when called in a list context. It returns all of the columns concatenated together if called in a scalar context. Data( ) can take an optional list argument to specify which columns are returned and in what order (otherwise they are returned in an "unspecified" order according to the documentation).

DataHash( ) returns a hash with the column names as keys for the column values. This is similar to DBI's fetchrow_hashref( ) except it returns a hash instead of a hash reference. LikeData( ), DataHash( ) can also take an optional list argument to specify which columns are returned.

In context, they look like this:

if ($dbh->FetchRow(  )){
  @ar = $dbh->Data(  ); 
  do-stuff-with-@ar-values 
}

and:

if ($dbh->FetchRow(  )){
   $ha = $dbh->DataHash('name','ipaddr'); 
   do-stuff-with-$ha{name}-and-$ha{ipaddr}
}

Just for parity's sake in this discussion, the information we found through DBI's statement handle attribute {NAME} can be found in Win32::ODBC-land via the FieldNames( ) call. If you need to know the number of fields (like in {NUM_OF_FIELDS}), you'll have to count the number of elements in the list returned by FieldNames( ).

Step 5: Close the connection to the server

$dbh->close(  );

If you created a DSN and want to delete it to clean up after yourself, use a statement similar to the one used to create it:

# replace ODBC_REMOVE_DSN with ODBC_REMOVE_SYS_DSN if you created a system DSN
if (Win32::ODBC::ConfigDSN(ODBC_REMOVE_DSN,
                           "SQL Server","DSN=PerlSysAdm")){
  print "DSN deleted\n";
}
else {
  die "Unable to delete DSN:".Win32::ODBC::Error(  )."\n";
}

You now know how to work with a database from Perl using both DBI and ODBC. Let's put your knowledge to work with some more extended examples from the database administration realm.