Just ran across an instance where I had SET EXACT ON but didn't specify SET ANSI (for which the default is OFF). After seeing a query for CHICAGO contain results including others like CHICAGO HEIGHTS, I investigated and thought my SET EXACT was OFF. Nope, it was ON but my SET ANSI was OFF. Setting it to ON gave me the results I wanted.
I'm just surprised this never bit me in all my years doing VFP; I guess because I've usually been using a RDBMS (like MySQL/MariaDB).
I use it OFF, but then I make exact comparisons using == and using the full length of the field, not just the length of the string.
In example, if the field is C(20) and the string is "word", I first assign PADR(lcString, 20) and then I search it, so I make it independent of SET ANSI.
El 8/4/2017 1:20 a. m., mbsoftwaresolutions@mbsoftwaresolutions.com escribió:
Just ran across an instance where I had SET EXACT ON but didn't specify SET ANSI (for which the default is OFF). After seeing a query for CHICAGO contain results including others like CHICAGO HEIGHTS, I investigated and thought my SET EXACT was OFF. Nope, it was ON but my SET ANSI was OFF. Setting it to ON gave me the results I wanted.
I'm just surprised this never bit me in all my years doing VFP; I guess because I've usually been using a RDBMS (like MySQL/MariaDB).
[excessive quoting removed by server]
If you leave them both OFF, you won't run into trouble. Use == for "exactly equals" and you don't need to mess with the setting command.
But set them both ON if that's what you want. With the two settings different, SEEK and SELECT yield different results. Think of ANSI as SET EXACT for SQL statements.
On Fri, Apr 7, 2017 at 7:20 PM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
Just ran across an instance where I had SET EXACT ON but didn't specify SET ANSI (for which the default is OFF). After seeing a query for CHICAGO contain results including others like CHICAGO HEIGHTS, I investigated and thought my SET EXACT was OFF. Nope, it was ON but my SET ANSI was OFF. Setting it to ON gave me the results I wanted.
I'm just surprised this never bit me in all my years doing VFP; I guess because I've usually been using a RDBMS (like MySQL/MariaDB).
[excessive quoting removed by server]
On 2017-04-08 06:51, Ted Roche wrote:
If you leave them both OFF, you won't run into trouble. Use == for "exactly equals" and you don't need to mess with the setting command.
But set them both ON if that's what you want. With the two settings different, SEEK and SELECT yield different results. Think of ANSI as SET EXACT for SQL statements.
Am I right in saying that my VFP SET ANSI setting had no bearing on my queries to MySQL/MariaDB?
-
Fernando D. Bozzo -
mbsoftwaresolutions＠mbsoftwaresolutions.com -
Ted Roche