Aiming for elegance, one thought at a time

Installing Oracle Instant Client (and connecting to Oracle from Excel)

Posted: May 1st, 2010 | Author: | Filed under: IT | 2 Comments »

‘Instant’ probably overstates it somewhat, but the Oracle Instant Client does let you connect to an Oracle database in a reasonably snappy way. It’s pretty straight-forward too, but there are a few hoops to jump through. Here’s how I got it working.

Installing Instant Client

  1. Download the Instant Client. You’ll need the ‘basic’ or ‘basiclite’ packages, and you’ll probably want one of the add-ons, like the jdbc driver (for Java) or the odbc driver. I grabbed the odbc driver, because I’m going to connect via Excel. You’ll need to sign up to the Oracle website to access the downloads. I’ve been a member for a while, and it seems pretty harmless – no spam that I’ve noticed. Once you’ve downloaded the packages, unzip them to the same directory.
  2. The current packages ship without some necessary DLLs, as detail on the OTN Discussion Forum. The missing DLLs are MFC71.dll, msvcr71.dll and MFC71ENU.dll. I believe they’re part of the Visual Studio install, and I had them on my PC, but I needed to drag them into the install directory. If you don’t have them, you can google them (if you’re feeling lucky.) Update: looks like they’ve updated the packages, and you shouldn’t need to track down these dlls any longer.
  3. Place this directory where you want it and run odbc_install.exe. The install adds some registry settings to register to odbc driver, and it points to the driver in the directory you’re using.
  4. Create an environment variable called TNS_ADMIN. The value should be the path to the directory that contains tnsnames.ora, which lets the Oracle driver know what servers are available. Managing tnsnames.ora can be frustrating, especially for the uninitiated (that is, me), and in a subsequent post, I’ll detail how to connect without tnsnames.ora.
  5. You’ll also need to create an NLS_LANG environmental value. Oracle recommends you set this in the registry, but the instantclient doesn’t create the registry structures needed. You could create them, but it’s easier to create the environmental variable. Oracle provides a list of possible values.
  6. You can now connect to your Oracle DB using Microsoft Query.

Update: The promised post to connect using VBA is on it’s way! In the meantime, to connect with Microsoft Query, there’s a few things to be aware of.

Connecting with Microsoft Query

Firstly, your TNS_ADMIN environment variable must point to a valid file – or this won’t work. If you’re connecting to Oracle Express Edition, then you’re tnsnames.ora will look like this:

XE =

If you’re connecting to another Oracle database, you’ll need to find the appropriate tnsnames.ora. It might be under a path like C:/oracle/network/admin/. Without TNS_ADMIN pointing to a directory with a valid tnsnames.ora, you won’t be able to connect using this method.

There are two ways of setting up the connection. The first is directly through Microsoft Excel. The second is through ODBC Data Source Administrator. ODBC Data Source Administrator is probably the better way, but I’ll look at setting it up through Excel first.

New connection through excel

  1. In Excel, start Microsoft Query. In Office 2007, go to the Data ribbon and click on Get External Data -> From Other Sources -> Microsoft Query.
    The Choose Data Source dialog shown in Excel when you choose to import external data using Microsoft Query
  2. Leave <New Data Source> selected and click OK. The Create New Data Source dialogue will be displayed. Enter a name for the data source, and the driver drop-down becomes enabled. Select Oracle in instantclient_11_2 (or similar).
    The Create New Data Source window in Microsoft Query
  3. Click connect. The service name must much a valid service name in the tnsnames.ora. If you’re using the Oracle Express example above (and have installed the Oracle Express client with default settings) this will be XE. The username and password will be whatever you or the sysadmin set.
  4. Click OK. If you cannot connect at this point, but you can connect to the database by other means, it most likely means that your tnsnames.ora is wrong or that TNS_ADMIN is not pointing to the right directory (note that if you change the environmental variable, you’ll need to restart Excel for the change to take effect.)
  5. All being well, you will now be able to select a default table (if you choose to) and use Microsoft Query as you normally would.

Congratulations! You’re now connected to Oracle using Microsoft Query!

Creating the connection in ODBC Data Source Administrator

It’s often easier and more convenient to set up the new data source through the ODBC Data Source Administrator. This way, the new data source will be available whenever you want to use it, rather than needing to recreate it every time.

  1. Open the ODBC Data Source Administrator. This is in Control Panels. Under Windows 7 64bit you’ll need to choose the appropriate version: odbcad32.exe under either system32 or SysWOW64, depending on whether you’re setting up a connection for 32bit or 64 bit applications.
  2. Click Add. The Create New Data Source window appears. Choose the Oracle in instantclient_11_2 driver and click OK.
  3. The Oracle ODBC Driver Configuration page will open. This page gives you far more options and is more intelligent than the equivalent if you create the connection in Excel. The TNS Service Names drop-down box will populate with the databases specified in tnsnames.ora: if no options appear, then either your tnsnames.ora file is invalid, or TNS_ADMIN is not specified correctly. Again, if you change TNS_ADMIN, you’ll need to restart ODBC Data Source Administrator for the change to take effect.
  4. Click ‘Test Connection’. You’ll be prompted to enter a password, and all being well, you’ll get this dialogue:
  5. Click OK in the Data Source Configuration dialogue, and open Microsoft Excel. The new Data Source will appear when you open Microsoft Query.
  6. Click OK and you can use the connection in Microsoft Query as usual.

Still to come…

So that’s two different ways to connect to Oracle in Excel using Microsoft Query. As soon as I have time, I’ll be posting a sample workbook and instructions on how to connect to Oracle using VBA instead of Microsoft Query, which is especially handy if you want to distribute the workbook.

Tags: , , , ,

MySQL, parameterized queries, PHP Data Objects and stored procedure out parameters

Posted: April 4th, 2010 | Author: | Filed under: IT | 1 Comment »

For the first time in a decade, I’m doing some PHP development. That’s scary in itself. The usual thing: connect to a database, get some data, serve up a page. The usual CRUD. I’ve elected not to use a framework because this is a bit of an experimental project and I’m not sure what I need – which makes the choice of frameworks difficult.

So I’m doing the database connection myself. No big deal, but I was surprised to find that the traditional way to handle dynamic queries in PHP is by building your own query string. Naturally, this means that you need to protect against SQL injection attacks yourself. Now, perhaps this is my own fault for not using a framework, but I really don’t want to roll my own SQL injection protection. Thankfully, there’s PHP Data Objects (PDO) which provide parameterized queries – which pretty much come standard in every other language on the planet (including VBA, of all places… technically, it’s standard in the PHP install as well, but I get the impression that it’s not been used traditionally.)

The syntax will be familiar to anyone who’s used parameterized queries before:

// configuration

$dbhost     = "localhost";
$dbname     = "notes";
$dbuser     = "root";
$dbpass     = "password";

// database connection

$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

$subject = $_POST['subject'];
$object = $_POST['object'];
// query
$sql = "CALL SetContent(?,?)";
$q = $conn->prepare($sql);

$sql = "SELECT object from threestore where subject = ?";

$q = $conn->prepare($sql);
$object = $q->fetchColumn();


This is a simplified example, with all non-essential code removed. It writes something to a database and then reads it back straight away: useful, no?

You’ll note that I don’t use a stored procedure to retrieve the object: that’s because MySQL version 5 doesn’t support out parameters properly, as detailed in this bug. The patch is scheduled for version 6, and given that the production release is at 5.1, it’s going to be quite a wait. There’s a few different ways of working around the bug, but I wasn’t that attached to using stored procs at this stage.

Tags: , , , , ,