GigaMegaBlog

This article is the third and final part of a series that covers some lessons learned when trying to plug Visual Studio into our IBM U2 UniVerse database using IBM’s ADO.NET driver.  Part 1 was an overview of how well the ADO.NET driver has worked for us, and the prerequisite software that is required to use it.  Part 2 gave some tips on preparing your UniVerse dictionaries for use with Visual Studio.  In this part of the series, we finally get to fire up Visual Studio and connect it to the database.

One of the great things about the ADO.NET driver, when compared to other UniVerse APIs like UniOLEDB and UniVerse Objects.NET, is that it allows Visual Studio to finally recognize UniVerse as a database.  That means you don’t have to write a bunch of code in order to see the data — you can use use Visual Studio to explore the database and test your SQL statements.  (On the other hand, if you prefer to just write code and avoid all the point-and-click stuff, you can skip ahead to this point in the article.)

The first step is to configure your UniVerse database in Visual Studio’s Server Explorer.  This process is explained well in IBM’s developerWorks article "Access IBM U2 data server from your .NET applications, Part 2".  Since I would strongly advise you to read IBM’s three-part tutorial series anyway, I won’t repeat the information here.

You should now have a new node in the Data Connections section of the Server Explorer, as shown in the screenshot on the right, with a name like "<user-ID>@<account>[UNIVERSE 10.02.0000]".  Expand that node, and you’ll see 4 sections: Tables, Views (which will be forever empty), Procedures and Functions (also empty).  Expand the Tables node and, hopefully, you’ll see a list of the tables in your account.  Expand one of the tables, and you’ll hopefully see the columns in that table.  Right-click on the table and select "Show Data", and you’ll see a grid containing all the rows of the table.  Groovy!  UniVerse and Visual Studio sitting in a tree-ee…

Alas, you might, like I, find that Visual Studio’s love for UniVerse is not so easily requited.  When I expand the Tables node in our development account, I see the warning on the left.  After clicking on the Yes button, I am then left staring at the hourglass for a few minutes before the table list appears.  If I expand one of the tables to view its columns, I once again find myself contemplating the Windows hourglass for a minute or so, even if the table only contains a few columns.  If I want to use Visual Studio’s query builder with my UniVerse database: you guessed it, again with the hourglass.  Trust me, that gets real old real fast!

It would seem that Visual Studio’s Explorers like to, well, explore.  If you have a large UniVerse master dictionary, all that exploring takes some time.  The key to speeding up Visual Studio is to limit how much of your master dictionary it sees.  If you read Chapter 4 of the UniVerse ODBC Guide, you’ll find that happily there is a mechanism for doing just that: create a file named HS_FILE_ACCESS and add an entry in it for each file that you want to use with Visual Studio.  Unhappily, while this limits the number of tables that you’ll see in Visual Studio, it only makes things slightly faster.

I suspect that the solution to this problem is to create a new account whose master dictionary contains only the data files you want Visual Studio to work with.  I’ll be trying this for myself at some point in the future, and I’ll blog about it then. 

Fortunately, even if you find that slow performance makes some of Visual Studio’s point-and-click features unusable, you are still left with enough tools to get the job done. 

Let’s start with some SELECT queries.  Right-click the database connection in the Server Explorer and, instead of New Query, choose New Script.   This opens the Script Builder (shown in the screenshot below), an IBM tool that is basically a stripped-down version of Visual Studio’s Query Builder: no Diagram pane, no Criteria pane, just a box for your SQL and a  grid with your results.  (And a tiny little 1-icon toolbar – cute!).  More typing, but a lot less waiting, since this dialog do a scan of your UniVerse master dictionary.

The SELECT commands that you enter here follow most of the same rules as for a SQL database, but there are a few tricky things:

1) Case matters – if your columns are defined in your DICT in upper-case, you must use upper-case here, something that is sure to trip up any SQL Server or Oracle developer.

2) Columns and tables that contain hyphens must be surrounded with double quotes — the square brackets that you may be used to using with other databases won’t cut it here.  For example,

select USER.ID from "CSUSER-FILE"

is valid, but not

select USER.ID from [CSUSER-FILE]

or

select user.id from "csuser-file"

Oddly, names that contain a period do not have to be quoted – unlike its SQL database brethren, UniVerse is smart enough to figure out when the period delimits the table ID from the column name.

3) A "SELECT *" query will return the key fields along with whatever fields are defined in a special dictionary entry named "@".  For example, if you want SELECT * FROM USERS to return the FIRST-NAME and LAST-NAME fields, you’ll need to edit the dictionary as follows:

ED DICT USERS
Record name = @
2 lines long.

----: 2
0002: 1 2
Bottom at line 2.
----: R FIRST-NAME LAST-NAME
0002: FIRST-NAME LAST-NAME
Bottom at line 2.
----: FI
"@" filed in file "DICT USER".

If you don’t have a record named "@" in your file’s dictionary, then "SELECT *" will return the column named "@ID" if you have one (which you probably do, since the primary key is automatically added to all dictionaries under this name when you first create them).  If you happen to have deleted the "@ID" column, then SELECT * will fail with a "No column rows" error.

What if you have a 200 column table and you want SELECT * to return all 200 columns?  Sorry, but you’ll have to enter all 200 column names in that "@" record.  And if you add, delete or rename a column, you’ll have to remember to edit that "@" record. I feel your pain!  This cumbersome approach dates back to UniVerse’s ODBC driver, and I suppose they can’t change it now without breaking a lot of applications.  You can find the details described in inscrutable IBM-ese in the UniVerse ODBC Guide.

4) Errors encountered when executing your SQL statement are reported in a pretty obscure manner.  You’ll see a message box with the "Error in SQL.  Check the SQL and try again.  For error detail information refer to the IBM Output Message Pane".  It took me awhile to figure out where the "IBM Output Message Pane" was hidden.  It’s in the Output window (Ctrl-W O), but you’ll need to change the "Show output from" combo to "IBM Output Message Pane" as shown below. 


5) If you have subvalued fields, only the first subvalue in the field will appear in the Results grid.  (Subvalues are ASCII 252 delimiters which are used to fit multiple fields into the same column).  This is presumably a side effect of a bug in IBM’s ADO.NET driver which replaces subvalue delimiters with CR/LF.

6) There are a couple of bugs in the ADO.NET driver for which, as far as I know, there is no good workaround.  The first is that the SQL processor can’t handle hard-coded values which contain the "at sign", such as:

SELECT * FROM MY.TABLE WHERE THE.KEY = "DAN@WORK"

Depending on the structure of your data you may be able to get at the data by using a wildcard instead

SELECT * FROM MY.TABLE WHERE THE.KEY LIKE "DAN%WORK"

The other bug is that zero-length numeric fields such as the one in attribute 5 below will result in an “Input string was not in a correct format” error (that funny little accented character is ASCII 253, the multivalue delimiter):

0001: SMITH
0002: 1414 W. 8TH
0003: DENVER
0004: CO
0005: V2001ýýC8181
0006: 200ý3599ý1050

This latter bug was fixed by IBM in the recently released Fix Pack 3, but if you have users on earlier releases there is a workaround you can implement in your code, as explained later in the article.

7) Last, but not least, multivalues.  As described in my previous article there are a few different ways to configure your dictionary to return multivalues, but the method that IBM recommends is to list the multivalued fields in a special "association" dictionary entry that looks like this:

    DETAILS
001 PH ASSOCIATION OF ORDER DETAIL FIELDS
002 ORDER.IDS ORDER.AMOUNTS

The advantage of this approach is that you can return the items within the multivalued field as if they were separate columns, which makes the data a lot easier to work with.  The disadvantage is that you’ll have to puzzle out the syntax for a join with a "virtual table".   If the above field DETAILS was defined in the dictionary of a table named CUSTOMERS, then a SQL statement to return a list of order IDs and amounts would be:

SELECT a.KEY.FULL, ORDER.IDS, ORDER.AMOUNTS FROM CUSTOMERS a JOIN CUSTOMERS_DETAILS b ON a.KEY.FULL = b.KEY.FULL

Since the dictionary field that is automatically added for the primary key, "@ID", can’t be used in SQL statements, you’ll have to add one yourself in order to use it in the JOIN — in the above example I added a field named KEY.FULL to CUSTOMERS.

If by this point you’re fully freaked out by the UniVerse way of implementing SQL, then I’ve got some good news for you: the code itself is pretty much plain vanilla and won’t require much explanation. 

Before you start coding, you’ll need to add a reference to the IBM.Data.DB2 DLL to your project References list, as shown at the right.  You’ll also need to add "IBM.Data.DB2" to your list of namespaces:

using IBM.Data.DB2;

The process of building a connection string is quite similar to other databases like SQL Server.  You’ll need to set the Server, User, Password and Database (which is the UniVerse account name).   In addition, you need to set two less familiar properties — ServerType and Pooling:

private string providerName = "IBM.Data.DB2";
private DbConnectionStringBuilder builder;
private DbConnection conUniVerse;

builder = new DbConnectionStringBuilder();
builder["Server"] = txtServer.Text.Trim();
builder["User ID"] = txtUserID.Text.Trim();
builder["Password"] = txtPassword.Text.Trim();
builder["ServerType"] = "universe";
builder["Pooling"] = "false";
builder["Database"] = txtAccount.Text.Trim();
string strConnectionString = builder.ConnectionString;

DbProviderFactory m_provider = DbProviderFactories.GetFactory(providerName);
conUniVerse= m_provider.CreateConnection();

conUniVerse.ConnectionString = strConnectionString;
conUniVerse.Open();

You’ll then need to create an ADO Data Adapter and feed it the SQL statement.

DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
DbDataAdapter adapter = factory.CreateDataAdapter();
adapter.FillError += new FillErrorEventHandler(adapter_FillError);
adapter.SelectCommand = DB_GetCommand(strSQL);

Lastly, you create an ADO Dataset to hold the results of the query, then bind that Dataset to a grid on your form to display the data:

DataSet dsData = new DataSet();
adapter.Fill(dsData , "MyData");

bindingSource1.DataSource = dsData ; // bindingSource1 is a BindingSource object that I dragged from the Toolbox onto my form
bindingSource1.DataMember = "MyData";
gridData.DataSource = bindingSource1; // gridData is a DataGridView object on my form
conUniVerse.Close();

You may have noticed that I added a FillError event handler to my adapter.  This is a workaround for the zero-length multivalue problem that I mentioned earlier in the article.  In the error handler, you can examine the Exception’s properties and, if it appears to be the zero-length multivalue error, set the e parm’s Continue property to true.

void adapter_FillError(object sender, FillErrorEventArgs e)
{
    if (e.Errors.Message.Contains("Input string was not in a correct format"))
        e.Continue = true;
}

This will result in the dataset being filled, but without the multivalued " virtual record" that contained the zero-length value. If the zero-length value is just bad data then that’s probably what you want — otherwise, you’ll need Fix Pack 3.

Whew!   That might seem like a lot of work to read some data into a grid, but it’s stable, it’s pretty fast, and it’s still a lot less coding than the only other stable and fast UniVerse API out there, UniVerse Objects.  Once you’ve learned the ropes, UniVerse is now a viable database option for .Net development. 

This article is a continuation of my previous post about using IBM U2 UniVerse’s ADO.NET driver with Visual Studio.  Today I’m going to deal with the part of the process that makes .Net programmers cringe but is the key to crafting effective SELECT statements: the UniVerse dictionary.

You probably already have dictionary entries that you (or others) use with UniVerse Basic code, but there are a few cases where you may need to modify them in order to access the fields through ADO.Net.  UniVerse Basic is pretty forgiving about incorrect dictionary entries — as long as the entry tells the code which attribute to look in, UniVerse Basic is happy.  Unfortunately, ADO.Net (and other SQL-oriented APIs) are picky about data types and conversion codes — they frown on UniVerse’s attempt to portray every piece of data as a string.

IBM has provided a utility named HS.SCRUB which analyzes your dictionaries and flags any fields which might cause problems for the database driver.  If you are brave, you can even let HS.SCRUB "autofix" your dictionaries. This utility was actually introduced with the UniVerse ODBC driver, and you’ll find the documentation for it in the UniVerse ODBC Guide and the Using UniOLEDB manual.  (You can download PDF copies of any of the UniVerse manuals from here.) Frankly, the utility found so many problems in our dictionaries, including a lot of false positives, that I ended up ignoring it and building new dictionary entries from scratch.  Your mileage may vary.

However, even after letting HS.SCRUB have its way with your dictionaries, there are some cases that will require special attention:

1) Non-string fields that may be null.

If you don’t specify the data type of a field, the database driver will helpfully try to guess the data type on-the-fly by looking at the data.  HS.SCRUB uses a similar approach to determine the correct data type.  Unfortunately, both of them are tripped up by non-string fields which contain some zero-length values — if you   define these as any data type except strings, the zero-length values will result in "invalid data type" .Net Exceptions at run time.

In cases like this, I’d suggest you force the field to be read as a string, by specifying "CHAR" in the data type attribute of the dictionary entry.  The data type attribute is 6 for A and S-type dictionary entries, and attribute 8 for D- and I-type correlatives.  If you have no idea what that means, then you’d better have a look at Chapter 5 of the UniVerse System Description manual.  (As mentioned earlier, PDF copies of all of the UniVerse manuals can be found here.)

2) Time fields with contain milliseconds. 

In Universe files, time fields are written in a "Julian" format which converts the time to the number of seconds past midnight: for example, 1:30 am is 5400.  Optionally, these time fields can contain the number of milliseconds, which is written as a decimal value: for example, 5400.5 is half a second after 1:30 am.  Unfortunately, the UniVerse ADO.Net driver can’t convert a string with that format into a TimeSpan.  One workaround is to define the field as a string, by specifying "CHAR" as the data type (see point (a), above).  You will then have to write a bit of code to convert the string to a .Net TimeSpan value:

public static TimeSpan TimeSpan_FromUniverseString(string strUniverseTime)
{
    int intUniverseTime = 0;
    // if the string contains milliseconds, discard them
    int intIndex = strUniverseTime.IndexOf('.');
    if (intIndex >= 0)
    {
        strUniverseTime = strUniverseTime.Substring(0, intIndex);
    }

    if (!Int32.TryParse(strUniverseTime, out intUniverseTime))
    {
        // if not a numeric, then return 00:00:00
        return new TimeSpan(0);
    }
    int intDays = intUniverseTime / 86400;
    int intRemainder = intUniverseTime % 86400;
    int intHours = intRemainder / 3600;
    intRemainder = intRemainder % 3600;
    int intMinutes = intRemainder / 60;
    int intSeconds = intRemainder % 60;
    return new TimeSpan(intDays, intHours, intMinutes, intSeconds);
}

However, an easier workaround (which I stumbled across after using the above approach for months) is to take advantage of the "correlative" code. 

This code only exists in A- and S-type dictionary entries, in attribute 8.  Ordinarily, time fields are defined in UniVerse dictionaries by specifying a "conversion code", such as "MTS" in attribute 7.

    ACCESS.TIME
001 A
002 3
005 S
007 MTS
009 R
010 5

This tells ADO.Net (or UniVerse Basic) that the value in this field can be regarded as a time.  However, by specifying the same conversion code in attribute 8, the conversion to a time value is handled internally by the UniVerse database before ADO.Net sees it.

    ACCESS.TIME.SQL
001 A
002 3
005 S
008 MTS
009 R
010 5

Therefore, a value such as "3600.5" will be seen by the ADO.Net driver as 1:30 am, without that nasty millisecond value that it so dislikes.

3) Multivalues

Ah yes, multivalues.  This is a method for storing multiple values into the same field, separated by a special delimiter.  For example, attribute 1 might contain a list of order numbers, and attribute 2 the amount (in cents) of each of order:

001 C2000ýM3000ýS3000
002 600782ý700422ý101456

Multivalued strings are the primary feature that distingishes UniVerse and its brethern databases from all the others, but .Net doesn’t really understand the concept (nor do many .Net programmers, actually).  UniVerse provides some tools which allow you to dress up the multivalued data to resemble columns in a normalized SQL database — it is up to you whether you want to play dress-up, or just process the multivalued fields in their underlying form: delimited strings.

There are 3 different approaches to definining multivalued strings in the dictionary so that they are safe for consumption by .Net:

i) The documented approach is to use a D-type dictionary entry which contains an "M" in attribute 6 and an "association" name in attribute 7.  For example, the definition of the Order ID and Order Amount fields might look like:

    ORDER.IDS
001 D Order IDs
002 1
004 Order IDs
005 10L
006 M
007 DETAILS

    ORDER.AMOUNTS
001 D Order Amounts
002 2
003 MD0,$
004 Order Amount
005 7R
006 M
007 DETAILS

And the dictionary entry which defines the virtual "DETAILS" table would be:

    DETAILS
001 PH ASSOCIATION OF ORDER DETAIL FIELDS
002 ORDER.IDS ORDER.AMOUNTS

If the main table which contained the multivalued fields was named SALES, then there would now be a virtual table named SALES_DETAILS containing three fields: ORDER.IDS, ORDER.AMOUNTS, and a foreign key field that points back to the primary key of the SALES table. 

For details, see chapter 5 of the UniVerse System Description manual, and for a thorough example see the sample code which is included with Part 2 of IBM’s tutorial on the ADO.Net driver.  In the third part of this series, I will show an example of how you would use a JOIN clause in a SELECT statement to return the fields in the virtual table in the same record as the fields in the main table.

The advantage of this approach is that the resulting SQL statements will closely resemble those that would be used for a relational database, and this should make it easier to port your application from UniVerse to a relational database should you wish to.  The downside is that it can become quite cumbersome to code when your records contain multivalued attributes that aren’t related on one another. For example, if a customer record contains a list of order numbers in attribute 1, and a list of customer contacts in attribute 2, you’ll have to specify a different association name for these 2 entries, and retrieve them using either a very complicated 3-way JOIN, or using 2 different SELECT statements. Also, since UniVerse does not offer any constraints or other mechanisms for enforcing the integrity of your data, you are likely to find that bad data fouls up your application, resulting in missing rows or runtime Exceptions.  In our application, we use this approach only for small and simple tables.

ii) You can return the multivalued strings in their "native" form — strings with delimiters separating the values.  You do this by using an I-type dictionary entry that converts the multivalue delimiter (ASCII 253) to whatever character you want.  For example, to return attribute 1 with the multivalued fields delimited by commas:

   FIELD.LIST
001 I
002 CONVERT(@VM,',',@RECORD<1>)
004 FIELD.LIST
005 50L
006 S
008 VARCHAR,32767

You might be wondering why you can’t just return the strings using the original delimiter, ASCII 253.  For reasons known only to IBM, this results in a runtime exception.  Incidentally, there is is a second type of delimiter which is also common in UniVerse, the subvalue delimiter (ASCII 252).  UniVerse subvalues  occur when the parts of a multivalued fields are, in turn, delimited into smaller fields.  For reasons also known only to IBM, these are automatically converted by the ADO.NET driver to a carriage return and line feed (i.e. "\r\n").   IBM support has indicated that this delimiter conversion is actually a bug and will be changed back to ASCII 252 to a future Fix Pack.

Once you have the delimited string, you’ll need to use .Net’s string parsing capabilities to separate it into individual fields and convert the fields to their correct data type.  You’ll find that .Net isn’t ideally suited for this type of string processing.  UniVerse, on the other hand, is very much suited to it, and IBM provides a library named UniVerse Objects.Net that contains a variety of methods for extracting data from delimited strings.  Frustratingly, your ability to use UniVerse Objects to parse your data is hampered by the fact that ADO.NET has forced you to replace all the multivalue and subvalue delimiters with other delimiters that UniVerse Objects doesn’t support.

If you decide to read UniVerse multivalued data in its native form, you might want to consider bypassing ADO.NET altogether and reading the data using UniVerse Objects.  This is what our application does when dealing with tables that contain data that is mostly multivalued.  It is much faster, and will likely require less code.

iii)  While "official" UniVerse multivalued fields are delimited using ASCII 253 and 252, many UniVerse Basic programmers grow so fond of multivalues that they use them all over the place, with whatever delimiter character tickles their fancy.  I’ve seen multivalued strings used as the primary key, and I’ve seen multivalued strings that contain embedded dates and time fields.  While that kind of data structure would make SQL programmer cringe and a SQL database crawl, UniVerse is tuned to handle these strings efficiently and provides functions that you can insert in your dictionary to extract these fields relatively easily.

The trick is to place a correlative in attribute 8.  Appendix C of the UniVerse Basic manual documents these correlatives, but be forewarned: they are cryptic enough to make a Perl programmer weep with frustration.  Here are a couple of examples to get you started.

Say you had a key that was delimited with "@" signs.  Just to make things interesting, say that one of the fields was a UniVerse Julian date, and another was a UniVerse Julian time.  A typical key would be: FIRSTPART@14927@49055.75.

If you wanted to return the 1st part of the key ("FIRSTPART") as a separate field in the SELECT statement, you could define it as follows in the UniVerse dictionary:

    THE.STRING
001 S
002 0
005 S
008 F;0;(G0@1)
009 L
010 20

The correlative in attribute 8 means "take attribute 0 (the key), then split it into sections with ‘@’ as the delimiter and return part 0".  If you want all the gory details of what the "F code" can do, see Appendix C of the UniVerse Basic manual. 

To return the date string as a date, you would use a similar "F code" string in attribute 8, along with a conversion code in attribute 7:

    THE.DATE
001 S
002 0
005 S
007 D2
008 F;0;(G1@1)
009 R
010 9

The time field is trickier – as mentioned above, the ADO.NET driver doesn’t like time fields with milliseconds.  The best way to handle these fields is by putting the conversion code in attribute 8, but that’s where we need to put the correlative, and (as far as I know) you can’t put both a conversion code and a correlative in the same dictionary attribute.  You can, however, combine 2 extract commands in a correlative:

	THE.TIME
001 S
002 0
005 S
007 MTS
008 F;0;(G2@1);(G0.1)
009 R
010 12

This correlative tells the database to extract the time field from the key and then, having done that, extract the part before the decimal point.  The conversion code in attribute 7 then converts that value to a .Net TimeSpan.  (The Universe Basic manual refers to this as "reverse Polish format (Lukasiewicz)" — how geeky is that?)

So, there you have it: an overview of the tricks that I’ve used to whip our UniVerse data into a form suitable for consumption by .Net. 

If you are new to UniVerse, you are likely thinking at this point: "um, so how do I edit a dictionary".  As a crash course on UniVerse for Windows programmers, I would strongly recommend the "Learner Pack" put together by the U2 User Group.  The UniVerse manuals are a well written, exhaustive reference when you are ready to dive deeper, but as a crash couse you can’t do better than the U2UG Learner Pack.

In third and final part of this series, I’ll show you the (relatively simple) coding required to load UniVerse data into a DataGridView using ADO.Net.