Click here to Skip to main content
Click here to Skip to main content

Tagged as

Windows NT Win32 Perl Programming: The Standard Extensions - Chapter 7: Data Access

, 12 Mar 2001
Rate this:
Please Sign up or sign in to vote.
This chapter provides an overview on using the Win32::ODBC extension using Perl
<!-- Article image -->
Sample Image
Title Windows NT Win32 Perl Programming: The Standard Extensions
Authors Dave Roth
PublisherNew Riders
PublishedJan 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 (Smile | :) 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:

  1. An ODBC environment
  2. An ODBC connection to the data source
  3. 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:

  1. Connecting to the database
  2. Submitting a query
  3. Processing the results
  4. Retrieving the data
  5. 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( $DSNList{$DSN} ne $Driver );
11.   my( %Config ) = Win32::ODBC::GetDSN( $DSN );
12.   if( $Config{DBQ} =<sup>~</sup> s/^$OldDrive/$NewDrive/i )
13.   {
14.     if( ! Win32::ODBC::ConfigDSN( ODBC_MODIFY_DSN,
15.                                   $Driver,
16.                                   "DSN=$DSN",
17.                                   "DBQ=$Config{DBQ}" ) )
18.     {
19.        # If the previous attempt to modify the DSN
20.        # failed then try again but using a system DSN.
21.        Win32::ODBC::ConfigDSN( ODBC_MODIFY_SYS_DSN,
22.                                $Driver,
23.                                "DSN=$DSN",
24.                                "DBQ=$Config{DBQ}" );
25.     }
26.   }
27. }     
28.
29. sub Error
30. {
31.   my( $Reason ) = @_;
32.   die "Error $Reason: " . Win32::ODBC::Error() . "\n";
33. }
34. 
35. sub GetDriver
36. {
37.   my( $Extension ) = @_;
38.   my( %Sources, $Driver, $Description );
39.   Extension =~ s/([\.\\\$])/\\$1/gs;
40.   if( %Sources = Win32::ODBC::Drivers() )
41.   {
42.     foreach $Driver ( keys( %Sources ) )
43.     {
44.       if( $Sources{$Driver} =~ /FileExtns=[^;]*$Extension/i )
45.       {
46.         $Description = $Driver;
47.         last;
48.       }
49.     }
50.   }
51.   return $Description;
52. }

The script in Example 7.35 first seeks the driver description for the ODBC driver that recognizes databases with an .mdb extension. This is performed by calling a subroutine GetDriver() on line 5. The subroutine makes a call to Win32::ODBC::Drivers() to get a list of all installed drivers, and then tests them looking for one which has a keyword FileExtns that matches the specified file extension (lines 40-50). Notice that line 39 prepends any period, backslash, and dollar sign with an escaping backslash. This is so that when the $Extension variable is used in the regular expression (line 44), the characters are not interpreted.

After the script has the driver description, it retrieves a list of available DSNs (line 7) and compares their drivers with the target one.

If the drivers match, the DSN's configuration is obtained (line 11) and the database file is compared to see whether the database is on the old D: drive. If so, the drive is changed to E:. The DSN is then modified to use the new path by first modifying it as a user DSN (line 14); if that fails, it then tries it as a system DSN (line 21).

By running this, Jane could quickly fix all the DSNs on her server in just a few minutes and with no errors. If she had manually altered the DSNs through a graphical ODBC administrator program, it would have taken much longer and would be prone to mistakes. Jane also added a task to her calendar reminding her to install the server's RAID subsystem.

Miscellaneous Functions

The ODBC API has a multitude of functions, many of which the Win32::ODBC extension exposes to a Perl script. A typical user will never use most of these, but for those who are migrating data or need to perform complex queries and cursor manipulation, among other tasks, these functions are required. This section discusses these functions and methods.

It is possible, for instance, to retrieve an array of column names using the FieldNames() method, although this is not the most useful feature because it only reports column names and nothing else. The syntax for the FieldNames() method is as follows:

@List = $db->FieldNames();

The returned array consists of the column names of the result set. There is no guarantee to the order in which the names are listed.

Managing ODBC Connections

Connections to data sources quite often have attributes that govern the nature of the connection. If a connection to a data source occurs over a network, for example, it may allow the network packet size to be changed. Likewise logon timeout values, ODBC tracing, and transaction autocommit modes are considered to be connection attributes.

These attributes can be modified and examined by two Win32::ODBC methods: GetConnectOption() and SetConnectOption():

$db->GetConnectOption( $Option );

$db->SetConnectOption( $Option, $Value );

For both methods, the first parameter ($Option) is the connection option as defined by the ODBC API. Appendix B contains a list of connect options.

The second parameter in the SetConnectOption() ($Value) indicates the value to set for the specified option.

The return value for GetConnectOption() is the value for the specified option.

Warning

Be careful with this, because GetConnectOption() does not report any value to indicate an error-if the method fails, it will still return some value that may be invalid.

The return value for SetConnectOption() is either TRUE if the option was successfully set or FALSE if the method failed to set the option.

In Example 7.36, the ODBC tracing state is queried in line 4. (Tracing is where all ODBC API calls are copied into a text file so that you can later see what your ODBC driver was doing.) If ODBC tracing is already active, the current trace file is retrieved (in line 12) and is printed. Otherwise the trace file is set (line 6) and tracing is turned on (line 7).

Example 7.36 Using the GetConnectOptions() and SetConnectOptions() methods

01. use Win32::ODBC;
02. $db = new Win32::ODBC( "MyDSN" ) || die "Error: " . Win32::ODBC::Error();
03. $TraceFile = "C:\\TEMP\\TRACE.SQL";
04. if($db->GetConnectOption( $db->SQL_OPT_TRACE() ) == $db->SQL_OPT_TRACE_OFF )
05. {
06.   $db->SetConnectOption( $db->SQL_OPT_TRACEFILE(), $TraceFile );
07.   $db->SetConnectOption( $db->SQL_OPT_TRACE(), $db->SQL_OPT_TRACE_ON() );
08.   print "ODBC tracing is now active.\n";
09. }
10. else
11. {
12.   $TraceFile = $db->GetConnectOption( $db->SQL_OPT_TRACEFILE() );
13.   print "Tracing is already active.\n";
14. }
15. print "The ODBC tracefile is '$TraceFile'.\n";
16.
17. ...continue with your code...
18. $db->Close();

Note

The ODBC API specifies so many options that are quite useful (and in some cases necessary) for a user but are just far beyond the scope of this book. Appendix B lists most of these options and includes a brief description of them. Some of these descriptions are quite technical so that ODBC programmers can understand their impact.

For those who need more information on the ODBC options and their values or for those who are just curious, it is highly recommended to consult a good book on the ODBC API. A couple of recommended books are Microsoft's "ODBC SDK and Programmer Reference" and Kyle Geiger's "Inside ODBC."

Managing ODBC Statement Options

Just as an ODBC connection has attributes that can be queried and modified, so can an ODBC statement. When a script creates a Win32::ODBC object, it has both connection and statement handles. This means that you can not only manage the connection attributes, but you can also manage statement attributes such as the cursor type, query timeout, and the maximum number of rows in a dataset from a SELECT query. These statement attributes are managed by the GetStmtOption() and SetStmtOption() methods:

$db->GetStmtOption( $Option );

$db->SetStmtOption( $Option, $Value );

The first parameter is the statement option as defined by the ODBC API. Appendix B contains a list of these statement options.

The second parameter in SetStmtOption() indicates the value to set for the specified option.

The return value for GetStmtOption() is the value for the specified option.

Warning

Be careful with this, because GetStmtOption() does not report any value to indicate an error-if the method fails, it will still return some value that may be invalid.

The return value for SetStmtOption() is either TRUE if the option was successfully set or FALSE if the method failed to set the option.

In Example 7.37, the SQL_ROWSET_SIZE statement option is set to 100. This will retrieve a rowset of no more than 100 rows every time FetchRow() is called. Because the rowset size is greater than 1, the actual row processed after the FetchRow() will increase by 100. This is why we are using the GetStmtOption() with the SQL_ROW_NUMBER option to determine the current row number.

Example 7.37 Using the GetStmtOption() and SetStmtOption() methods

01. use Win32::ODBC;
02. $db = new Win32::ODBC( "MyDSN" ) || die "Error: " . Win32::ODBC::Error();
03. if( ! $db->Sql( "SELECT * FROM Foo" ) )
04. {
05.   $db->SetStmtOption( $db->SQL_ROWSET_SIZE(), 100 );
06.   while( $db->FetchRow( 1, SQL_FETCH_NEXT ) )
07.   {
08.     my( %Data ) = $db->DataHash();
09.     ...process data…
10.     if( $Row = $db->GetStmtOption( $db->SQL_ROW_NUMBER() ) )
11.     {
12.       print "Processed row $Row.\n";
13.     }
14.     else
15.     {
16.       print "Unable to determine row number.\n";
17.     }
18.   }
19. }
20. $db->Close();

Obtaining General ODBC Information

There is one additional method that will retrieve information pertaining to the ODBC driver: GetInfo(). The information retrieved by GetInfo() is read-only and cannot be set.

$db->GetInfo( $Option );

The only parameter ($Option) is a value that represents the particular information that is desired. Appendix B provides a list of values.

Example 7.38 shows how GetInfo() can be used to determine whether the data source is read-only.

Example 7.38 Using the GetInfo() method

01. use Win32::ODBC;
02. $db = new Win32::ODBC( "MyDSN" ) || die "Error: " . Win32::ODBC::Error();
03. if( ! $db->Sql( "SELECT * FROM Foo" ) )
04. {
05.   $Result = $db->GetInfo( $db->SQL_DATA_SOURCE_READ_ONLY() );
06.   if( $Result =~ /y/i )
07.   {
08.     print "OOOPS! This data source is read only!\n";
09.     $db->Close();
10.     exit;
11.   }
12.   ...process data...
13. }
14. $db->Close();

Checking for ODBC Function Support

Not all ODBC drivers support all ODBC functions. This can cause problems for script writers. To check whether a connection supports a particular ODBC function, you can use the GetFunctions() method:

%Functions = $db->GetFunctions( [$Function1[, $Function2, ...]]);

The optional parameters are constants that represent ODBC functions such as SQL_API_SQLTRANSACT (which represents the ODBC API function SQLTransact()). There can be any number of functions passed in as parameters. If no parameters are passed in, all functions are checked.

A hash is returned consisting of keys that represent an ODBC API function, and the key's value is either a TRUE or FALSE. If parameters were passed into the method, the resulting hash consists of only the keys that represent the parameters passed in.

In Example 7.39, the GetFunctions() method is used to learn whether the ODBC driver supports transaction handling by means of the ODBC API's SQLTransactions() function. If it does, the Perl script can call $db->Transaction().

Example 7.39 Using the GetFunctions() method

use Win32::ODBC;
$db = new Win32::ODBC( "MyDSN" ) || die "Error: " . Win32::ODBC::Error();
%Functions = $db->GetFunctions();
if( $Functions{$db->SQL_API_SQLTRANSACT()} )
{
  print "Hey, this ODBC driver supports the SQLTransact() function!\n";
}

Limits on Column Size

When a query returns a result set, a buffer must be created for each column. Generally Win32::ODBC can determine the size of the buffer based on the column data type. Some data types, however, do not describe the size of their data (such as the memo data type found in MS Access). In these cases, Win32::ODBC allocates a buffer of a predetermined size. This size is the maximum size that a buffer can be. This limit, however, can be both queried and changed with the GetMaxBufSize() and SetMaxBufSize() methods:

$db->GetMaxBufSize();

$db->SetMaxBufSize( $Size );

The SetMaxBufSize() method takes one parameter ($Size), which represents the size in bytes that the limit of a buffer can be. Both functions return the number of bytes that the current buffer size is limited to.

Retrieving Data Type Information

Because each database has its own way of handling data, it can become quite difficult to know how to manage a particular data type. One database may require all text literals to be enclosed by single quotation marks, whereas another database may require double quotation marks. Yet the MONEY data type may require a prefix of some character such as the dollar sign ($) but nothing to terminate the literal value. Because a script using ODBC must be able to interact with any kind of database, it is important to be able to query the database to learn this information. This is where GetTypeInfo() comes in:

$db->GetTypeInfo( $DataType );

The first, and only, parameter is a data type. This value can be any one of the following data types:

SQL_ALL_TYPES SQL_TYPE_DATE

SQL_CHAR SQL_TYPE_TIME

SQL_VARCHAR SQL_TYPE_TIMESTAMP

SQL_LONGVARCHAR SQL_INTERVAL_MONTH

SQL_DECIMAL SQL_INTERVAL_YEAR

SQL_NUMERIC SQL_INTERVAL_YEAR_TO_MONTH

SQL_SMALLINT SQL_INTERVAL_DAY

SQL_INTEGER SQL_INTERVAL_HOUR

SQL_REAL SQL_INTERVAL_MINUTE

SQL_FLOAT SQL_INTERVAL_SECOND

SQL_DOUBLE SQL_INTERVAL_DAY_TO_HOUR

SQL_BIT SQL_INTERVAL_DAY_TO_MINUTE

SQL_BIGINT SQL_INTERVAL_DAY_TO_SECOND

SQL_BINARY SQL_INTERVAL_HOUR_TO_MINUTE

SQL_VARBINARY SQL_INTERVAL_HOUR_TO_SECOND

SQL_LONGVARBINARY SQL_INTERVAL_MINUTE_TO_SECOND

If successful, the GetTypeInfo() method returns TRUE and a dataset that describes the data type passed is returned; otherwise, the method returns FALSE.

Any resulting dataset will contain one row representing the specified data type. Use the FetchRow() and DataHash() methods to walk through the resulting dataset. Table 7.4 describes the columns of the dataset. If SQL_ALL_TYPES is specified as the data type, the dataset will contain a row for every data type that the data source is aware of.

Example 7.40 illustrates the use of the GetTypeInfo() method. Line 8 assigns the value SQL_ALL_TYPES to the variable $Type. This could be any valid data type constant from the preceding list. Notice the hack to obtain that value; referring to it as a method from the $db. This is necessary because the data type constants are not exported from the ODBC.PM file (unless you edit ODBC.PM and add the constants to the EXPORT list).

Table 7.4 Dataset returned by the GetTypeInfo() method

Column Name Description

TYPE_NAME Data source dependent name (such as MONEY). This is a text value.
DATA_TYPE The SQL equivalent data type. This is an integer value.
COLUMN_SIZE The maximum column size (in bytes) the data source supports for the data type.
LITERAL_PREFIX The string used to prefix literal value. On most data sources, for example, a char data type would specify a single quotation mark (').
LITERAL_SUFFIX The string used to terminate a literal value. On most data sources, for example, a char data type would specify a single quotation mark (').
CREATE_PARAMS A comma-separated list of keywords required when specifying this data type (as described by the TYPE_NAME column). The order of these keywords is the order to specify. The DECIMAL data type, for example, would return "precision, scale". This indicates that when you specify a DECIMAL data type (as when creating a table), you must supply precision and scale values.
NULLABLE Whether the data type can be NULL. Possible values are
 SQL_NO_NULLS

SQL_NULLABLE

SQL_NULLABLE_UNKNOWN
CASE_SENSITIVE

Whether the data type is case sensitive. Possible values are SQL_TRUE SQL_FALSE SEARCHABLE This value illustrates how the data type is used in a WHERE clause.

UNSIGNED_ATTRIBUTE Whether the data type is unsigned. Possible values: SQL_TRUE SQL_FALSE NULL (if not applicable, such as for a char type)
FIXED_PREC_SCALE Whether the data type has a predefined fixed precision and scale such as MONEY. Possible values: SQL_TRUE SQL_FALSE
AUTO_UNIQUE_VALUE Whether the data type is auto-incrementing (such as a counter). Possible values:

      SQL_TRUE
SQL_FALSE NULL (if this is not applicable, or if the data type is a character).
LOCAL_TYPE_NAME A localized (based on language such as English or German) version of the data type's name.
MINIMUM_SCALE The minimum scale of the data type, or NULL if it's not applicable.
MAXIMUM_SCALE The maximum scale of the data type, or NULL if it's not applicable.



Example 7.40 Determining how an SQL literal is handled using GetTypeInfo()

01. use Win32::ODBC;
02. $DSN = "My DSN" unless $DSN = $ARGV[0];
03. if( ! ( $db = new Win32::ODBC( $DSN ) ) )
04. {
05.   print "Error: Could not connect to \"$DSN\".\n" . Win32::ODBC::Error();
06.   exit;
07. }
08. $Type = $db->SQL_ALL_TYPES();
09. if( $db->GetTypeInfo( $Type )
10. {
11.   my %Data;
12.   if( $db->FetchRow() )
13.   {
14.     my (%Data) = $db->DataHash();
15.     print "$Data{TYPE_NAME} data is referred to as: " .
              "$Data{LITERAL_PREFIX}data$Data{LITERAL_SUFFIX}\n";
16.   }else{
17.     $Data{TYPE_NAME} = "---not supported---";
18.   }
19. }else{
20.   print "Can't retrieve type information: " . $db->Error() . "\n";
21. }

Processing More Results

If supported by your ODBC driver, you can submit multiple queries in one call to Sql(). If the query is successful, you would fetch and process the data from the first SQL statement and then call the MoreResults() method and repeat the process of fetching and processing the data:

$db->MoreResults();

The return value is either TRUE, indicating that another result set is pending, or FALSE, indicating that no more result sets are available. Example 7.41 demonstrates using MoreResults().

Example 7.41 Processing multiple result sets with MoreResults()

01. use Win32::ODBC;
02. $db = new Win32::ODBC( "MyDSN" ) || die "Error: " . Win32::ODBC::Error();
03. $Query = "SELECT * FROM Foo SELECT * FROM Bar";
04. if( ! $db->Sql( $Query ) )
05. {
06.   do
07.   {
08.     while( $db->FetchRow() )
09.     {
10.       my( %Data ) = $db->DataHash();
11.       ...process data...
12.     }
13.   } while ( $db->MoreResults );
14. }
15. $db->Close();

ODBC Transaction Processing

By default, most ODBC drivers are in autocommit mode. This means that when you perform an INSERT, UPDATE, DELETE, or some other query that modifies the database's data, the changes are made immediately; they are automatically committed. For some situations, however, this is unacceptable. Consider a CGI-based shopping cart script. Suppose this script will submit an order by adding information to a table in a database. Then it updates another table which describes the particular customer (the time he placed the order, what that order number was, and so forth). This is a case where autocommit mode can be a problem. Suppose that for some odd reason the second update fails. The script will tell the user that the order failed, but the first update has already been submitted, so the order is now queued to be processed.

This situation can be avoided by setting autocommit mode to off. When autocommit is off, you can submit as many modifications to the database as you want without having the modifications actually committed until you explicitly tell it to do so with the Transact() method:

$db->Transact( $Type )

The only parameter ($Type) is the type of transaction. This can be either of the following listed values:

SQL_COMMIT. All modifications are committed and saved to the database.

SQL_ROLLBACK. All modifications are ignored and the database remains as it was before any modifications were made to it.

The Transact() method will return a TRUE if the transaction was successful and a FALSE if it failed.

Note

Not all ODBC drivers support the Transact() method. You can use the GetFunctions() method to check whether your driver does support it.

Before using the Transact() method, you need to make sure that the autocommit mode is turned off. Example 7.42 describes how to turn autocommit off as well as how to use Transact().

Example 7.42 Using the Transact() method

01. use Win32::ODBC;
02. $db = new Win32::ODBC( "MyDSN" ) || die "Error: " . Win32::ODBC::Error();
03.
04. $db->SetConnectOption( $db->SQL_AUTOCOMMIT, $db->SQL_AUTOCOMMIT_OFF );
05. $Query = "INSERT INTO Foo (Name, Age) VALUES ('Joe', 31 )";
06. if( ! $db->Sql( $Query ) )
07. {
08.   ...process something that sets $bSuccess...
09.
10.   if( $bSuccess == 1 )
11.   {
12.     $db->Transact( $db->SQL_COMMIT );
13.   }
14.   else
15.   {
16.     $db->Transact( $db->SQL_ROLLBACK );
17.   }
18. }
19. $db->Close();

Row Counts

Some SQL statements (UPDATE, INSERT, DELETE, and sometimes SELECT) return a value that represents the number of rows that were affected by the statement. Not all drivers support this. If it is supported, however, you can obtain this number with the RowCount() method:

$db->RowCount();

The return value is either the number of rows affected or -1 if the number of affected rows are not available. The -1 value can be a result of an ODBC driver that does not support this function (such as the Access driver).

Advanced Features of Win32::ODBC

So far, this chapter has discussed the simplest usage of the Win32::ODBC extension. This is how most scripts use it; however, Win32::ODBC provides access to most of the ODBC 2.0 API. This means that if you are familiar with ODBC you can control your interactions with databases using some pretty powerful features, including fetching rowsets, cursor control, and error processing.

Fetching a Rowset

Suppose that you submit a query to a SQL Server. This query will return 10,000 rows and you need to process each row. Every time you use the FetchRow() method, a command will be sent to the server requesting the next row. Depending on the network traffic, this can be quite a slow process because your ODBC driver must make 10,000 network requests and the SQL Server must respond with data 10,000 times. Add on top of this all the data required to package this network data up (such as TCP/IP headers and such) and you end up with quite a bit of network traffic. To top it all off, each time you fetch the next row your script must wait until the request has made it back from the server. All this can cause inefficient and slow response times.

If you could convince the ODBC driver to always collect 1,000 rows from the server each time a FetchRow() was called, the driver would only have to call to the server 10 times to request data.

This is where the concept of a rowset comes in. When you fetch rows, the ODBC driver really fetches what is known as a rowset. A rowset is just a collection of rows. By default a rowset consists of one row. Typically this configuration suffices, but it can be changed.

You can change the number of rows that make up a rowset by calling the SetConnectOption() method specifying the SQL_ROWSET_SIZE option. Once done, the advanced options of the FetchRow() method can be used to obtain a desired rowset. Then by just resetting the rowset size to 1, a regular call to FetchRow() will retrieve one row at a time. Refer to the next section, "Advanced Row Fetching," as well as Example 7.44 for more details.

Advanced Row Fetching

Although the FetchRow() method was described earlier, you need to revisit it now. A few parameters that can be passed into it need some explaining:

( $Result[, @RowResults] ) = $db->FetchRow( [$Row [, $Type]] );

If no parameters are passed in, the method will act as was described in the section titled "Processing the Results."

The first parameter ($Row) is a numeric value that indicates a row number.

The second value ($Type) indicates the mode in which the row number (the first parameter) will be used. The following list shows possible values for this parameter. If this parameter is not specified, the default value of SQL_FETCH_RELATIVE will be used.

  • SQL_FETCH_NEXT. Fetch the next rowset (typically this is just one row) in the dataset. This will disregard the row value passed into FetchRow(). If this is the first FetchRow() to be called, this is the equivalent to SQL_FETCH_FIRST.
  • SQL_FETCH_FIRST. Fetch the first rowset in the dataset.
  • SQL_FETCH_LAST. Fetch the last rowset in the dataset.
  • SQL_FETCH_PRIOR. Fetch the previous rowset in the dataset. If the cursor is positioned beyond the dataset's last row, this is equivalent to SQL_FETCH_LAST.
  • SQL_FETCH_ABSOLUTE. Fetch the rowset that begins at the specified row number in the dataset. If the row number is 0, the cursor is positioned before the start of the result set (before physical row number 1).
  • SQL_FETCH_RELATIVE. Fetch to rowset beginning with the specified row number in relation to the beginning of the current rowset. If row is 0, the current rowset will not change (instead, it will be updated).
  • SQL_FETCH_BOOKMARK. This is not supported, but is included for completeness.

The FetchRow() method will move the rowset to the new position based on the row and mode parameter.

There are two return values for the FetchRow() method. The first indicates whether the fetch was successful. It will return a TRUE if successful; otherwise, it returns FALSE-unless, however, optional parameters are passed into the method. If this is the case, the first return value will be SQL_ROW_SUCCESS if successful; otherwise, it will be some other value.

The second return value is only returned if optional parameters are passed into the method. This second return value is an array of values. The array consists of a return value for each row fetched in the rowset. By default ODBC drivers use a value of 1 for the rowset size so the FetchRow() will only return one value (because it only fetches one row). If you change the rowset to greater than one, however, your return value array will reflect the number of rows in your rowset.

Each value in the returned array corresponds with one of the values in the following list:

  • SQL_ROW_SUCCESS. The row was successfully fetched.
  • SQL_ROW_UPDATED. The row has been updated since the last time it was retrieved from the data source.
  • SQL_ROW_DELETED. The row has been deleted since the last time it was retrieved from the data source.
  • SQL_ROW_ADDED. The row has been added since the last time it was retrieved from the data source.
  • SQL_ROW_ERROR. The row was unable to be retrieved.

Note that it is possible to set your cursor to a type that will reflect alterations made to the data. Suppose, for example, that you are retrieving all the rows from a data source with "SELECT * FROM Foo". Your script then begins to retrieve rows one at a time. While your script is fetching row 100, another program has deleted row 195 from the database. When you get to row 195, your ODBC driver will try to fetch it even though it has been deleted. This is because when you execute your query the driver receives a list of row numbers which it will fetch. Because row number 195 was included in this list, the driver will try to fetch it when requested to, regardless of whether it has since been deleted. The driver will report the fact that it has been deleted by returning a value of SQL_ROW_DELETED in the return array.

For the following example, assume that the rowset size was set to ten so that every time you perform a FetchRow(), ten rows are actually fetched. When you fetch the rowset starting at 190, FetchRow() will return a ten-element array that will consist of the values shown in Example 7.43.

Example 7.43 The returned row result array from FetchRow() indicating that the fifth row has been deleted

01. $RowResults[0] == SQL_ROW_SUCCESS;
02. $RowResults[1] == SQL_ROW_SUCCESS;
03. $RowResults[2] == SQL_ROW_SUCCESS;
04. $RowResults[3] == SQL_ROW_SUCCESS;
05. $RowResults[4] == SQL_ROW_SUCCESS;
06. $RowResults[5] == SQL_ROW_DELETED;
07. $RowResults[6] == SQL_ROW_SUCCESS;
08. $RowResults[7] == SQL_ROW_SUCCESS;
09. $RowResults[8] == SQL_ROW_SUCCESS;
10. $RowResults[9] == SQL_ROW_SUCCESS;

Example 7.44 demonstrates how you can use the extended features of FetchRow() to jump ahead several rows in a result set.

Example 7.44 Advanced and simple example of FetchRow()

01. use Win32::ODBC;
02. $db = new Win32::ODBC( "MyDsn" ) || die "Error: " . Win32::ODBC::Error();
03.
04.   # Prevent the cursor from closing automatically
05. $db->SetStmtCloseType( SQL_DONT_CLOSE );
06.
07.   # Change our cursor type to static (assuming the driver supports it)
08. $db->SetStmtOption( $db->SQL_CURSOR_TYPE, $db->SQL_CURSOR_STATIC );
09.
10. if( ! $db->Sql( "SELECT * FROM Foo" ) )
11. {
12.   if( ( $db->FetchRow( 9000, SQL_FETCH_ABSOLUTE ))[0] ==
            $db->SQL_ROW_SUCCESS )
13.   {
14.     do
15.     {
17.       my( %Data )= $db->DataHash();
18.       print "User: $Data{Name}\n";
19.     } while( $db->FetchRow() );
20.   }
21. }
22. $db->Close();

The operation in Example 7.44 can save one quite a bit of both time and network bandwidth (if the ODBC driver is talking to a network database server) because the first FetchRow() (line 12) will position the cursor to point at row 9,000 right away. The alternative would be to walk through the database one row at a time until it got to the nine thousandth row.

At this point, the column's data will be retrieved and printed for the rest of the remaining rows by using the simple FetchRow() method.

Managing Cursors

Those familiar with SQL will be happy to know that cursors are supported. If you have no idea what a cursor is, you probably don't need to be concerned about them. Cursors are beyond the scope of this book, so very little time will be spent on this topic. Several ODBC functions pertain to their use, however, and these are described in this section.

Basically, the cursor is an indicator that points to the current row in a given rowset. This is just like a cursor on a DOS window; it shows you where your current position in the DOS window is. The ODBC cursor just shows you where the current row is from which you will be retrieving data.

Win32::ODBC, by default, resets the state of all cursors automatically for you whenever you use the Sql() or any other method that returns rows of data (such as GetTypeInfo() and any of the cataloging functions). Whenever these methods are used, the current cursor is dropped-that is to say, it is destroyed and forgotten.

This automatic dropping can be a problem when you need to keep a cursor open or if you have named a cursor and need to retain its name. This handling of the cursor can be overridden by changing the statement close type with the following method:

$db->SetStmtCloseType( $CloseType[, $Connection ] );

The first parameter ($CloseType) is one of the close types documented in the following list.

  • SQL_CLOSE. The current statement will not be destroyed, only the cursor. For all practical reasons, this is the same as SQL_DROP.
  • SQL_DROP. The current statement is destroyed as well as the cursor.
  • SQL_DONT_CLOSE. This will prevent the cursor from being destroyed anytime new data is to be processed.
  • SQL_UNBIND. All bound column buffers are unbound. This is of no use and is only included for completeness.
  • SQL_RESET_PARAMS. All bound parameters are removed from their bindings. Because Win32::ODBC does not yet support parameter binding, this is of no use and is only included for completeness.

The optional second parameter ($Connection) is the connection number for an existing ODBC connection object. If this is empty, the current object is used. This is the object whose close type will be set.

The SetStmtCloseType() method will return a text string, indicating which close type is set on the object.

Yet another method that will enable you to retrieve the current close type on a connection object is as follows:

$db->GetStmtCloseType( [$Connection] );

The optional first parameter ($Connection) is an ODBC connection object number that indicates which object will be queried. If nothing is passed in, the current object is assumed.

Just like the SetStmtCloseType() method, GetStmtCloseType() will return a text string indicating which of the five values documented in the list for SetStmtCloseType() is the close type.

A connection's cursor can be dropped by force if you need to. This can be very handy if you have previously set its close type to SQL_DONT_CLOSE:

$db->DropCursor( [$CloseType] );

The first optional parameter ($CloseType) indicates how the cursor is to be dropped. Valid values are the same as documented for SetStmtCloseType(), with the exception of SQL_DONT_CLOSE; this value is not allowed. If no value is specified, SQL_DROP is assumed.

Note that this will not only drop the cursor, but also the current statement and any outstanding and pending result sets for the connection object.

All cursors that are created are given a name either by the programmer or by the ODBC driver. This name can be useful in queries and other SQL statements that allow you to use the cursor name such as "UPDATE table ... WHERE CURRENT OF cursorname." To retrieve the cursor name, you use the GetCursorName() method:

$db->GetCursorName();

The method will return a text string that is the name of the cursor. If no cursor is defined, the method returns an undef.

If you want, you can name the cursor yourself. This may be necessary for stored procedures that require particular cursor names to be set:

$db->SetCursorName( $Name );

The first and only parameter ($Name) is the name of the cursor. Each ODBC driver defines the maximum length of it's cursor names but the ODBC API recommends not exceeding 18 characters in length.

If the cursor's name is successfully set, the method returns a TRUE; otherwise, it returns a FALSE.

Note

A cursor's name will be lost when the cursor is reset or dropped. For this reason it is important that you set the statement close type to SQL_DONT_CLOSE before you set the cursor name. Otherwise, the moment the SQL query is generated the cursor will be destroyed and the name will be lost. Win32::ODBC closes cursors before executing SQL statements unless the close type is set to SQL_DONT_CLOSE. You can always force the cursor to be dropped with the DropCursor() method.

Cloning ODBC Connections

ODBC connection objects do not share anything with each other. For example if you create two objects ($db1 and $db2), even from the same database, the two objects cannot communicate with each other. If $db1 has created a dataset with a named cursor $db2 cannot access $db1's data. There is a way, however, for $db1 to talk with $db2, using cloning. When you clone a Win32::ODBC object you are creating a duplicate object. This cloned object talks with the same data source and for all practical matters is the same as the original object. Because these objects are separate and discrete from each other they can run separate queries and process results as if they were two totally separate connections.

The wonderful nature of cloned objects is that they can talk with each other. If one object issues a query that produces a dataset, the other object can use that dataset for its own query. Cloned objects are created using the new command:

new Win32::ODBC( $Object );

If you pass in another Win32::ODBC connection object rather than a DSN name, the object will be cloned-that is, another object will be created which shares the same connection to the database. In technical terms, the objects will share the same environment and connection handles although their statement handles will be unique.

This is used mostly in conjunction with cursor operations.

Error Processing

An ODBC error is an error generated by either the ODBC Manager or an ODBC driver. This is a number that refers to an error that occurred internally to ODBC. In practice this error is really only useful if you have an ODBC API manual or information on error numbers for a particular ODBC driver. The SQL state, however, is a standardized string that describes a condition that all ODBC drivers adhere to.

Retrieving Errors

Win32::ODBC tracks ODBC errors in two ways. The first way is that the module itself will always track the last ODBC error that occurred. The second way is that a particular ODBC connection object will track its own errors. You retrieve the error information by calling Error() either as an object method or as a module function:

Win32::ODBC::Error();

$db->Error();

If used as a module function (the first example), it will report the last error that the ODBC module had generated regardless of which connection was responsible for the error. If used as an object's method (the second example), however, the last error that the particular object generated will be reported.

When retrieving error information with the Error() method (or function), the results could be in one of two formats, depending upon the context of the assignment: as an array or as a text string in a scalar context.

Array Context

If a call were made to Error() in an array context:

@Errors = $db->Error();

the returning array, @Errors, would consist of the following elements:

( $ErrorNumber, $Tagged Text, $Connection Number, $SQL State)

Table 7.5 lists a description for each element.

Scalar Context

If a call were made to Error() in a scalar context:

$Error = $db->Error();

the result would resemble the following string:

"[Error Number] [Connection Number] [Tagged Text]"

Table 7.5 lists descriptions for these elements.

Table 7.5 Fields returned by the Error() function and method

Field Description

Error Number The actual error number as reported by ODBC (not too useful unless you have an ODBC API manual). This number may be specific to an ODBC driver, so it could have one meaning to an Oracle driver but another meaning to a Sybase driver. When an error occurs, there is always an ODBC-specific error number. This is different from the SQL State because it identifies an error that ODBC generated. This error is not an ISO standard, but instead it is an ODBC-specific error.
Tagged Text The text description of the error. Either the ODBC Manager or the ODBC driver report this. This text is in a tagged format-that is, it identifies both the vendor and the component that generated the error. For example, the error text "[Microsoft][ODBC Microsoft Access 2.0 Driver] Unable to open database file" identifies that the Microsoft ODBC Access driver reported an error "Unable to open database file." The first tag is the vendor, and the second tag is the ODBC component that generated the error. Another example:[Microsoft][ODBC Manager] The data could not be retrieved because the database server is offline illustrates that the Microsoft ODBC Manager reported the error that the server is unavailable.
Connection Number The number of the ODBC connection that generated the error. If there was no connection number, the number is left as an undef. Each Win32::ODBC object has an associated unique connection number. You can discover an ODBC connection object's connection number by calling the Connection() method.
SQLState The SQL state of the error. This value complies with the ISO SQL-92 standard; hence a programmer can rely on it to represent a particular error condition regardless of which ODBC driver generated the error. It would be a good idea to invest in a book on ODBC so that the SQL state will make sense.


The SQL State

The current SQL state can be retrieved by using the SQLState() method:

$db->SQLState();

The value returned constitutes the last SQL state of the particular ODBC connection object. Because the SQL state represents the state of the connection and it is not driver specific (as the error number is), it is the same for all ODBC drivers. Any good book on the ODBC API will list all possible SQL state codes.

Summary

Interacting with databases is not a very difficult thing to do, but having to interact with several, potentially different, databases can cause nightmares because of a lack of conformity across them. The expectation of writing a Perl script to recognize all different types of databases is just too high to be realistic.

The ODBC API is commonly found on Win32 machines and it addresses this issue of database conformity. By using ODBC, a programmer can create huge scripts that will work with any database and can be easily transported from machine to machine.

The Win32::ODBC extension has provided thousands of CGI, administrative, and other types of scripts with access to ODBC. This extension provides a relatively thin layer of abstraction from the ODBC API that is ideal for anyone who is prototyping an application in Perl that will be programmed in C or C++ to access the ODBC API. Any coder who is familiar with the ODBC API will feel at home using this extension.

If a programmer is not familiar with ODBC, the extension hides most of the tedious work required so that the coder can focus on manipulating data and not worry about how to retrieve it.

You can also access ODBC data sources in other ways such as using the Perl DataBase Interface (DBI) which has a basic ODBC driver. Considering that the DBI ODBC extension exists on both Win32 and UNIX platforms, it is ideal for cross-platform scripts. Additionally the Win32::OLE extension (refer to Chapter 5) provides access into Microsoft's ActiveX Database Objects (ADO) and other COM-based ODBC implementations.

Copyright © 2000 New Riders Publishing

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

New Riders

United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
Info
| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141216.1 | Last Updated 13 Mar 2001
Article Copyright 2001 by New Riders
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid