SQL result difference between VFP8 and VFP9

CREATE CURSOR cur1 (field1 c(1) NULL)
INSERT into cur1 VALUES (.Null.)
SELECT field1 as field2, field1 from cur1 INTO CURSOR cur2

VFP 8 shows
field2 - blank
field1 - .Null.

VFP9 shows
field2 - .Null.
field1 - .Null.

Any setting can fix it?

Jack
Wed Aug 16 11:56:08 CDT 2006

On Tue, 15 Aug 2006 23:51:02 -0700, Pazu
<Pazu@discussions.microsoft.com> wrote:

>CREATE CURSOR cur1 (field1 c(1) NULL)
>INSERT into cur1 VALUES (.Null.)
>SELECT field1 as field2, field1 from cur1 INTO CURSOR cur2
>
>VFP 8 shows
>field2 - blank
>field1 - .Null.
>
>VFP9 shows
>field2 - .Null.
>field1 - .Null.
>
>Any setting can fix it?

It looks to me like the VFP9 behavior is correct and VFP8 is wrong.
Why do you expect field2 to be blank when the field it comes from is
NULL?

Dan
Wed Aug 16 11:59:58 CDT 2006

It looks fixed to me. VFP8 is clearly wrong.

If you inserted NULL and you want a value to come out of your table, look at
NVL() in the help file.

Dan

Pazu wrote:
> CREATE CURSOR cur1 (field1 c(1) NULL)
> INSERT into cur1 VALUES (.Null.)
> SELECT field1 as field2, field1 from cur1 INTO CURSOR cur2
>
> VFP 8 shows
> field2 - blank
> field1 - .Null.
>
> VFP9 shows
> field2 - .Null.
> field1 - .Null.
>
> Any setting can fix it?

Pazu
Wed Aug 16 20:18:02 CDT 2006

Because now i need to check over a thousand of programs, and fix all of
programs with a chance to be failure.

Did MS offically announce this change? and any suggestions?

many thanks.

"Jack Jackson" wrote:

> On Tue, 15 Aug 2006 23:51:02 -0700, Pazu
> <Pazu@discussions.microsoft.com> wrote:
>
> >CREATE CURSOR cur1 (field1 c(1) NULL)
> >INSERT into cur1 VALUES (.Null.)
> >SELECT field1 as field2, field1 from cur1 INTO CURSOR cur2
> >
> >VFP 8 shows
> >field2 - blank
> >field1 - .Null.
> >
> >VFP9 shows
> >field2 - .Null.
> >field1 - .Null.
> >
> >Any setting can fix it?
>
> It looks to me like the VFP9 behavior is correct and VFP8 is wrong.
> Why do you expect field2 to be blank when the field it comes from is
> NULL?
>
>

AA
Thu Aug 17 04:16:02 CDT 2006

VFP9 is now consistent with the SQL standard in this case. It's also
consistent with MS SQLServer.

h=SQLSTRINGCONNECT("driver=sql native
client;server=(local);trusted_connection=yes;")
SQLEXEC(h,"USE Tempdb")
?SQLEXEC(h,"create table #cur1 (field1 Char(1) NULL)")
?SQLEXEC(h,"insert into #cur1 values(NULL)")
?SQLEXEC(h,"SELECT field1 as field2, field1 from #cur1",'cur2')
BROWSE LAST NOWAIT
* Shows .NULL. .NULL.

-Anders

"Pazu" <Pazu@discussions.microsoft.com> skrev i meddelandet
news:DE8EFC7A-112B-43C8-AE4B-CA761754010D@microsoft.com...
> CREATE CURSOR cur1 (field1 c(1) NULL)
> INSERT into cur1 VALUES (.Null.)
> SELECT field1 as field2, field1 from cur1 INTO CURSOR cur2
>
> VFP 8 shows
> field2 - blank
> field1 - .Null.
>
> VFP9 shows
> field2 - .Null.
> field1 - .Null.
>
> Any setting can fix it?