|
| Title | Windows NT Win32 Perl Programming: The Standard Extensions | | Authors | Dave Roth | | Publisher | New Riders | | Published | Jan 1999 | | ISBN | 1578700671 | | Price | US 40.00 | | Pages | 630 |
|
Chapter 7 - Data Access
For most administrators, their jobs take their Perl programming
into realms of maintaining user accounts and managing servers. More and more
administrators are finding it important to write scripts that interact with
databases, however, whether for Web-based Common Gateway Interface (CGI)
scripts or for querying administrative databases for reports.
Many Perl modules and extensions support databases such as the
variations of xDBM_File and libraries for SQL Server, Sybase,
Oracle, MySQL, Dbase, and various others. A couple of Win32-specific
extensions, however, marry the Win32 platform with generic database services
such as ODBC. This chapter provides an overview on using the Win32::ODBC extension.
What Is ODBC?
All databases have their
own unique way of doing things. When a programmer wants to access database
services from within his code, he has to learn how to use that particular
database. Really this is not so difficult because most of the major database
vendors document their systems and provide libraries that a coder can link to.
This is all fine and good as long as you always use one particular database.
The moment you need to access a different type of database, however, not only
will you have to learn an entire set of commands and procedures, but you will
also need to change your scripts so that they can interface with this new
database. Usually this means a total rewrite of the code.
Now imagine that you want to write a database application that
would work with any database that your client may have. A perfect example is
that you want to write a shopping cart CGI script for the Web. Because you do
not know which database your client may have implemented, you would have to
write several variations of the same script to handle all the possible
databases. If this is not more than you want to contend with, just imagine what
it would be like to support all those scripts. Testing them would be just as
horrific because you would have to install each and every database system.
Wouldn't it be nice if all databases conformed to just one standard
so that if you programmed your scripts to utilize it, then one script would
work across all database systems? This
is where ODBC (Open DataBase Connectivity) comes in.
ODBC is an initiative that the database industry has come to accept
as the standard interface. Now many people believe that ODBC is a Microsoft
product, but they are incorrect in believing so. Microsoft did champion the API
and was one of the first companies to have working implementations of it. The
actual interface standard was designed, however, by a consortium of
organizations such as X/Open SQL Access Group, ANSI, ISO, and several vendors
such as IBM, Novell, Oracle, Sybase, Digital, and Lotus, among others.
The standard was designed to be a platform-independent
specification, and it has been implemented on the Win32, UNIX, Macintosh, and
OS/2 platforms, just to name a few. ODBC has become so widely accepted that
some vendors like IBM, Informix, and Watcom have designed their database
products' native programming interface based on ODBC.
ODBC Models
When a program uses ODBC, it just makes calls into functions
defined by the ODBC API. When a Perl script makes a call in to the ODBC API, it
is typically calling in to a piece of software known as the ODBC Manager. This
manager is a dynamic link library (DLL) that decides how to handle the call.
Sometimes the Manager will perform some task, such as listing all available
Data Source Names (DSNs), and return to the calling script. Other times the
ODBC Manager will have to load a specific ODBC driver and request that driver
(usually another DLL) to perform the task, such as connecting to the database
and executing a query. Each level of software that the ODBC Manager has to pass
through to accomplish a task is known as a tier.
ODBC has different tiered models that describe how the basic
infrastructure works. Each model is premised on how many tiers exist between
the ODBC Manager and the actual database. There could, in theory, be an
unlimited number of tiers, but the impracticality of administrating and
configuring so many tiers renders only three common models: the one-, two-, and
three-tier models.
One-Tier Model
The one-tier model consists of only one step (or tier). The client
application talks to the ODBC Manager, which asks the ODBC driver to perform
the task. (This is the first tier.) The driver opens the database file.
This is a very simple model in which the ODBC driver performs the
work of database lookup and manipulation itself. Examples of single-tier ODBC
drivers include Microsoft's Access, FoxPro, and Excel ODBC drivers.
Two-Tier Model
Just like the one-tier model, the client application talks to the
ODBC Manager, which talks to the ODBC driver. (This is the first tier.) Then the
ODBC driver will talk to another process (usually on another machine via a
network), which will perform the actual database lookup and manipulation. (This
is the second tier.) Examples of this are IBM's DB2 and MS's SQL Server.
Three-Tier Model
Just like the first two models, in the three-tier model the client
application talks to the ODBC Manager that talks to the ODBC driver (tier one).
The ODBC driver then talks to another process (usually running on another
machine) which acts as a gateway (tier two) and relays the request to the
database process (tier three) which can be a database server or a mainframe
such as Microsoft's SNA server.
These different tiers are not so important to the programmer as
they are to the administrator who needs to configure machines with ODBC
installed. However, understanding the basic infrastructure helps in making
decisions such as how to retrieve data from the driver so that network traffic
is held to a minimum, as discussed later in the section titled "Fetching a
Rowset."
Data Source Names
To truly separate the client application from the database, there
are Data Source Names (DSNs). DSNs are sets of information that describe to the
ODBC Manager what ODBC driver to use, how the driver should connect to the
database, what userid is needed to log on to the database, and so forth. This
information is collectively referred to by using a simple name, a Data Source
Name. I may set up a DSN that tells the ODBC Manager that I will be using a
Microsoft SQL Server on computer \\DBServer, for example, and that it will
be accessed via named pipes using a userid of 'JoeUser'. All this information I
will call "Data."
When writing an application, I will just make an ODBC connection to
the DSN called "Data" and ODBC will take care of the rest of the work. My
application needs only to know that I connect to "Data," and that is it. I can
later move the database to an Oracle server, and all I need to do is change the
ODBC driver (and, of course, configure it) that the "Data" DSN uses. The actual
Perl script never needs to be altered. This is the beauty of ODBC.
ODBC is an application programming interface (API), not a language.
This means that when you are using ODBC you are using a set of functions that
have been predefined by the groups who created the ODBC specification. The ODBC
specification also defines what kinds of errors can occur and what constants
exist. So ODBC is just a set of rules and functions.
Now, when you use ODBC you need to interact somehow with the
database. ODBC provides a set of rules and functions on how to access the database but not how to manipulate data in the database. To
do this you need to learn about a data querying language known as SQL.
SQL
The language that ODBC uses to request the database engine to
perform some task is called SQL (pronounced SEE-kwel), an acronym for
Structured Query Language. SQL was designed by IBM and was later standardized
as a formal database query language. This is the language that ODBC uses to
interact with a database. A full discussion on SQL is beyond the scope of this
book, but this chapter covers the general concepts that most people use.
Before discussing how to use SQL, first you need to understand some
very simple but basic SQL concepts such as delimiters and wildcards.
Note
SQL is not case sensitive, so
preserving case is not imperative; however, conventionally, SQL keywords are
all in caps. So, if you were to issue a query like this:
SELECT * FROM Foo
it would yield the same results as
this:
SelECt * froM Foo
Delimiters
When you need to specify a separation of things, you use a
delimiter. Typically, a delimiter is an object (such as a character) that
symbolizes a logical separation. We all know about these, but some may not know
them by name. When you refer to a path such as c:\perl\lib\win32\odbc.pm,
for example, the backslashes (\) delimit the directory names with the last
backslash delimiting the file name. The colon (:) delimits the drive letter
from the directory names. In other words, each backslash indicates a new
directory; backslashes separate directory names or they delimit the directory names.
Many coders will delimit things to make them easier to handle. You
may want to save a log file of time, status, and the name of the user who ran a
script, for example. You could separate them by colons so that later you can
parse them out. If you have saved to a file the line joel:896469382:success,
when you read it back you can parse it out using Example 7.1.
Example 7.1 Parsing
data using delimiters
open( FILE, "< test.dat" ) || die "Failed to open: ($!)";
while( <FILE> )
{
my( @List ) = split( ":", $_ );
print "User=$List[0]\nDate=" . localtime($List[1]) . "\nStatus=$List[2]\n";
}
close( FILE );
When it comes to SQL, delimiters are quite important. SQL uses
delimiters to identify literals.
A literal is just a value that you provide. If you are storing a user's name of
'Frankie'
into a database, for example, 'Frankie' is a literal. Perl would refer
to it as a value (as in, "you assigned the value 'Frankie' to the
variable $UserName"). In the SQL query in Example 7.3, the number 937
and the string 'Noam Chomsky' are both considered literals.
When you delimit a literal, you must understand that there are
actually two separate delimiters: one for the beginning (known as the literal prefix), and one for the
end of the literal (the literal
suffix). If that isn't enough to remember, consider this: Each data type
has its own set of delimiters! Therefore you use different delimiters when
specifying a numeric literal, time literal, text literal, currency literal, and
the list goes on.
The good news is that it is not too difficult to discover the
delimiters that a particular ODBC driver expects you to use. By using the GetTypeInfo() method, you can
discover what delimiters, if any, are required by your ODBC driver for a
particular data type (as in Example 7.2). For more on how to use this method,
refer to the "Retrieving Data Type Information" section.
Example 7.2 Determining
literal delimiters
01. # This assumes that $db is a valid Win32::ODBC object.
02. $String = "Your data";
03. $DataType = $db->SQL_CHAR();
04. if( $db->GetTypeInfo( $DataType ) )
05. {
06. if( $db->FetchRow())
07. {
08. %Type = $db->DataHash();
09. }
10. }
11. $Field = "$Type{LITERAL_PREFIX}$String$Type{LITERAL_SUFFIX}";
The even better news is that most databases follow a simple rule of
thumb: Text literals are delimited by single quotation marks (both the prefix
and suffix), and all other literals are delimited by null strings (in other
words, they do not require delimiters). So you can usually specify a SQL query
such as the one in Example 7.3 where the text literal is delimited by single
quotation marks and the numeric literal has null or empty string delimiters
(nothing delimiting it).
Example 7.3 Delimiting
a text literal in SQL
SELECT * FROM Foo WHERE Name = 'Noam Chomsky' AND ID > 937
The use of delimiters when specifying literals can cause problems
when you need to use a delimiter in the literal itself. If the prefix and
suffix delimiters for a text literal are the single quotation mark ('), for
example, it causes a problem when the literal text has a single quotation mark
or apostrophe within itself. The problem is that because the text literal has
an apostrophe (a single quotation mark) it looks to the database engine as if
you are delimiting only a part of your literal, leading the database engine to
consider the remaining part of the literal as a SQL keyword. This usually leads
to a SQL error. In Example 7.4, the text literal is 'Zoka's
Coffee Shop', and the SQL statement is this:
SELECT * FROM Foo WHERE Company like 'Zoka's Coffee Shop'
Notice the three text delimiters, one of which is meant to be the
apostrophe in "Zoka's". This will be parsed out so that the
database will think you are looking for a company name of "Zoka",
and that you are using a SQL keyword of "s Coffee Shop", and
then starting another text literal without a delimiter. This will result in an
error.
Example 7.4 Specifying
a text literal with a character that is a delimiter. (This would cause a SQL
error.)
$CompanyName = "Zoka's Coffee Shop";
$Sql = "SELECT * FROM Foo WHERE Company like '$CompanyName' ";
The way around this is to escape the apostrophe. This is performed
by prepending the apostrophe with an escape character. There is no difference
between this and how Perl uses the backslash escape character when printing a
new line ("\n") or some other special character. The SQL escape
character is the single quotation mark. "But wait a moment," you may be
thinking, "the single quotation mark is typically a text literal delimiter!"
Well, although that is true, if SQL finds a single quotation mark in between
delimiters and the single quotation mark is followed by a valid escapable
character, SQL will consider it indeed to be an escape character.
What this means is that when you are using a single quotation mark
in your text literals, you must escape it with another single quotation mark.
The most practical way of doing this is to search through your strings and
replace all single quotation marks with two single quotation marks (not a double quotation mark-there
is a big difference) as in Example 7.5.
Example 7.5 How
to replace single quotation marks with escaped single quotation marks
$TextLiteral =~ s/'/''/g;
If you wanted to correct the code in Example 7.4, you could add a
function that performs the replacement for you, as in Example 7.6.
Example 7.6 Escaping
apostrophes
$CompanyName = Escape( "Zoka's Coffee Shop" );
$Sql = "SELECT * FROM Foo WHERE Company = '$CompanyName' ";
sub Escape
{
my( $String ) = @_;
$String =~ s/'/''/g;
return $String;
}
Tip
Not all ODBC drivers use single
quotation marks to delimit text literals. Some may use other characters, and
some may go as far as to use different delimiters for the beginning and ending
of a literal.
The way you find out which characters to use is by calling the GetTypeInfo() method, which is
discussed in the "Retrieving Data Type Information" section.
Wildcards
SQL allows for wildcards when you are querying text literals. The
two common wildcards are as follows:
• Underscore (_). Matches
any one character.
• Percent sign (%). Matches any number (zero or more)
of characters.
These wildcards are used only with the LIKE predicate
(discussed later in this chapter in the section titled "SELECT
Statement"). For now you need to be aware that wildcards are supported by most
databases (but not all). You can use the GetInfo()
method to determine whether your ODBC driver supports wildcards (see Example
7.7).
If you need to specify a percent sign or an underscore in your text
literal and not have it interpreted as a wildcard, you will have to escape it
first, just like you have to escape apostrophes. The difference between
escaping apostrophes and escaping wildcards is twofold:
• Not all ODBC drivers support escaping wildcards (believe it or
not).
• Usually, the escape character is the backslash (\), but you can
change it to be something else.
The first of the differences is pretty much self-explanatory; not
all ODBC drivers allow support for escaping wildcards. You can check whether
your ODBC driver supports wildcards and escapable wildcards by using the GetInfo() method, as in Example 7.7.
Example 7.7 Determining
the wildcard escape character
# This assumes that $db is a valid Win32::ODBC object.
if( "Y" eq $db->GetInfo( $db->SQL_LIKE_ESCAPE_CLAUSE( ) ))
{
$EscapeChar = $db->GetInfo( $db->SQL_SEARCH_PATTERN_ESCAPE( ));
print "Escaping wildcards is supported.\n";
print "The wildcard escape character is: $EscapeChar\n";
}
If your ODBC driver supports the use of escaped wildcards, you can
set the escape character to be whatever you want it to be (you don't have to
settle for what the ODBC driver uses by default) by using a LIKE predicate escape character sequence.
This process is described later in the chapter in the section on escape
clauses.
Quoted Identifiers
At times, you may need to use special characters in a query.
Assume, for example, that you have a table name that has a space in its name,
such as "Accounts Receivable." If you were to use the table name in a SQL
query, the parser would think you are identifying two separate tables: Accounts
and Receivable. This is because the space is a special character in SQL.
Ideally you would rename your table to "Accounts_Receivable" or something else
that does not have such special characters. Because this is not an ideal world
all the time, ODBC provides a way around this called the identifier quote character.
Most databases use a double quotation mark (") as the identifier
quote character, but because some do not, you may want to query your ODBC
driver to discover which character you should use. This is done using the GetInfo() method, which is discussed
later in the "Managing ODBC Options" section.
When you need to use special characters in an identifier, surround
the identifier with the identifier quote character. Example 7.8 shows a SQL
query making use of the identifier quote characters for the table name
"Accounts Receivable."
Example 7.8 Using
identifier quote characters
SELECT * FROM "Accounts Receivable"
SQL Grammar
When you construct a SQL statement, you are using a standardized
language that has been around for a while. There are dozens of good books
available that explore SQL and can provide insight on how to optimize your
queries and take full advantage of SQL's powerful features. This section is
just provided for the sake of completeness because SQL is the language that
ODBC uses.
When you use SQL, you construct what is called a statement. A statement is a type of
command that can have many details. If you want to get all the rows from a
database table that meet some criteria (such as a value of 25 in the Age
column), for example, you use a SELECT statement.
SELECT
Statement
A SELECT statement retrieves a set of data rows
and columns (known as a dataset)
from the database. It is quite simple to use. The basic structure of a SELECT
statement is
SELECT [ALL | DISTINCT] columnname [, columnname] …
FROM tablename
[WHERE condition]
[ORDER BY columnname [ASC|DESC] [, columnname [ASC|DESC]] …]
where:
columnname
is the name of a column in the table.
tablename
is the name of a table in the database.
condition is a condition that determines whether to include
a row.
A SELECT statement will retrieve a dataset
consisting of the specified columns from the specified table. If an asterisk (*)
is specified rather than a column list, all columns are retrieved.
Every row in the table that meets the criteria of the WHERE
clause will be included in the dataset. If no WHERE clause is
specified, all rows will be included in the dataset.
The rows in the dataset will be sorted by the column names listed
in the order listed if you specify an ORDER BY clause. If you use
ORDER
BY
LastName, FirstName, the resulting dataset will be sorted in
ascending order (the default) first by
LastName and then by Fir
stName.
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 <code> = Y
|
X is equal to Y. |
X > Y
|
X is greater than Y. |
X < Y |
X is less than Y. |
X >= Y |
X is greater or equal to Y. |
X <= Y |
X is less than or equal to
Y. |
X <> Y |
X is not equal to Y |
X [NOT] BETWEEN Y AND Z
| X
is between the values of Y and Z. If the NOT keyword is used, X
is not between the values Y
and Z. |
X [NOT] LIKE 'Y' |
X
is the same as Y (when comparing text data types). If the NOT
keyword is
used, X is not the
same as Y. The Y value can consist of a text literal with
wildcards. This
comparison is only needed when using wildcards; otherwise, it is
more efficient
to use =. |
EXISTS (subquery) |
This will be true
for every row returned from a subquery (another SELECT
statement). |
X IS [NOT] NULL |
The
column X is NULL. If the NOT keyword is used, column X is not NULL. |
X [NOT] IN (Y, Z, ...) |
The
value X is found in the list of (Y, Z, …). If the NOT keyword is used
then the
value X is not found
in the list (Y, Z, …). The list (Y, Z, …) could be another
SELECT subquery. |
X <operator> {ALL|ANY}(subquery) | A
subquery (another SELECT statement) is {ALL|ANY} operator>
(subquery)performed and the rows from the resulting dataset are compared
to
the value X using an operator, which could be any in this list. If the ALL
keyword is used, then all rows from the subquery must match the condition set
by the operator. If the ANY keyword is used, any row that matches the
condition set by the operator will return a true value. |
Usually, the conditional statement includes the name of a column.
When a column is referred to, you are referring to the value in the column. In
Example 7.10, a search condition of Age > 21 is used. Every row that has a
value of greater than 21 in the Age column will
satisfy the condition; therefore, it will be returned in the dataset.
Example 7.10 Specifying
a WHERE
clause
SELECT *
FROM Foo
WHERE Age > 21
Example 7.11 makes use of the WHERE predicate to
indicate a condition of the search. The query will retrieve a dataset
consisting of the Firstname, Lastname, and City
fields in the Users table only if the user's first name begins
with the letter 'C' (the '%' is a wildcard that
indicates that you don't care what comes after the 'C'-refer to the
section on wildcards). The dataset will then be sorted by Lastname
(by default the order will be ascending).
Example 7.11 Complex
SELECT
query
SELECT Firstname, Lastname, City
FROM Users
WHERE Firstname like 'C%'
ORDER BY Lastname
Multiple conditions can be used in a WHERE clause by using
Boolean conjunctions such as AND and OR. Example 7.12 will
return a dataset with rows whose Age column has values of greater than 21andLastName column
begins with either the letters 'C' or 'R'.
Example 7.12 Using
multiple conditions in a SELECT statement
SELECT *
FROM Foo
WHERE Age > 21 AND
(LastName like 'C%' OR LastName like 'R%')
For another SELECT statement consider Example 7.13. This
query will return a dataset consisting of all fields only if the City
field is 'Seattle' and the Zip code is not equal to 98103.
The list will then be sorted in descending order (starting with Z's and ending
with A's) by last name.
Example 7.13 SELECT
statement with sorting and a condition
SELECT *
FROM Users
WHERE City like 'Seattle' AND Zip <> 98103
ORDER BY Lastname DESC
INSERT Statement
An INSERT statement adds a row to a table in a database. The structure of an INSERT statement is as follows:
INSERT INTO tablename
[(columnname1[, columnname2] ... )]
VALUES (value1[, value2] ... )
where:
tablename
is the name of a table in the database.
columnname
is the name of the column to receive a value.
value is the value to be placed into the column.
The INSERT statement is used when you need to add a
record to a table. The trick here is that when you specify a list of column
names, you must list the values for those columns in the same order as the
columns.
The list of column names is optional and if left out, the first
value specified will be stored into the first column, the second value will be
stored into the second column, and so on.
Example 7.14 adds a row into the table Foo, assigns the value
'Dave'
to the Name
column, 'Seattle' to the City column, and 98103 to the Zip
column.
Example 7.14 A
simple INSERT
statement
INSERT INTO Foo
(Name, City, Zip)
VALUES ('Dave', 'Seattle', 98103)
Just to demonstrate how you can use the INSERT statement without providing any column names, look at Example 7.15.
Example 7.15 Using the INSERT statement without any column names
INSERT INTO Foo
VALUES ('Zoka''s Coffee Shop', 'Double Late', 2.20)
This example assumes that the first and second columns are text
data types and the third is either a floating type or a currency type (notice
that the example escapes the apostrophe in the first value).
Note
It is rather important to understand
that although the SQL language is fairly standardized, not all data sources
implement it in the same way. To MS SQL Server, the INTO keyword for
an INSERT
statement is optional, for example; however, MS Access requires it.
It is most practical to never use data source-specific shortcuts or
leave out optional keywords unless you are sure that your script will always be
used with a particular data source.
UPDATE
Statement
Not only can you select and insert data into a table, but you can
also change the values in a given row of a table. The capability to update a row is tremendously
powerful and is performed using an UPDATE statement. The statement's syntax
is similar to that of the INSERT statement:
UPDATE tablename
SET columnname1=value1
[, columnname2=value2]
...
[WHERE condition]
where:
tablename is the name of a table in the database.
columnname is the name of the column to receive a value.
value is the value the column should be set to.
condition is a search condition as defined in the SELECT
statement section.
This statement can be used to modify the existing rows of data.
Example 7.16 uses an UPDATE statement to change the Department
column for all rows in the Users table. If a row's Department
column contains the value Personnel it is changed to the more politically
correct Human Resources.
Example 7.16 Using the UPDATE statement
UPDATE Users
SET Department = 'Human Resources'
WHERE Department = 'Personnel'
DELETE
Statement
To remove a row from a table is not difficult at all. This is done
with a DELETE
statement:
DELETE [FROM] tablename
[WHERE condition]
where:
tablename
is the name of a table in the database from which rows will be deleted.
condition is a search condition as defined in the SELECT
statement section.
The first FROM keyword is optional-some data sources may
require it. The tablename (following the optional first FROM
keyword) is the table that will be affected by the statement.
If search condition specified is TRUE for a row, the
row is deleted from tablename. In Example 7.17, all rows from the
table Students
will be deleted as long as the row has a value greater than 4
in the Year
column and the SchoolName column contains the value Michigan
State University.
If no search condition is specified, all
rows from tablename are deleted as in Example 7.18.
Example 7.17 Using
the DELETE
statement
DELETE FROM Students
WHERE Year > 4 AND
SchoolName = "Michigan State University"
Example 7.18 Deleting
all rows in a table
DELETE FROM Students
Escape Sequences
Each and every data source has its own way of dealing with specific
data types. The DATE data type format for Oracle 6 is "Aug 20,
1997", for example, but IBM's DB2 is "1997-08-20". This becomes quite a problem
when creating a SQL statement where you have no idea what the actual database
engine will be.
Because ODBC attempts to abstract the particulars of different
databases, the ODBC standard has adopted a technique to contend with this
problem. This technique is called an escape
sequence. When an ODBC driver finds an escape clause in a SQL statement,
it converts it to whatever format it needs to be to suit the particular
database the statement will be sent to.
Date and Times
The escape clause for a date is {d 'yyyy-mm-dd'}. So
to create an ODBC SQL statement that will be properly interpreted by all
databases, you could use the following:
SELECT * FROM Foo WHERE Date = {d '1997-08-20'}
The time/date-based escape sequences are as follows:
• Date: {d
'yyyy-mm-dd'}
• Time: {t
'hh:mm:ss'}
• Timestamp: {ts
'yyyy-mm-dd hh:mm:ss'}
Outer Joins
If you need to use an outer join, you can use the outer join escape
sequence:
{oj outer_join}
where the outer join
consists of:
tablename {LEFT | RIGHT | FULL} OUTER JOIN{tablename |outer_join} ON search_condition
In Example 7.19, the outer join specifies all the fields from every
record of the Machine table and every record from the Users
table in which the field MachineName (from the Users table)
matches Name (from the Machine table) as long as the field Processor
(from the Machine table) is greater than 487.
Example 7.19 An
outer join escape clause
SELECT *
FROM {oj Machine LEFT OUTER JOIN Users ON Machine.Name = Users.MachineName}
WHERE Machine.Processor > 486
Because this query uses an outer join as an escape sequence, you
can be guaranteed that it will work on any ODBC driver that supports outer
joins. Even if the particular driver uses a nonstandard syntax for outer joins,
the ODBC driver will convert the escape sequence into the correct syntax before
executing it.
Before you actually make use of an outer join, you may want to
check to make sure that your ODBC driver supports them. Example 7.20 shows a
simple line that will check for outer join support. If the variable $CanUseOuterJoins
is 1,
outer joins are supported.
Example 7.20 Discovering
whether an ODBC driver supports outer joins
# This assumes that $db is a valid Win32::ODBC object.
$CanUseOuterJoins = $db->GetInfo( $db->SQL_OJ_CAPABILITIES() ) & $db->SQL_OJ_FULL();
Scalar Functions
Scalar functions such as time and date, character, and numeric
functions can be implemented by means of escape sequences:
{fn function}
where function is a scalar function supported by the
ODBC driver. Example 7.21 compares the Date column (which is of a timestamp
data type) with the current date.
Example 7.21 A
SQL statement that uses a scalar function
SELECT *
FROM Foo
WHERE Date = {fn curdate()}
For a full list of scalar functions, refer to Appendix B. Before
using a scalar function, you should see whether it is supported by the ODBC
driver. You can use Example 7.22 to discover this. If the variable $IsSupported
is 1,
the curdate() scalar function
is supported. The value returned from GetInfo( $db->SQL_TIMEDATE_FUNCTIONS )
is a bitmask for all the supported time and date functions.
Example 7.22 Checking
whether the ODBC driver supports the curdate()
function
$IsSupported = $db->GetInfo($db->SQL_TIMEDATE_FUNCTIONS())& $db->SQL_FN_TD_CURDATE();
Stored Procedures
Stored procedures can be called by means of escape sequences. The
syntax is as follows:
{[?=]call procedurename[([parameter][,parameter]...)]}
If a return value is expected, you need to specify the preceding ?=, as in the following:
{? = call MyFunction('value')}
Otherwise, a call can be constructed like this:
{call MyFunction('value')}
Note
Win32::ODBC
does not support parameter binding, so using the '?' as a "passed in"
parameter to a stored procedure is not supported and will probably result in an
error.
The only exception to this is the
return value of a stored procedure. For example, if a SQL statement was
submitted like this:
{? = call MyStoredProc( 'value1', value2 ) }
The value returned by the procedure would be stored in a dataset
containing one row. Future versions of this extension will support parameter
binding.
LIKE Predicate Escape Character
Even though it is not common to do so (because you can always use
the default escape character), you can change the character used to escape a
wildcard by using the LIKE predicate escape character sequence:
{escape 'character')
where 'character' is any valid SQL
character.
Example 7.23 demonstrates this.
Example 7.23 Specifying
an escape character
SELECT *
FROM Foo
WHERE Discount LIKE '28#%' {escape '#'}
This example will return all columns from the table Foo
that have "28%" in the Discount column. Notice that by
specifying the LIKE predicate escape character sequence, the
pound character (#) is used to escape the wildcard. This way, the
wildcard is deemed a regular character and not interpreted as a wildcard.
Because the resulting condition will not include any wildcards, the condition
could have been this:
WHERE Discount = '28%'
This particular example demonstrates the use of the LIKE
predicate escape clause, however, so we are using the LIKE predicate.
Usually, this escape sequence is not necessary because the default
escape character is the backslash (\) and the same SQL statement could have
been this:
SELECT *
FROM Foo
WHERE Discount LIKE '28\%'
How to Use Win32::ODBC
The Win32::ODBC
extension attempts to make connecting to an ODBC data source as easy as
possible. In doing this, it hides many details from the user. This is either a
good or bad thing, depending on your point of view. Nonetheless, the extension
is designed to be as flexible as the ODBC API is without requiring the user to
learn the API itself. Of course, to utilize the more exotic ODBC features a
good book on ODBC is recommended; otherwise you will drive yourself insane with
all the constants, functions, and terminology!
When an application wants to connect to an ODBC data source, it
must create the following items, in order:
- An ODBC environment
- An ODBC connection to the data source
- A statement handle
Because all database interaction uses a statement handle, all three
steps must be performed, in order, before any database interaction can occur.
It is possible to have multiple statement handles per connection. This is how
there can be many queries simultaneously.
Win32::ODBC
attempts to simplify this process. When you create a new Win32::ODBC object, all interaction
with the data source goes through this object. If you need to connect to many
data sources at once, you just create many Win32::ODBC
objects. For those users who need to create multiple queries to one data source
(the equivalent of having multiple statement handles), the capability to clone an object has been
implemented. Cloned objects share the same ODBC connection but have separate
statement handles.
The use of Win32::ODBC
is very straightforward in most instances; there are basically five steps:
- Connecting to the database
- Submitting a query
- Processing the results
- Retrieving the data
- Closing the database
Because Win32::ODBC
is a Perl extension, it must be loaded by means of the use command. Typically this is placed in the beginning
of your script, as in Example 7.24.
Example 7.24 Loading
the ODBC extension
use Win32::ODBC;
Connecting to the Database
After your Perl application loads the Win32::ODBC extension, it needs to initiate a
conversation with a database. This is performed by creating an ODBC connection
object using the new command:
$db = new Win32::ODBC( $DSN [, $Option1, $Option2, ... ] );
The first parameter ($DSN) is the data source name. This
parameter can be either a DSN or a DSN string. This is described later in this
section.
The optional additional parameters are connection options that can
also be set by using the SetConnectOption()
method, which is discussed in the "Connection Options" section. Some options,
however, must be set before the actual connection is made to the ODBC driver and
database. Therefore, you can specify them in the new
command.
Example 7.25 assumes a few things: It assumes that you have already
created a DSN called "My DSN", and that it is configured correctly.
The example also assumes that the current user has permissions to access the
DSN. This is not so much a problem for Windows 95 users as it is for Windows NT
users.
Example 7.25 Connecting
to DSN
$db = new Win32::ODBC( "MyDSN" );
If all went well, you will have an object, $db, which you
will use later. Otherwise something went wrong, and the attempt to connect to
the database failed. If something indeed did go wrong, the object will be
empty. A simple test, as in Example 7.26, can be used to determine success or
failure in connecting to the database. If the connection fails, the script will
die printing out the error. Error processing is discussed later in the chapter
in the section titled, strangely enough, "Error Processing."
Example 7.26 Testing
if a connection to a database succeeded
if(! $db)
{
die "Error connecting: " . Win32::ODBC::Error() . "\n";
}
You can override your DSN configuration by specifying particular
configuration elements in the DSN name when creating a new ODBC connection
object. To do this, your DSN name must consist of driver-specific keywords and
their new values in the following form:
keyword=value;
You can specify as many keywords as you like and in any order you
like, but the "DSN" keyword must be included (and should be the first one).
This keyword indicates which DSN you are using and the other keyword/value
pairs that will override the DSN's configuration.
Table 7.2 provides a list of standard keywords. A data source may
allow additional keywords (for example, MS Access defines the keyword DBQ
to represent the path to the database file) to be used.
Table 7.2 Standard DSN keywords
Keyword
Description
| DSN
|
The value would be the name of an existing DSN. |
| FILEDSN
|
The value would be the name of a file-based DSN.
Such files end with the .DSN extension. This is only available with ODBC 3.0 or higher. |
| DRIVER |
A description of the ODBC driver that is to be
used. This value can be obtained by using the Win32::ODBC::Drivers()
function. |
| UID |
The value represents a user ID (or user name) that
will be sent to the data source. |
| PWD |
The value represents a password that will be sent
to the data source. |
| SAVEFILE
|
The value is a name of a file-based DSN that the
DSN string will be saved as. This is only available with ODBC 3.0 or higher. |
Note
If ODBC 3.0 or higher is being used,
there are two keywords that cannot be used together. They are DSN
and FILEDSN.
If they both appear in a connection string, only the first one is used.
Additionally ODBC 3.0 allows for a DSNless connection string in
which no DSN or FILEDSN keyword is used. A DRIVER
keyword must be defined, however, in addition to any other keywords necessary
to complete the connection.
Suppose a DSN exists called "OZ" that points to an
Access database. If you want to use that DSN but specify a userid to log on to
the database (in Access, a keyword of UID) and password (keyword of PWD),
you could use the line:
$db = new Win32::ODBC( "DSN=OZ;UID=dorothy;PWD=noplacelikehome" );
A connection would be made to the "OZ" DSN, overriding
the default userid and password would be difficult.
Other than the practical limitations of memory, you have no real
limit as to how many database connections you can have. Although some tricks
can help speed things up, you should conserve on memory use and make the most
of an ODBC connection (but more on that later).
After you have your ODBC object(s), you are ready to begin querying
your database.
Tip
The Win32::ODBC
extension's functions also map to another namespace called ODBC. It is not necessary to
always use the full Win32::ODBC
namespace when calling functions or constants. For example, the following two
function calls are the same:
@Error = Win32::ODBC::Error();
@Error = ODBC::Error();
Likewise, when you create a new ODBC
object, you can use either of the following:
$db = new Win32::ODBC( "MyDSN" );
$db = new ODBC( "MyDSN" );
Anywhere that you may need to use the full namespace of the
extension, you instead use the abbreviated version: ODBC.
Submitting the Query
Now that you have an ODBC connection object, you can begin to
interact with your database. You need to submit a SQL query of some sort. This
is where you use the Sql()
method:
$db->Sql( $SqlStatement );
The first and only parameter is a SQL statement. This can be any
valid SQL statement.
The Sql() method
will return a nonzero integer corresponding to a SQL error number if it is
unsuccessful.
Note
It is very important to note that the Sql() method is the only
method that returns a non-zero integer upon failure.
The reason for this is to keep backward
compatible with the original version of Win32::ODBC,
then called NT::ODBC,
which was written by Dan DeMaggio.
Originally, NT::ODBC
used the return value of the sql()
method (then the method was all lowercase) to indicate the error number. This
technique of error checking has been made obsolete with the introduction of the
Error()
method. For the
sake of backward compatibility, however, the return values have not changed.
Suppose you have a database called OZ with a table called Characters.
The table consists of the fields (also known as columns) in Table 7.3.
Table 7.3 The table called Characters in a fictitious
database called OZ
Field Name Data Type
| Name |
char(20) |
| Origin |
char(20) |
| Goal | char(30) |
Suppose also that you want to query the database and find out who
is in OZ.
Example 7.27 will connect to the database and submit the query.
Example 7.27 Submitting
the SQL query
01. use Win32::ODBC;
02. if( ! $db = new Win32::ODBC( "OZ" ) )
03. {
04. die "Error connecting: " . Win32::ODBC::Error() . "\n";
05. }
06. if( $db->Sql( "SELECT * FROM Characters" ) )
07. {
08. print "Error submitting SQL statement: " . $db->Error() . "\n";
09. }
10. else
11. {
12. ... process data ...
13. }
The SQL statement "SELECT * FROM Characters" in line 6 will,
if successful, return a dataset containing all fields from all rows of the database.
By passing this statement into the Sql()
method, you are requesting the database to perform this query.
If the Sql() method
returns a nonzero result, there was an error and the error is printed. If the
query was successful (a return value of zero), you will need to move onward and
process the results.
Processing the Results
After you have a dataset that has been prepared by a SQL statement,
you are ready to process the data. The way this is achieved is by moving, row
by row, through the dataset and extracting the data from columns in each row.
The first thing you must do is tell the ODBC connection that you
want to move to the next available row. Because you just performed the query,
you are not even looking at a row yet; therefore, you need to move to the next
available row, which in this case will be the first row. The method used to
move from row to row is FetchRow():
( $Reult, @RowResults ) = $db->FetchRow( [$Row[, $Mode]] );
Before explaining the optional parameters, it is important to
understand that the parameters are usually not used. They refer to the extended
capabilities of the ODBC function SQLExtendedFetch().
This is explained later in the section titled "Advanced Features of Win32::ODBC."
The first optional parameter ($Row) is the row
number that you want to move to. For more details refer to the aforementioned
section on advanced Win32::ODBC features.
The second optional parameter ($Mode) is the mode in
which the cursor will be moved. If this parameter is not specified, SQL_FETCH_RELATIVE
mode is assumed. For more details, refer to the aforementioned section on
advanced Win32::ODBC features.
The FetchRow()
method will return a one value if it successfully moved to the next row. If it
returns a zero, it usually means that no more data is left (that is, you have
reached the end of your dataset) or that an error has occurred.
Another value is returned, but is typically not of any use unless
you use the advanced features of FetchRow().
For more information, refer to the section titled "Advanced Row Fetching" later
in the chapter.
Example 7.28 shows a typical usage of the FetchRow() method. The loop continues to execute as long
as you can advance to the next row. After the last row has been obtained, $db->FetchRow() returns a
FALSE
value that causes the loop to terminate.
Example 7.28 Fetching
rows
while( $db->FetchRow() )
{
...process data...
}
Note
As of version 970208, the FetchRow() makes use of the SQLExtendedFetch() ODBC
function. Unfortunately not all ODBC drivers support this and therefore fail
when FetchRow() is
called.
If this happens, you can either use
another ODBC driver, such as a newer version or from another vendor, or you can
use an older version of Win32::ODBC.
Versions of the extension before 970208 use the regular SQLFetch() ODBC function.
Future releases of Win32::ODBC
will support both fetch methods and use whichever the ODBC driver supports.
Retrieving the Data
After a row has been fetched, the data will need to be retrieved
from it. There are two methods for doing this: the Data() method and the
DataHash()
method.
The Data() method
returns an array of values:
@Data = $db->Data( [$ColumnName1[, $ColumnName2 ... ]] );
A list of parameters can be passed in. Each parameter is a column
name of which you are retrieving data.
The Data() method
returns an array of values corresponding, in order, to the column names that
were passed into the method. If nothing is passed into the method, all column
values are returned in the order that they appear in the row. Example 7.29
shows how the Data() method can
be used.
Example 7.29 Using
the Data() method
if( $db->FetchRow() )
{
@Data = $db->Data( "LastName", "FirstName" );
print "First name is $Data[1]\n";
print "Last name is $Data[0]\n";
}
Notice how the first element in the @Data array ($Data[0])
is the value of the first column name specified. The order of the column names
passed in determines the order they are stored in the array.
The second and more practical way to retrieve data is to use the DataHash() method. The
DataHash() method is the preferred
method when retrieving data because it associates the column name with the
column's data. The DataHash()
method returns either an undef if the method failed or a hash with column
names as keys and the hash's values consisting of the column's data:
%Data = $db->DataHash( [$ColumnName1[, $ColumnName2 ... ]] );
A list of parameters can be passed in. Each parameter is a column
name of which you are retrieving data. If no parameters are passed in, the data
for all columns will be retrieved.
If you were to query the table previously described in Table 7.3
using the code in Example 7.30, you may get output that looks like this:
1) Dorothy is from Kansas and wants to go home.
2) The Scarecrow is from Oz and wants a brain.
3) The Wicked Witch is from The West and wants the ruby slippers.
This output would continue until all rows have been processed.
Example 7.30 Extracting
data using the DataHash() method
01. use Win32::ODBC;
02. if( ! $db = new Win32::ODBC( "OZ" ) )
03. {
04. die "Error connecting: " . Win32::ODBC::Error() . "\n";
05. }
06. if( ! $db->Sql( "SELECT * FROM Characters" ) )
07. {
08. while( $db->FetchRow() )
09. {
10.
11. my(%Data) = $db->DataHash();
12. $iRow++;
13. print "$iRow) $Data{Name} is from $Data{Origin} and ",
"wants $Data{Goal}.\n";
14. }
15. }
16. $db->Close();
Tip
When using the DataHash()
method, it is best that you undef the hash used to hold the data
before the method call. Because the DataHash()
method is typically called from within a while
loop that fetches a row and retrieves data, it is possible that the hash may
retain values from a previous call to DataHash().
Closing the Connection
Up to now, you have opened the database, submitted a query,
retrieved and processed the results. Now you need to finish by closing the
database. The proper way to perform this is by calling the Close() method. This method tells the
ODBC Manager to properly shut down the ODBC connection. The ODBC Manager will,
in turn, tell the driver to clean up after itself (removing temporary files,
closing network connections, flushing buffers, and so on) and the ODBC
connection object will be destroyed so that it cannot be used any longer. The
syntax for the Close() method
is this:
$db->Close();
The Close() method
has no return value.
Example 7.30 in the preceding section shows a full working example
of how you may use the Win32::ODBC
module, including the Close()
method.
The Win32::ODBC API
Win32::ODBC
supports a rich set of features, most of which are never used (yet still
exist). Some of these require knowledge of ODBC that is beyond the scope of
this book. You can find several books that do discuss this topic.
Constants
For almost every method and function in Win32::ODBC, a set of constants are needed. These
constants represent a numeric value that may change as the ODBC API changes
over time, so it is important that you use the constant names and not the
values they represent.
One of the most questioned aspects of the Win32::ODBC extension is how to make use of the
constants. Only a small group of constants are actually exported from the ODBC
module, so to use most of the constants you need to specify a namespace
function such as:
Win32::ODBC::SQL_COLUMN_TABLE_NAME()
Or, if you have an ODBC connection object, you can access the
constant value as if it were a member of the object, as in:
$db->SQL_DATA_SOURCE_NAME()
Because Win32::ODBC
creates a synonymous ODBC
namespace and maps it to Win32::ODBC,
you could use:
ODBC::SQL_CURSOR_COMMIT_BEHAVIOR()
Notice that the preceding example just uses the ODBC namespace rather than the Win32::ODBC namespace. Both are
valid, but the latter is a bit shorter.
The reason why all constants are not exported into the main
namespace is because the ODBC
API defines more than 650 constants, each of which is important to have. The
decision was made to not export all the constants because it would bloat memory
use and clutter the main namespace with an entire list of constants that will
most likely not be used.
You could always edit the ODBC.pm file and export constants that
you would like to export; but then again, it is not so difficult to just make
use of one of the formats listed earlier.
Catalog Functions
The ODBC API
supports metadata functions such as cataloging. Win32::ODBC
supports access to such information with two methods:
$db->Catalog( $Qualifier, $Owner, $Name, $Type );
$db->TableList( $Qualifier, $Owner, $Name, $Type );
Both of these are really the same method, so they can be used
interchangeably. The only difference is in how the results are obtained.
The first parameter ($Qualifier) represents the source the
database will use. In Access, for example, the $Qualifier would be
the database file (such as c:\data\mydatabase.mdb); whereas in MS
SQL Server, it would be the database name.
The second parameter ($Owner) is the owner of the table. Some
database engines can put security on tables either granting or denying access
to particular users. This value would indicate a particular owner of a
table-that is to say, the user who either created the table or to whom the
table has been given.
The third parameter ($Name) is the name of the table.
The fourth parameter ($Type) is the table type. This can be any
number of database-specific types or one of the following values:
- TABLE
- VIEW
- SYSTEM TABLE
- GLOBAL TEMPORARY
- LOCAL TEMPORARY
- ALIAS
- SYNONYM
A call to the Catalog()
or TableList() method can
include search wildcards for any of the parameters. Passing in "USER%"
for the third parameter will result in retrieving all the tables with names
that begin with USER.
The difference between these two methods is that the Catalog() method returns a result set
that you need to process with FetchRow()
and Data() or DataHash(). On the other hand, TableList() returns an array of table
names that meet the criteria you specified.
Basically, TableList()
is a quick way of getting a list of tables, and Catalog()
is a way of getting much more information.
If the Catalog()
method is successful, it returns a TRUE and results in a dataset with five
columns: TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, TABLE_TYPE,
and REMARKS. There may also be additional columns that are specific to the
data source. Each row will represent a different table. You can use the normal FetchRow() and
DataHash() methods to retrieve this
data. If the method fails, a FALSE is returned.
Note
The resulting dataset generated by a
call to the Catalog()
method will result in a different result set if ODBC 3.0 or higher is used. In
this case, TABLE_QUALIFIER becomes TABLE_CAT and TABLE_OWNER
becomes TABLE_SCHEM.
This is due to a change in the ODBC specification for version 3.0.
If the TableList()
method is successful, it will return an array of table names.
If all parameters are empty strings except the fourth parameter
(table type), which is only a percent sign (%), the resulting dataset will
contain all valid table types. You can use this when you need to discover which
table types a data source can use.
If the $Owner parameter is a single percent sign and
the qualifier and name parameters are empty strings, the resulting dataset will
contain the list of valid owners that the data source recognizes.
If the $Qualifier is only a percent sign and the $Owner
and $Name
parameters are empty strings, the resulting dataset will contain a list
of valid qualifiers. Example 7.31 describes how you can use both of these
methods.
Example 7.31 Using
the TableList() and Catalog() methods
01. use Win32::ODBC;
02. $db = new ODBC("MyDSN" ) || die "Error connecting: " . ODBC::Error();
03. $TableType = "'TABLE','VIEW','SYSTEM TABLE'," .
"'GLOBAL TEMPORARY','LOCAL TEMPORARY'," . "'ALIAS','SYNONYM'";
04. @Tables = $db->TableList();
05. print "List of tables:\n";
06. map {$iCount++; print "$iCount) $_\n";} @Tables;
07.
08. if( $db->Catalog( "", "", "%", $TableType ) )
09. {
10. while( $db->FetchRow() )
11. {
12. my( %Data);
13. %Data = $db->DataHash();
14. print "$Data{TABLE_NAME}\t$Data{TABLE_TYPE}\n";
15. }
16. }
17. $db->Close();
18.
Managing Columns
Each column (or field-whichever way you prefer to say it) can be of
a different data type. One column may be a text data type (for a user's name,
for example) and another may be a numeric data type representing a user's age.
At times, a programmer may need to learn about a column-things such
as what data type the column is, whether he can conduct a search on that
column, or whether the column is read-only. If the programmer created the
database table, chances are that he already knows this information; if the
table came second-hand, the programmer may not know such information. This is
where the ColAttributes()
method comes into play:
$db->ColAttributes( $Attribute, [ @ColumnNames ]);
The first parameter ($Attribute) is the attribute, a numeric
value representing attributes of a column. Appendix B contains a list of valid
constants that can be used.
Additional parameters may be included-specifically, the names of
columns you want to query. If no column names are specified, all column names
will be queried.
The output of the ColAttributes()
method is a hash consisting of column names as keys and the attribute for the
column as the key's value.
The code in Example 7.32 will print out all the data types in a
table called "Foo" from the DSN called "MyDSN". In line 14,
the column's data types are retrieved with a call to the ColAttributes() method. The resulting
hash is passed into a subroutine, DumpAttribs(),
which prints out each column's data type.
Example 7.32 Printing
out a table's column data types
01. use Win32::ODBC;
02.
03. $DSN = "MyDSN";
04. $Table = "Foo";
05. $db = new ODBC($DSN) || die "Error connecting: " . ODBC::Error();
06.
07. if( ! $db->Sql("SELECT * FROM $Table") )
08. {
09. if( $db->FetchRow() )
10. {
11. @Fields = $db->FieldNames();
12. foreach $Field (@Fields)
13. {
14. %Attrib = $db->ColAttributes( $db->SQL_COLUMN_TYPE_NAME(), $Field);
15. DumpAttribs( %Attrib );
16. }
17. }
18. else
19. {
20. print "Fetch error: " . $db->Error();
21. }
22. }
23. else
24. {
25. print "SQL Error: " . $db->Error();
26. }
27.
28. sub DumpAttribs
29. {
30. my( %Attributes ) = @_;
31. my( $ColumnName );
32. foreach $ColumnName (sort (keys ( %Attributes ) ) )
33. {
34. print "\t$ColumnName = $Attributes{$ColumnName}\n";
35. }
36. }
Data Source Names
Most administrators will create and manage a Data Source Name (DSN)
by using the nifty GUI interface such as the ODBC Administrator program or the
Control Panel ODBC applet. Both of these (actually they are the same
application) do a tremendous job at managing DSNs. Be aware, however, that at
times you may need to programmatically manage DSNs. An administrator may want
to write a Web-based CGI script enabling the management of DSNs, for example. Win32::ODBC uses the
ConfigDSN() function to do just this:
Win32::ODBC::ConfigDSN( $Action, $Driver, $Attribute1 [, $Attribute2, ...] );
The first parameter ($Action) is the action specifier. The
value of this parameter will determine what action will be taken. The valid
actions and their values are as follows:
ODBC_ADD_DSN. (0x01) Adds a new DSN.ODBC_MODIFY_DSN. (0x02) Modifies an existing DSN.ODBC_REMOVE_DSN. (0x03) Removes an existing DSN.ODBC_ADD_SYS_DSN. (0x04) Adds a new System DSN.ODBC_MODIFY_SYS_DSN. (0x05) Modifies an existing System
DSN.ODBC_REMOVE_SYS_DSN. (0x06) Removes an existing System
DSN.
In some versions of Win32::ODBC,
the system DSN constants are not exported so their values can be used instead.
The second parameter ($Driver) is the ODBC driver name which
will be used. The driver must be one of the ODBC drivers that are installed on
the computer. You can retrieve a list of available drivers by using either the DataSources() or the
Drivers() function.
The remaining parameters are the list of attributes. These may
differ from one ODBC driver to the next, and it is up to the programmer to know
which attributes must be used for a particular DSN. Each attribute is
constructed in the following format:
"AttributeName=AttributeValue"
These examples were taken from a DSN using the Microsoft Access
ODBC driver:
"DSN=MyDSN"
"UID=Cow"
"PWD=Moo"
"Description=My little bitty Data Source"
The "DSN" attribute is one that all ODBC drivers share. This
attribute must be in the list of attributes you provide; otherwise, ODBC will
not know what to call your DSN or, in the case of modifying and removing, which
DSN you alter. It is wise to always include the "DSN" attribute as the
first attribute in your list.
When you are adding or removing, you need only to specify the "DSN"
attribute; others are not necessary. In the case of adding, any other attribute
can be added later by modifying the DSN.
Modifying DSNs
When you are modifying, you must include the "DSN"
attribute so that ODBC will know which DSN you are modifying. Any additional
attributes can either be added to the DSN or replace any attributes that
already exist with the same name.
Some ODBC drivers require you to specify additional attributes (in
addition to the "DSN" attribute) when using ConfigDSN(). When adding a new DSN
that uses the Microsoft Access driver, for example, you must include the
following database qualifier attribute:
"DBQ=C:\\SomeDir\\MyDatabase.mdb"
In Example 7.33, the ConfigDSN() function is used three
times. The first time (line 10), ConfigDSN()
creates a new DSN. The second time (line 18) ConfigDSN() modifies the new DSN by adding the password
(PWD)
attribute and changing the user (UID) attribute. The third call to the ConfigDSN() function (line 22)
removes the DSN that was just created. This code is obviously not very useful
because it creates and then removes a DSN, but it shows how to use the ConfigDSN() function.
Example 7.33 Adding
and modifying a DSN
01. use Win32::ODBC;
02.
03. $DSN = "My DSN Name";
04. $User = "administrator";
05. $Password = "adminpassword";
06. $Dir = "C:\\Database";
07. $DBase = "mydata.mdb";
08. $Driver = "Microsoft Access Driver (*.mdb)";
09.
10. if( Win32::ODBC::ConfigDSN( ODBC_ADD_DSN,
11. $Driver,
12. "DSN=$DSN",
13. "Description=A Test DSN",
14. "DBQ=$Dir\\$DBase",
15. "DEFAULTDIR=$Dir",
16. "UID=" ) )
17. {
18. Win32::ODBC::ConfigDSN( ODBC_MODIFY_DSN,
19. $Driver,
20. "UID=$User",
21. "PWD=$Password");
22. Win32::ODBC::ConfigDSN( ODBC_REMOVE_DSN,
23. $Driver,
24. "DSN=$DSN" );
25. }
Line 8 assigns the $Driver variable with the name of the
ODBC driver to be used. This value should come from a value obtained with a
call to Win32::ODBC::Drivers().
The reason for this is because this value could change based on localization. A
German version of ODBC, for example, would require line 8 to be:
$Driver = "Microsoft Access Treiber (*.mdb)";
The value returned by the Drivers()
function is obtained from the ODBC driver directly, so the value will be
correct for the locale.
The ConfigDSN()
function returns a TRUE if it is successful; otherwise, it returns
a FALSE.
Tip
If you do not know what attributes to
use in a call to ConfigDSN(),
you can always cheat! You just use the ODBC administrator program or the
Control Panel's ODBC applet and create a temporary DSN.
After you have completed this, you need
to run the Registry Editor (regedit.exe or regedt32.exe).
If you created a system DSN, open this key:
HKEY_LOCAL_MACHINE\Software\ODBC\Your_DSN_Name
If you created a user DSN, open this
key:
HKEY_CURRENT_USER\Software\ODBC\Your_DSN_Name
The value names under these keys are the attributes that you
specify in the ConfigDSN()
function.
After a DSN has been created, you may want to review how it is
configured. This is done by using GetDSN():
Win32::ODBC::GetDSN( $DSN ); $db->GetDSN();
GetDSN() is
implemented as both a function and a method. When called as a function, you
must pass in a parameter that is the name of a DSN whose configuration will be
retrieved.
When used as a method, nothing is passed in to GetDSN(). The DSN that the ODBC
connection object represents will be retrieved.
A hash is returned consisting of keys that are the DSN's attribute
keyword. Each key's associated value is the DSN's attribute value. These
key/value pairs are the same as those used in the ConfigDSN() function.
It is possible to retrieve a list of available DSNs by using the DataSources() function:
Win32::ODBC::DataSources();
DataSources()
returns a hash consisting of data source names as keys and ODBC drivers as
values. The ODBC drivers represented in the hash's values are in a descriptive
format that is used as the second parameter in a call to ConfigDSN().
Example 7.34 illustrates how to retrieve the list of available DSNs
and how to use ConfigDSN() to
remove these DSNs.
Example 7.34 Removing
all DSNs
01. use Win32::ODBC;
02.
03. if( %DSNList = Win32::ODBC::DataSources() )
04. {
05. foreach $Name ( keys( %DSNList ) )
06. {
07. print "$Name = '$DSNList{$Name}'\n";
08. if( ! Win32::ODBC::ConfigDSN( ODBC_REMOVE_DSN,
$DSNList{$Name},
"DSN=$Name" ) )
09. {
10. # If we were unable to remove the
11. # DSN maybe it is a system DSN...
12. Win32::ODBC::ConfigDSN( ODBC_REMOVE_SYS_DSN,
$DSNList{$Name},
"DSN=$Name" );
13. }
14. }
15. }
Notice how Example 7.34 uses the names and drivers that make up the
hash returned by the DataSources()
function. These values are used as the DSN name and driver in the calls to ConfigDSN().
Returning Available ODBC Drivers
Drivers() is yet
another DSN-related function:
Win32::ODBC::Drivers();
This function returns a hash consisting of available ODBC drivers
and any attributes related to the driver. Note that these attributes are not
necessarily the same as the ones you provide in ConfigDSN().
The returned hash consists of keys that represent the ODBC driver
name (in descriptive format), and the key's associated value contains a list of
ODBC driver attributes separated by semicolons (;) such as this:
"Attribute1=Value1;Attribute2=Value2;..."
These attributes are really not that useful for the common
programmer, but may be of use if you are programming ODBC drivers or if you
need to make sure that a particular driver is configured correctly.
Note
The attributes returned by the Drivers()
function (which are the ODBC driver's configuration attributes) are not the
same type of attributes used in the ConfigDSN()
attributes (which are an ODBC driver's DSN attributes).
Case Study: Changing the Database Paths
for All MS Access DSNs
Jane's boss came running into her office and told her that the
junior administrator did something really devastating to one of her Web
servers. Somehow, he has managed to corrupt the D: drive, the one
where all the Access database files were kept.
Jane realized that she could not take the server down to reinstall
a drive until the weekend. She also began kicking herself for not having
already installed the RAID subsystem.
To correct the problem, she had someone restore the database files
from a tape backup on to the server's E: drive. She figured that all she had to
do was change the ODBC DSNs to point to their respective databases on the E:
drive rather than the D: drive. No problem…until she realized that
there were hundreds of DSNs!
So, Jane sat down and wrote the Perl script in Example 7.35.
Example 7.35 Changing
the database paths for all MS Access DSNs
01. use Win32::ODBC;
02. $OldDrive = "d:";
03. $NewDrive = "e:";
04. # We are looking for Access databases
05. $Driver = GetDriver( ".mdb" ) || Error( "finding the ODBC driver" );
06.
07. %DSNList = Win32::ODBC::DataSources() ||
Error( "retrieving list of DSNs" );
08. foreach $DSN ( keys( %DSNList ) )
09. {
10. next if( $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<br>
SQL_NULLABLE<br>
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:
<br>
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
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
Submit an article or tip
