Re: Null in string concatenation

June Tong wrote: > Jacques P Renaut wrote: > > Jamie Hatfield wrote: > > > A question pertaining to this: > > > > > > Why can I use NVL(...) and the FIRST # syntax in ISQL, but in > > > my 4gl code, the only way I can use them is to prepare them? Basically, because all ISQL does is prepare them too. > > > I think the FIRST # would be a great way to just get the > > > first row that my query returns without having to OPEN, FETCH, > > > and CLOSE a cursor, but it kinda defeats my purpose when I have > > > to prepare the statement instead of just using it inline. In I4GL, you can't use any syntax inline which was not allowed in version 4.10 SQL. > > Are you sure you are using them in ISQL and not DBACCESS? > > Dbaccess ships with the engine and is built with the current > > version of esql that is used to also build the engine. So it > > is aware of new syntax, whereas ISQL and I4GL tend to not be > > built with the newest version of esql and so they are > > not aware of the new syntax. That is true. > > So basically, yes, the front end tools occasionally have > > different parsers then the engine, that is why it works when > > you prepare it, so the front end parser doesn't look at it. Always have different parsers. > Yes, some of the "newer" syntax is "supported" by I-SQL. For > example, I found that "DROP PROCEDURE" works in I-SQL, even > though "CREATE PROCEDURE" doesn't. As near as I can make out, > this is because I-SQL simply sends a single statement (terminated > by ';') to the engine without parsing it. Precisely; I've diagnosed this on a number of occasions over the past three of four years in this news group. The whole CREATE PROCEDURE syntax with its embedded semi-colons is a travesty (they are not necessary; each statement has a keyword at the start), but that's why you cannot use CREATE PROCEDURE statements on anything except trivial procedures in ISQL. > If it sends "DROP PROCEDURE foobar;" to the engine, the engine > handles that without a problem. If it sends "CREATE PROCEDURE > foobar (i int) RETURNING int;" the engine returns an error. > > I haven't tested it, but I wouldn't be surprised to find that NVL > and FIRST x are "supported" in this same way. Yes; ISQL is blind to the syntax so it works. > Officially, neither of them is supported (unless this has been > added to 7.20 I-SQL, but I don't think so), but they may > both work. They will. Things like CONNECT, DISCONNECT and SET CONNECTION won't; they are not preparable statements. Just about everything else will work. Of course, you can't get at things like OPEN CURSOR c WITH REOPTIMIZATION (because the statement isn't preparable, and anyway wouldn't give you any benefit since no ISQL statement can be meaningfully reoptimized since there are no variables in ISQL scripts). -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>