Varchar mapping, varchars & sql

I'm having difficulty with the following concept. Does anyone have the
patience to explain it?

Situation: I have a table in which all of the char fields are type varchar
and my app runs with varcharmapping on and set exact off . When it comes
time to run a few simple queries I get rows with blank values when the where
clause compares one of these fields to a character literal like

where extension = "PJX"

I have already determined that I can get the results I expect if I change
this to:

where extension == "PJX"

but I can't understand how or why comparing an empty column value to a
non-empty string can return .t. since the command line statements:

? "" = "PJX" && -> .f.

and

? "PJX" = "" && -> .t.

both make perfect sense to me, but the query suggests that "" = "PJX" is
true. (VFP9/sp1, enginbehavior 90 if that makes a difference).

-Lew

Dan
Fri Oct 27 10:53:48 CDT 2006

Check out SET ANSI in the help file:

Determines how comparisons between strings of different lengths are made
with the = operator in Visual FoxPro SQL commands.

Dan


Lew Schwartz wrote:
> I'm having difficulty with the following concept. Does anyone have the
> patience to explain it?
>
> Situation: I have a table in which all of the char fields are type
> varchar and my app runs with varcharmapping on and set exact off .
> When it comes time to run a few simple queries I get rows with blank
> values when the where clause compares one of these fields to a
> character literal like
>
> where extension = "PJX"
>
> I have already determined that I can get the results I expect if I
> change this to:
>
> where extension == "PJX"
>
> but I can't understand how or why comparing an empty column value to a
> non-empty string can return .t. since the command line statements:
>
> ? "" = "PJX" && -> .f.
>
> and
>
> ? "PJX" = "" && -> .t.
>
> both make perfect sense to me, but the query suggests that "" = "PJX"
> is true. (VFP9/sp1, enginbehavior 90 if that makes a difference).
>
> -Lew

sim
Fri Oct 27 10:56:25 CDT 2006

may be you need to SET EXACT ON


"Lew Schwartz" <lschwartz@sionline.com> wrote in message
news:usGxOtd%23GHA.924@TK2MSFTNGP03.phx.gbl...
> I'm having difficulty with the following concept. Does anyone have the
> patience to explain it?
>
> Situation: I have a table in which all of the char fields are type varchar
> and my app runs with varcharmapping on and set exact off . When it comes
> time to run a few simple queries I get rows with blank values when the
> where clause compares one of these fields to a character literal like
>
> where extension = "PJX"
>
> I have already determined that I can get the results I expect if I change
> this to:
>
> where extension == "PJX"
>
> but I can't understand how or why comparing an empty column value to a
> non-empty string can return .t. since the command line statements:
>
> ? "" = "PJX" && -> .f.
>
> and
>
> ? "PJX" = "" && -> .t.
>
> both make perfect sense to me, but the query suggests that "" = "PJX" is
> true. (VFP9/sp1, enginbehavior 90 if that makes a difference).
>
> -Lew
>
>

AA
Fri Oct 27 12:53:17 CDT 2006

SET EXACT does not affect comparisons in SQL queries, only compariasons in
Xbase code. Use SET ANSI for that.
-Anders

"sim" <sim3030@streamyx.com> skrev i meddelandet
news:45422e47_1@news.tm.net.my...
> may be you need to SET EXACT ON
>
>
> "Lew Schwartz" <lschwartz@sionline.com> wrote in message
> news:usGxOtd%23GHA.924@TK2MSFTNGP03.phx.gbl...
>> I'm having difficulty with the following concept. Does anyone have the
>> patience to explain it?
>>
>> Situation: I have a table in which all of the char fields are type
>> varchar and my app runs with varcharmapping on and set exact off . When
>> it comes time to run a few simple queries I get rows with blank values
>> when the where clause compares one of these fields to a character literal
>> like
>>
>> where extension = "PJX"
>>
>> I have already determined that I can get the results I expect if I change
>> this to:
>>
>> where extension == "PJX"
>>
>> but I can't understand how or why comparing an empty column value to a
>> non-empty string can return .t. since the command line statements:
>>
>> ? "" = "PJX" && -> .f.
>>
>> and
>>
>> ? "PJX" = "" && -> .t.
>>
>> both make perfect sense to me, but the query suggests that "" = "PJX" is
>> true. (VFP9/sp1, enginbehavior 90 if that makes a difference).
>>
>> -Lew
>>
>>
>
>

Lew
Fri Oct 27 14:21:32 CDT 2006

Ok, so I understand that set ANSI on means "follow the ansi standard for
string comparisons" which was the behavior I expected BUT with ansi set off,
the code doesn't seem to conform to *any* standard, not even VFP's string
comp/set exact protocols.
-Lew
"Dan Freeman" <spam@microsoft.com> wrote in message
news:OknnYAe%23GHA.1224@TK2MSFTNGP05.phx.gbl...
> Check out SET ANSI in the help file:
>
> Determines how comparisons between strings of different lengths are made
> with the = operator in Visual FoxPro SQL commands.
>
> Dan
>
>
> Lew Schwartz wrote:
>> I'm having difficulty with the following concept. Does anyone have the
>> patience to explain it?
>>
>> Situation: I have a table in which all of the char fields are type
>> varchar and my app runs with varcharmapping on and set exact off .
>> When it comes time to run a few simple queries I get rows with blank
>> values when the where clause compares one of these fields to a
>> character literal like
>>
>> where extension = "PJX"
>>
>> I have already determined that I can get the results I expect if I
>> change this to:
>>
>> where extension == "PJX"
>>
>> but I can't understand how or why comparing an empty column value to a
>> non-empty string can return .t. since the command line statements:
>>
>> ? "" = "PJX" && -> .f.
>>
>> and
>>
>> ? "PJX" = "" && -> .t.
>>
>> both make perfect sense to me, but the query suggests that "" = "PJX"
>> is true. (VFP9/sp1, enginbehavior 90 if that makes a difference).
>>
>> -Lew
>
>

Dan
Fri Oct 27 15:20:45 CDT 2006

Yup.

With VFP's SQL implementation you've always had to pay particular attention
to SET EXACT and SET ANSI. Mismatched settings can have dire effects on
performance (more so in older versions).

It's just one of those fox-isms.

Dan

Lew wrote:
> Ok, so I understand that set ANSI on means "follow the ansi standard
> for string comparisons" which was the behavior I expected BUT with
> ansi set off, the code doesn't seem to conform to *any* standard, not
> even VFP's string comp/set exact protocols.
> -Lew
> "Dan Freeman" <spam@microsoft.com> wrote in message
> news:OknnYAe%23GHA.1224@TK2MSFTNGP05.phx.gbl...
>> Check out SET ANSI in the help file:
>>
>> Determines how comparisons between strings of different lengths are
>> made with the = operator in Visual FoxPro SQL commands.
>>
>> Dan
>>
>>
>> Lew Schwartz wrote:
>>> I'm having difficulty with the following concept. Does anyone have
>>> the patience to explain it?
>>>
>>> Situation: I have a table in which all of the char fields are type
>>> varchar and my app runs with varcharmapping on and set exact off .
>>> When it comes time to run a few simple queries I get rows with blank
>>> values when the where clause compares one of these fields to a
>>> character literal like
>>>
>>> where extension = "PJX"
>>>
>>> I have already determined that I can get the results I expect if I
>>> change this to:
>>>
>>> where extension == "PJX"
>>>
>>> but I can't understand how or why comparing an empty column value
>>> to a non-empty string can return .t. since the command line
>>> statements:
>>>
>>> ? "" = "PJX" && -> .f.
>>>
>>> and
>>>
>>> ? "PJX" = "" && -> .t.
>>>
>>> both make perfect sense to me, but the query suggests that "" =
>>> "PJX" is true. (VFP9/sp1, enginbehavior 90 if that makes a
>>> difference).
>>>
>>> -Lew

AA
Fri Oct 27 13:18:17 CDT 2006

The rules for string comparisons are nbot the same in SQL and Xbase. Set
Exact only affects Xbase, Set Ansi affects SQL.
SQL follows the mathematical rule the if x=y then y=x.
In SQL
WHERE ''='PJX' and
WHERE 'PJX'=''
return the same result.
-Anders

"Lew Schwartz" <lschwartz@sionline.com> skrev i meddelandet
news:usGxOtd%23GHA.924@TK2MSFTNGP03.phx.gbl...
> I'm having difficulty with the following concept. Does anyone have the
> patience to explain it?
>
> Situation: I have a table in which all of the char fields are type varchar
> and my app runs with varcharmapping on and set exact off . When it comes
> time to run a few simple queries I get rows with blank values when the
> where clause compares one of these fields to a character literal like
>
> where extension = "PJX"
>
> I have already determined that I can get the results I expect if I change
> this to:
>
> where extension == "PJX"
>
> but I can't understand how or why comparing an empty column value to a
> non-empty string can return .t. since the command line statements:
>
> ? "" = "PJX" && -> .f.
>
> and
>
> ? "PJX" = "" && -> .t.
>
> both make perfect sense to me, but the query suggests that "" = "PJX" is
> true. (VFP9/sp1, enginbehavior 90 if that makes a difference).
>
> -Lew
>

Lew
Sat Oct 28 13:25:07 CDT 2006

...and the purpose of set ansi off is................?
"Dan Freeman" <spam@microsoft.com> wrote in message
news:%23C8EjVg%23GHA.4544@TK2MSFTNGP05.phx.gbl...
> Yup.
>
> With VFP's SQL implementation you've always had to pay particular
> attention
> to SET EXACT and SET ANSI. Mismatched settings can have dire effects on
> performance (more so in older versions).
>
> It's just one of those fox-isms.
>
> Dan
>
> Lew wrote:
>> Ok, so I understand that set ANSI on means "follow the ansi standard
>> for string comparisons" which was the behavior I expected BUT with
>> ansi set off, the code doesn't seem to conform to *any* standard, not
>> even VFP's string comp/set exact protocols.
>> -Lew
>> "Dan Freeman" <spam@microsoft.com> wrote in message
>> news:OknnYAe%23GHA.1224@TK2MSFTNGP05.phx.gbl...
>>> Check out SET ANSI in the help file:
>>>
>>> Determines how comparisons between strings of different lengths are
>>> made with the = operator in Visual FoxPro SQL commands.
>>>
>>> Dan
>>>
>>>
>>> Lew Schwartz wrote:
>>>> I'm having difficulty with the following concept. Does anyone have
>>>> the patience to explain it?
>>>>
>>>> Situation: I have a table in which all of the char fields are type
>>>> varchar and my app runs with varcharmapping on and set exact off .
>>>> When it comes time to run a few simple queries I get rows with blank
>>>> values when the where clause compares one of these fields to a
>>>> character literal like
>>>>
>>>> where extension = "PJX"
>>>>
>>>> I have already determined that I can get the results I expect if I
>>>> change this to:
>>>>
>>>> where extension == "PJX"
>>>>
>>>> but I can't understand how or why comparing an empty column value
>>>> to a non-empty string can return .t. since the command line
>>>> statements:
>>>>
>>>> ? "" = "PJX" && -> .f.
>>>>
>>>> and
>>>>
>>>> ? "PJX" = "" && -> .t.
>>>>
>>>> both make perfect sense to me, but the query suggests that "" =
>>>> "PJX" is true. (VFP9/sp1, enginbehavior 90 if that makes a
>>>> difference).
>>>>
>>>> -Lew
>
>

Dan
Mon Oct 30 14:17:29 CST 2006

....... open to speculation. <s>

Lew wrote:
> ...and the purpose of set ansi off is................?
> "Dan Freeman" <spam@microsoft.com> wrote in message
> news:%23C8EjVg%23GHA.4544@TK2MSFTNGP05.phx.gbl...
>> Yup.
>>
>> With VFP's SQL implementation you've always had to pay particular
>> attention
>> to SET EXACT and SET ANSI. Mismatched settings can have dire effects
>> on performance (more so in older versions).
>>
>> It's just one of those fox-isms.
>>
>> Dan
>>
>> Lew wrote:
>>> Ok, so I understand that set ANSI on means "follow the ansi standard
>>> for string comparisons" which was the behavior I expected BUT with
>>> ansi set off, the code doesn't seem to conform to *any* standard,
>>> not even VFP's string comp/set exact protocols.
>>> -Lew
>>> "Dan Freeman" <spam@microsoft.com> wrote in message
>>> news:OknnYAe%23GHA.1224@TK2MSFTNGP05.phx.gbl...
>>>> Check out SET ANSI in the help file:
>>>>
>>>> Determines how comparisons between strings of different lengths are
>>>> made with the = operator in Visual FoxPro SQL commands.
>>>>
>>>> Dan
>>>>
>>>>
>>>> Lew Schwartz wrote:
>>>>> I'm having difficulty with the following concept. Does anyone have
>>>>> the patience to explain it?
>>>>>
>>>>> Situation: I have a table in which all of the char fields are type
>>>>> varchar and my app runs with varcharmapping on and set exact off .
>>>>> When it comes time to run a few simple queries I get rows with
>>>>> blank values when the where clause compares one of these fields
>>>>> to a character literal like
>>>>>
>>>>> where extension = "PJX"
>>>>>
>>>>> I have already determined that I can get the results I expect if I
>>>>> change this to:
>>>>>
>>>>> where extension == "PJX"
>>>>>
>>>>> but I can't understand how or why comparing an empty column value
>>>>> to a non-empty string can return .t. since the command line
>>>>> statements:
>>>>>
>>>>> ? "" = "PJX" && -> .f.
>>>>>
>>>>> and
>>>>>
>>>>> ? "PJX" = "" && -> .t.
>>>>>
>>>>> both make perfect sense to me, but the query suggests that "" =
>>>>> "PJX" is true. (VFP9/sp1, enginbehavior 90 if that makes a
>>>>> difference).
>>>>>
>>>>> -Lew