Like many developers, I’ve bounced between various relational databases on my coding projects, primarly DB2, SQL Server and Oracle. If someone asked me if the SQL I write is compatible with all three, I’d sheepishly admit that most of it is.
Sheepishly, because generic, cross-platform SQL tends to be inefficient SQL. You can get away with it when working with modest amounts of data and users, but it’s still lazy, shoddy work.
Although not obvious from its title, SQL Cookbook is actually a great way to hone your SQL-writing skills, taking advantage of the powerful, vendor-specific features available in each major RDBMS brand. (The specific types of RDBMS covered by the book are DB2, SQL Server, Oracle, MySQL and PostgreSQL.)
The book’s format is to present a problem, such as creating a pivot table or generating a delimited list, then provide one or two solutions to the problem for each type of RDBMS. Sometimes the same solution works for multiple RDBMS, but in most cases a particular RDBMS has proprietary functions that result in a shorter or faster piece of SQL.
A notable example are “window functions” (also known as “analytic functions” in Oracle). They are a relatively new technology, having become an ISO SQL standard in 2003, but all recent versions of the major RDBMS products implement them to some extent. An example from Oracle is the following, which returns each employee’s department and the # of employees in that department.
select ename,
deptno,
count(*) over (partition by deptno) as cnt
from emp
The generic/lazy way to implement that is with a subquery or joined query that returns the number of employees in each department, such as:
select e.ename,
e.deptno,
(select * from emp where deptno = e.deptno) as cnt
from
(select ename, deptno from emp)
The latter approach works, but it’s more complex and less efficient than using a window function.
If you’ve been writing SQL for more than 10 years, you learned to do it the long way and probably haven’t been forced to change your ways since. To learn about window functions, Appendix A of SQL Cookbook is a great place to start.
Another example is Oracle’s ability to return an object from a subquery. I had always thought it was a cardinal rule (and major pain-in-the-ass) of SQL: subqueries can return only 1 field. I couldn’t tell you how many times I’ve pasted multiple copies of the same subquery into a SQL statement because I needed to return a few different fields from it. I knew that it would hurt performance, but what option did I have?
It turns out that, when using Oracle, an option is to return those fields in an object. For example, the following query returns a couple of fields from the department table for each employee:
select
x.deptno,
x.ename,
x.multival.val1 dname,
x.multival.val2 loc,
x.multival.val3 today
from
(select e.deptno, e.ename, e.sal,
(select generic_obj(d.dname,d.loc,sysdate+1)
from dept d
where e.deptno=d.deptno) multival
from emp e
) x
I also liked the format of SQL Cookbook. As the title suggests, one way of using the book is as a reference. If stuck on a SQL coding problem, you can run through the table of contents, looking for a problem description that is similar to your own.
However, I found it worthwhile to read through the book chapter by chapter. Every piece of SQL is accompanied by a detailed but easy-to-follow narrative describing how and why it works. By reading these sections you’re sure to add a lot of new tools to your toolbox, even if you don’t need to solve the specific problem covered by that “recipe”.
Also, since the book separates the solutions by RDBMS, you’ll probably find that you can skip at least 50% of the book (for now), focusing only on the one or two RDBMS systems that you are currently working with. It’s refreshing to come across a software development book that can realistically be read in a week.
With the emergence of alternative ways of interfacing with databases, like LINQ, CouchDB and the various technologies gathering under the “NoSQL” banner, the day might come when crafting SQL statements is a dying art. A parallel might be drawn between SQL today and assembly language in the past — increasingly less important as higher level languages and the underlying hardware grow stronger. However, if this happens the remaining “artists” who’ve mastered SQL will be much in demand. This book is a good step towards raising your SQL to a higher level — from Betty Crocker to Julia Child, if you will.
A few months ago, technology publisher O’Reilly began selling some of their books as iPhone apps [iTunes link] for a surprisingly low price — generally just $5. These are the full versions of the books, not just an extract. The apps come bundled with Lexcyle’s Stanza e-reader, which is feature-rich, fast, and stable. All things considered, these books are quite a bargain.
There is a catch, of course: for some books, and many humans, the iPhone isn’t the best reading platform. Books about software development and tools are generally most useful when you are working hands-on at your computer. Switching from the iPhone to the PC is rather awkward, and copying and pasting code fragments from the iPhone to your computer is pretty much impossible. (Stanza, unlike Kindle, does support copy and paste of text by way of their annotation feature, but getting that copied text onto your computer is a byzantine procedure).
Fortunately, O’Reilly chose to package their e-books using the open ePub standard, without ePub’s optional DRM (Digital Rights Management) encryption. This means that it’s relatively easy to extract the ePub document from the iPhone app, at which point you can read it on whichever platform you choose. The number of software and hardware e-readers that support ePub is rapidly expanding (with one notable holdout), and it is widely expected that ePub will eventually replace today’s myriad incompatible formats.
The following method for extracting the ePub document from one of O’Reilly’s iPhone apps is based on an article on the excellent TeleRead site. The packaging of the apps has changed a little since that article was written, so a couple of extra steps are required. I use a Windows PC, but I’m sure a similar approach would work on a Mac since the only software tool required is one that can read and write .zip files.
Locate the iPhone app file. The easiest way to do this is to right-click on the app in iTunes, then select “Open in Windows Explorer”. The example I’m working with is the wonderful Coding4Fun book (which costs $32 when bought as an eBook right now), and its app file is named Code4Fun 1.0.ipa. Copy the .ipa file to another folder so that you won’t confuse iTunes with the following steps.
Extract the contents of the .ipa file. Despite the extension, this is a zip-compressed file. Most zip extraction tools (like 7-Zip in the following screenshot) are quite happy to take a whack at opening the file without knowing what an .ipa is, but if necessary you can rename the file to Code4Fun.zip first.
A zip in app's clothing
The contents of the app should consist of a couple of files and a folder named “Payload”. If you open Payload you’ll find another folder named Code4Fun.app. Another level down is a folder named “book”, as shown in the following screenshot. (Incidentally, the parent folder of “book” also contains a file named default.pub. This is actually a bonus ePub book: The Time Machine by H.G. Wells. I don’t think you can get at this book from within the Code4Fun iPhone app – it presumably is there as part of the Stanza packaging).
In the book, is a book
Select the contents of the “book” folder (2 folders and a file) and add them to a new .zip file, as shown below.
A ePub in zip's clothing
That .zip file is actually your ePub document, so rename it to something more suitable like Code4Fun.pub. At this point you should be able to open the .pub file in Adobe Digital Editions, or MobiPocket Reader, or Stanza Reader. (Mobipocket and Stanza are generally used on mobile devices, such as Blackberry or Windows Mobile smartphones, but both offer a desktop reader). My own preference is to keep things simple and flexible by using the browser-based Bookwork reader.
Enjoy, but please, please don’t pass along the .pub file to your friends (or, worse, a Torrent site). O’Reilly is doing us a great favour by selling these ebooks at such a low price and supporting the open ePub standard.
I’m pretty sure that O’Reilly is OK with you extracting the .pub file for your own use — it was an article on an O’Reilly site where I first came across this procedure. Other companies would have you believe that DRM-encrusted proprietary standards are the only way to prevent the unwashed masses from pirating ebooks. Please don’t help them to prove their point.
A little while ago I wrote about an electronics kit from kitusrus that interfaces an LCD to a PC through its parallel port. Like all kitsrus stuff it was fun to build, and the instructions included with the kit do a great job of explaining the design of the circuit.
However, I was a little surprised that they didn’t include the source code for the Windows program that communicates with the board. Sending a “hello world” message to the LCD is fine for verifying that the kit works, but an LCD connecting to a PC could be used for so much more.
When Googling for some .Net code that I could use as a starting point for writing my own interface, I was delighted to find that somebody had already done most of the work for me. An article on The Code Project site describes the construction of a homebrew parallel port LCD connection, and includes the C# .Net code that the creator used to send data to the LCD. Out of curiosity I downloaded the code and ran the .exe to see what happened, and I was astounded to see it successfully writing The Code Project’s RSS feed to the kitsrus LCD, under Vista no less. “Hello World”, indeed.
The Code Project article’s creator wasn’t using the kitrsrus kit, but because his LCD is based on a similar controller chip, and by a lucky coincidence in the selection of parallel port pins, the code mostly worked. The only problem was that the data wasn’t scrolling across the LCD as intended, but was stuck in the last column of the display, writing only to that one position. The data was getting across, it just wasn’t being positioned correctly. This is a pretty common and easily solved problem when writing data to an LCD, as I’ll explain below.
(In order to preserve your sanity and mine, I’ll refrain some referring to “the Code Project project” and the “kitsrus kit”, and call them the “CP project” and “the kit”. Apologies to their respective trademark owners.)
Although there is no “standard interface” for connecting an LCD to a parallel port, there is a fairly close correspondence between the parallel port’s pins and the pins used by all LCDs that use an HD44780-compatible controller chip. (Most LCDs intended for use by hobbyists are compatible with this standard, dating back to the late 90s. The Wikipedia entry for HD44780 links to an Everyday Practical Electronics article from 1997.)
The CP project used an LCD based on an old Samsung KS0066 chip, and the kit contains an LCD based on the KS0070 and manufactured in 1999, and both of those are HD44780-compatible.
The Code Project author wrote 2 articles that contain all the technical information you need to know in order to understand his .Net source code: this article covers the parallel port’s pins, and this one covers the LCD’s. The illustration below is linked to his second article.
Both the parallel port and the LCD use 8 data pins, D0 through D7. Not surprisingly, both the CP project and the kit wire the 2 sets of pins in a one-to-one correspondence, D0 to D0, D1 to D1, etc. The data sent by the .Net code arrives just fine at the LCD, then.
LCDs based on the HD44780 interface also have 3 control pins: RS (register select), R/W (read/write) and E (enable). Since LCDs don’t have much to say, both the CP project and the kit do the same thing with the R/W pin – wire it to ground, putting it in a permanent write state.
By a happy coincidence, both the CP project and the kit happened to wire parallel port pin C0 to the E pin of the LCD – without pin C0 turning things off and on, nothing would have made it to the LCD. There is a non-intuitive pattern of setting the enable pin low then high then low again that is necessary to write data to the LCDs — this seems to be the thing that trips up most novice LCD programmers, but the CP project’s source code does a nice job of commenting this code so that you can understand what’s going on.
The only area where the 2 approaches differ is in the selection of the RS pin: the CodeProject board uses pin C2 while the kitsrus board uses C1. (See the CP project’s pinout diagram below). Since this pin is used to tell the LCD whether it is receiving data or instructions, and the pin needs to be high for data, this difference should have caused everything being sent to the LCD to the treated as instructions, resulting in a jumpy cursor but no characters on the screen. However, by another lucky coincidence, the parallel port pin used by the kit, C1, is reversed – it is normally high and is set to low by sending it a “1″. This results in everything sent to the LCD being treated as data — characters with no cursor control, exactly what we got.
Parallel port connections to the LCD, by Levent Saltuklaroglu, courtesy of The Code Project
To adapt the CP project’s code to the kit, the only change required is to redirect all signals intended for C2 to C1, and flip the bit from allow for the fact that kit’s pin is reversed.
I decided to implement this change by adding a go-between method that would convert the instructions sent by the CodeProject code to the ones required by the kitsrus board:
private void writeToControl(int intValue)
{
int intModifiedValue = intValue;
if ((intValue & 4) > 0)
// if C2 is being set high, set C1 high by sending it a 0
intModifiedValue = intModifiedValue & 253;
else
// else, if C2 is being set low, set C1 low by sending it a 1
intModifiedValue = intModifiedValue | 2;
PortAccess.Output(intControl, intModifiedValue);
}
And that’s it — the kit can now be fully controlled by the CP project’s code.
This is quite cool, since it breathes new life into a 10-year old kit. When originally introduced the kit had only a command line interface that would only run in DOS – real DOS, not the command line in Windows XP. However, the .Net code works just fine under Vista and Windows 7. By tinkering with the code you can now use the LCD as a remote display for whatever you like: RSS feeds, e-mail, twitter. A poor geek’s Chumby!
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!
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:
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.
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 
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!
