CodeProject: Windows NT Win32 Perl Programming: The Standard Extensions - Chapter 7: Data Access. Free source code and programming help

Report Article

Discuss

Send to a friend

5 votes for this Article.

Popularity: 3.32 Rating: 4.75 out of 5

Title	Windows NT Win32 Perl Programming: The Standard Extensions
Authors	Dave Roth
Publisher	New Riders
Published	Jan 1999
ISBN	1578700671
Price	US 40.00
Pages	630

Chapter 7 - Data Access

For most administrators, their jobs take their Perl programming into realms of maintaining user accounts and managing servers. More and more administrators are finding it important to write scripts that interact with databases, however, whether for Web-based Common Gateway Interface (CGI) scripts or for querying administrative databases for reports.

Many Perl modules and extensions support databases such as the variations of xDBM_File and libraries for SQL Server, Sybase, Oracle, MySQL, Dbase, and various others. A couple of Win32-specific extensions, however, marry the Win32 platform with generic database services such as ODBC. This chapter provides an overview on using the Win32::ODBC extension.

What Is ODBC?

All databases have their own unique way of doing things. When a programmer wants to access database services from within his code, he has to learn how to use that particular database. Really this is not so difficult because most of the major database vendors document their systems and provide libraries that a coder can link to. This is all fine and good as long as you always use one particular database. The moment you need to access a different type of database, however, not only will you have to learn an entire set of commands and procedures, but you will also need to change your scripts so that they can interface with this new database. Usually this means a total rewrite of the code.

Now imagine that you want to write a database application that would work with any database that your client may have. A perfect example is that you want to write a shopping cart CGI script for the Web. Because you do not know which database your client may have implemented, you would have to write several variations of the same script to handle all the possible databases. If this is not more than you want to contend with, just imagine what it would be like to support all those scripts. Testing them would be just as horrific because you would have to install each and every database system.

Wouldn't it be nice if all databases conformed to just one standard so that if you programmed your scripts to utilize it, then one script would work across all database systems? This is where ODBC (Open DataBase Connectivity) comes in.

ODBC is an initiative that the database industry has come to accept as the standard interface. Now many people believe that ODBC is a Microsoft product, but they are incorrect in believing so. Microsoft did champion the API and was one of the first companies to have working implementations of it. The actual interface standard was designed, however, by a consortium of organizations such as X/Open SQL Access Group, ANSI, ISO, and several vendors such as IBM, Novell, Oracle, Sybase, Digital, and Lotus, among others.

The standard was designed to be a platform-independent specification, and it has been implemented on the Win32, UNIX, Macintosh, and OS/2 platforms, just to name a few. ODBC has become so widely accepted that some vendors like IBM, Informix, and Watcom have designed their database products' native programming interface based on ODBC.

ODBC Models

When a program uses ODBC, it just makes calls into functions defined by the ODBC API. When a Perl script makes a call in to the ODBC API, it is typically calling in to a piece of software known as the ODBC Manager. This manager is a dynamic link library (DLL) that decides how to handle the call. Sometimes the Manager will perform some task, such as listing all available Data Source Names (DSNs), and return to the calling script. Other times the ODBC Manager will have to load a specific ODBC driver and request that driver (usually another DLL) to perform the task, such as connecting to the database and executing a query. Each level of software that the ODBC Manager has to pass through to accomplish a task is known as a tier.

ODBC has different tiered models that describe how the basic infrastructure works. Each model is premised on how many tiers exist between the ODBC Manager and the actual database. There could, in theory, be an unlimited number of tiers, but the impracticality of administrating and configuring so many tiers renders only three common models: the one-, two-, and three-tier models.

One-Tier Model

The one-tier model consists of only one step (or tier). The client application talks to the ODBC Manager, which asks the ODBC driver to perform the task. (This is the first tier.) The driver opens the database file.

This is a very simple model in which the ODBC driver performs the work of database lookup and manipulation itself. Examples of single-tier ODBC drivers include Microsoft's Access, FoxPro, and Excel ODBC drivers.

Two-Tier Model

Just like the one-tier model, the client application talks to the ODBC Manager, which talks to the ODBC driver. (This is the first tier.) Then the ODBC driver will talk to another process (usually on another machine via a network), which will perform the actual database lookup and manipulation. (This is the second tier.) Examples of this are IBM's DB2 and MS's SQL Server.

Three-Tier Model

Just like the first two models, in the three-tier model the client application talks to the ODBC Manager that talks to the ODBC driver (tier one). The ODBC driver then talks to another process (usually running on another machine) which acts as a gateway (tier two) and relays the request to the database process (tier three) which can be a database server or a mainframe such as Microsoft's SNA server.

These different tiers are not so important to the programmer as they are to the administrator who needs to configure machines with ODBC installed. However, understanding the basic infrastructure helps in making decisions such as how to retrieve data from the driver so that network traffic is held to a minimum, as discussed later in the section titled "Fetching a Rowset."

Data Source Names

To truly separate the client application from the database, there are Data Source Names (DSNs). DSNs are sets of information that describe to the ODBC Manager what ODBC driver to use, how the driver should connect to the database, what userid is needed to log on to the database, and so forth. This information is collectively referred to by using a simple name, a Data Source Name. I may set up a DSN that tells the ODBC Manager that I will be using a Microsoft SQL Server on computer \\DBServer, for example, and that it will be accessed via named pipes using a userid of 'JoeUser'. All this information I will call "Data."

When writing an application, I will just make an ODBC connection to the DSN called "Data" and ODBC will take care of the rest of the work. My application needs only to know that I connect to "Data," and that is it. I can later move the database to an Oracle server, and all I need to do is change the ODBC driver (and, of course, configure it) that the "Data" DSN uses. The actual Perl script never needs to be altered. This is the beauty of ODBC.

ODBC is an application programming interface (API), not a language. This means that when you are using ODBC you are using a set of functions that have been predefined by the groups who created the ODBC specification. The ODBC specification also defines what kinds of errors can occur and what constants exist. So ODBC is just a set of rules and functions.

Now, when you use ODBC you need to interact somehow with the database. ODBC provides a set of rules and functions on how to access the database but not how to manipulate data in the database. To do this you need to learn about a data querying language known as SQL.

SQL

The language that ODBC uses to request the database engine to perform some task is called SQL (pronounced SEE-kwel), an acronym for Structured Query Language. SQL was designed by IBM and was later standardized as a formal database query language. This is the language that ODBC uses to interact with a database. A full discussion on SQL is beyond the scope of this book, but this chapter covers the general concepts that most people use.

Before discussing how to use SQL, first you need to understand some very simple but basic SQL concepts such as delimiters and wildcards.

Note

SQL is not case sensitive, so preserving case is not imperative; however, conventionally, SQL keywords are all in caps. So, if you were to issue a query like this:
SELECT * FROM Foo
it would yield the same results as this:
SelECt * froM Foo

Delimiters

When you need to specify a separation of things, you use a delimiter. Typically, a delimiter is an object (such as a character) that symbolizes a logical separation. We all know about these, but some may not know them by name. When you refer to a path such as c:\perl\lib\win32\odbc.pm, for example, the backslashes (\) delimit the directory names with the last backslash delimiting the file name. The colon (:) delimits the drive letter from the directory names. In other words, each backslash indicates a new directory; backslashes separate directory names or they delimit the directory names.

Many coders will delimit things to make them easier to handle. You may want to save a log file of time, status, and the name of the user who ran a script, for example. You could separate them by colons so that later you can parse them out. If you have saved to a file the line joel:896469382:success, when you read it back you can parse it out using Example 7.1.

Example 7.1 Parsing data using delimiters

open( FILE, "< test.dat" ) || die "Failed to open: ($!)";
while( <FILE> )
{
  my( @List ) = split( ":", $_ );
  print "User=$List[0]\nDate=" . localtime($List[1]) . "\nStatus=$List[2]\n";
}
close( FILE );

When it comes to SQL, delimiters are quite important. SQL uses delimiters to identify literals. A literal is just a value that you provide. If you are storing a user's name of 'Frankie' into a database, for example, 'Frankie' is a literal. Perl would refer to it as a value (as in, "you assigned the value 'Frankie' to the variable $UserName"). In the SQL query in Example 7.3, the number 937 and the string 'Noam Chomsky' are both considered literals.

When you delimit a literal, you must understand that there are actually two separate delimiters: one for the beginning (known as the literal prefix), and one for the end of the literal (the literal suffix). If that isn't enough to remember, consider this: Each data type has its own set of delimiters! Therefore you use different delimiters when specifying a numeric literal, time literal, text literal, currency literal, and the list goes on.

The good news is that it is not too difficult to discover the delimiters that a particular ODBC driver expects you to use. By using the GetTypeInfo() method, you can discover what delimiters, if any, are required by your ODBC driver for a particular data type (as in Example 7.2). For more on how to use this method, refer to the "Retrieving Data Type Information" section.

Example 7.2 Determining literal delimiters

01. #  This assumes that $db is a valid Win32::ODBC object.
02. $String = "Your data";
03. $DataType = $db->SQL_CHAR();
04. if( $db->GetTypeInfo( $DataType ) )
05. {
06.   if( $db->FetchRow())
07.   {
08.     %Type = $db->DataHash();
09.   }
10. }
11. $Field = "$Type{LITERAL_PREFIX}$String$Type{LITERAL_SUFFIX}";

The even better news is that most databases follow a simple rule of thumb: Text literals are delimited by single quotation marks (both the prefix and suffix), and all other literals are delimited by null strings (in other words, they do not require delimiters). So you can usually specify a SQL query such as the one in Example 7.3 where the text literal is delimited by single quotation marks and the numeric literal has null or empty string delimiters (nothing delimiting it).

Example 7.3 Delimiting a text literal in SQL
SELECT * FROM Foo WHERE Name = 'Noam Chomsky' AND ID > 937

The use of delimiters when specifying literals can cause problems when you need to use a delimiter in the literal itself. If the prefix and suffix delimiters for a text literal are the single quotation mark ('), for example, it causes a problem when the literal text has a single quotation mark or apostrophe within itself. The problem is that because the text literal has an apostrophe (a single quotation mark) it looks to the database engine as if you are delimiting only a part of your literal, leading the database engine to consider the remaining part of the literal as a SQL keyword. This usually leads to a SQL error. In Example 7.4, the text literal is 'Zoka's Coffee Shop', and the SQL statement is this:

SELECT * FROM Foo WHERE Company like 'Zoka's Coffee Shop'

Notice the three text delimiters, one of which is meant to be the apostrophe in "Zoka's". This will be parsed out so that the database will think you are looking for a company name of "Zoka", and that you are using a SQL keyword of "s Coffee Shop", and then starting another text literal without a delimiter. This will result in an error.

Example 7.4 Specifying a text literal with a character that is a delimiter. (This would cause a SQL error.)
$CompanyName = "Zoka's Coffee Shop";

$Sql = "SELECT * FROM Foo WHERE Company like '$CompanyName' ";

The way around this is to escape the apostrophe. This is performed by prepending the apostrophe with an escape character. There is no difference between this and how Perl uses the backslash escape character when printing a new line ("\n") or some other special character. The SQL escape character is the single quotation mark. "But wait a moment," you may be thinking, "the single quotation mark is typically a text literal delimiter!" Well, although that is true, if SQL finds a single quotation mark in between delimiters and the single quotation mark is followed by a valid escapable character, SQL will consider it indeed to be an escape character.

What this means is that when you are using a single quotation mark in your text literals, you must escape it with another single quotation mark. The most practical way of doing this is to search through your strings and replace all single quotation marks with two single quotation marks (not a double quotation mark-there is a big difference) as in Example 7.5.

Example 7.5 How to replace single quotation marks with escaped single quotation marks
$TextLiteral =~ s/'/''/g;

If you wanted to correct the code in Example 7.4, you could add a function that performs the replacement for you, as in Example 7.6.

Example 7.6 Escaping apostrophes
$CompanyName = Escape( "Zoka's Coffee Shop" );
$Sql = "SELECT * FROM Foo WHERE Company = '$CompanyName' ";
 
sub Escape
{
  my( $String ) = @_;
  $String =~ s/'/''/g;
  return $String;
}
Tip

Not all ODBC drivers use single quotation marks to delimit text literals. Some may use other characters, and some may go as far as to use different delimiters for the beginning and ending of a literal.

The way you find out which characters to use is by calling the GetTypeInfo() method, which is discussed in the "Retrieving Data Type Information" section.

Wildcards

SQL allows for wildcards when you are querying text literals. The two common wildcards are as follows:

• Underscore (_). Matches any one character.

• Percent sign (%). Matches any number (zero or more) of characters.

These wildcards are used only with the LIKE predicate (discussed later in this chapter in the section titled "SELECT Statement"). For now you need to be aware that wildcards are supported by most databases (but not all). You can use the GetInfo() method to determine whether your ODBC driver supports wildcards (see Example 7.7).

If you need to specify a percent sign or an underscore in your text literal and not have it interpreted as a wildcard, you will have to escape it first, just like you have to escape apostrophes. The difference between escaping apostrophes and escaping wildcards is twofold:

• Not all ODBC drivers support escaping wildcards (believe it or not).

• Usually, the escape character is the backslash (\), but you can change it to be something else.

The first of the differences is pretty much self-explanatory; not all ODBC drivers allow support for escaping wildcards. You can check whether your ODBC driver supports wildcards and escapable wildcards by using the GetInfo() method, as in Example 7.7.

Example 7.7 Determining the wildcard escape character

#  This assumes that $db is a valid Win32::ODBC object.
if( "Y" eq $db->GetInfo( $db->SQL_LIKE_ESCAPE_CLAUSE( ) ))
{
  $EscapeChar = $db->GetInfo( $db->SQL_SEARCH_PATTERN_ESCAPE( ));
  print "Escaping wildcards is supported.\n";
  print "The wildcard escape character is: $EscapeChar\n";
}

If your ODBC driver supports the use of escaped wildcards, you can set the escape character to be whatever you want it to be (you don't have to settle for what the ODBC driver uses by default) by using a LIKE predicate escape character sequence. This process is described later in the chapter in the section on escape clauses.

Quoted Identifiers

At times, you may need to use special characters in a query. Assume, for example, that you have a table name that has a space in its name, such as "Accounts Receivable." If you were to use the table name in a SQL query, the parser would think you are identifying two separate tables: Accounts and Receivable. This is because the space is a special character in SQL. Ideally you would rename your table to "Accounts_Receivable" or something else that does not have such special characters. Because this is not an ideal world all the time, ODBC provides a way around this called the identifier quote character.

Most databases use a double quotation mark (") as the identifier quote character, but because some do not, you may want to query your ODBC driver to discover which character you should use. This is done using the GetInfo() method, which is discussed later in the "Managing ODBC Options" section.

When you need to use special characters in an identifier, surround the identifier with the identifier quote character. Example 7.8 shows a SQL query making use of the identifier quote characters for the table name "Accounts Receivable."

Example 7.8 Using identifier quote characters
SELECT * FROM "Accounts Receivable"

SQL Grammar

When you construct a SQL statement, you are using a standardized language that has been around for a while. There are dozens of good books available that explore SQL and can provide insight on how to optimize your queries and take full advantage of SQL's powerful features. This section is just provided for the sake of completeness because SQL is the language that ODBC uses.

When you use SQL, you construct what is called a statement. A statement is a type of command that can have many details. If you want to get all the rows from a database table that meet some criteria (such as a value of 25 in the Age column), for example, you use a SELECT statement.

SELECT Statement

A SELECT statement retrieves a set of data rows and columns (known as a dataset) from the database. It is quite simple to use. The basic structure of a SELECT statement is

SELECT [ALL | DISTINCT] columnname [, columnname] …
FROM tablename
[WHERE condition]
[ORDER BY columnname [ASC|DESC] [, columnname [ASC|DESC]] …]

where:

columnname is the name of a column in the table.

tablename is the name of a table in the database.

condition is a condition that determines whether to include a row.

A SELECT statement will retrieve a dataset consisting of the specified columns from the specified table. If an asterisk (*) is specified rather than a column list, all columns are retrieved.

Every row in the table that meets the criteria of the WHERE clause will be included in the dataset. If no WHERE clause is specified, all rows will be included in the dataset.

The rows in the dataset will be sorted by the column names listed in the order listed if you specify an ORDER BY clause. If you use ORDER BY LastName, FirstName, the resulting dataset will be sorted in ascending order (the default) first by LastName and then by FirstName.

For each column specified, you can use the ASC or DESC keyword to indicate sorting the column by ascending or descending order, respectively. If neither keyword is specified, the column will be sorted however the previous column was. If no keyword is specified for the entire ORDER BY clause, ASC will be assumed.

It is interesting to note that instead of specifying a column name for the ORDER BY clause, you can refer to a column number (for example, ORDER BY 5 ASC, LastName DESC, 4, 3).

Example 7.9 retrieves a dataset consisting of all the fields (the asterisk indicates all fields) from all the records contained in the table called 'Foo'.

Example 7.9 Simple SELECT query
SELECT * FROM Foo

The WHERE predicate specifies a search condition. Only rows that meet the criteria set by the WHERE clause are retrieved as part of the dataset. WHERE clauses consist of conditional statements that equate to either true or false such as "Age > 21". Table 7.1 lists the valid conditions.

Table 7.1 Valid conditional predicates

Operator Description

X = Y X is equal to Y.

X > Y X is greater than Y.

X < Y X is less than Y.

X >= Y X is greater or equal to Y.

X <= Y X is less than or equal to Y.

X <> Y X is not equal to Y

X [NOT] BETWEEN Y AND Z X is between the values of Y and Z. If the NOT keyword is used, X
is not between the values Y and Z.

X [NOT] LIKE 'Y' X is the same as Y (when comparing text data types). If the NOT keyword is
used, X is not the same as Y. The Y value can consist of a text literal with
wildcards. This comparison is only needed when using wildcards; otherwise, it is
more efficient to use =.

EXISTS (subquery) This will be true for every row returned from a subquery (another SELECT
statement).

X IS [NOT] NULL The column X is NULL. If the NOT keyword is used, column X is not NULL.

X [NOT] IN (Y, Z, ...) The value X is found in the list of (Y, Z, …). If the NOT keyword is used then the
value X is not found in the list (Y, Z, …). The list (Y, Z, …) could be another
SELECT subquery.

X <operator> {ALL|ANY}(subquery) A subquery (another SELECT statement) is {ALL|ANY} operator>
(subquery)performed and the rows from the resulting dataset are compared to
the value X using an operator, which could be any in this list. If the ALL
keyword is used, then all rows from the subquery must match the condition set
by the operator. If the ANY keyword is used, any row that matches the
condition set by the operator will return a true value.

Usually, the conditional statement includes the name of a column. When a column is referred to, you are referring to the value in the column. In Example 7.10, a search condition of Age > 21 is used. Every row that has a value of greater than 21 in the Age column will satisfy the condition; therefore, it will be returned in the dataset.

Example 7.10 Specifying a WHERE clause
SELECT *
FROM Foo
WHERE Age > 21

Example 7.11 makes use of the WHERE predicate to indicate a condition of the search. The query will retrieve a dataset consisting of the Firstname, Lastname, and City fields in the Users table only if the user's first name begins with the letter 'C' (the '%' is a wildcard that indicates that you don't care what comes after the 'C'-refer to the section on wildcards). The dataset will then be sorted by Lastname (by default the order will be ascending).

Example 7.11 Complex SELECT query

SELECT Firstname, Lastname, City
FROM Users
WHERE Firstname like 'C%'
ORDER BY Lastname

Multiple conditions can be used in a WHERE clause by using Boolean conjunctions such as AND and OR. Example 7.12 will return a dataset with rows whose Age column has values of greater than 21andLastName column begins with either the letters 'C' or 'R'.

Example 7.12 Using multiple conditions in a SELECT statement
SELECT *
FROM Foo
WHERE Age > 21 AND
      (LastName like 'C%' OR LastName like 'R%')

For another SELECT statement consider Example 7.13. This query will return a dataset consisting of all fields only if the City field is 'Seattle' and the Zip code is not equal to 98103. The list will then be sorted in descending order (starting with Z's and ending with A's) by last name.

Example 7.13 SELECT statement with sorting and a condition

SELECT *
FROM Users
WHERE City like 'Seattle' AND Zip <> 98103
ORDER BY Lastname DESC
INSERT Statement
An INSERT statement adds a row to a table in a database. The structure of an INSERT statement is as follows:
INSERT INTO tablename
  [(columnname1[, columnname2] ... )]
VALUES (value1[, value2] ... )

where:

tablename is the name of a table in the database.

columnname is the name of the column to receive a value.

value is the value to be placed into the column.

The INSERT statement is used when you need to add a record to a table. The trick here is that when you specify a list of column names, you must list the values for those columns in the same order as the columns.

The list of column names is optional and if left out, the first value specified will be stored into the first column, the second value will be stored into the second column, and so on.

Example 7.14 adds a row into the table Foo, assigns the value 'Dave' to the Name column, 'Seattle' to the City column, and 98103 to the Zip column.

Example 7.14 A simple INSERT statement

INSERT INTO Foo
(Name, City, Zip)
VALUES ('Dave', 'Seattle', 98103)
Just to demonstrate how you can use the INSERT statement without providing any column names, look at Example 7.15.
Example 7.15  Using the INSERT statement without any column names
INSERT INTO Foo
VALUES ('Zoka''s Coffee Shop', 'Double Late', 2.20)

This example assumes that the first and second columns are text data types and the third is either a floating type or a currency type (notice that the example escapes the apostrophe in the first value).

Note

It is rather important to understand that although the SQL language is fairly standardized, not all data sources implement it in the same way. To MS SQL Server, the INTO keyword for an INSERT statement is optional, for example; however, MS Access requires it.

It is most practical to never use data source-specific shortcuts or leave out optional keywords unless you are sure that your script will always be used with a particular data source.

UPDATE Statement

Not only can you select and insert data into a table, but you can also change the values in a given row of a table. The capability to update a row is tremendously powerful and is performed using an UPDATE statement. The statement's syntax is similar to that of the INSERT statement:

UPDATE tablename
SET columnname1=value1
    [, columnname2=value2]
    ...
[WHERE condition]

where:

tablename is the name of a table in the database.

columnname is the name of the column to receive a value.

value is the value the column should be set to.

condition is a search condition as defined in the SELECT statement section.

This statement can be used to modify the existing rows of data. Example 7.16 uses an UPDATE statement to change the Department column for all rows in the Users table. If a row's Department column contains the value Personnel it is changed to the more politically correct Human Resources.

Example 7.16 Using the UPDATE statement
UPDATE Users
SET Department = 'Human Resources'
WHERE Department = 'Personnel'

DELETE Statement

To remove a row from a table is not difficult at all. This is done with a DELETE statement:

DELETE [FROM] tablename
[WHERE condition]

where:

tablename is the name of a table in the database from which rows will be deleted.

condition is a search condition as defined in the SELECT statement section.

The first FROM keyword is optional-some data sources may require it. The tablename (following the optional first FROM keyword) is the table that will be affected by the statement.

If search condition specified is TRUE for a row, the row is deleted from tablename. In Example 7.17, all rows from the table Students will be deleted as long as the row has a value greater than 4 in the Year column and the SchoolName column contains the value Michigan State University.

If no search condition is specified, all rows from tablename are deleted as in Example 7.18.

Example 7.17 Using the DELETE statement
DELETE FROM Students
WHERE Year > 4 AND
      SchoolName = "Michigan State University"
Example 7.18 Deleting all rows in a table
DELETE FROM Students

Escape Sequences

Each and every data source has its own way of dealing with specific data types. The DATE data type format for Oracle 6 is "Aug 20, 1997", for example, but IBM's DB2 is "1997-08-20". This becomes quite a problem when creating a SQL statement where you have no idea what the actual database engine will be.

Because ODBC attempts to abstract the particulars of different databases, the ODBC standard has adopted a technique to contend with this problem. This technique is called an escape sequence. When an ODBC driver finds an escape clause in a SQL statement, it converts it to whatever format it needs to be to suit the particular database the statement will be sent to.

Date and Times

The escape clause for a date is {d 'yyyy-mm-dd'}. So to create an ODBC SQL statement that will be properly interpreted by all databases, you could use the following:

SELECT * FROM Foo WHERE Date = {d '1997-08-20'}

The time/date-based escape sequences are as follows:

• Date: {d 'yyyy-mm-dd'}

• Time: {t 'hh:mm:ss'}

• Timestamp: {ts 'yyyy-mm-dd hh:mm:ss'}

Outer Joins

If you need to use an outer join, you can use the outer join escape sequence:

{oj outer_join}

where the outer join consists of:

tablename {LEFT | RIGHT | FULL} OUTER JOIN{tablename |outer_join} ON search_condition

In Example 7.19, the outer join specifies all the fields from every record of the Machine table and every record from the Users table in which the field MachineName (from the Users table) matches Name (from the Machine table) as long as the field Processor (from the Machine table) is greater than 487.

Example 7.19 An outer join escape clause

SELECT *
FROM {oj Machine LEFT OUTER JOIN Users ON Machine.Name = Users.MachineName}
WHERE Machine.Processor > 486

Because this query uses an outer join as an escape sequence, you can be guaranteed that it will work on any ODBC driver that supports outer joins. Even if the particular driver uses a nonstandard syntax for outer joins, the ODBC driver will convert the escape sequence into the correct syntax before executing it.

Before you actually make use of an outer join, you may want to check to make sure that your ODBC driver supports them. Example 7.20 shows a simple line that will check for outer join support. If the variable $CanUseOuterJoins is 1, outer joins are supported.

Example 7.20 Discovering whether an ODBC driver supports outer joins
#  This assumes that $db is a valid Win32::ODBC object.
$CanUseOuterJoins = $db->GetInfo( $db->SQL_OJ_CAPABILITIES() ) & $db->SQL_OJ_FULL();

Scalar Functions

Scalar functions such as time and date, character, and numeric functions can be implemented by means of escape sequences:

{fn function}

where function is a scalar function supported by the ODBC driver. Example 7.21 compares the Date column (which is of a timestamp data type) with the current date.

Example 7.21 A SQL statement that uses a scalar function
SELECT *
FROM Foo
WHERE Date = {fn curdate()}

For a full list of scalar functions, refer to Appendix B. Before using a scalar function, you should see whether it is supported by the ODBC driver. You can use Example 7.22 to discover this. If the variable $IsSupported is 1, the curdate() scalar function is supported. The value returned from GetInfo( $db->SQL_TIMEDATE_FUNCTIONS ) is a bitmask for all the supported time and date functions.

Example 7.22 Checking whether the ODBC driver supports the curdate() function
$IsSupported  = $db->GetInfo($db->SQL_TIMEDATE_FUNCTIONS())& $db->SQL_FN_TD_CURDATE();

Stored Procedures

Stored procedures can be called by means of escape sequences. The syntax is as follows:

{[?=]call procedurename[([parameter][,parameter]...)]}

If a return value is expected, you need to specify the preceding ?=, as in the following:

{? = call MyFunction('value')}

Otherwise, a call can be constructed like this:

{call MyFunction('value')}

Note

Win32::ODBC does not support parameter binding, so using the '?' as a "passed in" parameter to a stored procedure is not supported and will probably result in an error.

The only exception to this is the return value of a stored procedure. For example, if a SQL statement was submitted like this:
{? = call MyStoredProc( 'value1', value2 ) }
The value returned by the procedure would be stored in a dataset containing one row. Future versions of this extension will support parameter binding.

LIKE Predicate Escape Character

Even though it is not common to do so (because you can always use the default escape character), you can change the character used to escape a wildcard by using the LIKE predicate escape character sequence:

{escape 'character')

where 'character' is any valid SQL character.

Example 7.23 demonstrates this.

Example 7.23 Specifying an escape character
SELECT *
FROM Foo
WHERE Discount LIKE '28#%' {escape '#'}

This example will return all columns from the table Foo that have "28%" in the Discount column. Notice that by specifying the LIKE predicate escape character sequence, the pound character (#) is used to escape the wildcard. This way, the wildcard is deemed a regular character and not interpreted as a wildcard. Because the resulting condition will not include any wildcards, the condition could have been this:

WHERE Discount = '28%'

This particular example demonstrates the use of the LIKE predicate escape clause, however, so we are using the LIKE predicate.

Usually, this escape sequence is not necessary because the default escape character is the backslash (\) and the same SQL statement could have been this:

SELECT *
FROM Foo
WHERE Discount LIKE '28\%'

How to Use Win32::ODBC

The Win32::ODBC extension attempts to make connecting to an ODBC data source as easy as possible. In doing this, it hides many details from the user. This is either a good or bad thing, depending on your point of view. Nonetheless, the extension is designed to be as flexible as the ODBC API is without requiring the user to learn the API itself. Of course, to utilize the more exotic ODBC features a good book on ODBC is recommended; otherwise you will drive yourself insane with all the constants, functions, and terminology!

When an application wants to connect to an ODBC data source, it must create the following items, in order:

An ODBC environment
An ODBC connection to the data source
A statement handle

Because all database interaction uses a statement handle, all three steps must be performed, in order, before any database interaction can occur. It is possible to have multiple statement handles per connection. This is how there can be many queries simultaneously.

Win32::ODBC attempts to simplify this process. When you create a new Win32::ODBC object, all interaction with the data source goes through this object. If you need to connect to many data sources at once, you just create many Win32::ODBC objects. For those users who need to create multiple queries to one data source (the equivalent of having multiple statement handles), the capability to clone an object has been implemented. Cloned objects share the same ODBC connection but have separate statement handles.

The use of Win32::ODBC is very straightforward in most instances; there are basically five steps:

Connecting to the database
Submitting a query
Processing the results
Retrieving the data
Closing the database

Because Win32::ODBC is a Perl extension, it must be loaded by means of the use command. Typically this is placed in the beginning of your script, as in Example 7.24.

Example 7.24 Loading the ODBC extension
use Win32::ODBC;

Connecting to the Database

After your Perl application loads the Win32::ODBC extension, it needs to initiate a conversation with a database. This is performed by creating an ODBC connection object using the new command:

$db = new Win32::ODBC( $DSN [, $Option1, $Option2, ... ] );

The first parameter ($DSN) is the data source name. This parameter can be either a DSN or a DSN string. This is described later in this section.

The optional additional parameters are connection options that can also be set by using the SetConnectOption() method, which is discussed in the "Connection Options" section. Some options, however, must be set before the actual connection is made to the ODBC driver and database. Therefore, you can specify them in the new command.

Example 7.25 assumes a few things: It assumes that you have already created a DSN called "My DSN", and that it is configured correctly. The example also assumes that the current user has permissions to access the DSN. This is not so much a problem for Windows 95 users as it is for Windows NT users.

Example 7.25 Connecting to DSN
$db = new Win32::ODBC( "MyDSN" );

If all went well, you will have an object, $db, which you will use later. Otherwise something went wrong, and the attempt to connect to the database failed. If something indeed did go wrong, the object will be empty. A simple test, as in Example 7.26, can be used to determine success or failure in connecting to the database. If the connection fails, the script will die printing out the error. Error processing is discussed later in the chapter in the section titled, strangely enough, "Error Processing."

Example 7.26 Testing if a connection to a database succeeded
if(! $db)
{
  die "Error connecting: " . Win32::ODBC::Error() . "\n";
}

You can override your DSN configuration by specifying particular configuration elements in the DSN name when creating a new ODBC connection object. To do this, your DSN name must consist of driver-specific keywords and their new values in the following form:

keyword=value;

You can specify as many keywords as you like and in any order you like, but the "DSN" keyword must be included (and should be the first one). This keyword indicates which DSN you are using and the other keyword/value pairs that will override the DSN's configuration.

Table 7.2 provides a list of standard keywords. A data source may allow additional keywords (for example, MS Access defines the keyword DBQ to represent the path to the database file) to be used.

Table 7.2 Standard DSN keywords

Keyword Description

DSN The value would be the name of an existing DSN.

FILEDSN The value would be the name of a file-based DSN. Such files end with the .DSN extension. This is only available with ODBC 3.0 or higher.

DRIVER A description of the ODBC driver that is to be used. This value can be obtained by using the Win32::ODBC::Drivers() function.

UID The value represents a user ID (or user name) that will be sent to the data source.

PWD The value represents a password that will be sent to the data source.

SAVEFILE The value is a name of a file-based DSN that the DSN string will be saved as. This is only available with ODBC 3.0 or higher.

Note

If ODBC 3.0 or higher is being used, there are two keywords that cannot be used together. They are DSN and FILEDSN. If they both appear in a connection string, only the first one is used.

Additionally ODBC 3.0 allows for a DSNless connection string in which no DSN or FILEDSN keyword is used. A DRIVER keyword must be defined, however, in addition to any other keywords necessary to complete the connection.

Suppose a DSN exists called "OZ" that points to an Access database. If you want to use that DSN but specify a userid to log on to the database (in Access, a keyword of UID) and password (keyword of PWD), you could use the line:

$db = new Win32::ODBC( "DSN=OZ;UID=dorothy;PWD=noplacelikehome" );

A connection would be made to the "OZ" DSN, overriding the default userid and password would be difficult.

Other than the practical limitations of memory, you have no real limit as to how many database connections you can have. Although some tricks can help speed things up, you should conserve on memory use and make the most of an ODBC connection (but more on that later).

After you have your ODBC object(s), you are ready to begin querying your database.

Tip

The Win32::ODBC extension's functions also map to another namespace called ODBC. It is not necessary to always use the full Win32::ODBC namespace when calling functions or constants. For example, the following two function calls are the same:
@Error = Win32::ODBC::Error();
@Error = ODBC::Error();

Likewise, when you create a new ODBC object, you can use either of the following:

$db = new Win32::ODBC( "MyDSN" );
$db = new ODBC( "MyDSN" );

Anywhere that you may need to use the full namespace of the extension, you instead use the abbreviated version: ODBC.

Submitting the Query

Now that you have an ODBC connection object, you can begin to interact with your database. You need to submit a SQL query of some sort. This is where you use the Sql() method:

$db->Sql( $SqlStatement );

The first and only parameter is a SQL statement. This can be any valid SQL statement.

The Sql() method will return a nonzero integer corresponding to a SQL error number if it is unsuccessful.

Note

It is very important to note that the Sql() method is the only method that returns a non-zero integer upon failure.

The reason for this is to keep backward compatible with the original version of Win32::ODBC, then called NT::ODBC, which was written by Dan DeMaggio.

Originally, NT::ODBC used the return value of the sql() method (then the method was all lowercase) to indicate the error number. This technique of error checking has been made obsolete with the introduction of the Error() method. For the sake of backward compatibility, however, the return values have not changed.

Suppose you have a database called OZ with a table called Characters. The table consists of the fields (also known as columns) in Table 7.3.

Table 7.3 The table called Characters in a fictitious database called OZ

Field Name Data Type

Name char(20)

Origin char(20)

Goal char(30)

Suppose also that you want to query the database and find out who is in OZ. Example 7.27 will connect to the database and submit the query.

Example 7.27 Submitting the SQL query

01. use Win32::ODBC;
02. if( ! $db = new Win32::ODBC( "OZ" ) )
03. {
04.   die "Error connecting: " . Win32::ODBC::Error() . "\n";
05. }
06. if( $db->Sql( "SELECT * FROM Characters" ) )
07. {
08.   print "Error submitting SQL statement: " . $db->Error() . "\n";
09. }
10. else
11. {
12.   ... process data ...
13. }

The SQL statement "SELECT * FROM Characters" in line 6 will, if successful, return a dataset containing all fields from all rows of the database. By passing this statement into the Sql() method, you are requesting the database to perform this query.

If the Sql() method returns a nonzero result, there was an error and the error is printed. If the query was successful (a return value of zero), you will need to move onward and process the results.

Processing the Results

After you have a dataset that has been prepared by a SQL statement, you are ready to process the data. The way this is achieved is by moving, row by row, through the dataset and extracting the data from columns in each row.

The first thing you must do is tell the ODBC connection that you want to move to the next available row. Because you just performed the query, you are not even looking at a row yet; therefore, you need to move to the next available row, which in this case will be the first row. The method used to move from row to row is FetchRow():

( $Reult, @RowResults ) = $db->FetchRow( [$Row[, $Mode]] );

Before explaining the optional parameters, it is important to understand that the parameters are usually not used. They refer to the extended capabilities of the ODBC function SQLExtendedFetch(). This is explained later in the section titled "Advanced Features of Win32::ODBC."

The first optional parameter ($Row) is the row number that you want to move to. For more details refer to the aforementioned section on advanced Win32::ODBC features.

The second optional parameter ($Mode) is the mode in which the cursor will be moved. If this parameter is not specified, SQL_FETCH_RELATIVE mode is assumed. For more details, refer to the aforementioned section on advanced Win32::ODBC features.

The FetchRow() method will return a one value if it successfully moved to the next row. If it returns a zero, it usually means that no more data is left (that is, you have reached the end of your dataset) or that an error has occurred.

Another value is returned, but is typically not of any use unless you use the advanced features of FetchRow(). For more information, refer to the section titled "Advanced Row Fetching" later in the chapter.

Example 7.28 shows a typical usage of the FetchRow() method. The loop continues to execute as long as you can advance to the next row. After the last row has been obtained, $db->FetchRow() returns a FALSE value that causes the loop to terminate.

Example 7.28 Fetching rows

while( $db->FetchRow() )
{
  ...process data...
}

Note

As of version 970208, the FetchRow() makes use of the SQLExtendedFetch() ODBC function. Unfortunately not all ODBC drivers support this and therefore fail when FetchRow() is called.

If this happens, you can either use another ODBC driver, such as a newer version or from another vendor, or you can use an older version of Win32::ODBC. Versions of the extension before 970208 use the regular SQLFetch() ODBC function.

Future releases of Win32::ODBC will support both fetch methods and use whichever the ODBC driver supports.

Retrieving the Data

After a row has been fetched, the data will need to be retrieved from it. There are two methods for doing this: the Data() method and the DataHash() method.

The Data() method returns an array of values:

@Data = $db->Data( [$ColumnName1[, $ColumnName2 ... ]] );

A list of parameters can be passed in. Each parameter is a column name of which you are retrieving data.

The Data() method returns an array of values corresponding, in order, to the column names that were passed into the method. If nothing is passed into the method, all column values are returned in the order that they appear in the row. Example 7.29 shows how the Data() method can be used.

Example 7.29 Using the Data() method

if( $db->FetchRow() )
{
  @Data = $db->Data( "LastName", "FirstName" );
  print "First name is $Data[1]\n";
  print "Last name is $Data[0]\n";
}

Notice how the first element in the @Data array ($Data[0]) is the value of the first column name specified. The order of the column names passed in determines the order they are stored in the array.

The second and more practical way to retrieve data is to use the DataHash() method. The DataHash() method is the preferred method when retrieving data because it associates the column name with the column's data. The DataHash() method returns either an undef if the method failed or a hash with column names as keys and the hash's values consisting of the column's data:

%Data = $db->DataHash( [$ColumnName1[, $ColumnName2 ... ]] );

A list of parameters can be passed in. Each parameter is a column name of which you are retrieving data. If no parameters are passed in, the data for all columns will be retrieved.

If you were to query the table previously described in Table 7.3 using the code in Example 7.30, you may get output that looks like this:

1) Dorothy is from Kansas and wants to go home.
2) The Scarecrow is from Oz and wants a brain.
3) The Wicked Witch is from The West and wants the ruby slippers.

This output would continue until all rows have been processed.

Example 7.30 Extracting data using the DataHash() method

01. use Win32::ODBC;
02. if( ! $db = new Win32::ODBC( "OZ" ) )
03. {
04.   die "Error connecting: " . Win32::ODBC::Error() . "\n";
05. }
06. if( ! $db->Sql( "SELECT * FROM Characters" ) )
07. {
08.   while( $db->FetchRow() )
09.   {
10. 
11.     my(%Data) = $db->DataHash();
12.     $iRow++;
13.     print "$iRow) $Data{Name} is from $Data{Origin} and ",
        "wants $Data{Goal}.\n";
14.   }
15. }
16. $db->Close();

Tip

When using the DataHash() method, it is best that you undef the hash used to hold the data before the method call. Because the DataHash() method is typically called from within a while loop that fetches a row and retrieves data, it is possible that the hash may retain values from a previous call to DataHash().

Closing the Connection

Up to now, you have opened the database, submitted a query, retrieved and processed the results. Now you need to finish by closing the database. The proper way to perform this is by calling the Close() method. This method tells the ODBC Manager to properly shut down the ODBC connection. The ODBC Manager will, in turn, tell the driver to clean up after itself (removing temporary files, closing network connections, flushing buffers, and so on) and the ODBC connection object will be destroyed so that it cannot be used any longer. The syntax for the Close() method is this:

$db->Close();

The Close() method has no return value.

Example 7.30 in the preceding section shows a full working example of how you may use the Win32::ODBC module, including the Close() method.

The Win32::ODBC API

Win32::ODBC supports a rich set of features, most of which are never used (yet still exist). Some of these require knowledge of ODBC that is beyond the scope of this book. You can find several books that do discuss this topic.

Constants

For almost every method and function in Win32::ODBC, a set of constants are needed. These constants represent a numeric value that may change as the ODBC API changes over time, so it is important that you use the constant names and not the values they represent.

One of the most questioned aspects of the Win32::ODBC extension is how to make use of the constants. Only a small group of constants are actually exported from the ODBC module, so to use most of the constants you need to specify a namespace function such as:

Win32::ODBC::SQL_COLUMN_TABLE_NAME()

Or, if you have an ODBC connection object, you can access the constant value as if it were a member of the object, as in:

$db->SQL_DATA_SOURCE_NAME()

Because Win32::ODBC creates a synonymous ODBC namespace and maps it to Win32::ODBC, you could use:

ODBC::SQL_CURSOR_COMMIT_BEHAVIOR()

Notice that the preceding example just uses the ODBC namespace rather than the Win32::ODBC namespace. Both are valid, but the latter is a bit shorter.

The reason why all constants are not exported into the main namespace is because the ODBC API defines more than 650 constants, each of which is important to have. The decision was made to not export all the constants because it would bloat memory use and clutter the main namespace with an entire list of constants that will most likely not be used.

You could always edit the ODBC.pm file and export constants that you would like to export; but then again, it is not so difficult to just make use of one of the formats listed earlier.

Catalog Functions

The ODBC API supports metadata functions such as cataloging. Win32::ODBC supports access to such information with two methods:

$db->Catalog( $Qualifier, $Owner, $Name, $Type );
$db->TableList( $Qualifier, $Owner, $Name, $Type );

Both of these are really the same method, so they can be used interchangeably. The only difference is in how the results are obtained.

The first parameter ($Qualifier) represents the source the database will use. In Access, for example, the $Qualifier would be the database file (such as c:\data\mydatabase.mdb); whereas in MS SQL Server, it would be the database name.

The second parameter ($Owner) is the owner of the table. Some database engines can put security on tables either granting or denying access to particular users. This value would indicate a particular owner of a table-that is to say, the user who either created the table or to whom the table has been given.

The third parameter ($Name) is the name of the table.

The fourth parameter ($Type) is the table type. This can be any number of database-specific types or one of the following values:

TABLE
VIEW
SYSTEM TABLE
GLOBAL TEMPORARY
LOCAL TEMPORARY
ALIAS
SYNONYM

A call to the Catalog() or TableList() method can include search wildcards for any of the parameters. Passing in "USER%" for the third parameter will result in retrieving all the tables with names that begin with USER.

The difference between these two methods is that the Catalog() method returns a result set that you need to process with FetchRow() and Data() or DataHash(). On the other hand, TableList() returns an array of table names that meet the criteria you specified.

Basically, TableList() is a quick way of getting a list of tables, and Catalog() is a way of getting much more information.

If the Catalog() method is successful, it returns a TRUE and results in a dataset with five columns: TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, and REMARKS. There may also be additional columns that are specific to the data source. Each row will represent a different table. You can use the normal FetchRow() and DataHash() methods to retrieve this data. If the method fails, a FALSE is returned.

Note

The resulting dataset generated by a call to the Catalog() method will result in a different result set if ODBC 3.0 or higher is used. In this case, TABLE_QUALIFIER becomes TABLE_CAT and TABLE_OWNER becomes TABLE_SCHEM.

This is due to a change in the ODBC specification for version 3.0.

If the TableList() method is successful, it will return an array of table names.

If all parameters are empty strings except the fourth parameter (table type), which is only a percent sign (%), the resulting dataset will contain all valid table types. You can use this when you need to discover which table types a data source can use.

If the $Owner parameter is a single percent sign and the qualifier and name parameters are empty strings, the resulting dataset will contain the list of valid owners that the data source recognizes.

If the $Qualifier is only a percent sign and the $Owner and $Name parameters are empty strings, the resulting dataset will contain a list of valid qualifiers. Example 7.31 describes how you can use both of these methods.

Example 7.31 Using the TableList() and Catalog() methods

01. use Win32::ODBC;
02. $db = new ODBC("MyDSN" ) || die "Error connecting: " . ODBC::Error();
03. $TableType = "'TABLE','VIEW','SYSTEM TABLE'," .
                 "'GLOBAL TEMPORARY','LOCAL TEMPORARY'," .                 "'ALIAS','SYNONYM'";
04. @Tables = $db->TableList();
05. print "List of tables:\n";
06. map {$iCount++; print "$iCount) $_\n";} @Tables;
07.
08. if( $db->Catalog( "", "", "%", $TableType ) )
09. {
10.   while( $db->FetchRow() )
11.   {
12.     my( %Data);
13.     %Data = $db->DataHash();
14.     print "$Data{TABLE_NAME}\t$Data{TABLE_TYPE}\n";
15.   }
16. }
17. $db->Close();
18.

Managing Columns

Each column (or field-whichever way you prefer to say it) can be of a different data type. One column may be a text data type (for a user's name, for example) and another may be a numeric data type representing a user's age.

At times, a programmer may need to learn about a column-things such as what data type the column is, whether he can conduct a search on that column, or whether the column is read-only. If the programmer created the database table, chances are that he already knows this information; if the table came second-hand, the programmer may not know such information. This is where the ColAttributes() method comes into play:

$db->ColAttributes( $Attribute, [ @ColumnNames ]);

The first parameter ($Attribute) is the attribute, a numeric value representing attributes of a column. Appendix B contains a list of valid constants that can be used.

Additional parameters may be included-specifically, the names of columns you want to query. If no column names are specified, all column names will be queried.

The output of the ColAttributes() method is a hash consisting of column names as keys and the attribute for the column as the key's value.

The code in Example 7.32 will print out all the data types in a table called "Foo" from the DSN called "MyDSN". In line 14, the column's data types are retrieved with a call to the ColAttributes() method. The resulting hash is passed into a subroutine, DumpAttribs(), which prints out each column's data type.

Example 7.32 Printing out a table's column data types

01. use Win32::ODBC;
02.
03. $DSN = "MyDSN";
04. $Table = "Foo";
05. $db = new ODBC($DSN) || die "Error connecting: " . ODBC::Error();
06.
07. if( ! $db->Sql("SELECT * FROM $Table") )
08. {
09.   if( $db->FetchRow() )
10.   {
11.     @Fields = $db->FieldNames();
12.     foreach $Field (@Fields)
13.     {
14.       %Attrib = $db->ColAttributes( $db->SQL_COLUMN_TYPE_NAME(), $Field);
15.       DumpAttribs( %Attrib );
16.     }
17.   }
18.   else
19.   {
20.     print "Fetch error: " . $db->Error();
21.   }
22. }
23. else
24. {
25.   print "SQL Error: " . $db->Error();
26. }
27.
28. sub DumpAttribs
29. {
30.   my( %Attributes ) = @_;
31.   my( $ColumnName );
32.   foreach $ColumnName (sort (keys ( %Attributes ) ) )
33.   {
34.     print "\t$ColumnName = $Attributes{$ColumnName}\n";
35.   }
36. }

Data Source Names

Most administrators will create and manage a Data Source Name (DSN) by using the nifty GUI interface such as the ODBC Administrator program or the Control Panel ODBC applet. Both of these (actually they are the same application) do a tremendous job at managing DSNs. Be aware, however, that at times you may need to programmatically manage DSNs. An administrator may want to write a Web-based CGI script enabling the management of DSNs, for example. Win32::ODBC uses the ConfigDSN() function to do just this:

Win32::ODBC::ConfigDSN( $Action, $Driver, $Attribute1 [, $Attribute2, ...] );

The first parameter ($Action) is the action specifier. The value of this parameter will determine what action will be taken. The valid actions and their values are as follows:

ODBC_ADD_DSN. (0x01) Adds a new DSN.
ODBC_MODIFY_DSN. (0x02) Modifies an existing DSN.
ODBC_REMOVE_DSN. (0x03) Removes an existing DSN.
ODBC_ADD_SYS_DSN. (0x04) Adds a new System DSN.
ODBC_MODIFY_SYS_DSN. (0x05) Modifies an existing System DSN.
ODBC_REMOVE_SYS_DSN. (0x06) Removes an existing System DSN.

In some versions of Win32::ODBC, the system DSN constants are not exported so their values can be used instead.

The second parameter ($Driver) is the ODBC driver name which will be used. The driver must be one of the ODBC drivers that are installed on the computer. You can retrieve a list of available drivers by using either the DataSources() or the Drivers() function.

The remaining parameters are the list of attributes. These may differ from one ODBC driver to the next, and it is up to the programmer to know which attributes must be used for a particular DSN. Each attribute is constructed in the following format:

"AttributeName=AttributeValue"

These examples were taken from a DSN using the Microsoft Access ODBC driver:

"DSN=MyDSN"
"UID=Cow"
"PWD=Moo"
"Description=My little bitty Data Source"

The "DSN" attribute is one that all ODBC drivers share. This attribute must be in the list of attributes you provide; otherwise, ODBC will not know what to call your DSN or, in the case of modifying and removing, which DSN you alter. It is wise to always include the "DSN" attribute as the first attribute in your list.

When you are adding or removing, you need only to specify the "DSN" attribute; others are not necessary. In the case of adding, any other attribute can be added later by modifying the DSN.

Modifying DSNs

When you are modifying, you must include the "DSN" attribute so that ODBC will know which DSN you are modifying. Any additional attributes can either be added to the DSN or replace any attributes that already exist with the same name.

Some ODBC drivers require you to specify additional attributes (in addition to the "DSN" attribute) when using ConfigDSN(). When adding a new DSN that uses the Microsoft Access driver, for example, you must include the following database qualifier attribute:

"DBQ=C:\\SomeDir\\MyDatabase.mdb"

In Example 7.33, the ConfigDSN() function is used three times. The first time (line 10), ConfigDSN() creates a new DSN. The second time (line 18) ConfigDSN() modifies the new DSN by adding the password (PWD) attribute and changing the user (UID) attribute. The third call to the ConfigDSN() function (line 22) removes the DSN that was just created. This code is obviously not very useful because it creates and then removes a DSN, but it shows how to use the ConfigDSN() function.

Example 7.33 Adding and modifying a DSN

01. use Win32::ODBC;
02.
03. $DSN = "My DSN Name";
04. $User = "administrator";
05. $Password = "adminpassword";
06. $Dir = "C:\\Database";
07. $DBase = "mydata.mdb";
08. $Driver = "Microsoft Access Driver (*.mdb)";
09.
10. if( Win32::ODBC::ConfigDSN( ODBC_ADD_DSN,
11.                             $Driver,
12.                             "DSN=$DSN",
13.                             "Description=A Test DSN",
14.                             "DBQ=$Dir\\$DBase",
15.                             "DEFAULTDIR=$Dir",
16.                             "UID=" ) )
17. {
18.   Win32::ODBC::ConfigDSN( ODBC_MODIFY_DSN,
19.                           $Driver,
20.                           "UID=$User",
21.                           "PWD=$Password");
22.   Win32::ODBC::ConfigDSN( ODBC_REMOVE_DSN,
23.                           $Driver,
24.                           "DSN=$DSN" );
25. }

Line 8 assigns the $Driver variable with the name of the ODBC driver to be used. This value should come from a value obtained with a call to Win32::ODBC::Drivers(). The reason for this is because this value could change based on localization. A German version of ODBC, for example, would require line 8 to be:

$Driver = "Microsoft Access Treiber (*.mdb)";

The value returned by the Drivers() function is obtained from the ODBC driver directly, so the value will be correct for the locale.

The ConfigDSN() function returns a TRUE if it is successful; otherwise, it returns a FALSE.

Tip

If you do not know what attributes to use in a call to ConfigDSN(), you can always cheat! You just use the ODBC administrator program or the Control Panel's ODBC applet and create a temporary DSN.

After you have completed this, you need to run the Registry Editor (regedit.exe or regedt32.exe). If you created a system DSN, open this key:

HKEY_LOCAL_MACHINE\Software\ODBC\Your_DSN_Name

If you created a user DSN, open this key:

HKEY_CURRENT_USER\Software\ODBC\Your_DSN_Name

The value names under these keys are the attributes that you specify in the ConfigDSN() function.

After a DSN has been created, you may want to review how it is configured. This is done by using GetDSN():

Win32::ODBC::GetDSN( $DSN ); $db->GetDSN();

GetDSN() is implemented as both a function and a method. When called as a function, you must pass in a parameter that is the name of a DSN whose configuration will be retrieved.

When used as a method, nothing is passed in to GetDSN(). The DSN that the ODBC connection object represents will be retrieved.

A hash is returned consisting of keys that are the DSN's attribute keyword. Each key's associated value is the DSN's attribute value. These key/value pairs are the same as those used in the ConfigDSN() function.

It is possible to retrieve a list of available DSNs by using the DataSources() function:

Win32::ODBC::DataSources();

DataSources() returns a hash consisting of data source names as keys and ODBC drivers as values. The ODBC drivers represented in the hash's values are in a descriptive format that is used as the second parameter in a call to ConfigDSN().

Example 7.34 illustrates how to retrieve the list of available DSNs and how to use ConfigDSN() to remove these DSNs.

Example 7.34 Removing all DSNs

01. use Win32::ODBC;
02.
03. if( %DSNList = Win32::ODBC::DataSources() )
04. {
05.   foreach $Name ( keys( %DSNList ) )
06.   {
07.   print "$Name = '$DSNList{$Name}'\n";
08.     if( ! Win32::ODBC::ConfigDSN( ODBC_REMOVE_DSN,
                                      $DSNList{$Name},
                                      "DSN=$Name" ) )
09.     {
10.       #  If we were unable to remove the
11.       #  DSN maybe it is a system DSN...
12.       Win32::ODBC::ConfigDSN( ODBC_REMOVE_SYS_DSN,
                                  $DSNList{$Name},
                                  "DSN=$Name" );
13.     }
14.   }
15. }

Notice how Example 7.34 uses the names and drivers that make up the hash returned by the DataSources() function. These values are used as the DSN name and driver in the calls to ConfigDSN().

Returning Available ODBC Drivers

Drivers() is yet another DSN-related function:

Win32::ODBC::Drivers();

This function returns a hash consisting of available ODBC drivers and any attributes related to the driver. Note that these attributes are not necessarily the same as the ones you provide in ConfigDSN().

The returned hash consists of keys that represent the ODBC driver name (in descriptive format), and the key's associated value contains a list of ODBC driver attributes separated by semicolons (;) such as this:

"Attribute1=Value1;Attribute2=Value2;..."

These attributes are really not that useful for the common programmer, but may be of use if you are programming ODBC drivers or if you need to make sure that a particular driver is configured correctly.

Note

The attributes returned by the Drivers() function (which are the ODBC driver's configuration attributes) are not the same type of attributes used in the ConfigDSN() attributes (which are an ODBC driver's DSN attributes).

Case Study: Changing the Database Paths for All MS Access DSNs

Jane's boss came running into her office and told her that the junior administrator did something really devastating to one of her Web servers. Somehow, he has managed to corrupt the D: drive, the one where all the Access database files were kept.

Jane realized that she could not take the server down to reinstall a drive until the weekend. She also began kicking herself for not having already installed the RAID subsystem.

To correct the problem, she had someone restore the database files from a tape backup on to the server's E: drive. She figured that all she had to do was change the ODBC DSNs to point to their respective databases on the E: drive rather than the D: drive. No problem…until she realized that there were hundreds of DSNs!

So, Jane sat down and wrote the Perl script in Example 7.35.

Example 7.35 Changing the database paths for all MS Access DSNs

01. use Win32::ODBC;
02. $OldDrive = "d:";
03. $NewDrive = "e:";
04.   #  We are looking for Access databases
05. $Driver = GetDriver( ".mdb" ) || Error( "finding the ODBC driver" );
06.
07. %DSNList = Win32::ODBC::DataSources() ||
         Error( "retrieving list of DSNs" );
08. foreach $DSN ( keys( %DSNList ) )
09. {
10.   next if