Everyone play nice: BOLT, OLEDB, Oracle, 64-bit Windows
With 64-bit machines becoming more and more prevalent, we’ve seen some confusion popup around installing and using applications. Is the app 32-bit? 64-bit? which database provider do I use? Is my web app 32 or 64 bit? etc.
It’s confusing. No doubt.
tl; dr
Short answer: If you’re on a 64-bit machine, and using Oracle, use the 64-bit Oracle provider for OLE DB for BOLT.
64-bit Windows explained
There’s a great post from last year that “explains 64 bit”:
Programs are still in the same place, in %ProgramFiles%, unless you need the 32 bit version, which is in %ProgramFiles(x86)%, except on a 32 bit machine, where it’s still %ProgramFiles%.
All those dll’s are still in %SystemRoot%System32, just now they’re 64 bit. The 32 bit ones, they’re in %SystemRoot%SysWOW64. You’re with me so far, right? Oh, and the 16 bit ones are still in %SystemRoot%System – moving them would just be weird.
Registry settings are in HKLMSoftware, unless you mean the settings for the 32 bit programs, in which case they’re in HKLMSoftwareWow6432Node.
So the rule is easy: stick to the 64 bit versions of apps, and you’ll be fine. Apps without a 64 bit version are pretty obscure anyway, Office and Visual Studio for example[1]. Oh, and stick to the 32 bit version of Internet Explorer (which is the default) if you want any of your add-ins to work. The ‘default’ shortcut for everything else is the 64 bit version. Having two shortcuts to everything can be a bit confusing, so sometimes (cmd.exe) there’s only the one (64 bit) and you’ll have to find the other yourself (back in SysWOW64, of course). And don’t forget to ‘Set-ExecutionPolicy RemoteSigned’ in both your 64 bit and 32 bit PowerShell environments.
Always install 64 bit versions of drivers and stuff, unless there isn’t one (MSDORA, JET), or you need both the 32 bit and 64 bit versions (eg to use SMO / SqlCmd from a 32 bit process like MSBuild). Just don’t do this if the 64 bit installer already installs the 32 bit version for you (like Sql Native Client).
Anything with a ‘32’ is for 64 bit. Anything with a ‘64’ is for 32 bit. Except %ProgramW6432% which is the 64 bit ProgramFiles folder in all cases (well, except on a 32 bit machine). Oh and the .net framework didn’t actually move either, but now it has a Framework64 sibling.
Yep. Confusing.
But back to the topic at hand…
If you’re on 32-bit Windows, life is pretty easy. But if you’re on 64-bit Windows, things can be a bit confusing.
This post will hopefully clear up some of the 64-bit Windows confusion, especially around Oracle database providers.
OLEDB providers for Oracle
There are a couple of different OLEDB client providers for Oracle. One is from Microsoft, and one is from Oracle.
The Microsoft one is the Microsoft OLEDB Provider for Oracle. In an OLEDB connection string, it looks like: Provider=MSDAORA.1
The Microsoft provider is only available in a 32-bit version.
The Oracle one is the Oracle Provider for OLEDB. In an OLEDB connection string, it looks like: Provider=OraOLEDB.Oracle or Provider=OraOLEDB.Oracle.1
The Oracle provider is available in both 32-bit and 64-bit versions.
Installing the Oracle Provider for OLEDB
The Oracle Provider for OLEDB is installed using the Oracle Universal Installer. It’s common to just install the Oracle client software, but this doesn’t necessarily install the OLEDB provider. On Oracle 11, as an example, it’s an optional component that needs to be installed.
Keep in mind – there are Universal Installers for both 32-bit Windows and 64-bit Windows!
Oracle Downloads: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
In the Oracle Universal Installer, select Custom as an Installation Type:
Then, under Oracle Windows Interfaces, select Oracle Provider for OLEDB:
I actually selected all of the Oracle Windows Interfaces, except for the Oracle Services for Microsoft Transaction Services, as I know I don’t do MTS stuff.
I also selected the Oracle Client option, including Oracle Database Utilities.
That will install the Oracle Provider for OLEDB.
As I mentioned earlier, there are Universal Installers for both 32-bit Windows and 64-bit Windows.
If I was a customer running 64-bit Windows, I would just install the 64-bit Oracle stuff.
But because I do development, and was trying to understand all of this better, I repeated the above steps with both installers, so I ended up with Oracle for both 32-bit and 64-bit on my machine.
I installed the 32-bit Oracle stuff into c:oracle_32, and the 64-bit stuff into c:oracle_64.
No problem – they can both co-exist.
TNSNAMES.ORA
The TNSNAMES.ORA file contains the aliases and connection information that allows you to connect to remote Oracle databases.
You can use the same file for both 32 and 64 bit, but a copy of the file should exist under oth the 32-bit and 64-bit install directories.
For me, I have two:
- C:oracle_64product11.1.0client_1networkadmintnsnames.ora
- C:oracle_32product11.1.0client_1networkadmintnsnames.ora
So I can connect whether I’m running 32 or 64 bit apps.
BOLT
BOLT is a web application. A web application belongs to an application pool. The application pool is configured for 64-bit by default. But you can configure your application pool to support 32-bit applications.
From within IIS Manager, click on Application Pools, click on your bolt application pool, then click on Advanced Settings.
You now have the option to support 32-bit applications or not:
UDL Files
BOLT uses a UDL (Universal Data Link) file to configure a database
provider and login credentials. It’s a simple UI for configuring and testing database connections.
Opening UDL file in 64-bit mode
To open a UDL file in 64-bit mode, simply double-click it from Windows Explorer.
You should see the Oracle Provider for OLE DB available:
Opening UDL file in 32-bit mode
To open a UDL file in 32-bit mode:
1. Open a Command Prompt (64-bit, which is the default)
2. cd to the directory where your udl file exists. Example:
DOS>cd c:appsdovetailbolt
3. Use the following command to open the UDL file:
C:Windowssyswow64rundll32.exe "C:Program Files (x86)Common FilesSystemOle DBoledb32.dll",OpenDSLFile bolt.udl
You should see the Microsoft OLEDB Provider and the Oracle Provider both available:
So, what works and what doesn’t?
The following table summarizes what works and what doesn’t:
|
Provider: Microsoft OLEDB: |
Provider: Oracle OLEDB: |
|
| 64-bit application (App Pool configured to not support 32-bit applications) |
Does not work |
Works |
| 32-bit or 64-bit application (App Pool configured to support 32-bit applications) |
Works |
Works |
Recommendations
If you’re on a 64-bit machine, and using Oracle, use the 64-bit Oracle provider for OLE DB for BOLT. It allows you to keep everything 64-bit. The Oracle provider also handles CLOBs, which the Microsoft provider doesn’t.
In your BOLT application pool, leave Enable 32-bit applications set to False.
Edit your bolt.udl file in 64-bit mode – meaning just double-click it from Windows Explorer.
Postlude
I know there’s a lot of information here, but hopefully this will clear up some confusion around Oracle connectivity using OLEDB on 64-bit Windows.
Whew!
