ODBC from Perl

So we've established that the ODBC standard is a rather good thing, but how can you use it?

To use ODBC from Perl, there are only two practical options: the Win32::ODBC module and the DBI with the DBD::ODBC module. We'll describe DBD::ODBC first and then take a deeper look at Win32::ODBC.

DBD::ODBC

The DBD::ODBC module was written by Tim Bunce and Jeff Urlwin, based on original code by Thomas K. Wenrich. It's a Perl extension written in C and is not tied to Microsoft Win32 platforms. That makes it a good option for directly using ODBC on Unix, VMS, and other non-Windows systems.

Being a DBI driver, the main goal of the DBD::ODBC module is to implement the functionality required by the DBI, not simply to give access to ODBC from Perl.

The DBD::ODBC driver is described in more detail in Appendix B, "Driver and Database Characteristics ".

Win32::ODBC

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:

• There is no separate statement handle
• 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.
• There are no separate prepare and execute steps
• You cannot prepare a statement for execution later. The Sql() method, like the DBI do() method, combines both.
• Placeholders and bind parameters are not supported
• 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.

• Fetching rows is a two-step process
• 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.

• There is no automatic error handling
• 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.

• Win32::ODBC is slightly slower than DBD::ODBC
• 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:

• Most of the ODBC API is available to use
• 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.

• Attributes, options, and metadata are available
• 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 are supported
• 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.