Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

Enabling ODBC support in Perl with Perl DBI and DBD::ODBC

This document describes how to build Perl DBI, add the DBD::ODBC module, and use an ODBC driver provided by Easysoft in your Perl scripts.

Contents

How does ODBC work in Perl

To use an ODBC driver in Perl. you generally use the following components:

  1. DBI module The DBI module is a database interface module for Perl.
  2. Database driver For example, DBD::ODBC, which is a Perl module the DBI loads. DBD::ODBC is the ODBC driver for DBI.
  3. ODBC Driver Manager For example, unixODBC, which comes with all Easysoft ODBC drivers for UNIX and Linux platforms. You can also use iODBC, but Easysoft recommend unixODBC.
  4. ODBC driver Easysoft provide a number of UNIX and Windows ODBC drivers.

To use ODBC in Perl, you write a Perl script that contains use DBI; and call the DBI->connect method with a data source. DBI's data sources are in the form dbi:DRIVER where DRIVER is the DBI driver to use. For DBD::ODBC, specify dbi:ODBC and DBI will load the DBD::ODBC module for you.

You build DBD::ODBC with a dependency on an ODBC Driver Manager such as unixODBC, so when DBI loads the DBD::ODBC Perl module your ODBC Driver Manager will be loaded too.

ODBC needs an ODBC data source to work with so your DBI->connect call becomes dbi:ODBC:ODBC_DSN, where ODBC_DSN is an ODBC data source generally defined in an odbc.ini file. At this point, the ODBC Driver Manager gets a SQLDriverConnect or SQLConnect ODBC API call containing the ODBC data source. The Driver Manager locates that DSN, looks up the ODBC driver identified by the DSN, and then loads the ODBC driver. The ODBC driver connects to your database engine and you can then use DBI methods.

Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

What you need to do before attempting to use an ODBC driver with Perl

  1. Make sure you have tested your Perl installation. If you built Perl yourself, run the Perl tests.
  2. Make sure you meet the minimum requirements for Perl DBI (refer to the README file that comes with DBI). For DBI 1.43, you need a minimum of Perl 5.6.

    You can download the DBI module from:

    http://www.cpan.org/modules/by-module/DBI

    –Or–

    http://search.cpan.org/search?query=DBI

  3. Make sure meet the minimum requirement for Perl DBD::ODBC (refer to the README file that comes with DBD::ODBC). For DBD::ODBC 1.10, you need at least DBI 1.201.

    You can download the DBD::ODBC module from:

    http://www.cpan.org/modules/by-module/DBD

    –Or–

    http://search.cpan.org/search?query=DBD::ODBC

Now verify that your ODBC driver is installed and working:

  1. Locate the unixODBC Driver Manager:
    • If you installed unixODBC as part of your Easysoft installation, its default location is /usr/local/easysoft/unixODBC. unixODBC programs are in /usr/local/easysoft/unixODBC/bin.
    • If you built unixODBC yourself, its location is /usr/local or wherever you set --prefix to. unixODBC programs are in /usr/local/bin or prefix/bin.
    • if you installed a unixODBC package, its location will vary, but the unixODBC programs should be on your path. (They're probably /usr/bin.)

    In the rest of this section, the path to unixODBC programs is called UNIXODBCBIN.

  2. Find out where unixODBC stores ODBC drivers and system DSNs: 
    UNIXODBCBIN/odbcinst -j
    
unixODBC 2.2.3
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /home/myuser/.odbc.ini

    In this case, unixODBC stores ODBC drivers in /etc/odbcinst.ini and system DSNs in /etc/odbc.ini.

    The odbcinst.ini file should already contain an entry for the Easysoft ODBC driver you have installed. For example, the ODBC-ODBC Bridge entry is similar to:

    [OOB]
Description     = Easysoft ODBC-ODBC Bridge
Driver          = /usr/local/easysoft/oob/client/libesoobclient.so
Setup           = /usr/local/easysoft/oob/client/libesoobsetup.so
FileUsage       = 1

    If you haven't got an ODBC driver in odbcinst.ini, you need to check whether the Easysoft product installation found or installed unixODBC.

    The odbc.ini file is where you define your ODBC data sources (DSNs). There are system and user DSNs. System DSNs are stored in the file pointed to by odbcinst > SYSTEM DATA SOURCES. User DSNs are in the file pointed to odbcinst > USER DATA SOURCES. The Driver Manager looks for user DSNs before system DSNs. If you want a DSN to be accessible to everyone on a machine, you should make it a system DSN.

  3. Create and test your DSN.

    When you install an Easysoft ODBC driver, the installation usually creates a system DSN in odbc.ini.

    Check your odbc.ini file for the DSN to test. If your system odbc.ini file does not contain a DSN, create one. (Refer your Easysoft ODBC driver documentation.)

    To test the DSN, run:

    UNIXODBCBIN/isql -v DSN

    where DSN is the name of your DSN.

    This should successfully connect to your database and provide a prompt at which you can type in SQL. If it doesn't:

    • Check you've set any necessary environment variables. For example, ORACLE_HOME or LD_LIBRARY_PATH. (Refer to the manual for the Easysoft ODBC driver you're using.)
    • Consult the documentation that came with the ODBC driver.
    • Contact Easysoft support ().

    A few common problems are listed in Appendix A: Common problems when accessing your DSN

  4. To test DBD::ODBC, your ODBC DSN must specify a database in which you can create and delete tables and procedures.

There's no point in continuing with these instructions until you have a DSN you can connect to. DBD::ODBC is just like isql, it's another application using ODBC, so if isql does not work, DBD::ODBC won't either.

Building Perl DBI, DBD:ODBC with ODBC

  1. Unpack, build, install, and test DBI. (Refer to the README file in the Perl DBI distribution.)
  2. Unpack DBD-ODBC.
  3. To test DBD::ODBC, you need access to an ODBC DSN that specifies a database in which you can create and delete tables and procedures.
  4. Read the README that's included in the DBD:ODBC distribution. You must define and export the following environment variables:

    DBI_DSN The DBI data source. For example, dbi:ODBC:YOUR_DSN_HERE

    DBI_USER The database user name

    DBI_PASS The database password.

    ODBCHOME The directory where the unixODBC Driver Manager was installed. For example, /usr/local/easysoft/unixODBC.

    If you built unixODBC or it came with your system, there's information in What you need to do before attempting to use an ODBC driver in Perl to help you locate unixODBC.

    DBD::ODBC looks for unixODBC shared objects (libodbc.xx) in $ODBCHOME/lib and header files (sql*.h) in $ODBCHOME/include.

    Note More recent versions of DBD::ODBC try to guess what Driver Manager is installed and where it is, and you rarely need to set ODBCHOME now. However, if you have multiple ODBC Driver Managers (for example, iODBC and unixODBC, even if they are only partly installed) DBD::ODBC will pick iODBC up before unixODBC. (The reasons for this are too complicated to describe here.) If you want DBD::ODBC to pick unixODBC before iODBC, specify the -x switch when running the Makefile.PL.

  5. Enter: 
    perl Makefile.PL

    Ignore any warnings like Warning: LD_LIBRARY_PATH for now.

    Note If you want to use Unicode on Linux or UNIX with DBD::ODBC, include the -u switch when running the Makefile.PL. For example, perl Makefile.PL -u. On Windows, DBD::ODBC currently defaults to using Unicode. For Unicode support on any platform in Perl, you need at least Perl 5.8.1. Use perl --version to find out what version of Perl you have installed. For information about Unicode support in DBD::ODBC, refer to the DBD::ODBC documentation and changes log.

  6. Enter: 
    make
  7. Make sure the database user you're using can create tables in the database as the DBD::ODBC test creates a test table.
  8. Enter: 
    make test

    At this stage, you may get errors like:

    install_driver(ODBC) failed: Can't load
'blib/arch/auto/DBD/ODBC/ODBC.so

    This generally means that the ODBC driver shared object or the unixODBC Driver Manager shared object can't be found by the dynamic linker. You should ensure the path to any shared objects your ODBC driver depends on and libodbc.so are on the dynamic linker search path. The method for doing this depends on the platform you're running on, but may mean adding a path to the LD_LIBRARY_PATH, SHLIB_PATH, LD_RUN_PATH, or LIBPATH environment variables or adding the same path to /etc/ld.so.conf and running ldconfig(8) (Linux). For Easysoft ODBC drivers, you need:

    /usr/local/easysoft/lib
/usr/local/easysoft/unixODBC/lib

    in the dynamic linker search path.

    Once these changes have been made, rerun make test.

    If the tests still fail, what does the error message contain? You should have used the same DSN as in What you need to do before attempting to use an ODBC driver in Perl, but if you haven't, go back to that section and check the DSN you're using now.

    Some Easysoft ODBC drivers come with additional diagnostic tools like oobping, which comes with the Easysoft ODBC-ODBC Bridge.

    If it's a connection issue, consult the ODBC driver documentation.

    After connection problems are resolved, any further problems should be easily identified by the ODBC error message output. However, you should be aware that the Perl DBD::ODBC tests are somewhat dependent on the database and the ODBC driver you're using. For example, Microsoft's Access ODBC driver does not have SQLDescribeParam.

  9. Once the tests succeed, install Perl DBI:ODBC with: 
    make install

Building Perl DBI and DBD::ODBC with the ODBC-ODBC Bridge on OpenVMS

On OpenVMS (Alpha)

Easysoft has built and tested Perl 5.8.1 with DBI-1.35 and DBD::ODBC 1.05. We have also built and tested Perl 5.6.0 with DBI-1.13 and DBD::ODBC 0.28.

We used MMS 3.2-01 throughout as our copy of MMK appeared to give errors like:

%MMK-F-SDCMIX, single/double-colon dependency mix found for target
RE_EXEC.OBJ
%MMK-F-ERRUPD, error status %x1c148064

We also used DECC V6.0-001 on OpenVMS V7.2.

All the tools you need to uncompress and unpack Perl and the DBI and DBD modules may be found at the VMS Perl site.

  1. If you have not yet installed Perl, get Perl 5.8.1 (or newer), unpack it and run configure.com. Answer all the questions, run mms, mms test and if these succeed, mms install.

    When we did this, we got one failure:

    lib/vmsish...........................FAILED at test 22

    This was an issue for people with a positive offset from GMT and a patch was posted on the vmsperl mailing list. It does not however cause a problem.

  2. Get the latest Perl DBI module, uncompress and unpack. Read the README. The sequence you need is described below. Make sure you read all this section before starting, as you need to edit the Makefile.PL and DBD.PM files.

    perl Makefile.PL
mms
mms test
mms install

    With DBI-1.35 and this Perl snapshot, we had two problems with the descrip.mms generated from the Makefile.PL by MakeMaker:

    • "%MMS-F-GWKNOACTS, Actions to update CONFIG are unknown."

      This seems to be caused by a rule with no action. If you search Makefile.PL for the line:

      config :: $(changes_pm)

      add this on the next line:

      $(NOECHO) $(NOOP)

      Make sure you put a tab before $(NOECHO).

    • "%MMS-F-GWKNOACTS, Actions to update
[.BLIB.ARCH.AUTO.DBI]DRIVER.XST are unknown."

      This seems to be caused by the lines generated by MakeMaker commented as "these two keep make -j4 working". In [.lib.DBI]DBD.pm, search for:

      # these two keep make -j4 working
$(DBI_DRIVER_XST) :: pm_to_blib
        '.$xstf_h.' :: pm_to_blib

      and add to each rule:

      $(NOECHO) $(NOOP)

      so you have:

      # these two keep make -j4 working
$(DBI_DRIVER_XST) :: pm_to_blib
        $(NOECHO) $(NOOP)
'.$xstf_h.' :: pm_to_blib
        $(NOECHO) $(NOOP)

      Make sure you put a tab before $(NOECHO).

    If you need to make these changes, you need to rerun perl Makefile.PL.

    You might have to specify the target as all as mms picks the first target in the descrip.mms file and you might get an error because some of the [.blib] directory structure was not created.

    When building DBI, you will probably get a few warnings about symbols being trimmed, don't worry about this.

    You may also get %SYSTEM-W-BADFILEVER errors for dbiprof.pl.rno, dbiproxy.pl.rno, and possibly dbish.pl.rno (pre 1.35). This problem's cause is the double file extension, which is invalid in OpenVMS (you can ignore this error).

    When testing DBI, we got no errors. Some tests for iThreads, PurePerl (Kids, profiling, preparse) were skipped.

  3. Get the latest DBD::ODBC, uncompress and unpack. ODBC-ODBC Bridge was verified with DBD::ODBC 1.05, but it needs a patch to work properly on OpenVMS with ODBC-ODBC Bridge. Versions after DBD::ODBC 1.05 may include the required patch.

    You can get GNU patch for OpenVMS, apply the patch by hand (not difficult but possibly error prone) or apply the patches on a machine where you already have GNU patch. The patch is included in the extras subdirectory of the ODBC-ODBC Bridge distribution and is called [.DBD_ODBC_1_05]DBD-ODBC-1_05_VMS.PATCH.

    If you're applying the patch by hand, remove all lines starting with - and add all lines starting with +. Otherwise, unpack DBD::ODBC, change into the created directory and run:

    patch --verbose --backup --input=DBD-ODBC-1_05_VMS.PATCH -p0

    If you can't get GNU patch or don't understand enough about patch files to make the changes necessary, you can try editing Makefile.PL and change:

    $myodbc = 'esodbc'
        if !$myodbc && <$odbchome/*esoobclient*>;

    to:

    $myodbc = 'esodbc'
        if !$myodbc && <$odbchome/lib/*esoobclient*>;

    Search for the section starting:

    elsif ($myodbc eq 'esodbc')

    In this block, search for occurances of $odbchome used without a following / and add /lib. So $odbchome becomes $odbchome/lib. You also need to locate -leasyrpc and add -lextras. You should end up with something like this. (Comments have been omitted and changed lines are marked with ->.)

    The reason for this change is that the ODBC-ODBC Bridge distribution now puts the libraries in [.odbchome.lib]. Previously, they were in [.odbchome]. Additional functions are in libextras.olb.

    You now need to define where you installed ODBC-ODBC Bridge. You do this by defining the ODBCHOME logical as the directory where the ODBC-ODBC Bridge client was installed. For example, suppose you installed ODBC-ODBC Bridge in DKA200:[MARTIN.OOB], you need to:

    define/log ODBCHOME DKA200:[MARTIN.OOB]

    You also need to define a logical called DBI_DSN that specifies the DBD ODBC driver and the data source name. Create a local ODBC-ODBC Bridge client data source called test in the file []odbc.ini. which points to a remote data source. For example:

    [test]
serverport = ntserver:8888
targetdsn = ntsystemdsn
logonuser = nt_user_name
logonauth = nt_password

    Then define DBI_DSN as:

    define/log DBI_DSN "dbi:ODBC:test"

    then define DBI_USER as:

    define/log DBI_USER "database_user_name"

    then define DBI_PASS as:

    define/log DBI_PASS "database_password"

    You can now run:

    perl Makefile.PL

    to generate the mms description file.

    Before continuing the build, we should mention a few problems we came across that need minor alterations to the Makefile.PL file. With DBD-ODBC 1.05 and this Perl snapshot, we had a similar problem as with DBI (as described earlier) except that the MMS-F-GWKNOACTS for DRIVER.XST goes away if DBI is installed first. The descrip.mms generated from the Makefile.PL by MakeMaker produces these errors:

    • "%MMS-F-GWKNOACTS, Actions to update CONFIGare unknown."

      This seems to be caused by a rule with no action. If you search the Makefile.PL for the line:

      config :: $(changes_pm)

      add to the next line:

      $(NOECHO) $(NOOP)

      Make sure you put a tab before $(NOECHO).

    • "%MMS-F-GWKNOACTS, Actions to update
[.BLIB.ARCH.AUTO.DBI]DRIVER.XST are unknown."

      This seems to be caused by the lines generated by MakeMaker commented as these two keep make -j4 working. In Makefile.PL, search for:

      # these two keep make -j4 working
$(DBI_DRIVER_XST) :: pm_to_blib
$(DBI_INSTARCH_DIR)Driver_xst.h :: pm_to_blib

      and add to each rule:

      $(NOECHO) $(NOOP)

      so you have:

      # these two keep make -j4 working
$(DBI_DRIVER_XST) :: pm_to_blib
    $(NOECHO) $(NOOP)
$(DBI_INSTARCH_DIR)Driver_xst.h :: pm_to_blib
    $(NOECHO) $(NOOP)

      Make sure you put a tab before $(NOECHO).

    If you need to make these changes, rerun perl Makefile.PL.

    mms

    Don't be surprised if the build shows some IMPFUNC, PTRMISMATCH, PROMOTMATCHW, or OUTTYPELEN informational messages — these are normal.

    You can now test DBD::ODBC using the ODBC-ODBC Bridge:

    mms test

    and finally, install DBD::ODBC using:

    mms install

On OpenVMS (Itanium)

These instructions are specific to using the ODBC-ODBC Bridge with Perl on OpenVMS (Itanium).

We have built and tested Perl 5.8.8 with DBI-1.51 and DBD::ODBC 1.13.

We used the following versions:

and our machine was running ODS5.

We first tried installing a Perl 5.8.6 binary from HP's site, but we had a few problems with it. For example, mms clean failed with an access violation. If you've got the same problem:

perl "-MExtUtils::Command" -e "rm_f" foo.[0-9][0-9]

dies with an access violation.

After HP's binary failed, we downloaded the 5.8.8 source release of Perl and used that.

5.2.1 Prerequisites

Install a binary of vmstar or build it yourself. After installing VMS 8.2-1, we found the vmstar source files in sys$common:[GNV.src.GNV.VMSTAR].

set def sys$common:[GNV.src.GNV.VMSTAR]
mms clean
mms
copy VMSTAR.ALPHA_EXE VMSTAR.EXE
vmstar :== $sys$common:[GNV.src.GNV.VMSTAR]vmstar.exe

Now vmstar is a symbol pointing to the vmstar executable.

Building and installing Perl 5.8.8

Download the source for Perl 5.8.8.

Use vmstar to untar it:

vmstar xovf stable.tar

This creates the subdirectory perl-5_8_8.

Read the file README.vms.

Configure Perl (refer to README.vms). We used:

@ Configure "-d" "-Dprefix=dka0:[perl]"

Then type the command that Configure tells you to enter to build Perl, usually mms.

If the build fails, scroll back through the build process and look for warnings like:

%MMS-W-GMFUTURE, Time for [-.BLIB.MAN3].EXISTS is in the future:
6-AUG-2006 12:29:13.00

If you find these warnings, you have probably got the same time issue we had originally, which we have only found one solution to. If you can provide a better solution than we describe here, let us know. For your information:

@SYS$MANAGER:UTC$TIME_SETUP SHOW

produced:

AUTO_DLIGHT_SAV is set to "0" and DTSS is not in use.
You will have to manually change to/from Daylight Saving Time.
  
You can do this by executing SYS$MANAGER:UTC$TIME_SETUP.COM,
or you can use SYS$EXAMPLES:DAYLIGHT_SAVING.COM.
  
  
    LOCAL TIME ZONE          = GB -- DAYLIGHT TIME
    LOCAL SYSTEM TIME        =  6-AUG-2006 11:20:11.58 (BST)
    TIME DIFFERENTIAL FACTOR = 1:00
    TIME ZONE RULE           = GMT0BST-1,M3.4.0/01,M10.5.0/02
    Change GMT to BST on the Fourth Sunday of March (26-Mar-2006) at
01:00
    Change BST to GMT on the Last Sunday of October (29-Oct-2006) at
02:00

The only thing that worked for us was the following change:

--- lib/ExtUtils/Command.pm;-0  Fri Oct 21 03:55:12 2005
+++ lib/ExtUtils/Command.pm     Sun Aug  6 13:30:45 2006
@@ -149,12 +149,11 @@ Makes files exist, with current timestam
 =cut
  
 sub touch {
-    my $t    = time;
     expand_wildcards();
     foreach my $file (@ARGV) {
         open(FILE,">>$file") || die "Cannot write $file:$!";
         close(FILE);
-        utime($t,$t,$file);
+        utime(undef,undef,$file);
     }
 }

Edit the file [.lib.ExtUtils]Command.pm, delete the lines starting in a '-' and add the lines starting in a '+'.

Rerun mms.

Run:

mms test

We got the following errors:

t/io/fs...................................FAILED at test 5
t/op/stat.................................FAILED at test 33
ext/Devel/PPPort/t/ppphtest...............FAILED--unexpected output at test 0
ext/List/Util/t/p_tainted.................FAILED--no leader found
ext/List/Util/t/weak......................FAILED--unexpected output at test 7
lib/ExtUtils/t/basic......................FAILED at test 67
lib/ExtUtils/t/Command....................FAILED at test 8
lib/ExtUtils/t/Constant...................FAILED at test 23
lib/ExtUtils/t/FIRST_MAKEFILE.............FAILED at test 4
lib/ExtUtils/t/PL_FILES...................FAILED at test 3
lib/vmsish................................FAILED at test 22
  
Failed 13 test scripts out of 892, 98.54% okay.
### Since not all tests were successful, you may want to run some of
### them individually and examine any diagnostic messages they produce.
### See the INSTALL document's section on "make test".
### You have a good chance to get more information by running
###   ./perl harness
### in the 't' directory since most (>=80%) of the tests succeeded.
u=40.80  s=0.00  cu=0.00  cs=0.00  scripts=892  tests=114061

We don't know as yet whether these are test bugs or genuine errors but they don't appear to affect Perl DBI or DBD::ODBC, although they may affect other operations in Perl.

Run:

mms install

to install Perl in your chosen path.

If this command hangs with:

$ mms install
  
%DCL-I-SUPERSEDE, previous value of PERL_ROOT has been superseded
%DCL-I-SUPERSEDE, previous value of PERLSHR has been superseded
If F$TrnLnm("Sys") .nes. "" Then Deass SYS
MCR Sys$Disk:[]miniperl.exe "-I[.lib]" installperl
Deep recursion on subroutine "File::Path::mkpath" at lib/File/Path.pm line 162.

you may want to consult the following references:

We worked around this problem by creating the Perl installation directory before running mms install.

Running h2ph to generate the Perl copies of your header files produces:

Cannot open perl_root:[lib.site_perl.VMS_IA64]/_h2ph_pre.ph: file
 specification syntax error at perl_root:[utils]h2ph.com line 741.
 %RMS-F-SYN, file specification syntax error

but you can ignore this as it doesn't affect the installation of DBI or DBD::ODBC.

Now you have Perl installed, run the perl_setup.com, which is located in the directory where you installed Perl.

Building and installing Perl DBI

Download DBI-1.51 and unpack it with vmstar.

Use the normal sequence of:

perl Makefile.pl
mms
mms test
mms install

We got the following warnings, which we believe you can safely ignore:

Warning: long symbol DBD__Perl__db_selectall_arrayref
       trimmed to DBD_Perl_db_selectal_arayref
Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

Building and installing Perl DBD::ODBC

Download the ODBC-ODBC Bridge client, unzip it wherever you want it and make a note of the directory you installed it in. In this example, we assume you installed it in dka0:[oob].

Set up the following logicals:

define/log ODBCHOME DKA0:[OOB]
define/log DBI_DSN "dbi:ODBC:test"
define/log DBI_USER "database_user_name"
define/log DBI_PASS "database_password"

You'll need to change ODBCHOME to wherever you installed the ODBC-ODBC Bridge. DBI_DSN defines the DBD driver and the data source to use. (We've used a data source called test here.) DBI_USER and DBI_PASS are the database user name and password required to log in to your data source (amend as appropriate).

Create an odbc.ini file in the current working directory ([]) that contains:

[test]
serverport = ntserver:8888
targetdsn = ntsystemdsn
logonuser = nt_user_name
logonauth = nt_password

where:

Edit Makefile.PL and replace occurrances of ptrmismatch with ptrmismatch2. This is because decc has changed the warning from ptrmismatch to ptrmismatch2.

Create a file in [] called continue.com containing one empty DCL line. For example:

$

If you attempt to run mms without it, you'll get an error like:

MCR dka0:[perl]perl.exe "-MExtUtils::Command" -e cp Changes
[.blib.lib.DBD.ODBC]Changes.pm
@Continue
%DCL-E-OPENIN, error opening DKA0:[NICK.MARTIN.DBD-ODBC-1_13]Continue.COM; as
input
-RMS-E-FNF, file not found
%MMS-F-ABORT, For target CONFIG, CLI returned abort status: %X10018292.

We used MMS 3.2-01 throughout as our copy of MMK appeared to give errors like:

%MMK-F-SDCMIX, single/double-colon dependency mix found for target
RE_EXEC.OBJ
%MMK-F-ERRUPD, error status %x1c148064

We also used DECC V6.0-001 on OpenVMS V7.2.

Enter:

mms

As with DBI, you can safely ignore the warnings like:

Warning: long symbol DBD__ODBC__db_selectall_arrayref
        trimmed to DBD_ODBC_db_selectal_arayref

All the tools you need to uncompress and unpack Perl, DBI, and DBD can be downloaded from the VMS Perl site.

If your ODBC-ODBC Bridge server is up and running, you've set up the odbc.ini file in the current working directory, and created a working system ODBC data source on the ODBC-ODBC Bridge server machine, run:

mms test

We got failures in 02simple.t, because we're not using an ODBC Driver Manager (safe to ignore) and errors in 20SqlServer.t test 5 like:

1..37
ok 1 - use ODBCTEST;
ok 2 - use Data::Dumper;
ok 3 - errors on data comparison
ok 4 - temporary table handling
Can't change param 1 maxlen (51->50) after first bind at t/20sqlserver.t line
180.
# Looks like you planned 37 tests but only ran 4.
# Looks like your test died just after 4.

You can fix this problem by editing the dbdimp.c module. Search for the line:

else if (maxlen && maxlen != phs->maxlen) {

at around line 2931 and change it to:

else if (maxlen && maxlen > phs->maxlen) {

You can now run:

mms install

to install DBD::ODBC.

Notes about old versions of DBI and DBD::ODBC

A note about DBD::ODBC up to 0.45 and SQL_WLONGVARCHAR columns

Versions of DBD::ODBC from 0.25 conditionally build with support for SQL_WLONGVARCHAR data. A problem can arise in the ODBC-ODBC Bridge when DBD::ODBC is built directly with the ODBC-ODBC Bridge or unixODBC that manifests itself as the error:

[unixODBC][Easysoft ODBC (Server)]Memory allocation error
(SQL-S1001)(DBD: describe/SQLBindCol err=-1)

This occurs when you issue a query returning a SQL_WLONGVARCHAR column. (ntext in Microsoft SQL Server.) DBD::ODBC only builds support for SQL_WLONGVARCHAR if SQL_WLONGVARCHAR is defined in the ODBC headers it's built with. When built without support for SQL_WLONGVARCHAR, if a SQL_WLONGVARCHAR column is encountered, it's bound with the column size returned by SQLDescribeCol instead of LongReadLen. The Makefile.PL that comes with DBD::ODBC does not ensure sqlucode.h is written to dbdodbc.h when building with the ODBC-ODBC Bridge and unixODBC. You can correct this by adding #include <sqlucode.h> to dbdodbc.h after running perl Makefile.PL or by editing the esodbc or unixodbc sections of Makefile.PL and adding print SQLH qq{#include <sqlucode.h>\n}; to the end.

In addition, read the notes for 0.24 and 0.25 for further information.

A note about DBD::ODBC 0.28

If you're building DBD::ODBC with the unixODBC Driver Manager, modify DBD::ODBC's Makefile.PL prior to building DBD::ODBC.

When running make test you get something like:

PERL_DL_NONLAZY=1 /usr/bin/perl -Iblib/arch -Iblib/lib
-I/usr/lib/perl5/5.00503/i386-linux -I/usr/lib/perl5/5.00503 -e 'use
Test::Harness qw(&runtests $verbose); $verbose=0; runtests @ARGV;' t/*.t
t/01base............install_driver(ODBC) failed: Can't load
'blib/arch/auto/DBD/ODBC/ODBC.so' for module DBD::ODBC:
blib/arch/auto/DBD/ODBC/ODBC.so: undefined symbol: SQLParamData at
/usr/lib/perl5/5.00503/i386-linux/DynaLoader.pm line 169.

The undefined symbol SQLParamData is reported by the dynamic linker.

This happens when building DBD::ODBC with unixODBC because the Makefile.PL is now incompatible with newer unixODBC releases. The Makefile.PL searches ODBCHOME/lib for *odbc*.* and finds libodbc.so and libodbcinst.so. It then goes on to choose libodbcinst.so, which is the incorrect shared object. You can check this by running ldd on blib/arch/auto/DBD/ODBC/ODBC.so. You'll probably get something like:

libodbcinst.so1 (libc6) => /usr/local/easysoft/unixODBC/lib/libodbcinst.so1

To fix this, edit Makefile.PL and delete the second * in the line:

elsif ($myodbc eq 'unixodbc') {
        my @ilibs = <$odbchome/lib/*odbc*.*>;

so it becomes:

elsif ($myodbc eq 'unixodbc') {
        my @ilibs = <$odbchome/lib/libodbc.*>;

Now rerun perl Makefile.PL and it should pick up the correct libodbc shared object.

A note about DBD::ODBC 0.27

Refer to the notes on 0.25 and 0.24.

A note about DBD::ODBC 0.25

0.25 is like 0.24 except that SQL_Wxyz wide character support is conditionally compiled in if your ODBC driver defines wide characters. If you want wide character support, follow the instructions below for 0.24.

A note about DBD::ODBC 0.24

A change was made to DBD::ODBC 0.24 that sometimes prevents it building with the ODBC-ODBC Bridge (and a number of other ODBC drivers and Driver Managers) out of the box. From DBD::ODBC 0.24, the driver appears to be required to define SQL_Wxyz data types. The header files that come with ODBC-ODBC Bridge do define the SQL_Wxyz data types, but only if the WIN32 or __unix__ or __vms__ macros are defined. If your compiler does not define any of these, you'll get undefined references to SQL_WVARCHAR and so on. You need to modify the generated Makefile to add a -D__unix__ to the definition of CCFLAGS. Whether your compiler defines __unix__ or not, you should add -DSQL_NOUNICODEMAP to CCFLAGS too.

We hope this situation will be rectified in a future release of DBD::ODBC.

A note about versions of DBD::ODBC before 0.21

Prior to DBD::ODBC 0.21, DBD::ODBC did not require a Driver Manager. This changed in DBD::ODBC 0.21 and it's assumed versions thereafter. The Easysoft ODBC-ODBC Bridge does not require a Driver Manager on non-Windows platforms to work as it was written before ODBC 3.0 Driver Managers were available for UNIX and it contains all the necessary Driver Manager functionality. However, from DBD::ODBC 0.21, DBD::ODBC requires the ODBC Driver Manager APIs SQLDrivers and SQLDataSources. These functions were not available in versions of the ODBC-ODBC Bridge prior to the 0.4.0.0 beta but are included from 0.4.0.0 onwards.

If you're building DBD::ODBC 0.20 then you must apply the patch supplied with ODBC-ODBC Bridge in the extras/DBD_ODBC_0.20 subdirectory off the ODBC-ODBC Bridge installation path. If you have not got patch, the changes are minimal and can done by inserting the lines starting with a + in the patch file. For example:

patch < Makefile.PL.patch

Do not attempt to apply this patch to DBD::ODBC 0.21 as Jeff Urlwin has already applied it and the Makefile.PL that comes with 0.21 is fine.

DBD::ODBC 0.21 contains the iODBC Driver Manager distribution and ODBC-ODBC Bridge will work with this Driver Manager. However, if you want or need to use a Driver Manager then we recommend the unixODBC Driver Manager available from unixodbc.org. There are a number of reasons for this:

  1. The unixODBC project started by Peter Harvey is now maintained by Nick Gorham, who is an Easysoft developer. This means that there is much greater experience with unixODBC within Easysoft and we will be able to provide better support for the ODBC-ODBC Bridge running under unixODBC. It also means that if you find a problem in unixODBC, it's much easier for us to facilitate a fix.
  2. From ODBC-ODBC Bridge 0.4.0.0, the ODBC-ODBC Bridge installation for UNIX can automatically install itself using the unixODBC installer program to run under unixODBC.

To summarise the situation:

  1. If you have DBD::ODBC 0.20, you can directly build DBD with any version of ODBC-ODBC Bridge and you do not need to use a Driver Manager unless you want to support multiple ODBC drivers through Perl.
  2. If you have DBD::ODBC 0.21+, you need ODBC-ODBC Bridge 0.4.0.0 or above.
  3. If you want to use a Driver Manager or need to because you have multiple ODBC drivers, we recommend the unixODBC manager. ODBC-ODBC Bridge will however work with the iODBC Driver Manager.

Building Perl 5.6.0, DBI 1.13 and DBD:ODBC 0.28 with the ODBC-ODBC Bridge (OpenVMS)

We have built and tested Perl 5.6.0, DBI-1.13, and DBD::ODBC 0.28, although a patch to DBD::ODBC is required to build with the ODBC-ODBC Bridge (described later).

We used MMS 3.2-01 throughout as our copy of MMK appeared to give errors like:

%MMK-F-SDCMIX, single/double-colon dependency mix found for target
RE_EXEC.OBJ
%MMK-F-ERRUPD, error status %x1c148064

We also used DECC V6.0-001 on OpenVMS V7.2.

All the tools you need to uncompress and unpack Perl, DBI and DBD::ODBC can be downloaded from the VMS Perl site.

  1. If you have not yet installed Perl, get Perl 5.6.0 (or newer), unpack it and run configure.com. Answer all the questions, run mms, mms test and if OK, mms install.

    When we did this, we got three failures:

    glob-basic - failed on test 3
vmsish - failed on test 7
warnings - failed on test 216

    The reason for this glob failure is that the test does not take into account that glob() works differently on OpenVMS. There are patches for vmsish on the vmsperl mailing list.

  2. Get the latest Perl DBI module, uncompress and unpack. Read the README. You should be able to simply do:

    perl Makefile.PL
mms all
mms test
mms install

    Make sure that you specify the target as all. Otherwise, mms picks the first target in the descrip.mms file and you will probably get an error because the [.blib] directory structure was not created. For example:

    %MMS-F-GWKNOPRN, There are no known sources for the current target
[.BLIB.ARCH.AUTO.DBI]DRIVER.XST

    When building DBI, you'll probably get a few warnings about symbols being trimmed, don't worry about this.

    You may also get %SYSTEM-W-BADFILEVER errors for dbiproxy.pl.rno and dbish.pl.rno. This reason for this problem is the double file extension, which is invalid in VMS, but we currently do not have a solution.

    When testing DBI, we got errors from examp.t:

    t/examp
can't run t/examp.t. invalid argument
FAILED before any test output arrived

    Usually when this happens, the best thing to do is to rerun the tests with TEST_VERBOSE=1 set. For example, mms/macro=TEST_VERBOSE=1 test. If you then change into the t subdirectory and run the examp tests individually, the tests succeed. For example:

    set def [.t]
perl -"T" examp

    We have found a reference in the vmsperl mailing list to this which suggests the test calls Perl with a command line that is much too long while passing a library list explicitly.

  3. Get the latest DBD::ODBC, uncompress and unpack. The ODBC-ODBC Bridge was verified with DBD::ODBC 0.28 but the Makefile.PL needs a patch to work properly on VMS. Versions after DBD::ODBC 0.28 may include the required patch (you can check by searching for the string VMS in the DBD::ODBC Makefile.PL file).

    You can get a GNU patch for VMS, apply the patch manually (not difficult, but possibly error prone) or apply the patches on a machine where you already have patch. The patch is included in the extras subdirectory of the ODBC-ODBC Bridge distribution and is called [.DBD_ODBC_0_28.DBD-ODBC-0_28_VMS.PATCH].

    If you're applying the patch manually, you need to remove all lines starting with - and add all lines starting with +. Otherwise unpack DBD::ODBC, change into the created directory and run:

    patch --verbose --backup --input=DBD-ODBC-0_28_VMS.PATCH -p0

    You now need to define where you installed ODBC-ODBC Bridge. You do this by defining the ODBCHOME logical as the directory where the ODBC-ODBC Bridge client was installed. For example, suppose you installed the ODBC-ODBC Bridge in DKA200:[MARTIN.OOB], you need to:

    define/log ODBCHOME DKA200:[MARTIN.OOB]

    You also need to define a logical called DBI_DSN that specifies the DBD ODBC driver and the data source name. Create a local ODBC-ODBC Bridge client data source called test in the file []odbc.ini, which points to a remote data source. For example:

    [test]
serverport = ntserver:8888
targetdsn = ntsystemdsn
logonuser = nt_user_name
logonauth = nt_password
targetuser = db_user_name
targetauth = db_password

    Then define DBI_DSN as:

    define/log DBI_DSN "dbi:ODBC:test"

    You can now run:

    perl Makefile.PL
mms all
mms test
mms install

Examples

Some Easysoft ODBC drivers contain Perl examples in the examples subdirectory of the driver distribution.

There are some test programs in addition to those mentioned above in the Perl DBD:ODBC distribution. These are located in the mytest subdirectory (listtabs.pl and testfunc.pl). Unfortunately, listtabs.pl seems to be a little out of date in respect of Perl DBI as it expects a statement handle from the tables method and expects to find a fetchrow method.

Issues

Bound parameters and Microsoft Access

Perl DBD:ODBC may sometimes use bound parameters, but this requires support for SQLDescribeParam. The Microsoft Access ODBC driver does not currently support SQLDescribeParam and so bound parameters must be avoided when using Perl->Access or Perl->ODBC-ODBC Bridge->Access.

Memory leak bug

On March 30th 2001 Tim Bunce (author of DBI) reported the following bug to perl5-porters:

----- Forwarded message from Tim Bunce <Tim.Bunce@ig.co.uk> -----
Date: Fri, 30 Mar 2001 15:38:27 +0100
From: Tim Bunce <Tim.Bunce@ig.co.uk>
To: Perl 5 porters <perl5-porters@perl.org>
Cc: Tim Bunce <timbo@ig.co.uk>
Subject: Memory leak localizing a tied variable

The script appended below demonstrates a memory leak when doing a
local() on a tied variable.

I found this because someone reported that local($dbh->{RaiseError})=1
(and similar) leaked memory and thought it maybe a DBI bug.
The script demonstrates that it's not.

Perl 5.005 and 5.006 leak but 5.004 doesn't. I've not got a 5.7.0
handy. I'd be grateful if someone could test it on the current bleadperl.

Thanks.

Tim.

#!/usr/local/bin/perl -w

print "localizing tied variable leak test for perl $]...\n";
# 5.006   does leak
# 5.00503 does leak
# 5.00404 doesn't leak

use strict;
use Tie::Hash;
tie my %tie_hash => 'Tie::StdHash';

my $count = 0;
my $ps = (-d '/proc') ? "ps -lp " : "ps -l";

mem_test() while 1;

sub mem_test {
    system("echo $count; $ps$$") if (($count++ % 1000) == 0);
    local($tie_hash{Foo}) = 1;
}

----- End forwarded message -----

Appendix A. Common Perl and ODBC problems

Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.