Trivia: When does SE return a VARCHAR?

As the subject says, under what circumstances does SE return a VARCHAR value? Hint 1: 'never' is not the correct answer. Hint 2: it might not apply to all versions, but does apply to 7.24. Answer later, if no-one gets it right... -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>

Jonathan Leffler wrote: > > As the subject says, under what circumstances does SE return > a VARCHAR value? > > Hint 1: 'never' is not the correct answer. > Hint 2: it might not apply to all versions, but does apply to 7.24. > > Answer later, if no-one gets it right... Well, are you counting when the host variable (ESQL/C) is declared as type 'string'? In this case SE will return a char (or anything convertable to char) as a stripped NULL terminated "C" style string which I guess is the equivalent of what a varchar would return even into type char assuming the column did not contain explicit trailing spaces. Art S. Kagel

On Tue, 16 Mar 1999, Jonathan Leffler <jleffler@earthlink.net> asked: >As the subject says, under what circumstances does SE return >a VARCHAR value? > >Hint 1: 'never' is not the correct answer. I would have answered `never', so I'm out of the running. Kurt -- Drink your coffee! There are people sleeping in India!

Art S. Kagel wrote: > Jonathan Leffler wrote: > > Under what circumstances does SE return a VARCHAR value? > > > > Hint 1: 'never' is not the correct answer. > > Hint 2: it might not apply to all versions, but does apply to 7.24. > > > > Answer later, if no-one gets it right... > > Well, are you counting when the host variable (ESQL/C) is declared as > type 'string'? In this case SE will return a char (or anything > convertable to char) as a stripped NULL terminated "C" style string > which I guess is the equivalent of what a varchar would return even > into type char assuming the column did not contain explicit trailing > spaces. Reasonably close. Certainly, your ESQL/C code can use string or varchar variables to receive data from the database. The two are not completely equivalent - if you read a CHAR(30) variable containing "ABC" into an ESQL/C varchar[40] variable, then the 27 trailing blanks are present in the varchar variable, whereas if you read the same value into a string[40] variable, the 27 trailing blanks are omitted. The answer is: SELECT '' FROM SysTables WHERE TabID = 1; When you DESCRIBE the empty string, ESQL/C describes the value as a VARCHAR(0). On the other hand, if you replace that empty string with a single blank, then it is a CHAR(1) value. I only came across it because someone reported a bug to me in SQLCMD whereby the code does not allocate enough space when the value to be selected is a VARCHAR(0), leading to an error -1235. I was very, very surprised when I managed to reproduce the problem on SE! -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>

> > Jonathan Leffler wrote: > > > Under what circumstances does SE return a VARCHAR value? > > > > > > Hint 1: 'never' is not the correct answer. > > > Hint 2: it might not apply to all versions, but does apply to 7.24. Ooo! Ooo! I know this one! > The answer is: > > SELECT '' FROM SysTables WHERE TabID = 1; > > When you DESCRIBE the empty string, ESQL/C describes the value as a > VARCHAR(0). On the other hand, if you replace that empty string with > a single blank, then it is a CHAR(1) value. Yes, this was a change (bug fix) -- previously the above statement would return a single blank as a CHAR(1); this was a problem because if you concatenate '' to something else, you expect to get the something else, but instead you would get a blank tacked on. The fix to the bug was to return '' as VARCHAR(0), which also resulted in NULLs and '' being represented differently between different versions of the engine. Big can of worms, if you ask me. It may have started with Bug 54353 (don't quote me on that), and of course, after fixing that... Can I just suggest that people NOT use '' for *anything*? Ask yourself what you are trying to accomplish. What are the differences between NULL, '' and ' '? I worked with a customer who had a character column defined as NOT NULL, but inserted '' into this column. If the column REALLY shouldn't accept nulls, then what meaning does '' have in this column? Next problem: if '' (no spaces) is distinct from ' ' (one space), and you unload a '' column, it comes out as || (two delimiters with nothing in between), and, surprise surprise, that looks just like a NULL to the LOAD command. You are just asking for trouble. There is no good way to resolve these issues. Jonathan, I'm really mad at you now for reminding me of all this. I'll take this up with you separately. June -- june_t@hotmail.com Still alive (barely), recently escaped from Harrisburg, PA Please do not send Informix questions to this account. I would add 'Please do not send spam to this account' but I suppose I would be wasting my bits.

+---- June Tong <june_t@hotmail.com> wrote (Mon, 22 Mar 1999 21:02:27 -0800): |> > > Under what circumstances does SE return a VARCHAR value? |> > > |> > > Hint 1: 'never' is not the correct answer. |> > > Hint 2: it might not apply to all versions, but does apply to 7.24. |> The answer is: |> SELECT '' FROM SysTables WHERE TabID = 1; |> |> When you DESCRIBE the empty string, ESQL/C describes the value as a |> VARCHAR(0). On the other hand, if you replace that empty string with |> a single blank, then it is a CHAR(1) value. A strange choice indeed, since length=0 is valid for a character string descriptor - at least according to the 1992 SQL standard document ISO/IEC 9075:1992 [Third edition, 1992-11-01]. <snipped "I don't understand NULL" talk, possibly a big joke?> |There is no good way to resolve these issues. Yes there is. Follow a specific ISO/IEC SQL standard. Avoid using non-standard SQL extensions. And this implies: Don't use Oracle to learn SQL ;-) /pesky/