Re: String concatenation in 4gl

Hi, June asked: >From: junet@informix.com (June Tong) >Date: 22 Aug 1996 01:45:20 GMT >X-Informix-List-Id: <news.27336> > >Carlos Costa e Silva (minimal@mail.telepac.pt) wrote: > >: It's a surprise that > >: select a || b as c, sum(d) >: into x, y >: from e group by 1 > >: doesn't work in 4gl. > >Not so surprising, since the concatenation operator was only added in Version 5, >and 4GL (even version 6) is based on version 4 capabilities. Sad, but so. >What's sort of surprising is that you can do it in SQL. I forget why, but I'm >sure Jonathan knows... ISQL doesn't have to know very much about SQL statements to be able to send them to the database. Basically, it reckons that: * '--' marks the start of a comment and newline marks the end of it * '{' marks the start of a comment and '}' marks the end of it * A single quote marks the start of a string up to the next single quote * A double quote marks the start of a string up to the next double quote * A statement is terminated by a semi-colon which is neither in a comment nor in a quoted string This is accurate enough for statements other than CREATE PROCEDURE, which is why you cannot sensibly create stored procedures in ISQL, even though you can in DB-Access. As an aside for the curious, the above rules work even when you use two quote marks together to embed a single quote in a string -- ISQL simply sees to immediately adjacent strings. Because the '||' string concatenation operator does not fall into any of the special character categories listed above, ISQL 'understands' (or, rather, does not (mis)interpret) the '||' operator and allows the engine to handle it correctly. ISQL also thinks it knows which statements cannot be interpreted by the engine and handles them for you. The list includes: LOAD, UNLOAD, OUTPUT, INFO, CHECK TABLE, REPAIR TABLE ISQL does not know that the engine cannot handle non-preparable statements such as: PREPARE, DECLARE, EXECUTE, EXECUTE IMMEDIATE, FETCH, PUT, FREE, OPEN, CLOSE, DESCRIBE. Nor does it know about those statements introduced in 6.00 such as CONNECT, DISCONNECT, SET CONNECTION. All of these are passed to the engine, and the engine probably comes back with a -201 syntax error message. With EXECUTE PROCEDURE where the procedure returns values, ISQL almost certainly does not know that if there are return values in the output from DESCRIBE, then it should declare a cursor for the prepared statement, open it, fetch and display all the returned rows, and finally close and free the cursor. It would probably just try to use the normal EXECUTE command for a prepared statement, which might or might not work correctly -- it depends on the stored procedure, and on the engine. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>