ODP.NET OracleDecimal Conversion Fun

This is one of those self-supportive posts I love to discover.

I have used ODP.NET and Oracle for ages (the latter for more than 20 years), and generally I don't run into anything too odd, but in a very recent project I encountered something that threw me. In fact, it took a few kicks at the can to even be sure it was what I thought.

Since the project was in VB.NET, I'll show a similar line:

Dim intVar as Integer = CInt(OracleCmd.Parameters("ParamName").Value)

The Oracle command parameter in this case was a NUMBER type being returned from a stored procedure, which in ODP.NET returns an OracleDBType.OracleDecimal.

As soon as this line was encountered (and a Long conversion did the same) the code would fault with a conversion type error. Now, at first glance it threw me because I wasn't thinking as clearly as I probably should have been. And I asked myself, "How the hell can a cast from a decimal to an integer fault?" Worse was that I was misled by the actual value, which was the number 1; and my ignorance of the obvious was reinforced by a slew of documentation that noted the OracleDecimal type was equivalent to the .NET Framework's System.Decimal type.

In reality, of course, OracleDecimal is a boxed value. It may contain a decimal, but it is stored in a way that prevents an explicit cast. (I understand you can CDec it, or do a decimal cast from the ORacleDecimal, though I didn't test this.) That means that you can't expect the line shown above to work. That said, you can expect this to work reliably:

Dim intVar as Integer = CInt(OracleCmd.Parameters("ParamName").Value.ToString())

This works because the ToString() append returns a new string to memory that is a representation of the string equivalent of the primary stored number in the OracleDecimal box. It doesn't technically un-box the object, but it serves the same essential purpose and by casting a new string the CInt (Or CLng) then acts on the string returned. So, in the case of my quirky situation, ToString() returned "1" and that was then equivalent to writing CInt("1") to return a 1.

As inelegant as that all is (I'm sure there is a more pleasant way of working this magic) it works, and when you are supporting legacy code (in this case a conversion from the old MS Oracle data services provider to ODP.NET) it represents a workable solution. It is much faster than fiddling about trying to un-box the actual value, or whatnot, and you can search and replace the damn thing fairly efficiently.

The conclusion is that I was reminded of how boxed and unboxed objects behave, and how badly misleading documentation can be (even when it is correct). What hasn't been explained to me by this, of course, is why Oracle chose to not handle the conversion internal in the provider and return a System.Decimal. It strikes me as likely this would have been a much less jarring when having to manage legacy code conversions to the new ODP.NET model. But, hey, I guess I can admit it reminded me of the fabulous complexity of simple things!

Quality of Design

This weekend I had an opportunity to look at an Access 2003 platform database/application, and it reminded me that quality of design can exceed platform capabilities. As any developer who has ever dealt with Access, especially ancient versions, knows, the product is a kludge upon a kludge. It tends to encourage bad design at the outset because of its lack of data/presentation layer separation, and it's crimes against productivity (in terms of user interface choices) are myriad. But the application I looked at was atypical of Access-based design in two distinct ways: it was designed with a mostly correct data model, making it migration-friendly even after all these years; and it was developed with a degree of restraint that avoided at least many of the traps of Access applications. That isn't to say it was the perfect product model, because Access just doesn't manage to engender perfection, but it was one of those pleasant review experiences where it so easy to see how to move it ahead without damaging its quality of design, it's functionality, or sacrificing future opportunities to the gods of necessity.

The takeaway from this is simple: platform can be overcome by good design to a great extent.


A Little DB2 SQL Adventure

I don't usually blog about this sort of technical stuff, though I deal with it daily. That probably explains right there why I avoid it as if it is tainted by the plague. But this particular problem was interesting enough that I thought it might be worth conveying it.

The challenge for my associate came in the form of a bit of SQL that needed to pull apart a string (unknown until runtime) before it was inserted into a data store. A stored procedure wasn't possible, and the parsing requirement was unavoidable. It also had to be done with inline SQL.

It was known that this particular string could have several forms:
  1. A string containing alphanumeric elements separated by a dash;
  2. A string containing alphanumeric elements without a dash; and
  3. A zero-length string containing nothing (that could not be converted to a NULL on the incoming side for reasons not relevant here, but such that COALESCE was suddenly not a functional solution).
The SQL causing them grief had two variations in the INSERT, but to isolate the data elements I'll stay with simple SELECT statements from the system dummy table. And we'll call the input containing the string {Landmark} for the sake of abstraction.

The first SELECT would look thus:

In essence the data element expected for return would see a dash in the Landmark string and return everything left of that dash. (For those wondering why not use LEFT in this context, it ultimately creates the same basic problem in as much as it will fail to return correctly if the Landmark value has no dash. So, it would have been a different approach with the same sticking point.)

The second SELECT would look thus:

SELECT SUBSTR('{Landmark}',1,LOCATE('-','{Landmark}')) FROM SYSIBM.SYSDUMMY1;
A simpler view of the same issue, which is easily illustrated by plugging in a few actual values that landmark might pass in.

The First Form

To focus now we can strip the SELECT and FROM SYSIBM.SYSDUMMY1 parts of the exemplar. It leaves us with the inline SQL reading:


And the SQL works perfectly if the data coming in contains a dash and is not zero-length. In other words, '12345-12345-666' parses perfectly:

SUBSTR('12345-12345-666',1,LOCATE('-','12345-12345-666' )-1)

...returns a LOCATE value of 6, and when you subtract 1 it works out to return the substring starting at the first character and returning the first 5 characters.

The problem is that if the incoming string contains no dashes the LOCATE returns 0, and when you subtract the 1 from 0 the SUBSTR function rightfully chokes on the parameter. For example:

SUBSTR('1234512345666',1,LOCATE('-','1234512345666' )-1)

...is really:


And SUBSTR doesn't quite know hat to do with that.

An even more complex problem arises when the string coming in is zero-length, because the resultant SUBSTR not only faults on -1 for is third parameter, it chokes on the first one having no length.

Using a stored procedure here would be glorious because it could check the length and do all that work in an orderly way, but my associate was stumped by how to get it to work inline and handle the three possibilities of proper dashes, no dashes and a zero-length condition.

What does work though is the inline use of a dual CASE that can handle the three possibilities (yes, it is not the only possibility, but it turned out to be the easiest to convey, even if it is ugly as sin). The statement then would have looked thus:

CASE WHEN LENGTH('{Landmark}')=0 THEN ''
ELSE SUBSTR('{Landmark}',1,LOCATE('-','{Landmark}')
- (CASE WHEN LOCATE('-','{Landmark}') > 0 THEN 1 ELSE 0 END))

It parses by checking Landmark length and returning a zero-length safely if it is thus; and otherwise it runs the SUBSTR with an inner CASE block that tests the LOCATE return value and returns 1 if it is >0 and 0 otherwise. (This return is subtracted from the LOCATE value, so returning 1 subtracts 1, else we subtract 0.) With an example in place:


The benefit is that this protects you from the three possible data conditions.

The Second Form

For the stripped form of the second:

...we can modify it with a simpler inline CASE conditional. We end up with:

SUBSTR((CASE WHEN LENGTH('{Landmark}')= 0 THEN ' ' ELSE '{Landmark}' END),1,LOCATE('-','{Landmark}'))

Or in the actual SELECT form:

SELECT SUBSTR((CASE WHEN LENGTH('{Landmark}')= 0 THEN ' ' ELSE '{Landmark}' END),1,LOCATE('-','{Landmark}')) FROM SYSIBM.SYSDUMMY1;

This works because SUBSTR accepts a 0 as its third parameter, and all we need to do is ensure that Landmark (first parameter) is never a zero-length string. The specific CASE part is:

CASE WHEN LENGTH('{Landmark}')= 0 THEN ' '
ELSE '{Landmark}'

In other words, return a single space if the LENGTH of the actual field data is zero.

Final Thoughts

No one would ever claim that SQL was pretty to look at, but it illustrates how powerful SQL actually can be, and how it is possible to make the SQL layer of a solution (even without aid of a stored procedure) impose some order on incoming data elements.


Getting Paid

This is one of those posts of frustration, so take it for what it is worth.

I have been doing development work since the very early eighties, and a trend has been becoming more notable in those many, many years. Part of it is probably that the industry has devalued itself with the pretence that software is an entertainment-focused industry. You flop Facebook into place, for example, and it becomes the superficial representation of the industry. That it is a poorly designed consumers-as-products farce is irrelevant. Businesses see it, become distracted by it, and suddenly everything must connect, or be like it, or, worse, become some extension of it. This changes expectations in a way that makes it harder to sell the idea of functional software as a business helper, or at the very least changes how people value what is built to support their operational requirements.

The actual trend though is purely financial. A vast number of businesses seem to consider good, focused development of software as a nickel-and-dime extra. Getting a fair contract price for required software development is tough, and even when possible getting paid is sometimes nearly impossible. It means that custom (and consequently effective) solutions are fast becoming less common. It isn't lack of need , or even lack of recognized need, but that businesses just don't feel like paying for a proper solution when they can get something "close enough" without paying. And I'm not talking about open-source here, either, but the far more problematic application of poorly-fit tools to clearly defined problems. 

A recent client (who shall remain nameless, but is an enormous distributor of entertainment wares) proves the nature of the end-result of devaluing the idea of proper-fit software solutions. They essentially managed a multimillion dollar warehouse system via CSV files, and spreadsheets. Now, I have nothing against either, but the idea that this is a suitable platform for big enterprise management and operations is nonsensical. It creates latency, reporting issues, and data quality concerns when you have to manage across such transport forms. Of course, now the problem is that to change is costly, and scary, so more putty gets lodged into more cracks -- and there is no apparent cohesive approach to solving the real problem, which is the lack of responsive integrative systems on the operational front. 

But again the real issue isn't the poor thinking so much as that even with it there is an apparent sense that there's no need to actually that the provision of software services with any respect. The same client base who will carp about solutions being late (not a problem I've made any of them face) are not so conscientious as to make their payments to their service providers on time. It is as if there is some new level of self-centred entering the world of business, and it seems irrelevant whether the provider is good or indifferent. It seems like there is a new rule that reads, "pay as late as possible, and well past due if possible; screw whether we need this service again." And it is that last part that confuses me.

My clients have universally praised me for my focus, my solutions, and my concerns about their businesses. And yet, of late, I spend an inordinate amount of time chasing them for payments. That providing quality service costs something seems, to many, a foreign concept. Maybe they believe I have a money tree, and it will support the family so well that I will always be present when needed?

I know I'm not the only person in this industry who is experiencing this trend toward indifference about quality of service, quality of solution, and general quality. I talk to many each week who say the same thing, which is that many of their clients are well behind the curve in paying for what they received. And while some lag is always forgiven, it reaches a point where all of them have said the words, "I can't continue in an industry where the quality is irrelevant, and the pay is unreliable." For the world this represents a serious problem, because while the young dogs may provide the next new iPhone app, it is the seasoned developers who provide the applications no one sees, but everyone uses. This squeezing of that talent pool is a critical mass issue the industry will eventually face in a bad way.

Now, I will return to fishing for cash, since my money tree has not bloomed this year...yet.


Life Lessons

As I sit here with my coffee this morning I contemplate life lessons learned over the last 6 months. Primarily, I'm thinking about trust; but I'm also thinking about the method whereby we self-induce our experiences.

Trust is the principle focus of my thoughts today, though, specifically how perception can alter our awareness of relationships. If you take any two people who have a trust deficit you need to consider the views of both to reach an appreciation of truth. After all, nothing is black or white in this world. The sole exception of this is when trust is broken by intent, of course, since this represents a decision by one participant to use deception as a means to some end. Once that happens, they have abandoned their view as truthful. Any truth they had is washed away because they are choosing to abandon their integrity, and integrity of viewpoint is a key factor in associating it with truth. 

In the Star Wars films there is a line, in The Empire Strikes Back, where Obi-wan Kenobi remarks that his lies are true from a certain point of view. He is commenting upon his view that Anakin Skywalker was killed by Darth Vader. His view is that Anakin's acceptance of the title alone was an expression of choice, and therefore essentially an abandonment of the righteous path, and a form of spiritual death. Unlike Master Yoda, whose convictions about matters are, in Episodes 1 to 3, founded on dogma, Obi-wan is actually striding along a far more subtle path, and his weariness is a recognition of the price of holding a stringent view. When one harkens back to the fight at the end of Episode 3, the issue is further clarified, because there is another critical formative line. Obi-wan says at one point, just before Anakin's choice to finally attack, that he has the high ground. He is not only speaking tactically there, but from a spiritual perspective he is commenting upon the fact, without dismissing Anakin's views categorically, that there is a purity in his path that his brother lacks. This purity is why Obi-wan manages to sustain his view that purity of truth is less important than purity of intent.

In real life, obviously, there is never such a linear proof of the idea of truth being mutable. The "he-said-she-said" syndrome comes about precisely because of this, and it deeply affects all future communications between parties who suffer moments of mistrust. That said, the last 6 months of my life have pivoted around the ideas of trust, trust misplaced, and viewpoints.

About 13 years ago I became attached to a development project that involved 2 parties, neither of who, behaved entirely respectably. When they came to a falling out point, I had to make a choice whose products my effort was producing. I had to decide who owned the idea. Following logic, I decided that IP had to accrue to the people who seeded the idea, even if their original seed was pale by comparison to end results. This decision cost me financially at the time, leaving me holding a massive related debt, and drove me to sustain the development effort when, in lieu of being able to pay necessary rates, I was offered shares. To shorten the path, in retrospect I mistook my options then. I ought to have walked away, taken the loss, and attenuated my pain, because from the point I chose the other path I was essentially doomed to end that journey as a castaway. Again in retrospect, all the signs of manipulation were present, but despite my own con-man tendencies, I decided trust was fundamental to the process, and even when I mistrusted actions I didn't mistrust intent. That was probably the worst single decision I ever made, because I suspect I was used from the outset and thrown away when the risks of exposure of that use outweighed the value. That said, I would wager the principals of the company would hold I was the problem child, though it doesn't explain that they eliminated my share position by way of bankruptcy, before they did some deal with a company that had been courting them for months. In the end actions speak volumes, and the fact they are using the product I built under the guise of a new company is complimentary, I suppose.

The point I'm aiming at, though, is not a sour grapes moment. It is an observation about basic trust contracts, and how they are valueless if either party betrays the terms. Revisionist viewpoints cannot overcome intent, because the intent to gain is inherently selfish. There is no truth in business, because trust is sacrificial. It is the mechanical way of business. Contacts are, as they say, rather cheap. 

Truth cannot exist without trust, because only trust imparts a foundation for truthful exchanges. And trust that is one sided is a suicidal behaviour. I know in my case it has destroyed me financially, and is probably going to land my family on the streets, not because I can't work onward so much as because I'm still faced with having committed fully to a cause for 13 years, and I left with nothing to show for it. I have the pleasure to watch my efforts enrich others, without even a Merry Christmas. And yet I feel less bitter than weary, because the real violation happened so long ago it is not in the forefront of my mind. I caused my current distress by misplacing my trust early, and was repaid in full and then some by folks whose trust was never really founded. 

The life lesson I take from this is not that one should never trust. To the contrary, one has to. The lesson is more that we need to be conscious of the warning bells. My stress led to a mild heart attack (a pair of them 2 weeks apart), and my life is worth more than any creation my work generates. And to dwell upon this sort of thing is less important than to rise up and move on. No matter what happens in life, one can cling to Obi-wan's statement, with the caveat that to sustain truth you need to have a foundation of trust. Faith, I suppose, is an adequate word. Faith in self, if not in some higher power.

Ultimately the choices we make dish us, and we make those same choices today, even in moments of bleak struggle. To allow past mistakes to force one to repeat mistakes is counterproductive. And, really, as I can attest you can only run from your bad choices so long. I expect that is true for everyone.