Oracle ADO.Net Providers

Having just posted a piece about issues with deploying ODP.Net and Oracle Client with Winforms applications, I thought I'd best also give a quick summary of why, if it's such a pain to use, I've used ODP.Net at all.

Originally we were going to use Microsoft's intrinsic Oracle provider, located in the System.Data namespace of the.Net framework (System.Data.OracleClient). However this has a number of known issue relating to data types, memory leaks, performance and it is in fact now deprecated. Although it is still present in .Net Framework v4, it hasn't been developed in a number of years and will be removed entirely at some point. You can understand why MS don't want to develop it - why encourage people to use a competitor's database platform?

There are a number of commercially available alternatives, the two best know probably being  devArt's dotConnect For Oracle and Progress Software's DataDirect Oracle Data Provider For .Net. In fact, I originally started use dotConnect and it was pretty easy to get it working on the whole (if I can remember much about it I might put a post up as there were one or two things I had to spend some time working out). I was more or less at the testing stage of the proof of concept application I'd constructed, but as is often the case, it came purely and simply down to cost: dotConnect and DataDirect cost money whereas ODP.Net is free from Oracle and most commercial outfits, when confronted with that sort of choice, will inevitably take the freebie.

There are of course pros and cons for each of them and it is up to you (or your organisation) to decide what fits your needs and budget best, but don't be tempted to use the old .Net framework provider as it is a false economy. Hopefully my posts will help people make decisions and then implement them in much less time than it took me. Of course, please feel free to comment with any corrections, additions and the like. I might even make my proof of concept code available to download as well. It is currently in VB.Net but I might do a C# version as well for completeness, but that's a way off as I'm pretty busy.

The provider is not compatible with the version of Oracle client

I've decided to resurrect this blog and try to add more stuff as I find it. This is because these days I am coming up against more and more things which I've had to spend ages trying to track down the solution to, and it would be nice to think I might be able to help someone avoid the levels of sheer anger I've experienced!

Today we are going to talk about Oracle Client (specifically version 11.1) and it's use in Winforms applications. I've actually got a few things to say about this because I have had to start from a base of zero Oracle knowledge and yet make the entire suite of applications my employer produces (total of perhaps a couple of million LOC) work with Oracle when it has been engineered, not especially well, since the year dot to work with SQL Server. This has been a difficult and often highly frustrating project not helped by Oracle's utter intransigence when it comes to following conventions and standards. I will talk about making the Oracle Data Provider for .Net (ODP.Net) and Oracle Client work with systems that use Enterprise Library for data access abstraction; how to deploy a specific version of Oracle Client and ODP with your application to ensure it works no matter what might be installed on a target machine; things to look out for when converting a system to use Oracle and when converting a SQL Server database to Oracle, and possibly other things as I think of them.

However, today I'm going to talk a little about using the Oracle Instant Client, ODP.Net and how to deploy it with your application. I apologise in advance, but this post will be a little out of sequence because this concerns the deployment of an application that has already been developed, rather than the process of developing it. That will come in later posts.

Oracle Client comes in a confusing array of versions and downloadable options. Essentially, there are five main ways to use the Oracle Client:

1. Use the Oracle Universal Installer and install the whole kit and caboodle to a target machine, which is a huge amount of stuff and requires human interaction.
2. Use what is known as the Instant Client installer and install just the client to a target machine. It's still pretty big and of course requires some human interaction.
3. Use the XCopy deployment of Instant Client. You can easily automate this but it's still pretty chunky.
4. Use the XCopy deployment of the Instant Client Light. Getting there...
5. Use only the libraries you actually need and forget about the rest of the crap that Oracle tries to punt.

Since my goal was to make an existing application (a commercially saleable one: Always bear in mind that stuff written for internal and personal use can get away with being slacker and less polished although I would suggest you get in the habit of always making apps as good as they reasonably can be, within reason, whoever the target audience is. It saves you a ,lot of effort in the long run), we are going to discuss how to make an application work with ODP and Oracle Client in the simplest possible way from the point of view of development and deployment, so we are going to use option 5 from that list. 

There are good reasons for choosing the other options in other circumstances. For instance, you might want to work with all sorts of systems that use a particular version of Oracle and therefore you might decide that as a corporate standard every machine should have a particular version of the full Oracle Client installed and make it part of your standard OS build. That is in fact the very situation I faced as some the client for whom this work was originally done used Oracle Client 10.2 as a standard on their workstations. However, we had to consider that other clients would use other versions of Oracle and since Oracle Client and ODP are very fussy about version numbers we had to consider very carefully what version we wanted to use.

The rule of thumb is that Oracle guarantee that any given Client version will work with any database version within two major version either side. Thus Oracle Client 11.1 will work with Oracle 10.1, 10.2, 11.1, 11.2 and the next major version that comes out, whatever that may be. that's why we chose 11.1, but you should consider you current and future requirements carefully and choose appropriately. In another post I will talk about how to ensure that your application uses the version Oracle Client that you want it to, not the one Oracle thinks you should use.

With regards to the pickiness of Oracle Client and ODP, what I mean is this: You cannot mix and match ANY part of the Client and ODP binaries across ANY version numbers. Always but always make sure you get all the binaries you need from one single Oracle Client download or it simply will not work. In my case (and yours if you simply wnat to get your application to talk to Oracle) the list of binaries required is as follows:

• oci.dll
• orannzsbb11.dll
• oraociei11.dll
• OraOps11w.dll
• Oracle.DataAccess.dll

Note that oraociei11.dll is pretty huge - just shy of 115MB - because it is, I believe, a fully multilingual component. There is a smaller one you can use from the 'light' version of the instant client called oraociicus11.dll which is something like 33MB, but again make sure you get all the components from the same download. If you want the smaller, English-only oraociicus11.dll then download the 'Light' version of the Instant Client XCopy deployment package and get all the binaries from that, otherwise get the XCopy deployment of the full Instant Client and get your binaries from that.

Having done my development and got my build process to work (we use TeamCity continuous integration based on Wix and MSBuild to produce our installers), I wanted to do some unit testing on my work before sending it off to our testing team because I don't want to waste their time with problems I can find and fix before they get it. To do this I use a clean Win7 virtual machine so that none of my development stuff can influence the working of the application. As an aside, NEVER do any functional testing of your stuff on your development machine because there is nothing more annoying than hearing a dev say "It works on my machine" when presented with a bug that has occurred on a machine that doesn't have a million and one things installed on it.

Once installed on my VM, I fired it up and...got an exception, so I looked in the Windows Application Event Log (where we log our exceptions. You are handling and logging your exceptions, right?) and found that I was getting an error which says: 

'The provider is not compatible with the version of Oracle client'

That's odd. I know for sure that I got all my binaries from one Oracle Client download, so what's going on? I checked, re-checked and then checked again that I had the right versions, that I wasn't missing anything and so on. Finally, after much digging and Googling I realised that my VM did not have the Visual C++ 2003 redistributable on it. Of course, my development machine does so it doesn't go wrong at all, but without the file msvcr71.dll Oracle Client will not work. This is because some of the Client components have been compiled against this version of VC++ and therefore naturally they need this in order to work properly. You won't get this error if you used the Oracle installer to install the Client but of course we don't want to do that.

Microsoft recommends that if you need to add this file then you add it into your program directory and NOT into Windows\system32, so make sure that you include the msvcr71.dll binary in your library of dependencies and that your build process includes it. It will do no harm to have it in your program directory even if it has already been installed on the target machine.

Try it on a machine you can break with impunity (I keep one master 'clean' Win 7 VM and simply clone it as needed). Get your app installed and see if you have msvcr71.dll on the machine. Remove it if you do and you'll see the app fail with the error I've described. Add it back in (you kept a copy, yes?) and see it suddenly start working. This cost me nearly 3 days of frustration until I stumbled upon this blog post and a comment, about half way down, from a guy simply called Guilherme which fired a neuron in the back of my brain which reminded me that I'd had this file missing from a VM before and it caused problems.

I may edit this post at some point once I've re-read it to make sure it makes sense and I'll add new posts soon(ish) talking about the other challenges I've had to overcome to get our systems to use Oracle properly.

Decrypting DTSRun

Those of you who still use SQL Server 2000 will probably recognise this little problem. If you create a DTS package and then schedule it, a SQL job is created and the contents of the step will look a little like this:

DTSRun /~Z0x4555AB9D3224BC0C759856EF83AA07080B2...

This is about as much use a chocolate teapot if you want to see what this string actually does or, more commonly, you have inherited such a job and want to know what DTS package it refers to. Ask most SQL bods and you will get one of two answers; either that you can't decrypt it or that you can use a small utility written by some Russian dude. The truth is, though, that you can decrypt it and there is a utility written by some Russian dude out there to do it, but it's almost impossible to find and if you do it will probably be an uncompiled piece of C code which isn't a lot of use to most people.

But don't despair because the real solution is a lot easier than that. Microsoft actually provide the ability to do this with the DTSRun utility itself. It's documented, but very few people have ever read the documentation for this utility it would seem, but one person who did (or perhaps found out from someone else who did) is a chap called James Pua. He posted to the SQL Server Magazine forums with a handy tip about two switches for DTSRun, /!X and /!C which, if used in conjunction with your encrypted string, will decrypt the string and output the result to the clipboard ready for you to paste somewhere for reading. It looks like this in use:

DTSRun /~Z0x4555AB9D3224BC0C759856EF83AA07080B2... /!X /!C

And the output (once pasted into Notepad or similar) will look like this:

DTSRun /S "<server>\<instance>" /U "<uid>" /P "<pwd>" /N "<package name>" /!X /!C

Obviously I shortened the encrypted string (you wouldn't believe how many people say "But I get a longer string and no dots") but that's it. Now you can decrypt those mysterious strings into readable English.

You can find the full documentation for DTSRun on the MSDN.

3 Column CSS Layout with a Fixed Centre Column Width

A little while ago, I had to write an intranet-based interface to allow some of our accoutning users to kick off some of our cube building processes. That in itself was an interesting exercise, but more interesting was the problem of layout. We have a strict corporate style (like most large companies nowadays) which covers the width of the page at the top, but the actual content, well, where do you put that?

There were a number of considerations here. Firstly, the header has a very symmetrical look about it so it made sense to have the content in the middle of the screen. The problem then is "How wide do I make it?" Well, I have a 17" LCD monitor at a resolution of 1280x1024 (anything less looks awful on these cheapo monitors), but there are a lot of people in our organisation who, for some reason, run 800x600 even on 17" CRTs. So, taking into account that the interface itself is deliberately spartan, I plumped for an 800 pixel wide content area in the middle of the screen.

Naturally, I wanted to do this using divs rather than tables but the problem I found was that every 3 column layout I found used two fixed width outer containers and a fluid center column which was exactly the opposite of what I wanted. I also wanted a technique that was cross browser compatible. Eventually, after a lot of searching, I came accross a blog post by a chap called Alessandro who had done just what I wanted. As a result, I tried it out and sure enough it worked a treat in both IE and FireFox. I'm sure I'll be lambasted for it but I couldn't be bothered to check Opera et al, mainly because no one in the company uses it nor will they as access is through thin client and Citrix for most users, precluding the possibility of choosing their own browser. So, I thought I'd provide a practical example to go along side Alessandro's original concept code.


Here's the html:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
    <head>
        <meta http-equiv="Content-Style-Type" content="text/css">
        <link href="common800.css" type="text/css" rel="stylesheet">

    </head>
    <body>
        <div id="container">

        <!-- Place page header/branding in here -->

        <div id="leftContainer">
            <div id="padLeft">&nbsp;</div>
        </div>

        <div id="leftText800">
            <!-- Enter page contents in this container-->

        </div>

        <div id="rightContainer">
            <div id="padRight">&nbsp;</div>
        </div>

        </div>
    </body>
</html>

And here's the css:

div#leftContainer
{
    float:left;
    margin-left:-400px;
    width:50%;
}

div#padLeft
{
    margin-left:400px;
}

div#leftText800
{
    float:left;
    width:800px;
}

div#rightContainer
{
    float:right;
    margin-left:-415px;
    width:49.9%;
}

div#padRight
{
    margin-left:415px;
}

Unique ID's in ASP

Having recently re-vamped the style of our pitiful corporate intranet without altering the content beyond trying to make the code conform to W3C standards a little better, I got an email from one of our (less than useful, it must be said) project managers. She complained that when a user opened an 'e-form' (built using an utterly crap product called EIM), it launched in a new window, which was all well and good, but if they tried to open the guide on how to use this overly-complicated form at the same time, it opened in the same window as the form thus preventing you from seeing and using the form (or vice versa depending upon which order you clicked the links). I must admit that being from this particular PM, I was about to ignore the complaint completely when I suddenly realised it was a perfectly valid point, so I looked into it.

It seemed that the original author had written all the links (or written scripts to generate the links from document names in some cases) using the target property in the anchor tag and had set the value to _new, thus:

<a href="http://www.somesite.com" target="_new">A Link</a>

However, when you opened a new window by clicking on a link, _new is the assigned ID of the window, so the next link with a target of _new would end up being opened in that same window thus nobbling whatever you were viewing. The solution is to have a unique target reference for each link. But how do you guarantee a totally unique ID for each link with minimum effort, especially when many of the links are created dynamically? Step forward the GUID (Globally Unique IDentifier). I found a snippet which allows you to generate a system GUID very simply in ASP as follows:

<%
    Function CreateGUID
        Dim objGuid
        Dim sGUID
        Set objGuid = Server.CreateObject("scriptlet.typelib")
        sGUID = objGuid.guid
        Set objGuid = Nothing
    End Function
>%

Great. Except there was a problem. Every time I used my returned value, when the page rendered, chunks of HTML which should have been written out by the VBS code after the link were missing. Eventually, I happened into a solution, but I had no idea why it worked. After much searching, I discovered that everyone else was using the same solution as me but with no explanation.

Eventually I found an answer on webmaster-talk.com from a chap called Tommy Hanks who had had the same problem and asked the same question. He discovered that the .guid method adds an ASCII 0 to the end of the string which is interpreted as a string terminator. This completely nerfs anything after it. Quite why the hell it's there, I don't know but the solution is as follows:

<%
    Function CreateGUID
        Dim objGuid
        Dim sGUID
        Set objGuid = Server.CreateObject("scriptlet.typelib")
        sGUID = Left(objGuid.guid, 38)
        Set objGuid = Nothing
    End Function
>%

Notice the addition of the Left function to the objGuid.guid call which gives you the GUID itself and leaves off the annoying string terminator. Now, the links look like this in the source code:

<a href="http://www.somesite.com" target="<%=CreateGUID%>">A Link</a>

Which, when rendered, gives you:

<a href="http://www.somesite.com" target="{F2E09822-A527-406A-B11D-014C98B703C5}">A Link</a>

Thereby guaranteeing that each link will open in it's own window. Et voila - unique ID's in ASP.

Column Widths When Attaching Data in xp_sendmail

A colleague asked me for some help with a problem she was having whilst using the extended procedure xp_sendmail. She wanted to use xp_sendmail to mail the results of the system stored procedure sp_helpindex so that certain people could be aware of what indices were present on a table. The problem was that the data coming out had enormous amounts of white space after it no matter how many LTRIMS or RTRIM's you used which meant that it wouldn't present very well in an email. Now, although I use xp_sendmail a fair bit, I rarely use it for executing queries and sending back data. In the end, I came up with a dodgy looking hack that more or less sorted us out for the time being but is distinctly inelegant.

Basically, I created a wrapper SP for sp_helpindex like so:

CREATE PROC dbo.MyProc


AS

    SET NOCOUNT ON


    CREATE TABLE ##idx_temp (
        [index_name] SYSNAME,
        [index_description] VARCHAR(210),
        [index_keys] NVARCHAR(2078)
    )


    INSERT INTO ##idx_temp

    EXEC sp_helpindex 'tablename'

    SELECT CONVERT(VARCHAR(100), index_name), CONVERT(VARCHAR(100), index_description), CONVERT(VARCHAR(100), index_keys)

    FROM ##idx_temp


    DROP TABLE ##idx_temp


    SET NOCOUNT OFF


GO

It's not pretty: is simply limits the column widths to 100 characters (orwhatever your VARCHAR width is set to) but it was an improvement. If anyone knows of a better way of doing this (and I refuse to believe that there isn't) then please, let me know!

Geeky Developer Tips You Might Find Useful

Or you might not. I'm not bothered either way, but in the course of my daily grind as a developer working with SQL Server, VB6, VB.Net, ASP and the like, I often come accross oddities and sometimes a frustrating lack of any information on problems I'm having writing a piece of code or installing something. It occurred to me that I ought to document them, mainly to help myself, but also, in the spirit of fraternal brotherhood, to help all those other poor buggers who are having the same or a similar problem.

We're going to start with two nice little beauties, one of which didn't get entirely solved to my xsatisfaction (but was close enough that I couldn't justify any more time on it) and another which took me 2 days of buggering about to sort out because I couldn't find any clear explanataions of what I was trying to do.

If this helps only one person then at least it has done something useful. If you have any tips you want to submit then leave a comment and I'll see what I can do.

Cheers

Monkey Boy