Chapter 7. SQL Database Administration
Interacting with an SQL Server from Perl
In order to be a database-literate system administrator, you have to speak a little Structured Query Language (SQL), the lingua franca of most commercial and several noncommercial databases. Writing scripts in Perl for database administration requires some SQL knowledge because these scripts will contain simple embedded SQL statements. See Appendix D, "The Fifteen-Minute SQL Tutorial", for enough SQL to get you started. The examples in this chapter use the same databases in previous chapters to keep us from straying from the system administration realm.
7.1. Interacting with an SQL Server from Perl
There are two standard frameworks for communication with an SQL server: DBI (DataBase Interface) and ODBC (Open DataBase Connectivity). Once upon a time, DBI was the Unix standard and ODBC the Win32 standard, but this distinction has started to blur now that ODBC has become available in the Unix world and DBI has been ported to Win32. Further blurring the lines is the DBD::ODBC package, a DBD module that speaks ODBC from within the DBI framework.
DBI and ODBC are very similar in intent and execution, so we'll show you how to use both simultaneously. Both DBI and ODBC can be thought of as "middleware." They form a layer of abstraction that allows the programmer to write code using generic DBI/ODBC calls, without having to know the specific API of any particular database. It is then up to the DBI/ODBC software to hand these calls off to a database-specific layer. The DBI module calls a DBD driver for this; the ODBC Manager calls the data source-specific ODBC driver. This database-specific driver takes care of the nitty-gritty details necessary for communicating with the server in question. Figure 7-1 shows the DBI and ODBC architectures. In both cases, there is a (at least) three-tiered model:
Figure 7.1. DBI and ODBC architectures
The beauty of this system is that code written for DBI or ODBC is extremely portable between different servers from different vendors. The API calls made are the same, independent of the underlying database. That's the idea at least, and it holds true for most database programming. Unfortunately, the sort of code we're most likely to write (i.e., database administration) is bound to be server-specific, since virtually no two servers are administered in even a remotely similar fashion. Experienced system administrators love portable solutions, but they don't expect them.
That somber thought aside, let's look at how to use DBI and ODBC. Both technologies follow the same basic steps, so you may notice a little redundancy in the explanations, or at least in the headings.
The next sections assume you've installed a database server and the necessary Perl modules. For some of our DBI example code, we're going to use the MySQL server; for ODBC, we'll use Microsoft's SQL Server.
Copyright © 2001 O'Reilly & Associates. All rights reserved.