This file includes instructions on building Perl DBI with the DBD:ODBC module and ODBC support provided by the Easysoft ODBC-ODBC Bridge. Contents ======== Introduction A note about DBD::ODBC 0.27 A note about DBD::ODBC 0.25 A note about DBD::ODBC 0.24 A note about versions of DBD::ODBC before 0.21 Building Perl DBI, DBD:ODBC with OOB (not OpenVMS) Building Perl DBI, DBD:ODBC with OOB (OpenVMS) Examples Bound Parameters and MS Access Useful Links introduction ============ The Easysoft ODBC-ODBC Bridge has been proven with Perl DBI-1.02 - DBI-1.13 and DBD-ODBC 0.20-0.28 although it may work with other versions. If you are working on OpenVMS please see separate VMS notes later in this file. A note about DBD::ODBC 0.27 =========================== See 0.25 and 0.24. A note about DBD::ODBC 0.25 =========================== 0.25 is like 0.24 except the SQL_Wxyz wide character support is conditionally compiled in if your ODBC driver defines wide characters. If you want wide character support please follow the instructions below for 0.24. A note about DBD::ODBC 0.24 =========================== A change was made to DBD::ODBC 0.24 which sometimes prevents it building with OOB (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 OOB do define the SQL_Wxyz data types but only if the WIN32, __unix__ or __vms__ macros are defined. If your compiler does not define any of these (you will get undefined references to SQL_WVARCHAR etc) you will need to modify the generated Makefile (see below) 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 is 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 requires the ODBC driver manager APIs SQLDrivers and SQLDataSources. These functions were not available in versions of OOB prior to the 0.4.0.0 beta but are included from 0.4.0.0 onwards. DBD::ODBC 0.21 contains the iODBC driver manager distribution and OOB will work with this driver manager. However, if you want/need to use a driver manager then Easysoft recommend the unixODBC driver manager available from http://www.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 there is much greater experience with unixODBC within Easysoft and we will be able to provide better support for OOB running under unixODBC. It also means that if you find a problem in unixODBC it is much easier for us to facilitate a fix. [2] At this time unixODBC supports ODBC 3.5 and is thread-safe whilst this is not true for iODBC. [3] The unixODBC package contains much more than a driver manager. The aim of the unixODBC project is to provide all the ODBC functionality available on Windows for UNIX operating systems. The unixODBC package may be built with the QT libraries to allow GUI configuration of DSNs and drivers. It also contains the GUI DataManager program which may be used to explore your ODBC data. [4] From OOB 0.4.0.0, the OOB 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 then you can directly build DBD with any version of OOB 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 +, then you can do as in [1] but you need OOB 0.4.0.0 (or above). [3] If you want to use a driver manager or need to because you have multiple ODBC drivers then Easysoft recommend the unixODBC manager. OOB will however work with the iODBC driver manager. Building Perl DBI, DBD:ODBC with OOB (not OpenVMS) ================================================== 1. Unpack DBI and build as per the instructions in the README file in the Perl DBI distribution. Install DBI. 2. Unpack DBD-ODBC. 3. If you are building DBD::ODBC 0.20 then you must apply the patch supplied with OOB in the extras subdirectory off the OOB installation path. If you have not got patch the changes are minimal and can easily be hand edited by inserting the lines starting with a "+" in the patch file. e.g. 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. 4. Create a odbc.ini file containing a valid datasource. You can skip this step if you have not yet installed the OOB server on another machine but we would suggest you don't. Alternatively, if you are using a driver manager such as unixODBC you should define a datasource via the mechanisms provided by that driver manager. 5. Read the README accompanying the DBD:ODBC distribution. You must define and export the following environment variables: DBI_DSN The dbi data source, e.g. 'dbi:ODBC:YOUR_DSN_HERE' DBI_USER The username to use to connect to the database DBI_PASS The username to use to connect to the database ODBCHOME (Unix only) The directory the OOB client was installed in (e.g. /usr/local/easysoft/oob/client) or the directory where your drivermanager was installed. By default, the OOB is installed in /usr/local/easysoft and the OOB client is in the subdirectory oob/client. For the default installation you would set ODBCHOME to "/usr/local/easysoft/oob/client". The DBI_DSN setting depends on the datasource you have set in the odbc.ini file. 6. Type "perl Makefile.PL" Ignore any warnings like "Warning: LD_LIBRARY_PATH" for now. 7. Type "make" 8. Type "make test" You may at this stage get errors like: install_driver(ODBC) failed: Can't load 'blib/arch/auto/DBD/ODBC/ODBC.so This generally means that the Easysoft ODBC-ODBC Bridge client shared object cannot be found by the dynamic linker. You should ensure the path to the libesoobclient.so is on the dynamic linker search path. The method for doing this depends on the OS you are running but may mean adding a path to the LD_LIBRARY_PATH or LD_RUN_PATH environment variables or adding the same path to /etc/ld.so.conf and running ldconfig (Linux). Once these changes have been made rerun "make test". If the tests still fail then you need to look at the error message. Connections can be tested by telnetting to the machine and port you specified in the odbc.ini file (or driver manager DSN) where you should receive as string like the one below: 87FA9694x1:0:1998-1112-0000000001+01:NT^02:4.0.1381^03:Intel^04:4^05:^06:™p^ (some of the values above differ depending on the platform). 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 tests are somewhat dependent on the ODBC database and driver you are using on the remote machine e.g. The Access ODBC driver does not have SQLDescribeParam(). 9. Once the tests succeed you can install Perl DBI:ODBC with "make install". Building Perl DBI, DBD:ODBC with OOB (OpenVMS) ============================================== Easysoft 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 OOB (see 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 and the DBI/DBD modules may be found off the VMS Perl site (see links below). [1] If you have not yet installed Perl, get Perl 5.6.0 (or newer), unpack it and run the 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 It is our understanding that the glob failure is due to the fact that the test does not take into account that glob() works differently on VMS. There are patches for vmish 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 you specify the target as "all" or 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 e.g. %MMS-F-GWKNOPRN, There are no known sources for the current target [.BLIB.ARCH.AUTO.DBI]DRIVER.XST When building DBI, you will probably see a few warnings about symbols being trimmed as they are very long - don't worry about this. You may also see %SYSTEM-W-BADFILEVER errors for dbiproxy.pl.rno and dbish.pl.rno. This problem is due to the double file extension which is invalid in VMS but we currently do not know the 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 rerun the tests with TEST_VERBOSE=1 set e.g. mms/macro=TEST_VERBOSE=1 test however, as the test did not run this does not help. However, if you change into the t subdirectory and run the examp test individually the test succeeds. e.g. 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, uncompress and unpack. OOB 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 patch for VMS from the the links at the bottom of this document, apply the patch by hand (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 directory and is called DBD-ODBC-0_28_VMS.PATCH. If you are applying the patch by hand 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 OOB. You do this by defining the ODBCHOME logical as the directory where the OOB client was installed. e.g. suppose you installed OOB in DKA200:[MARTIN.OOB] you need to: define/log ODBCHOME DKA200:[MARTIN.OOB] You also need to define a logical called DBI_DSN which specifies the DBD ODBC driver and the data source name. Create a local OOB client datasource called test in the file []odbc.ini. which points to a remote data source e.g. [test] server = ntserver port = 8888 transport = tcpip targetdsn = ntsystemdsn logonuser = nt_username logonauth = nt_password targetuser = db_username 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 ======== There are some Perl examples in the examples directory. There are some test programs in addition to those mentioned above in the Perl DBD:ODBC distribution in the mytest subdirectory of DBD-ODBC (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. Bound Parameters and MS Access ============================== Perl DBD:ODBC may sometimes use bound parameters but this requires support for SQLDescribeParam. The MS Access ODBC driver does not currently support SQLDescribeParam and so bound parameters must be avoided when using Perl->OOB->MS Access. Useful Links ============ The Perl Home Page - http://www.perl.com Perl Mongers - The Perl advocacy people http://www.perl.org DBI - the database interface module for Perl http://www.symbolstone.org/technology/perl/DBI/index.html Perl DBI-Users mailing list archive http://www.bitmechanic.com/mail-archives/dbi-users/ VMS for Perl - http://www.sidhe.org/vmsperl/index.html Information page for Perl on VMS - http://www.crinoid.com/perl560.htmlx vmsperl mailing list archive http://www.xray.mpe.mpg.de/mailing-lists/vmsperl/ Peter Prymmer's VMS Perl page - http://www.sidhe.org/vmsperl/othervmsperllinks.html The Perl Zone - http://www.nobhead.com/perl/ gunzip for VMS - ftp://ftp.digital.com/pub/VMS/ vmstar - ftp://ftp.digital.com/pub/VMS/ patch for VMS - ftp://ftp.sidhe.org/VMSPerl_Community/non-perl_utils/patch