GigaMegaBlog

It has been a couple of months since we switched database APIs for accessing our IBM U2 UniVerse database from .Net.  We had a lot of reasons to leave UniOLEDB behind, and pretty high expectations from IBM’s new "DB2 Client" ADO.NET driver, but the 3 most important reasons behind the switch were:

1. Stability.  UniOLEDB’s intermittent "memory is corrupt" errors were driving me nuts.  Upgrading the database server from UniVerse 10.1 to 10.2 improved UniOLEDB’s stability significantly, but switching to the ADO.NET driver has made things better still.  Stability is no longer a concern — we’ve had no problems with dropped connections or failed queries.




2. Deployment.  UniOLEDB’s archaic install program was driving our clients’ IT guys nuts.  The fact that UniOLEDB had to be installed by manually on each desktop was a dealbreaker for the larger sites.  The DB2 Client’s installation program has its own quirks, as I wrote about here, but once we figured out the trick our clients have had no problems rolling it out with automated tools like Microsoft Systems Management Server.




3. Productivity.  The ability to have Visual Studio treat UniVerse like a full-fledged database would make it a lot easier to developer new forms.  With UniOLEDB we had to laboriously handcraft the DataAdapters for each table: write the SELECT, INSERT, UPDATE and DELETE statements, set their parameters, use them to construct the DataAdapter, then write the code to populate and manage the DataSet tables.  That was a lot of code, and we have a lot of tables!  Wouldn’t it be nice to replace all of that with a simple drag-and-drop?

Well, 2 out of 3 ain’t bad.

We haven’t quite reached that third goal yet.  We’ve got a relatively messy UniVerse database: our master dictionary and most of our data dictionaries are littered with references to things that don’t exist or aren’t correctly defined.  UniVerse itself ignores these obsolete entries, but it seems that the DB2 Client dutifully tries to make sense of them, each and every time that we connect.

As a result, any attempt to access a table through the Visual Studio IDE, be it from the Server Explorer or the Data Sources window, is painfully slooooooow.   I have an idea for doing an end-run around this problem, and when I have time to pursue it I’ll write a blog post describing what I did and how it turned out.

But, for now, drag and drop isn’t an option.

Our internal developers are still accessing UniVerse data through a labour-intensive code-based approach, without using the Server Explorer or Data Sources IDE at all.  That’s fine for us, since we’ve built a framework to support it, but when I was recently asked to help a client’s .Net developers get to our data, I knew I had to give them something easier.

My boss assured them that once they had the UniVerse 10.2 and its new IBM ADO.NET driver, UniVerse would be as easy to use as SQL Server.  Heck, we’d even create Views for the data they want.

Well, not so fast.  Even with an ADO.NET driver, UniVerse doesn’t support Views. There’s a node called "Views" sitting under the UniVerse data connection in Visual Studio’s Server Explorer, but it will always remain forlornly barren.  There is simply no equivalent entity in the world of UniVerse.

When I asked IBM about this, they recommended using UniVerse subroutines instead.  These are regarded by the ADO.NET driver as stored procedures, meaning that they can be viewed and invoked from the Server Explorer under the Procedures node.   You can use them with Visual Studio’s Data Source Configuration Wizard to create DataSets and DataAdapters without having to write any code.

Pretty nifty, and that’s something you couldn’t do with UniOLEDB.  (Predictably, it would throw up a "memory is corrupted" error if you tried).  But the subroutines are written in dowdy old UniVerse Basic, and our clients’ developers didn’t want to go there.  They opted for SQL.

Running SQL SELECT statements against a UniVerse database is a lot easier with the new ADO.NET driver, but it’s still tricky, with quite a few pitfalls that aren’t mentioned in IBM’s tutorials or (as far as I know) anywhere else.

So, having just played matchmaker between UniVerse and some .Net developers, I thought it might be useful to describe the quickest path to getting your UniVerse data into a .Net grid.

My next post will describe what needs to be done to prepare the UniVerse dictionaries for .Net, and the following post will show how to create UniVerse-friendly SELECT statements and feed the results into a DataGridView.

A week or so after writing the previous post about our almost bug-free upgrade of Infragistics NetAdvantage, I belatedly noticed another bug worth mentioning.

In NetAdvantage, there aren’t separate "menu bar" and "toolbar" controls — everything is a toolbar.  A menu bar is just a toolbar with lots of text and not many icons.  Fair enough.  But not all NetAdvantage toolbars are equal – you can set the IsMainMenuBar property on one (and only one) toolbar.  In the version of NetAdvantage that we were previously using, 2007 Volume 1, this property seemed to have only one effect: it forced that toolbar to be on a separate row from all of the others.  This seemed like an odd feature, so we never used it.

At some point between 2007 Volume 1 and 2008 Volume 2, the IsMainMenuBar property became a lot more important: if none of your toolbars have it, then you lose your accelerator keys.  (By "accelerator keys", I mean the underlined letters in menus that can be used with the Alt key as a shortcut.  Some of the young folk today call them "access keys", go figure.)

I didn’t notice until one of our internal testers diplomatically asked "um, didn’t those used to be underlined"?  It took me awhile to figure out that the IsMainMenuBar property was at the root of the problem – this behaviour isn’t documented and, oddly, I couldn’t find any reference to the problem on the Infragistics forums.   I submitted the problem to Infragistics, and so far they are handling it as a bug, not a feature. 

The workaround is easy enough: use the IsMainMenuBar property.  Since this forces other toolbars onto separate rows of the screen, we also merged everything into one toolbar: I figure the loss of screen real estate would be more annoying to users than losing the ability to position the toolbars on the screen.