An Empty String Is Not Null

Why Oracle persists in this ridiculously bad design decision is beyond me. An empty string is a STRING of length 0. NULL is not a string at all. Forget philosphical arguments, it doesn’t help in any way to fail to distinguish ” from null. Whoever decided this obviously never had to write any code to work with recordsets returned from the database. I end up putting a single space where I really just wanted an empty string.

Enterprise Library Logging to Oracle Database

When I first needed to log to an Oracle database from the Enterprise Library, I expected it to be a simple matter of running a script against my Oracle database and adding a few lines to my app.config. Thanks to bad decisions on the part of the Enterprise Library designers and problems with ADO.NET, I had to create my own script to create the necessary tables and stored procedures *and* create two classes that derived from two existing classes in Enterprise Library. This post applies to the Jan ’06 version of the Enterprise library. I have not had a chance to check the Apr ’07 edition to see if they now support Oracle directly.

When I first embarked on this incredibly long journey, I looked at the script included in the EntLib project that creates the tables. Unfortunately, it’s T-SQL specific. So, I converted the script to work with Oracle. That wasn’t so bad. I did have to put “timestamp” in double quotes in my Oracle version of WriteLog since its actually an Oracle datatype. But I was able to invoke my procedure directly using PL/SQL code and I foolishly assumed that EntLib would similarly be able to do so from it’s existing FormattedDatabaseTraceListener.

I fired up my test app and clicked the “Log” button that invoked the EntLib logger. Then I looked at the log table but there were no new entries. I added a flatfile listener and ran the app again. This time I was able to see that the EntLib logging system was trying to write logs out to my oracle database, but was getting the following error (which it kindly logged to the trace file):

PLS-00306: wrong number or types of arguments in call to ‘WRITELOG’

So, I double, triple, quadruple check the order, type, and name of the parameters I am passing and everything looks perfect. Finally, I break down and copy the code that invokes the stored procedure from the EntLib class and add an “Exec Stored Proc” button that will allow me to call the stored procedure directly. I get the same error. Now, I am puzzled. I can invoke this procedure from Pl/SQL but I can call it from ADO.Net. Out of desperation, I change the name of the “timestamp” parameter to TimeOf, recompile the stored proc, and things start working. Apparently, ADO.NET/Oracle cannot handle the parameter with the same name as a reserved word.

Now that I know what the problem is, it’s not very easy to fix. I have to create my own dll that extends the Enterprise Library just to change the name of one parameter in a stored procedure call.

Now that you know the background of why its not as easy as just modifying app.config and running a sql script, you can fully appreciate the attachmdents to this post. I will provide an overview of the importants steps of getting this working in your own project.

First, you will need to add references to the following EntLib dlls in the project in which you want to use the BlueCorner.OracleLogSink dll:

Microsoft.Practices.EnterpriseLibrary.Data
Microsoft.Practices.EnterpriseLibrary.Logging
Microsoft.Practices.EnterpriseLibrary.Logging.Database

Second, you will need to modify your app.config file. You can view the attached project for all the details, but the only-non-intuitve/non-standard part is the type reference:

<add databaseInstanceName="LoggingDb" writeLogStoredProcName="pkg_logger.WriteLog"
addCategoryStoredProcName="pkg_logger.AddCategory" formatter="Text Formatter"
listenerDataType
="BlueCorner.OracleLogSink.FormattedOracleDatabaseTraceListenerData, BlueCorner.OracleLogSink"
traceOutputOptions="None" 
type="BlueCorner.OracleLogSink.FormattedOracleDatabaseTraceListener, BlueCorner.OracleLogSink"
name="Database Trace Listener" 
/>

Note the “listenerDataType” and “type” attributes. When I first attempted this, I thought I could reuse the FormattedDatabaseTraceListenerData for the listenerDataType because I was not altering the structure of the configuration section, just the implementation of FormattedDatabaseTraceListener. Unfortunately, this did not work. I had to make a copy of FormattedDatabaseTraceListenerData and the only difference was the name of the class and the referenced classes.

Third, just add a reference to BlueCorner.OracleLogSink either as a project or as a built dll.

Fourth, run the attached database script against your database and alter the connectionString section of your app.config appropriately.

I’m not covering any standard connectivity steps like editing tnsnames.ora, etc. If you need help getting ADO.net to talk to your oracle schema you will need to look elsewhere.

Here are the working demo project and the sql script you will need. Enjoy.

Showing Off My Son’s Artwork: Demonhunter

Here’s a post solely to show off my talented son Alex’s artwork.

Demon Hunter

View Encrypted HTTPS Post/Form Data

If you are ever stuck having to scrape data from an unfriendly web source that uses forms instead of query data, having an HTTP sniffer is invaluable. I used to use Fiddler, but it didn’t support HTTPS. Now I use, Tamper Data, a Firefox add-on. It works great.

Databinding Magic with Dynamic PropertyDescriptors and Anonymous Methods

At the end of my post, Implementing ITypedList for Virtual Properties, I mentioned the possibility of having one, very-versatile, subclass of PropertyDescriptor that could handle all of your virtual property databinding needs. I was implementing ITypedList again and I didn’t want to write yet another set of type-specific PropertyDescriptor subclasses so I knocked this out. Everything went really well until I was creating anonymous methods inside of a foreach loop. Be sure to see the caveat below on this before you try to use this class. Without further ado, here is DynamicPropertyDescriptor (props to manoli.net for providing the code formatter I used in this post):

public delegate object DynamicGetValue(object component);
public delegate void DynamicSetValue(object component, object newValue);

public class DynamicPropertyDescriptor : PropertyDescriptor {
    protected Type _componentType;
    protected Type _propertyType;
    protected DynamicGetValue _getDelegate;
    protected DynamicSetValue _setDelegate;

    public DynamicPropertyDescriptor(Type componentType, string name, Type propertyType, 
        DynamicGetValue getDelegate, DynamicSetValue setDelegate)
        :
        base(name, null) {
        _componentType = componentType;
        _propertyType = propertyType;
        _getDelegate = getDelegate;
        _setDelegate = setDelegate;
    }

    public override bool CanResetValue(object component) {
        return false;
    }

    public override Type ComponentType {
        get { return _componentType; }
    }

    public override object GetValue(object component) {
        return _getDelegate(component);        
    }

    public override bool IsReadOnly {
        get { return _setDelegate == null; }
    }

    public override Type PropertyType {
        get { return _propertyType; }
    }

    public override void ResetValue(object component) {
    }

    public override void SetValue(object component, object value) {
        _setDelegate(component, value);
    }

    public override bool ShouldSerializeValue(object component) {
        return true;
    }
}

I will reuse the Person and related classes from the prior post. This time, I will demonstrate an actual crosstab with editable values by adding an “hours worked” array. I will also include a running solution at the end.

Here’s the upated Person Class:

public class Person {
    protected string _firstName;
    protected string _lastName;
    protected string _midName;
    protected DateTime _dob;
    protected decimal[] _hoursWorked;

    public Person(string firstName, string lastName, string midName, DateTime dob) {
        _firstName = firstName;
        _lastName = lastName;
        _midName = midName;
        _dob = dob;
        _hoursWorked = new decimal[7];
    }
    public string FirstName {
        get { return _firstName; }
    }
    public string LastName {
        get { return _lastName; }
    }
    public string MiddleName {
        get { return _midName; }
    }
    public DateTime DateOfBirth {
        get { return _dob; }
    }
    public decimal this[int day] {
        get { return _hoursWorked[day]; }
        set { _hoursWorked[day] = value; }
    }
}

The only change is the addition of the _hoursWorked member and the indexor property to allow getting and setting the values of _hoursWorked. Now to the good stuff. The method I demonstrated in the last post required me to create new subclasses of PropertyDescriptor for each type of component I wanted to work with. DynamicPropertyDescriptor uses anonymous methods to provide a reusable implementation of the PropertyDescriptor class. Here’s what the new view code looks like:

public class PersonFullNameAgeHoursView : IPersonViewBuilder {
    public PropertyDescriptorCollection GetView() {
        List<PropertyDescriptor> props = new List<PropertyDescriptor>();

        props.Add(new DynamicPropertyDescriptor(
            typeof(Person),
            "FullName",
            typeof(string),
            delegate(object p) { 
                return ((Person)p).FirstName + " " 
                    + ((Person)p).MiddleName + " " 
                    + ((Person)p).LastName; },
            null
        ));

        props.Add(new DynamicPropertyDescriptor(
            typeof(Person),
            "Age",
            typeof(string),
            delegate(object p) {
                return DateTime.Today.Year - ((Person)p).DateOfBirth.Year;
            },
            null
        ));

        for ( int day = 0; day < 7; day++ ) {
            // without this variable declared inside the loop, 
            // the anon methods won't work correctly
            // they will all reference the value of the 
            // foreach loop variables as set on the last iteration
            int bindableDay = day;
            props.Add(new DynamicPropertyDescriptor(
                typeof(Person),
                Enum.GetName(typeof(DayOfWeek), day),
                typeof(Decimal),
                delegate(object p) { 
                    return ((Person)p)[bindableDay];
                },
                delegate(object p, object newPropVal) { 
                    ((Person)p)[bindableDay] = (decimal) newPropVal; 
                }
            ));
        }


        PropertyDescriptor[] propArray = new PropertyDescriptor[props.Count];
        props.CopyTo(propArray);
        return new PropertyDescriptorCollection(propArray);
    }
}

Pay very close attention to the bindableDay variable. This whole thing would have been a piece of cake if not for that little issue. If you want to have a deep understanding of the issue, take a look at this post that explains how anonymous methods are implemented and this post that details the issue of generating anonymous methods that reference local variables in a loop.

Here’s the result of binding PersonCollection to a DataGridview (note that I typed the hour values into the grid, they did not come from the source):

Demo Output

Click here for a complete VS2005 solution.

I haven’t done any performance comparisons between using this method versus type specific subclasses other than to see that was “fast enough” for what I was using it for. However, in performance critical scenarios, you may want to go with the other method.

Why is Posting Source Code in a Blog So Hard?

I am really starting to hate HTML and markup even more than I already did. Can there be anything worse than trying to post a mix of regular writing and c# source code that contains generics? When I use the regular WordPress (online) editor in standard mode, it completely mangles my source code every time I save, even if I didn’t change anything. When I use it in block mode, I have to remember to escape greater than and less than, etc. I’ve searched for and tried several blog editing tools (BlogDesk, ScribeFire, etc.) and none of them can handle a mix of source. I see plenty of blogs out there with a nice mix of regular writing and source code all looking great. How do you do it?

P.S.: WordPress has added the “” metatag that solves this problem nicely. Remove the space between source and code.

Cross Joining a Table Created on the Fly Using Union All

Today I was working on a report to reconcile data between two systems that will be used to validate parallel testing as we work to move the new system into production. I mention this to provide you with some context around the two techniques I employed in one of the SQL queries I wrote that is the basis of this post. Sometimes it’s a very fine line between being clever and being obtuse, especially when you are writing SQL. Since this report will become obsolete once the new system goes to production and I will most likely be the only person that has to maintain it for a short period of time, I look at these techiques as more clever than obtuse in these circumstances.

Creating and dropping temp tables in batches (not stored procs) can be a hassle in SQL Server T-SQL. In Oracle, you only have global temporary tables and you have to deal with the select limitations of PL/SQL. Sometimes, you need a table but you don’t want to explicitly create it or you can’t because you are querying another system over which you have no control. You can create the table “on the fly” using a temporary view and the UNION ALL clause. For example, I frequently need to transform a block of time into hourly blocks and I don’t have a permanent table available and I don’t want to deal with temp tables. I can create the table I need on the fly like this:

(SELECT Hour = 0 UNION ALL SELECT Hour = 1 UNION ALL ... SELECT HOUR = 23) H

In Oracle, you would have to specify the FROM clause (good ol’ DUAL). You also can’t use the = construct but you get the idea. In my experience the performance of this “table on the fly” is pretty good. Obviously, if you are working in a stored proc, you can create a temp table in T-SQL or a TABLE varable in PL/SQL to achieve the same result. This technique is nice when you can’t use a stored procedure. It’s also useful if you only need a few rows once (say four or less). With less than four rows, it’s easier to create the table using a temporary view of UNION ALLs than it is to create the temp table or table variable.

Generally, you want to avoid CROSS JOINs (aka the dreaded Cartesian Product). However, there are many legitimate uses of CROSS JOIN. First, let me state that CROSS JOIN provides one of the most compelling arguments for using the ANSI-92 join syntax. If you see “CROSS JOIN” in a query, you can be fairly certain that the author of the query wanted a Cartesian product. If you see a FROM clause with a table that has no conditions specified in the WHERE clause, how certain can you be that the author intended a Cartesian product?

I digress. In the query I was developing today I was comparing schedules in two systems. In one of the systems, the schedule only included one side of a pair of transactions that offset each other. In the other system, both rows were there. I didn’t want the missing row from the one system to show up as a variance. Luckily, the existing query was already using a UNION to pick up this special case. I used a CROSS JOIN of a “table on the fly” to create the other row for me:

...
UNION (
SELECT
CASE WHEN D.Direction = 1 THEN 'BUY' ELSE 'SELL' END TransactionType,
A.Volume * D.Direction Volume
FROM OneSided A
CROSS JOIN (SELECT Direction = 1 UNION ALL Direction = -1) D
)
...

As you can see, I created the second row that I needed to make this system’s schedule match the other system’s schedule with just one line of SQL and minor modifications to the SELECT clause.

Configuration file (App.Config) for .NET Class Library (.dll)

If you ever deploy a .net class library that isn’t being called by a .net application and references items in the config file then you will have to determine where the .net configuration subsystem will look for the config file and what it should be called. In hindsight, the answer seems rather obvious but I spent several hours figuring this out one day. In my case, I was deploying a managed DLL with a thin COM wrapper that would be called by Excel. In order to get this to work, we placed all the DLLs in the same directory as Excel.exe, called REGASM on the generated .tlb file, and named our config file Excel.exe.config.

Like I said, so obvious in hindsight but not the first (or tenth) thing you think of.

Follow

Get every new post delivered to your Inbox.