Problem:

When using MFEEE 3.0, a strange compilation error occurs when using the follow SQL syntax...

           EXEC SQL

               UPDATE EMPLOYEE

                   SET STREET = CONCAT(:ASTRING, SUBSTR(STREET,1,20))

               WHERE E_NO = 1

           END-EXEC

When this code is compiled then  the following erroris produced:

COBSQ0999E -084 UNACCEPTABLE SQL STMT - ILLEGAL SYMBOL 'CONCAT'   :

The item STREET is defined as 20 characters long, and the host variable ASTRING is defined as 4 bytes, with a fixed value of 'ST. '.

This only appears when Validate is on and the following additional SQL Option directives are set:

SQLDA-VER=1 HYPHEN-IN-CURSOR AUTHID=TUTORIAL VALIDATE-ERR-LVL=E

How can this be avoided..?

Resolution:

There are 2 issues in this scenario.

By using CONCAT in this way, the resultant field would be 24 characters long, which would be too long for the column STREET.

This would result in an sqlcode of -1053, when the program is run, which translates as a -084 when compiled.

To avoid this, make sure only 20 characters fit in the column STREET from the CONCAT syntax.

It should be coded to use the  4 byte field "ST. " and only 16 of the character in the column STREET.

The actual code itself needs to have the CONCAT in brackets as follows...

EXEC SQL

   UPDATE EMPLOYEE

       SET STREET = (CONCAT(:ASTRING, SUBSTR(STREET,1,16)))

        WHERE E_NO = 1

END-EXEC

Please note that the (1,16) will only use 16 characters of the column STREET starting from character 1.

In the build settings, the VALIDATE-ERR-LVL=E directive should be  removed from the SQL Preprocessor, and the Message Level, on the GENERAL tab, should be changed to WARNING,so that the SQL messages are produced during the build.