FIX: "Syntax Error" with EVAL of ALL or LTRIM in SELECT-SQL (193079)


The information in this article applies to:

  • Microsoft Visual FoxPro for Windows 5.0
  • Microsoft Visual FoxPro for Windows 5.0a
This article was previously published under Q193079

SYMPTOMS

If you issue a SELECT SQL statement that contains a WHERE clause comparing a field value to an expression, which involves an ALLTRIM() or LTRIM() function that is passed to the EVALUATE() function, if the trim function trims a leading space or spaces the following error occurs when the SELECT statement is processed:
Syntax error.

RESOLUTION

As a workaround, trim the leading space or spaces by using STRTRAN() rather than ALLTRIM() or LTRIM().

The following example demonstrates the workaround: 
   SELECT *, .t. FROM temp_table ;
      WHERE temp_table.cUsageTL = EVALUATE(STRTRAN("lcService" ;
      + (" 91")," ","")) ;
      INTO CURSOR Temp4

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. This bug has been corrected in Visual FoxPro 6.0.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Run the following code from a program (.PRG) file: 
          CLOSE DATABASES ALL
      SET SAFETY OFF

      * Create table and index.
      CREATE table temp_table (cBTN c(2), cUsageTl c(10))
      INDEX on cUsageTl tag cUsageTl

      SET SAFETY ON

      * Insert some records.
      FOR lnj = 1 to 10
         INSERT into temp_table (cBTN, cUsageTl) values ("91", "Sp800/W")
         INSERT into temp_table (cBTN, cUsageTl) values ("91", "Sp800/X")
         INSERT into temp_table (cBTN, cUsageTl) values ("91", "Sp800/Y")
      ENDFOR

      * Initialize variables to Evaluate.
      lcService91="Sp800/W"
      lcService92="Sp800/X"
      lcService93="Sp800/Y"

      * Issue SELECT with trailing, no and leading space.
      SELECT *, .t. FROM temp_table WHERE temp_table.cUsageTl = ;
         EVALUATE("lcService"+ALLTRIM("91 ")) ;
         INTO CURSOR Temp2

      SELECT *, .t. FROM temp_table WHERE temp_table.cUsageTl = ;
         EVALUATE("lcService"+ALLTRIM("92")) ;
         INTO CURSOR Temp3

      * It fails with the leading space.
      SELECT *, .t. FROM temp_table WHERE temp_table.cUsageTl = ;
         EVALUATE("lcService"+ALLTRIM(" 93")) ;
         INTO CURSOR Temp4
In Visual FoxPro 5.0, the third select fails with a "Syntax error". This works without error in Visual FoxPro 6.0.

Demonstration of Workaround

Replace the third select statement in the preceding program example with the following code: 
   SELECT *, .t. FROM temp_table ;
      WHERE temp_table.cUsageTL = EVALUATE(STRTRAN("lcService" ;
      + (" 91")," ","")) ;
      INTO CURSOR Temp4
RESULTS: After running the program, no error occurs, and the STRTRAN() function trims the leading space.

REFERENCES

(c) Microsoft Corporation 1998. All Rights Reserved. Contributions by Jim Saunders, Microsoft Corporation
Modification Type:MajorLast Reviewed:8/12/1999
Keywords:kbbug KB193079
©2002 Microsoft Corporation. All rights reserved.