Print this page.  If your browser doesn't allow JavaScript, right-click this page and choose Print from the popup-menu.        
Using Mimer SQL with Perl DBI
Category: Tools and Interfaces
Introduction

Perl is a cross platform programming language, and DBI is the database interface for Perl. The DBI module defines a set of methods, variables and conventions that provide a consistent database interface, independent of the actual database being used.

The database dependent module, DBD, to use with Mimer SQL is "DBD::ODBC".

Further documentation and tutorials for Perl DBI can be found on the Perl DBI Home Page.
Environment

Get Perl from www.perl.com, where you can find precompiled Perl versions for both Unix and Windows, or from www.activestate.com, where ActivePerl is a recommended choice for Windows.

On Unix, you must install a driver manager for ODBC to be able to use the DBD::ODBC module. We can recommend unixODBC or iODBC that you can download from www.unixodbc.org and www.iodbc.org, respectively.


Installation

Unix and Linux

There are three ways to install the Perl DBI module.

1) The easiest way is to use the package manager of your Linux/Unix distribution. The packages are usually named as follows:
  • DBI module, libdbi-perl or perl-dbi

  • DBD::ODBC module, libdbd-odbc-perl or perl-dbd-odbc

Install the DBD::ODBC package first. This package will most likely depend upon both the DBI and ODBC Driver Manager packages, leading to an automatic verification/installation of those packages.

2) The second easiest way to install Perl DBI is to use the CPAN module to download and install the modules. Execute the following statement from the command line. Answer "no" to the question about manual configuration, if you are not an expert, and sensible defaults will be selected automatically.

perl -MCPAN -e shell

When you get the cpan> prompt, type the following:

cpan> install DBI

This will download the Perl DBI source code and compile it. You will need gcc and libc headers installed. You will also need the development package of unixodbc.

Before installing DBD::ODBC you should setup a test database that can be used for verfying the interface Perl and the database. Also, setup the following environment variables with suitable values:
  • DBI_DSN, Data Source Name

  • DBI_USER, database user name

  • DBI_PASS, database user password

  • ODBCHOME, home directory for ODBC installation

Then install DBD::ODBC:

cpan> install DBD::ODBC

This command will likely fail in one of the early tests. In addition, subsequent tests will fail as well since they depend on the success of previous tests.

To correct the problem, edit the file "path_to_cpan/build/DBD-ODBC-X.Y/t/02simple.t". Near line 79 replace:

if ($drv =~ /Oracle/i) {
$sql = q/select 1 from dual/;
} else {
$sql = q/select 1/;
}


with

if ($drv =~ /Oracle/i) {
$sql = q/select 1 from dual/;
} elsif ($drv =~ /Mimer/i) {
$sql = q/select 1 from system.onerow/;
} else {
$sql = q/select 1/;
}


Alternatively you can force an installation:

cpan> force install DBD::ODBC

3) The third option of installing Perl DBI and DBD::ODBC is to download the source from CPAN. This can be quite tricky so you should read the instructions in the README carefully.

Windows

Get the Perl package from www.activestate.com. Use the Perl Package Manager to install the modules.

C:> ppm
PPM> help
PPM> install DBI
PPM> install DBD::ODBC

Example

The example below illustrates how you perform the following steps:
  1. connect to a database

  2. execute a select statement

  3. retrieve and output the result

  4. end the statement

  5. disconnect

It also shows you the Perl modules you need to include.


use strict;
use warnings;

use DBI;

# Create a database handle.
my $dbh = DBI->connect('dbi:ODBC:Database Name', 'User','Password',
{RaiseError => 1, AutoCommit => 0}) or die $DBI::errstr;

# Prepare a statment
my $sth = $dbh->prepare('select * from table_name');

$sth -> execute();

# Get one line of the result as an array.
print join "|", $sth -> fetchrow_array();

# "finishing" the statement is not strictly necessary, but in this
# case we will get a warning if we don't.
$sth -> finish();

$dbh -> disconnect();
Links

For details about ODBC, please see the article about the ODBC Interface.

The article unixODBC - An open source ODBC Driver Manager can be of interest when seeking further information on the ODBC part of this instruction.

The complete Mimer SQL manual is availbale within the Mimer SQL Documentation Set, PDF-file. For details on programming with Mimer SQL see the Programmer's Manual part of the document.
Last updated: 2009-10-12

 

Powered by Mimer SQL

Powered by Mimer SQL