Welcome to Win32::ODBC
If you have come this far then you probably have run into
problems or confusion about this Perl 5 extension. Here you will not learn how to program
ODBC; you won't learn SQL; you will not learn how to program Perl and you will not learn
how to create Perl 5 extensions. What you will find is help for those commonly
asked questions that you may have regarding the Win32::ODBC extension.
Back to the top 
Open DataBase
Connectivity (ODBC) is an Application Programming Interface
(API) that allows a programmer to abstract a program from a database. When writing code to
interact with a database, you usually have to add code that talks to a particular database
using a proprietary language. If you want your program to talk to an Access, Fox and
Oracle databases you have to code your program with three different database languages.
This can be quite the daunting task causing much grief. Now, enter ODBC...
When programming to interact with ODBC you only need to talk the ODBC language (a
combination of ODBC API function calls and the SQL language). The ODBC Manager will figure
out how to contend with the type of database you are targeting. Regardless of the database
type you are using, all of your calls will be to the ODBC API. All that you need to do is
have installed an ODBC driver that is specific to the type of database you will be using.
NOTE: Win32::ODBC interfaces to the 32 bit versions of ODBC. If you use older 16 bit
versions the results are unpredictable.
Back to the top
There have been a few different versions of the ODBC API that have been released. The
current version is ODBC 3.0. This refers the the revision level of the API. There have
been several changes to the API resulting in versions: 1.0, 2.0, 2.5, 3.0 and now 3.5.
Typically when you install ODBC on your machine you are installing the ODBC Manager.
Win32::ODBC was written to conform to the ODBC API 2.0 specification. Since ODBC 3.0 is
backward compatible it should not cause any compatibility problems. To borrow
from Microsoft's web pages...
ODBC 3.0 is completely backward compatible. When using the ODBC 3.0 driver manager:
- An ODBC 2.x application works with an ODBC 3.0 driver or an ODBC 2.x driver.
- An ODBC 3.0 application works with an ODBC 3.0 driver.
- An ODBC 3.0 application works with an ODBC 2.x driver as long as the application uses
only ODBC 2.x features.
Where necessary, the ODBC 3.0 driver manager performs appropriate function and/or
argument mapping to ensure backward compatibility.
Back to the top
ODBC Manager
Over the years Microsoft has modified what they call ODBC. It used to be that you would
download their ODBC manager. You would then download and install any database specific
ODBC drivers that you need. However times have changed and so has marketing.
Now there are a bunch of different layers of database software. You can find
ADO, RDO, OLE DB and a variety of others. Microsoft has merged all of these technologies into
one nifty installation package called MDAC (Microsoft Data Access Components). Basically it consists of several
components that provide various database technologies; including ODBC. MDAC is a
royalty-free redistributable package that you can install on a Windows machine without
a cost.
You can read more on MDAC packages on Microsoft's web site:
http://www.microsoft.com/data
You can also download the MDAC packages from Microsoft's web site:
http://www.microsoft.com/data/download.htm
They usually list several versions of MDAC that you can download. Unless you
know that you need a particular version (for some reason) they you should
download the latest, most up-to-date version.
Note:
For those not aware of the lingo; the RTM version means Release
To Manufactering and represents the final prodcut. The SPx (as in SP1 or SP2)
are simpy Service Packs which represent bug fixes that build upon the
RTM releases.
If you are planning on programming ODBC in C or C++ then you will need the
MDAC Software Developer's Kit (SDK). This contains the header files and libraries
that your code will need. It is available from:
http://www.microsoft.com/data/download.htm
Others have developed ODBC Managers that run on non-Windows platforms:
The unixODBC Project. This
group's goals are to develop and promote a Linux implimentation of ODBC. This
looks to be a standard for the Linux platform.
IODBC
ODBC Manager for Unix by Ke Jin
<kejin@empress.com>
The FreeODBC project,
hosted by Brian Jepson
Commercial ODBC Drivers
Freeware ODBC Drivers
NOTE: Microsoft's ODBC Desktop Database Drivers 3.0 (32 bit version) --
Don't confuse this with the ODBC API 3.0!! The ODBC Desktop Database Drivers version 3.5
is just a collection of ODBC drivers that Microsoft released. These drivers are for
Oracle, MS Access, Fox, Excel, generic text files, and various other databases:
If you are running NT 4.0 you may want to check
out this
Microsoft
Knowledgebase article: PRA: Problems Running Microsoft Jet 3.0 on Windows NT 4.0.
This is especially important if you are using the MS Access Jet ODBC Driver with the MS
IIS on Windows NT 4.0.
Back to the top
Win32::ODBC is a Perl 5 extension that provides access to the ODBC API. ODBC currently
exists on the Microsoft Windows platforms as well as the Macintosh and Unix. (Refer to
How do I get ODBC?)
Back to the top
The current version of Win32::ODBC (as of this writing) is 970208. This version number
is in international date format (yymmdd). The original build is not compatible with Win32
Perl builds 303 and higher. There have been replacements for the ODBC.PLL file that work
with builds 303 and 304-307. Check the Roth
Consulting ftp site for these replacements. They have names like "ODBC_BUILD_304.ZIP".
Back to the top
Back to the top
No, not yet. However there is a DBI interface for the Win32::ODBC extension. To quote
from the DBI FAQ:
Supplied with DBI-0.79 ( and later ) is an experimental DBI 'emulation layer' for the
Win32::ODBC module.
More information about DBI is available at Alligator Descartes' DBI Page.
Back to the top
Win32::ODBC has been successfully tested on NT 3.51, NT 4.0, Windows 2000,
Windows 95, Windows 98 and Windows ME.
Supposedly any Win32 operating system that can run Win32 Perl should be capable of using
Win32::ODBC.
You will need Win32 Perl from
ActiveState Internet Corp.
Back to the top
Win32::ODBC works with Win32 Perl from ActiveState Internet Corp. It currently supports
builds 106-110 and 303-307.
Note: Builds 304-307 can use the Win32::ODBC for build 304.
Back to the top
When Win32::ODBC is compiled it is compiled to run with a specific build of Win32 Perl.
If ActiveState makes internal changes to Win32 Perl that are not compatible with the
specific build of Win32::ODBC you are using, you will see the infamous "Parse
Exception" error. This simply means that you can not use the particular build of
Win32::ODBC and Win32 Perl. This usually takes place if you upgrade to a newer build of
Win32 Perl.
The solution is to either not upgrade to the newer build of Win32 Perl or
wait for a newer build of Win32::ODBC. You can always recompile it yourself if you want.
Back to the top
Win32::ODBC has become more popular than I ever expected. I occasionally recieve words
of thanks and stories of what others are doing with the extension. Sometimes they are even
offering their contributions back to the perl community. This is a brief list of such
offerings:
Back to the top
You can specify a userid and password when configuring the DSN (check out section E of "How Do I Create A System DSN").
You can also specify a userid/password when connecting to the DSN. This allows you to
create the DSN with no userid/password specified but instead specify it during
runtime. All you need to do is connect to the DSN the way you normally would but specify a
usid and pwd attributes:
$db = new Win32::ODBC("DSN=My DSN;UID=Joel;PWD=Joel's Password;");
Notice that you must use a semicolon (;) between attributes.
Back to the top
A full discussion of ODBC is outside the scope of this FAQ but let's outline some
basics.
First you need to create a DSN (Data Source Name)
which is a name that represents the database file (or connection) and ODBC driver as well
as user id and password.
Second you add the following USE line to the beginning of your Perl script:
use Win32::ODBC;
Third you open a connection to your database with (note that this example checks
for failure):
$DSN = "My DSN";
if (!($db = new Win32::ODBC($DSN))){
print "Error connecting to $DSN\n";
print "Error: " . Win32::ODBC::Error() . "\n";
exit;
}
Fourth you execute your SQL command (NOTE: due to
backward compatibility with NT::ODBC the Sql() method returns undef if it was successful
and a non zero integer error number if it fails):
$SqlStatement = "SELECT * FROM Foo";
if ($db->Sql($SqlStatement)){
print "SQL failed.\n";
print "Error: " . $db->Error() . "\n";
$db->Close();
exit;
}
Fifth you fetch the next row of data until there are no more left to fetch. For
each row you retrieve data and process it:
while($db->FetchRow()){
undef %Data;
%Data = $db->DataHash();
...process the data...
}
Sixth you close the connection to the database:
$db->Close();
That is it! Oh, so easy!
There may be times in which you need to use more of the ODBC API than we discussed
above. The Perl Win32::ODBC extension allows for that. But to do this means that you need
to understand how ODBC works.
Back to the top
Back to the top
When you create a DSN it is created under a particular account, which is not
accessable from other accounts. If you are logged in as Administrator and you create a
DSN called "Foo Database" then only processes running under the
Administrator account can access "Foo Database". If your web server runs under
another account (which is usually the case) like "Web Server" or
"IUSR_MAIN" then you will either:
- Log on as the web server and create an identical DSN "Foo Database".
- Create a System DSN.
There is no way around this. ODBC data source information is placed in the
registry under a particular user's hive. System DSNs are placed in the registry under the LOCAL_MACHINE\SOFTWARE\ODBC
hive, which is accessable to all accounts.
The exception to this is that the ODBC information is usually replicated in an ODBC.INI
file that is located in the \WINNT directory. This is for backward compatibility
with 16 bit windows applications. But since Win32 Perl is a true 32 bit application it
does not use these .ini files.
Back to the top
It is just as easy to create a System DSN as it is to create a regular DSN, except
you need to press one extra button. You need access to the ODBC Administrator program
or you can use the ODBC Control Panel Applet. Once it is running you need
to...
A.
In order to create a System DSN you need to run the ODBC Administrator. At this point,
you need to hit the "System DSN..." button |
|
B.
Here you will find a list of System DSN's. You can either Setup an existing one
(ie. configure the DSN)
or
you can Add a new System DSN. |
|
C.
Select which ODBC driver the DSN will be using. |
|
E.
Give your new System DSN a name in the Data Source Name: field. Then you
need to Select the database file that the System DSN will use.
You can also fill out other info such as UserID, Password, System Database, etc. Consult
the Help file for more details. |
|
...and that is it!
Back to the top
The problem most likely has to do with the ODBC driver. Some drivers have
characteristics that others do not which may leave the user confused.
For example: The MS Access driver (the "Jet Engine") will always
attempt to write a file named the same as your database but with an .ldb
extension. This contains info on who is currently using the database, from what computer,
etc. The Access driver also writes temporary files to perform it's work. If the account
running the script does not have write (or add) permissions on the directory the script is
running from, the Access driver will fail because it can not write these temporary files. (I
wonder how this is supposed to work on a CDROM)
Some of the more common problems I have seen are:
Driver |
Problem |
Solution |
| MS SQL Server |
Can access the DSN but not the database. |
The user account does not have permissions to the database. Have the Admin grant the
proper permissions to the user account needing it. |
| Access |
Can not query the database. |
User account does not have read permissions on the database file or write (or add)
permissions on the directory where the script is run from. |
| Any |
The account has read/write permissions and the System DSN exists, but I am
refused access to the database. |
In the System DSN, you may have selected a Security Database. If so, then you need to
press the Advanced button and configure a username (UID) and a password (PWD) that are
both valid and in the Security Database that you selected. |
| MS SQL Server |
Some fields are returned with the wrong characters or values are incorrect. |
This particular driver converts OEM character set to ANSI character set.
Deselect this option from the DSN's configuration screens. |
Back to the top
There could be hundreds of different reasons why you are getting errors. Win32::ODBC
has been rather well used and tested. We have found some bugs and squashed them (with
pleasure, of course) but the bug reports seem to have tapered off. I suggest that if
you have a what appears to be a bug, check out your code first! It is usually a simple
mistake somewhere.
| |
Error |
Problem |
Solution |
| 1 |
Compile time error:
Can't locate Win32/ODBC.pm in @INC..... |
The Win32::ODBC module (ODBC.pm) is missing. |
Make sure that ODBC.pm is in the Win32 module library directory.
Copy the file into: Perl 5.005 (including ActivePerl):
[perl directory]\site\lib\win32
Perl 5.004 or lower
[perl directory]\lib\win32 |
| The PRIVLIB registry value is incorrectly set. |
Verify that the the following registry key and values are set: For
builds 106-110:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Resource Kit\Perl5
For builds 300-317:
HKEY_LOCAL_MACHINE\SOFTWARE\ActiveState\Perl5
The key should contain a value:
PRIVLIB: RegSZ: C:\PERL\LIB |
| 2 |
Can't load C:\perl\lib/auto/Win32/ODBC/ODBC.pll
or
Can't load C:\perl\lib/auto/Win32/ODBC/ODBC.dll |
The extension library file (ODBC.pll for builds 100-317 and ODBC.dll
for ActivePerl builds 500+ and the core distribution) is unable to be located or accessed. |
Check that you have installed the file:
[perl directory]\lib\auto\Win32\ODBC\ODBC.pll
or
[perl directory]\lib\auto\Win32\ODBC\ODBC.dll
You may need to create the dirctory.Make sure that the file is spelledt correctly. |
| NTFS file permissions are preventing the script from
accessing the extension file. |
Check that you have permissions on the ODBC.pll or ODBC.dll
file. You need at least read access. |
| Can't locate loadable object for module Win32ODBC in @INC
(@INC contains D:\perl\5.00502\lib/MSWin32-x86 D:\perl\5.00502\lib
D:\perl\site\5.00502\lib D:\perl\site\lib) at .... |
| Your machine does not have the 32bit version of ODBC installed (at least
ODBC API version 2.5). |
Make sure you have the 32 bit version of ODBC version 3.0 (compliant with the ODBC API level 2.5
spec) or higher installed. |
| 3 |
Can't locate Dynaloader.pm in @INC at
c:\perl\lib/Win32/ODBC.PM line X. |
Your Perl installation is missing the Dynaloader.pm
module. This is needed to load extension libraries. |
Some versions of pkunzip (and other unzip
programs) do not support long file names. When you unzipped the perl archive it truncated
the Dynaloader.pm name to something like Dnyaload.pm. You could just
rename this file. It should be somewhere in the: [perl directory]\lib
There may be some other files like this that you would want to rename so it may be a
good idea to reinstall Win32 Perl using a version of unzip that supports long filenames. |
| You may need to download and install Dynaloader.pm from CPAN. If
this is the case you should probably just reinstall Perl. |
Back to the top
Driver
|
Description
|
Solution
|
The Oracle version is 7.3.2.1.0.
The Oracle ODBC driver is SQO32_73.DLL - version 1.15.0301. |
Trying to access an Oracle database on the NT server you get a "program exception
error".
You can successfully establish a connection to the database, and can quite happily
retrieve the column names from a table. However, a FetchRow never returns any data, and a
subsequent Close causes the exception error. The "ODBC test connect" program
that comes with Oracle connects quite happily and allows me to retrieve data quite
merrily. |
There is a newer driver available at www.oracle.com.
The Oracle driver from Intersolv
works perfectly with Win32::ODBC and the version of Oracle. |
| Pervasive.SQL 2000 |
SQLExtendedFetch() is not supported (an error is generated whenever $db->Fetch() is called).
|
The pre 19990208 versions of Win32:ODBC use only SQLFetch() and the post 19990208 (currently in beta) version
of Win32::ODBC can use either SQLFetch() or SQLExtendedFetch(). Likewise the DBI version of ODBC (DBD::ODBC)
may work.
|
| Pervasive.SQL 2000 |
Pervasive.SQL 2000 does not provide a mechanism to enable/disable OEM-ANSI conversion inside the DSN
configuration, nor does it install a translation DLL to perform this task. This differs in functionality
from Pervasive.SQL v7.0.
|
Copy the translation DLL, W32BTXLT.DLL, from Pervasive.SQL v7.0 to any directory in
the current PATH on the client machine. It may be copied to the SYSTEM directory on Win95/98 or SYSTEM32 on NT.
After a successful connection to a Pervasive.SQL 2000 data source, the application must make the following ODBC call:
$db->SetConnectOption( $db->SQL_TRANSLATE_DLL, "W32BTXLT.DLL" );
This connect option must be set after every new connection is made.
Example:
SetConnectOption( $db->SQL_TRANSLATE_DLL, "W32BTXLT.DLL );
|
This FAQ has been visited times since July 27, 1997.
