Sample Perl MySQL ODBC Test Program

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:

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

This entry was posted in MySQL, Open Source, Perl, Tech. Bookmark the permalink.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>