FIX: "Syntax Error" with EVAL of ALL or LTRIM in SELECT-SQL
ID: Q193079
|
The information in this article applies to:
-
Microsoft Visual FoxPro for Windows, versions 5.0, 5.0a
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
- 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
Additional query words:
kbVFp500abug kbVFp600fix kbSQL
Keywords :
Version : WINDOWS:5.0,5.0a
Platform : WINDOWS
Issue type : kbbug