GigaMegaBlog

We’ve recently come across a couple of tricky bugs in IBM’s ADO.NET driver for their U2 UniVerse database, showing that there are still a few sharp edges on that shiny new finish.

The first and more serious bug is that the driver always returns numbers with a decimal point as a zero when using French “regional settings” in Windows.

For example, when using these Regional Settings…

Zut alors, c'est un comma!

…the sample .Net application that IBM provides in its QuickStart tutorial displays this:

French toast

We ran into this bug because many of our product’s users have the “French (Canada)” setting on their PCs. I assume that the root cause of the bug is that a comma, rather than a period, is used as the decimal point, as marked in yellow above. IBM support has confirmed that this is a bug are internally testing a fix in the Runtime Client, so the fix will likely be available in Fix Pack 3c or the one after that.

I’m surprised that nobody (including us) ran into this bug earlier, but that’s probably because most developers (including us) have got into the habit of reading data from UniVerse as strings, then converting the data into its proper format in their code. As I’ve mentioned in past articles, UniVerse has a “multivalue” heritage where all data is stored internally as strings. Developers should go against this grain at their own peril.

For example, rather than defining a currency field in the Universe dictionary like this…

COMMISSION
001 A
002 34
005 S
007 MD3
009 R
010 7

… define it as….

COMMISSION.STR
001 A
002 34
005 S
006 CHAR,7
009 R
010 7

… and read the value into a String. Then, you can convert it to a Decimal variable using code like…

Decimal.TryParse(strValue, System.Globalization.NumberStyles.Number,
System.Globalization.CultureInfo.InvariantCulture, out decValue);

The “InvariantCulture” thing is needed because, when defined as a CHAR, UniVerse will return a decimal value using — surprise, surprise — a period as the decimal point.

The second bug that we tripped over was a little more obscure. The bug was found by one of our developers, who was double-checking the configuration of our clients’ databases to make sure that all of our latest patches had been applied. He found that one particular SQL statement used in our application was failing with the following error:

Error  occurred: ERROR [Command_ExecuteDirect]  [IBM  U2][UCINET][UNIVERSE]:UniVerse/SQL: syntax error.  Unexpected verb.  Token was  "LIST". Scanned command was FROM VENDORS SELECT LIST  :  IBM.Data.DB2: -2147467259

This type of error usually indicates a mismatch between our dictionary entries and our application, which was the type of configuration issue that he was checking for. It wasn’t a big surprise, since the problem was found in a database that is only used for in-house training. Surprisingly, though, after reapplying the latest dictionary entries the bug remained.

Like most database systems that I’ve worked with, UniVerse’s SQL parser tends to return some pretty vague error messages. The particular SQL statement that was failing was quite long, containing about 20 columns, none of which were named “LIST”. By trial-and-error, I found that the culprit was a field named DEMO.LIST. The DICT definition of this field was fine – it exactly matched the dictionary entry used in the other databases at this client site, none of which were experiencing this error. While scratching my head over this, our developer told me that he found the same error in the same SELECT statement in a database at a different client. This database was also one used for in-house training.

This made me suspect that the error was caused by bad data. The databases used for in-house training are more likely to contain weird characters like embedded tabs or carriage-returns.

The dictionary definition of DEMO.LIST was a 100-character string:

DEMO.LIST
001 A
002 5
005 S
006 CHAR,100
009 L
010 100

As mentioned earlier, strings are usually a safe choice for data in UniVerse. I’m not aware of any type of data that can cause a SELECT of a a string to fail in UniVerse’s ADO.NET driver, but there’s a first time for everything. However, a bit of trial-and-error testing made it clear that this SELECT statement was failing on the plainest of records: vanilla text that couldn’t possibly cause a problem. It was a real whodunnit.

Back to the not-very-helpful error message, It was complaining about a “token” named LIST. If I simplified the variable name to something like FIELD1, did that affect the error message? Well, yeah: it made the error go away completely. The SELECT statement worked fine if the column name was changed to FIELD1, or DEMO or even DEMOLIST. Weird. We have periods in lots of our column names, and a lot of them are named LIST. DEMO, not so much.

At this point, things finally fell into place. The name that we give our databases that are used for in-house training is DEMO. And, of course, in standard SQL syntax, a period in a column name is used to identify the table, such as “SELECT TABLE1.FIELD1″. Sure enough, I ran some tests in our development database, named DEV, and found that naming a column “DEV.LIST” or “DEV.FIELD” or “DEV.WHATEVER” caused SELECT statements that use that field to fail.

The moral of the story, I suppose, is don’t use periods in your column names. That was IBM support’s suggestion, though I’m hoping that they will fix this bug as well.

I suppose that both of these bugs are indicative of what happens when you drag a 40-something year-old non-relational database kicking and screaming into the .Net world. Oops, better make that “dotNet” to be safe!