Tuesday, January 31, 2006

Oracle bind variables in C# (more bad examples)

I knew there was a reason why I hate developing code targeted for and using Microsoft objects.

A little background before I continue. I work for an employer that has an ERP system and other systems back-ended by Oracle database. We are being asked to start developing in Microsoft's C# language using Visual Studio 2005 and the .Net 2.0 framework. I have a little C and a little Java experience, a little more JavaScript experience and a decent amount of Oracle programming experience (growing every day, yay). The book I have, "Programming C#, 4th Edition", has minimal but decent examples and notes for developers transitioning from C/C++, Visual Basic and/or Java. My first task was to get a simple console application built to read the database.

Step One: Read the Database

Actually this wasn't too hard after I figured out when developing C# applications using ADO.NET you must add a reference to the Oracle client or you will get an error when you build and no Oracle specific references will appear in the drop-downs while you code. A Google search helped me on that one.

Step Two: Use Bind Variables on the Same Query

This task seemed simple enough; use the .Parameters.Add(variable, type, size, value) for each parameter in a query like "select * from schema.table where column between :lovalue and :hivalue". It's not that simple. To make a long story short: It took three hours after going through Oracle's site, Google, and searching through the local MSDN before I found the code I needed in the OracleParameter remarks. Here is the code:

oraCommand.Parameters.Add(":lovalue", OracleType.VarChar , 2).Value = "AA";

For the love of what's good in this whole damn universe would it help to actually have a decent, working piece of example code? I've bitched about this before and it is still just as tiring and frustrating and makes me want to gouge my damn eyeballs out with the serrated edge of my tape dispenser.

You know how it works in COBOL? The same way it has for years and years and years:

EXEC SQL Select column into :ws-column from schema.table were column between :ws-lo and :ws-hi
END-EXEC.

Bloody'ell. Can someone tell me why using about fifty statements (or more if you encapsulate database crap into a separate class) to read a damn table is better than a simple and standard two-line EXEC interface?

No comments: