Pages

▼

Monday, November 4, 2013

The Mysterious Ways of Informix

I was writing an SPL over the weekend, and for the life of me, I could not figure out why:
DEFINE l_spl_text CHAR(10000);
DEFINE l_spl_text_row CHAR(256);
...
  LET l_spl_text = "";
  FOREACH EXECUTE PROCEDURE sel_spl_text(i_procid) INTO l_spl_text_row
    LET l_spl_text = l_spl_text || l_spl_text_row;
  END FOREACH
did not work!  The variable l_spl_text would always be NULL.  It turns out that changing that statement to:
    LET l_spl_text = TRIM(l_spl_text) || l_spl_text_row;
fixed it.  Why would trimming extra spaces from an empty string fix this?
at

5 comments:

  1. asdfNovember 4, 2013 at 5:11 PM

    Don't know spl, but in some (most?) languages, the statement i_spl_text = "", actually gives you a full string of blanks, in this case, 1000 blanks. Then the concatenation is falling off the end, so to speak. You are trying to concatenate after all the blanks.

    Of course then i_spl_text should be blank, not null.

    ReplyDelete
  2. StormCchaserNovember 4, 2013 at 6:16 PM

    Wow - Informix.

    That's an ancient RDBMS - once a standalone company and last I heard, owned by IBM.

    I wish I could help, but we outlawed the use of Informix 4GL and stored procedures in our architecture. Life was much nicer that way, and we achieved very high performance without it.

    Those systems (big hotel on-line reservation systems) are still cranking out the reservations around the world, 24 years after we coded it.

    ReplyDelete
  3. DavidNovember 4, 2013 at 10:38 PM

    Perhaps this (confusing) page yields a clue.

    It seems to be saying that although in most cases for Informix an empty string is not equal to NULL, in the particular case of assigning an empty string to a variable you do indeed set it to NULL.

    In which case your first expression is appending something to NULL, which probably always yields NULL, while your TRIM function apparently coerces NULL to an empty string!

    NULLs are such a great idea and so well thought out in the SQL standard and the various implementations!

    ReplyDelete
  4. Clayton CramerNovember 5, 2013 at 8:45 AM

    Thanks -- I knew that there was a reason.

    And yes, Informix is ancient history. This is what happens when you get past 40 -- you are consigned to work appropriate to the senile.

    ReplyDelete
  5. StormCchaserNovember 6, 2013 at 6:27 PM

    BTW... when I called Informix ancient history, I guess I should have mentioned that the last work I did prior to retiring was one of those Informix based reservation systems.

    We chose Informix in 1989 because, at the time, it had the highest performance and it wasn't being sold by Larry Ellison. It turned out to be a good choice.

    ReplyDelete