Win32::ODBC
FAQ
This page is from : http://www.roth.net/odbc/faq/
(almost everything you wanted to know but didn't know that you
wanted to know)
Check out our new book!
Win32 Perl Programming: Standard
Extensions
You can also check out our recommended
reading list.
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.
List Of Topics:
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. |
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, and now 3.0.
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.
ODBC Manager
ODBC Drivers
Several companies have developed ODBC drivers that provide access to various
databases. A brief list of companies who offer ODBC drivers are:
And there are even some freeware drivers...
...if you have other Freeware drivers email me and let me know...
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:
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?)
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".
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.
Win32::ODBC has been successfully tested on NT 3.51, NT 4.0,
and Win 95. 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.
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.
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.
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:
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.
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.
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.
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!
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. |
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.
| Problem |
Solution |
Compile time error:
'Can't locate Win32/ODBC.pm in @INC at test.pl line 17' |
You are missing the ODBC.PM file from the
[perl directory]\lib\win32\ directory.
or
Your registry is not set correctly. You need the key:
For builds 106-110: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Resource Kit\Perl5
For builds 300 and higher: HKEY_LOCAL_MACHINE\SOFTWARE\ActiveState\Perl5
to contain a value of:
PRIVLIB: RegSZ: C:\PERL\LIB |
| Can't load 'C:\perl\lib/auto/Win32/ODBC/ODBC.pll' |
Check that you have installed the file:
[perl directory]\lib\auto\Win32\ODBC\ODBC.pll
You may need to create the dirctory.
Check that you have permissions on the ODBC.pll
file. You need at least read access.
Make sure you have the 32 bit version of ODBC
version 3.0 or higher installed. |
| Can't locate Dynaloader.pm in @INC at c:\perl5\lib/Win32/ODBC.PM line
X. |
It sounds like you installed Win32 Perl with pkunzip or some
other unzip program that does 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 perl\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. |
|
|
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. |
This FAQ has been visited 94,983 times since
July 27, 1997.
Last updated 99.01.12
