The Win32::ODBC module was written by Dave Roth,
based on original code by Dan DeMaggio. It’s a Perl extension
written in C++ and is closely associated with the Win32 platform.
The main goal of the Win32::ODBC module is to
provide direct access to the ODBC functions. From that point of view,
Win32::ODBC provides a fairly thin, low-level
interface.
Here’s a sample of Win32::ODBC code:
use Win32::ODBC;
### Connect to a data source
$db = new Win32::ODBC("DSN=MyDataDSN;UID=me;PWD=secret")
or die Win32::ODBC::Error();
### Prepare and Execute a statement
if ($db->Sql("SELECT item, price FROM table")) {
print "SQL Error: " . $db->Error() . "\n";
$db->Close();
exit;
}
### Fetch row from data source
while ($db->FetchRow) {
my ($item, $price) = $db->Data(); ### Get data values from the row
print "Item $item = $price\n";
}
### Disconnect
$db->Close();The most significant disadvantages of
Win32::ODBC
compared to DBD::ODBC are:
The database connection handle is used to store the details of the current statement. There is no separate statement handle, so only one statement can execute per database handle. But that’s not as bad as it may seem, because it’s possible to clone database handles so that more than one handle can share the same underlying ODBC database connection.
You cannot prepare a statement for execution later. The
Sql()
method, like the DBI do() method, combines both.
This is perhaps the most significant disadvantage of
Win32::ODBC. All values must be passed as literal
text values within the SQL statements.
The lack of support for placeholders, especially when coupled with
the inability to prepare statements, means that nontrivial
applications based on Win32::ODBC tend to place a
greater burden on database servers and thus run more slowly.
It also causes problems when trying to insert binary data such as images.
The
FetchRow()
method doesn’t actually return any data to the script. To get
the row of data values, you need to call either the
Data()
method to get a simple list (like
fetchrow_array()
),
or the
DataHash()
method to get a hash (like
fetchrow_hashref()
).
This is more of a nuisance than a significant disadvantage.
It’s also another reason why Win32::ODBC is
a little slower than using DBI.
In ODBC, there is no equivalent to the DBI’s
RaiseError and PrintError
mechanism. You need to explicitly test the return status of all
Win32::ODBC method calls if you want to write a
robust application.
The lack of automatic error handling makes
Win32::ODBC less suitable for nontrivial
applications when application reliability is important. This is
especially true where transactions are being used.
Even for simple queries, Win32::ODBC tends to be
slightly slower than DBD::ODBC for the same
platform and database. As always with benchmarks, your mileage may
vary, so test it yourself if this is an issue for you.
There are plans to address some of these disadvantages in a later
release. The most significant advantages of
Win32::ODBC compared to
DBD::ODBC are:
This is
currently the biggest advantage that Win32::ODBC
has over DBD::ODBC.
The remaining items in this list are really significant ODBC features
rather than features of the Win32::ODBC module
itself, but until DBD::ODBC supports them, they
still count as advantages of Win32::ODBC.
These are described in the previous section. A wide range of metadata functions is available, along with functions for controlling many attributes and options.
Scrolling cursors let you read the rows of data returned by a query
in any order. You can jump to the last row and read backwards. You
can jump to any row either by absolute row number or relative to the
current row. That’s very handy for interactive browsing
applications.