Fun with Perl and MySQL DBD::ODBC this week …

The MySQL ODBC Connectors (client drivers) convert ODBC requests to native MySQL protocol commands.

Notes on MySQL ODBC Connectors for Windows:

• Windows has an ODBC Manager Control Panel applet under the System group
• the most common Windows MySQL ODBC Connectors available are versions 3.51 and 5.1.x (good for MySQL 4.1+ and up as it requires information_schema feature)
• install the appropriate 32-bit or 64-bit driver depending on your version of Windows, or it will be silently installed in the wrong directory and not appear in the the ODBC Manager.
• Both ActiveState ActivePerl and Strawberry Perl releases for Windows come pre-built with DBI and DBD::ODBC Perl modules, so installation is trivial and no C compiler is needed.

Notes on MySQL ODBC Connectors for Mac OS X:

• OS X includes the ODBC Administrator program, which can be opened by searching for its name in the Finder
• MySQL ODBC Connector 5.1 for Mac OS X 10.6 (Snow Leopard) is available, and version 3.51 is for OS X 10.5 (Leopard)
• iOS ODBC support is built from the iODBC project, including Mac OS X, iPhoneSimulator, Developer kits, etc.
• The DBD::ODBC module can be installed with this command: sudo cpan DBD::ODBC

Notes on MySQL ODBC Connectors for UNIX/Linux:

• a driver manager is needed, such as iODBC (BSD/LGPL 2-licensed) or unixODBC (LGPL/GPL) to connect to an ODBC source (Mac OS X comes with iODBC pre-installed)

If you have problems installing or using the MySQL ODBC Connectors, there is a FAQ.

Here is a Perl program I wrote using Perl, DBI and DBD::ODBC to list the available databases and tables in a MySQL DSN:

#!/usr/bin/perl

# Program: test_schema.pl
# Purpose: Sample Perl program to list MySQL databases and tables using ODBC
# Env: Perl5 Windows or Mac OS X with DBD::ODBC and MySQL ODBC Connector installed
# Author: James Briggs
# Date: 2010 11 12
# Note: also could query information_schema in MySQL Server 4.1+

use strict;
use warnings;

use DBI;

  my $user = 'root';
  my $pw = 'mypw';
  my $dsn = 'mydsn';

  my $dbh = DBI->connect("dbi:ODBC:$dsn", $user, $pw,
       {PrintError => 1, RaiseError => 1});
  if (!$dbh) {
     print "error: connection: $DBI::err\n$DBI::errstr\n$DBI::state\n";
  }

  my $sql_d = 'show databases';
  my $sth_d = $dbh->prepare($sql_d);
  my $r_d   = $sth_d->execute;

  while (my ($db) = $sth_d->fetchrow_array) {
     print $db . "\n===\n";

     my $sql_t = 'show tables in `' . $db . '`';
     my $sth_t = $dbh->prepare($sql_t);
     my $r_t = $sth_t->execute;

     while (my ($table) = $sth_t->fetchrow_array) {
        print $table . "\n";
     }

     print "\n";
  }

  $dbh->disconnect if $dbh;

Siva Satish’s UnixODBC and MySQL Sample Program