One of the downsides of working with lesser known databases like IBM’s U2 is that you generally can’t solve a problem just by Googling it.
Something came up today at work that at first seemed like a tough nut to crack. A programmer was trying to port a custom decryption algorithm from U2‘s native language, Pick Basic, to C#. The algorithm wasn’t resulting in a correctly decrypted string. The problem seemed to be that the Pick Basic function for returning the ASCII code for a character, SEQ(), was often returning different values than .Net’s equivalent (I thought) method, Encoding.ASCII.GetBytes(). This had never been a problem for us before, since U2 and .Net agree on the ASCII code for all the characters on the keyboard. But “unprintable†characters that show up in an encrypted string, like a right single quote (’ – ASCII code 146), were being interpreted differently.
My first thought was that the string being decrypted wasn’t really a series of characters, just a series of bits, so what did it matter if U2 used a different code page? We’d just read the U2 data as binary data and feed that into the decryption algorithm.
The documentation for the API we use to read the database, UniObjects.Net, helpfully explains the advantages of retrieving certain types of data as binary sequences rather than strings, then mentions in passing that database files have to be “Type 1 or Type 19†in order for this to work. Ours wasn’t, so we were stuck with reading the data into strings.
My second thought was that there must be some way for C# code to invoke this mysterious SEQ() function directly. Why recreate the function when we could just invoke the original? The UniObjects.Net help file explains how to invoke various Pick Basic functions, but SEQ() wasn’t among them. UniObjects.Net provides 2 other ways of invoking database commands: Call() and Exec(). We use the former to invoke our custom Pick Basic subroutines, and the latter to run the U2 equivalent of SELECT commands, but I couldn’t figure out how to persuade either of these to just invoke the SEQ() function.
Well, surely this problem had already been solved by greater Windows programming minds than my own? But Googling “U2 SEQ Windows†just resulted in the inevitable bizarrely worded references to the band, a few pages about the database that happened to contain the other search terms (including an obscure blog named GigaMegaBlog), but nothing that suggested that any Windows programmer had ever called U2′s SEQ() function before.
So it appeared that the only way to solve this problem was to unlock the mysteries of the SEQ(). If it was translating characters into different values than our Windows app, it had to to be a code page problem. What code page was it using? Our U2 installs tend to be plain vanilla, so I’d be surprised if we weren’t using the default code page for U2. Which was…?
Googling U2 and “code page†didn’t return many hits either, but browsing through U2′s documentation uncovered the fact that, in NLS mode, the CHAR function (the inverse of SEQ()) returns values from the ISO-8859-1 character set. That rang a bell — ISO-8859-1 is a common character set in the Windows world too. Although our encrypted field was clearly never intended to be a Unicode string, this was the closest I could find to an indication of what character set SEQ() might be working with. So, I changedEncoding.ASCII.GetBytes() to Encoding.GetEncoding(“iso-8859-1″).GetBytes() and tried running the encrypted text through the algorithm again. No joy. The results were different than when I used ASCII, though. And, hold on a sec, when I was using ASCII a lot of characters were being translated to the same integer: 63. What the….?
Just then the programmer dropped by to show me that he’d found a web page that listed a character map that seemed to match what SEQ() was returning. The obscure, elusive character set was, in fact… ANSI.
ANSI? As in Windows ANSI? If it was just Windows ANSI, then entering Alt-0146 on the keypad would display a ’. Which it does. And changing the code to Encoding.Ansi.GetBytes() would fix the problem. Which it did. Duh.
So, a one word fix took an hour worth of detective work to uncover. All because nowhere on the great wide Internet was there a page that described how to write the U2 SEQ() function in .Net. Until now.