Microsoft KB Archive/193079

= FIX: "Syntax Error" with EVAL of ALL or LTRIM in SELECT-SQL =

Article ID: 193079

Article Last Modified on 8/12/1999

-

APPLIES TO


 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 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.



Steps to Reproduce Behavior
  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.

