Notes Client and Oracle ODBC on Windows 64bit

HCL Notes/Domino 8.5 Forum (includes Notes Traveler)

Subject: Notes Client and Oracle ODBC on Windows 64bit

Feedback Type: Comment

Product Area: Notes Client

Technical Area: Application Development

Platform: Windows

Release: 8.5

Reproducible: Always

Hi all

I'm leaving this post in the hope that it helps someone else.
All our corporate applications are distributed as MSI's via active directory group policy, including Lotus Notes, the Oracle Instant client (10.1) and an Oracle ODBC data source to connect to our backend Oracle system. This means that I can plug any XP machine on to the network, join it to the domain and it will receive all the apps the user will need to get going with.

For the last few days I've been working a Windows 7 64bit machine in to our Windows 2000 domain, checking all applications run fine and verifying the build process. Everything was going quite well after installing XP mode (WOW64, very cool) until I started testing the Notes client. All the applications I had tried seemed to work fine except the first that made use of ODBC to query an Oracle database. At the point of ODBC access, I got

<Data Access Application Layer>Could not execute @db function

The code in this particular application had been written years ago and was well tested so I was quite happy to believe there was nothing wrong with the code and it was more the machine. The first place to look was the Oracle Instant Client (10.1 32bit) that was installed on the machine.
Launching SQLPlus did indeed show that there was a minor connection fault. Resolved that quickly enough, and tried again, no joy.
Then I thought perhaps the ODBC DSN had not registered properly. On launching the ODBC Administrator, I found not only was the DSN not installed, the ODBC Driver wasn't installed either. Shock-Horror

It turns outh there are two versions of the ODBC Administrator in Windows 7 64bit, one for 32bit, one for 64bit. The one launched from the control panel is the 64bit version.

Since Lotus Notes launches as a 32bit process, it requires a 32Bit ODBC driver. So we launch the 32bit ODBC Administrator, which can be found in

%WINDIR%\SysWOW64\odbcad32.exe

By the way, don't be fooled by the filename, the 64bit edition is the same filename, but in a different location

%WINDIR%\odbcad32.exe

So launcing the 32bit ODBC Administrator showed both the DSN & driver installed correctly... huh.. OK, so know I realise that Windows 7 isn't supported on 8.5.0, but is in 8.5.2 (actualyl from 8.5.1 FP1). Upgrade to the 8.5.2 install, still not working.

Now I'm getting frustrated. After nearly 2 days of messing about, the damn thing still isn't working. Now I start resorting to SQL tracing (in the ODBC administrator, against the driver) to see if Oracle is throwing an error that isn't rising to the dialog box. Sure enough there is

authenticate bc0-9f0 EXIT SQLDriverConnectW with return code -1 (SQL_ERROR)
HDBC 0x0474F0E0
HWND 0x001602DA
WCHAR * 0x742B8BB4 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x742B8BB4
SWORD -3
SWORD * 0x00000000
UWORD 1 <SQL_DRIVER_COMPLETE>

DIAG [08004] [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specified
(12154)

I came across an article that mentioned the Oracle drivers being loaded from a folder containing round brackets in the file path isn't supported. But mine don't, my oracle instant client is installed in to

c:\oracle\ora10\

However, this can easily be verified. I launched the SQLPLUS utility to verify everything was still working there, and it was. So I made a copy of the instant client folder to

c:\oracle\ora10()\

And in a command prompt, changed to this new folder, and tried to run the SQLPLUS command again, and suprise suprise, it failed with the 12154 error. Excellent, getting somewhere at last.

Now it hits me, when Lotus Notes is installed (by policy or otherwise), it's getting installed as a 32bit app on a 64bit OS. Microsoft have very kindly created registry redirection for such applications so that they perceive a sub-section of a registry differently from the 64bit processes also running in the o/s. That's where the "Program Files (x86)" folder comes in. This is the redirected name for the "Program Files" folder so that 32 & 64bit applications are segregated.

After uninstalling and reinstalling Notes 8.5.2 manually to a folder without round brackets ANYWHERE in the filepath, I finally managed to get the ODBC connectivity to Oracle working again.

The end
-------

Feedback number WEBB8EXLDZ created by ~Yentl Ektootexoden on 03/14/2011

Status: Open
Comments: