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);did not work! The variable l_spl_text would always be NULL. It turns out that changing that statement to:
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
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?
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.
ReplyDeleteOf course then i_spl_text should be blank, not null.
Wow - Informix.
ReplyDeleteThat'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.
Perhaps this (confusing) page yields a clue.
ReplyDeleteIt 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!
Thanks -- I knew that there was a reason.
ReplyDeleteAnd yes, Informix is ancient history. This is what happens when you get past 40 -- you are consigned to work appropriate to the senile.
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.
ReplyDeleteWe 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.