Win32::ODBC

Microsoft Access Northwind database

Microsoft Access Northwind database

Administrative Tools Control Panel

Administrative Tools Control Panel

ODBC Data Source Administrator

ODBC Data Source Administrator

Create New Data Source

Create New Data Source

Select Database

Select Database

ODBC Microsoft Access Setup

ODBC Microsft Access Setup

ODBC Data Source Administrator after adding northwind user DSN

ODBC Data Source Administrator after adding northwind user DSN

#   DBI168.pl
#   08-Oct-2002
#
#   Conrad Halling
#   conrad.halling@sphaerula.com
#
#   This is a modification of the sample code from Programming the Perl DBI by
#   Alligator Descartes and Tim Bunce, p. 168. See http://perl.dbi.org/ for
#   more information.
#
#   The database used is the Microsoft Access Northwind.mdb database that
#   comes as a sample database with Microsoft Office 97, 2000, or XP.
#
#   This code uses the Win32::ODBC module that is included as part of the
#   ActiveState Perl distribution. ODBC must be set up through the control
#   panel so that it will recognize the Northwind.mdb database.
#
#   o   Under Windows XP, select Start->Settings->Control Panel.
#   o   In the Control Panel window, double-click "Administrative Tools".
#   o   In the Administrative Tools window, double-click
#       "Data Sources (ODBC)".
#   o   In the ODBC Data Source Administrator window, click on the User DSN
#       tab to bring it to the front.
#   o   Click on the Add... button.
#   o   In the Create New Data Source window, select the
#       "Microsoft Access Driver (*.mdb)" driver and click on the Finish
#       button.
#   o   In the ODBC Microsoft Access Setup window, enter "northwind" into the
#       "Data Source Name:" text entry box. ("northwind" is the data source
#       name you will use to access the database through the Win32::ODBC
#       module.) The Description field is optional, and provides a reminder
#       to you about what the database is. In the Database section of the
#       window, click on the "Select..." button.
#   o   In the Select Database window, move to the directory
#       C:\Program Files\Microsoft Office\Office10\Samples (or the equivalent
#       on your system), select "Northwind.mdb" on the left, and click the OK
#       button.
#   o   In the ODBC Microsoft Access Setup window, click the OK button.
#   o   In the ODBC Data Source Administrator window, northwind should now
#       appear in the list of data sources. Click on the OK button.
#
#   The default user ID (UID) for a Microsoft Access database is "Admin".
#   The default password (PWD) for a Microsoft Access database is "" (a blank
#       password).
#   This script was tested using DBI 1.27 and DBD-ODBC 0.28 with ActivePerl
#   5.6.1.633.

require 5.6.0;
use strict;
use warnings;
use Win32::ODBC;

MAIN:
{
    my( $db );
    my( $statement );

    #   Connect to the data source. The DSN name "northwind" must appear in
    #   the list of data sources in the ODBC Data Source Administrator window.
    #   See the instructions above.

    $db = Win32::ODBC->new( "DSN=northwind;UID=Admin;PWD=" ) ||
        die( Win32::ODBC::Error() );

    #   Prepare and execute a statement.

    $statement = "select CustomerID, CompanyName, ContactName, " .
        "ContactTitle, Address, City, Region, PostalCode, " .
        "Country, Phone, Fax from Customers";
    if ( $db->Sql( $statement ) )
    {
        print( "SQL Error: " . $db->Error() . "\n" );
        $db->Close();
        exit;
    }

    #   Print the 11 columns that should be found for each row,
    #   separating each column with a tab character.
    #   If a column is NULL, nothing is printed except the tab separator.

    while ( $db->FetchRow() )
    {
        my( $i );
        my( @items );

        ( @items ) = $db->Data();
        foreach $i ( 0 .. 10 )
        {
            if ( defined( $items[ $i ] ) )
            {
                print( STDOUT $items[ $i ] );
            }
            if ( 10 != $i )
            {
                print( STDOUT "\t" );
            }
        }
        print( STDOUT "\n" );
    }

    #   Disconnect.

    $db->Close();
}