Highlighted
Trusted Contributor.
Trusted Contributor.
97 views

9d156 invalid where clause near "AND" failure

Hi People

An Acu4GL START, we populate the where clause unconditionally 

effectively the where clause built is :

           INTEGLOG_STATUS = 'C'  AND INTEGLOG_SYSTEM = 'EDMS'
      

We are running a single thread and therefore have not felt the need to wrap LOCK THREAD and UNLOCK THREAD around the START as we have in many other cases.

This process runs daily and has twice failed on the START with 9D156.

 

Would wrapping LOCK THREAD, UNLOACK THREAD around the START provide any protection?

 

 

Full body of code is .....

 

    INITIALIZE POSINTEGLOG-REC.
    MOVE 0 TO WS-COUNT-1
    MOVE "C" TO POSINTEGLOG-STATUS.
    MOVE "EDMS" TO POSINTEGLOG-SYSTEM.
* the following START has on 2 different occasions failed with 9D156
    PERFORM START-POSINTEGLOG-STATUS-SYSTEM-KEY
    IF WS-STATUS NOT = "00"
        EXIT PARAGRAPH
    END-IF.
          
*------------------------------------------          
 START-INTEGLOG-STATUS-SYSTEM-KEY.
*------------------------------------------
    PERFORM  SETUP-WHERE-CL-4-STATUS-SYSTEM-KEY.
   
    START INTEGRATION-LOG KEY NOT < INTEGLOG-STATUS-SYSTEM-KEY
              INVALID KEY
    MOVE "92" TO WS-STATUS.
   
    MOVE LOW-VALUES               TO A4GL_WHERE_CONSTRAINT.
   
   
   
*------------------------------------------
  SETUP-WHERE-CL-4-STATUS-SYSTEM-KEY SECTION.
*------------------------------------------
* this sets up the where clause   
        IF  POSINTEGLOG-STATUS = SPACE OR LOW-VALUES OR HIGH-VALUES OR ZERO 
            MOVE LOW-VALUES               TO A4GL_WHERE_CONSTRAINT
        ELSE
            MOVE SPACE                    TO A4GL_WHERE_CONSTRAINT
            MOVE 1                        TO WS-4GL-STR-PTR
            STRING
                   "INTEGLOG_STATUS = '"
                   POSINTEGLOG-STATUS
                   "'"
                 DELIMITED BY SIZE
                   INTO A4GL_WHERE_CONSTRAINT
                   WITH POINTER WS-4GL-STR-PTR
            END-STRING
 
            IF  POSINTEGLOG-SYSTEM = SPACE  OR LOW-VALUES OR HIGH-VALUES OR ZERO
                CONTINUE
            ELSE
                STRING
                       " AND "
                       "INTEGLOG_SYSTEM = '"
                       POSINTEGLOG-SYSTEM
                       "'"
                  DELIMITED BY SIZE
                      INTO A4GL_WHERE_CONSTRAINT
                      WITH POINTER WS-4GL-STR-PTR
                END-STRING
            END-IF
 
            INSPECT A4GL_WHERE_CONSTRAINT REPLACING TRAILING SPACES BY
                 LOW-VALUES
 
        END-IF.
 
0 Likes
2 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: 9d156 invalid where clause near "AND" failure

Good morning, @JimmyBoy .

The SQL Code 156 returned by your database (I guess it's MSSQL) means "Incorrect syntax near the keyword...".
See an on-line reference here.

Reading your code, it can be that one or more iterations of your paragraph attempted to write a wrong character, or a space, in A4GL_WHERE_CONSTRAINT.
Thus giving the syntax error.

Unfortunately, we cannot tell what happened.
Can it be that one of the variables you are concatenating in the A4GL_WHERE_CONSTRAINT are empty in your program?
Are you checking all of them, before to use the STRING statement?

If you think this situation can occur again, verify your source code and add all the necessary verifications.

At the same time, add a runtime trace file (FILE_TRACE 9; wrun32.exe -le trace.log) so that all the database operations will be recorded.

This will help in finding the syntax error and verify what Acu4GL sent to the database.

Regards.

----------------------------
Claudio Contardi
Product Support Engineer, Senior
Micro Focus
0 Likes
Highlighted
Trusted Contributor.
Trusted Contributor.

Re: 9d156 invalid where clause near "AND" failure

Hi Claudio

Thanks for your reply.  The values going into the where clause are hard-coded before each iteration.

It repeats the START only 3 times, using STATUS values  "A","C" and "S".  SYSTEM value  is constant each time; "EDMS"

If we thought the wrun32 was going to be doing parallel processing we might place LOCK THREAD and UNLOCK THREAD around setting up of the "where" clause and the actual START.  This prevents some other START processing mucking up our WHERE constraint.

 

I think I will do this anyway .. even though I don't know what could be mucking it up.

 

We cant replicate this and we cannot employ file trace 9 in prod.

 

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.