ODBC from Perl (Programming the Perl DBI)

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

7.4.2. 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.

7.4. ODBC from Perl

7.4.1. DBD::ODBC

7.4.2. Win32::ODBC