Set AutoNullFields bug

There are cases where AutoNullFields (as in SET AUTONULLFIELDS OFF) does 
not seem to work properly. Roland Wingerter did some testing and posted 
these results in another newsgroup:

Result of my tests with SET AUTONULLFIELDS OFF:

xdml Append BLANK    OK
oodml beginAppend        OK (rowset.autonullfields = false)

Append From DBF         OK
Append From Delimited     BUG

SQL Insert                         BUG
db.executeSQL Insert        BUG

The SQL Insert is the one that bothers me a little right now. Not 
horribly, and the one trouble-spot I could work around.

Ken

-- 
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm

Ken Mayer [dBVIPS] wrote
> There are cases where AutoNullFields (as in SET AUTONULLFIELDS OFF) does 
> not seem to work properly. Roland Wingerter did some testing and posted 
> these results in another newsgroup:
>
> Result of my tests with SET AUTONULLFIELDS OFF:
>
> xdml Append BLANK    OK
> oodml beginAppend        OK (rowset.autonullfields = false)
>
> Append From DBF         OK
> Append From Delimited     BUG
>
> SQL Insert                         BUG
> db.executeSQL Insert        BUG
---------
Thanks, Ken. I got these results with the code below.

See also QAID 1462.

Roland


// Test: Bug with SET AUTONULLFIELDS
clear
set database to; close tables

cAutonull = set("autonullfields")

SET AUTONULLFIELDS OFF
? "AUTONULLFIELDS:", set("AUTONULLFIELDS")
?

testxdmlAppend()
testoodmlAppend()
testAppendFromDBF()
testAppendFromDelimited()
testInsert()
testExecuteSQLInsert()

set autonullfields &cAutonull

return


FUNCTION testxdmlAppend
   if _app.databases[1].tableExists("autonulltest")
      _app.databases[1].dropTable("autonulltest")
   endif

   create table autonulltest (ID char(3), Name char(10))

   use autonulltest
   append blank
   replace id with '001'

   ? "Append BLANK"
   if name = NULL
    ? "Bug: Table contains NULL value"
   else
    ? "OK"
   endif

   close tables
Return

FUNCTION testoodmlAppend
   if _app.databases[1].tableExists("autonulltest")
      _app.databases[1].dropTable("autonulltest")
   endif

   create table autonulltest (ID char(3), Name char(10))

 q1 = new query()
   q1.sql   := [select * from autonulltest]
   q1.active  := true

   r1 = q1.rowset
   r1.autonullfields = false

   if r1.beginAppend()
    r1.fields["id"].value = "001"
      r1.save()
   endif

   ? "oodml beginAppend"
   if r1.fields["name"].value = NULL
    ? "Bug: Table contains NULL value"
   else
    ? "OK"
   endif

   close tables
Return


FUNCTION testAppendFromDBF
   if _app.databases[1].tableExists("autonulltest")
      _app.databases[1].dropTable("autonulltest")
   endif

   create table autonulltest (ID char(3), Name char(10))

   use autonulltest excl
   append blank
   replace id with '001', name with ''

   set safety off
   copy to autonulltest2
   zap
   set safety on

 append from autonulltest2
   go top
   ? "Append From DBF"
   if name = NULL
    ? "Bug: Table contains NULL value"
   else
    ? "OK"
   endif

   close tables
   dele table autonulltest2
Return

FUNCTION testAppendFromDelimited
   if _app.databases[1].tableExists("autonulltest")
      _app.databases[1].dropTable("autonulltest")
   endif

   create table autonulltest (ID char(3), Name char(10))

   use autonulltest excl
   append blank
   replace id with '001', name with ''

   set safety off
   copy to autonulltest delimited
   zap
   set safety on

 append from autonulltest delimited
   go top
   ? "Append From Delimited"
   if name = NULL
    ? "Bug: Table contains NULL value"
   else
    ? "OK"
   endif

   close tables
Return


FUNCTION testInsert
   if _app.databases[1].tableExists("autonulltest")
      _app.databases[1].dropTable("autonulltest")
   endif

   create table autonulltest (ID char(3), Name char(10))

   insert into autonulltest (ID) values ('001')
   insert into autonulltest (ID) values ('002')

   // This works, but here we append a blank value
   * insert into autonulltest (id, name) values ('001','')
   * insert into autonulltest (id, name) values ('002','')

   use autonulltest

   ? "SQL Insert:"
   if name = NULL
    ? "Bug: Table contains NULL value"
   else
    ? "OK"
   endif

   close tables
Return


FUNCTION testExecuteSQLInsert
   if _app.databases[1].tableExists("autonulltest")
      _app.databases[1].dropTable("autonulltest")
   endif

   create table autonulltest (ID char(3), Name char(10))

   _app.databases[1].executeSQL("insert into autonulltest (ID) values 
('001')")
   _app.databases[1].executeSQL("insert into autonulltest (ID) values 
('002')")

   use autonulltest

   ? "db.executeSQL Insert:"
   if name = NULL
    ? "Bug: Table contains NULL value"
   else
    ? "OK"
   endif

   close tables
Return

It was my understanding that "append from" does not respect the
setting of 
autonullfeilds.
At least it has never worked for me.

Marty can you clarify?

- glenn

"Ken Mayer [dBVIPS]" <dbase@_nospam_goldenstag.net> wrote in
message 
news:AAJbgb%23eIHA.2188@news-server...
> There are cases where AutoNullFields (as in SET AUTONULLFIELDS OFF) does 
> not seem to work properly. Roland Wingerter did some testing and posted 
> these results in another newsgroup:
>
> Result of my tests with SET AUTONULLFIELDS OFF:
>
> xdml Append BLANK    OK
> oodml beginAppend        OK (rowset.autonullfields = false)
>
> Append From DBF         OK
> Append From Delimited     BUG
>
> SQL Insert                         BUG
> db.executeSQL Insert        BUG
>
> The SQL Insert is the one that bothers me a little right now. Not 
> horribly, and the one trouble-spot I could work around.
>
> Ken
>
> -- 
> /(Opinions expressed are purely my own, not those of dataBased
> Intelligence, Inc.)/
>
> *Ken Mayer* [dBVIPS]
> /Golden Stag Productions/
> dBASE at goldenstag dot net
> http://www.goldenstag.net/GSP
> http://www.goldenstag.net/dbase/dBASEBooks.htm
> http://www.goldenstag.net/dbase 

> It was my understanding that "append from" does not respect the
setting of 
> autonullfeilds.
> At least it has never worked for me.
>
> Marty can you clarify?

That's what I remember as well.
We will have to review this again to verify Roland's findings.

- Marty -

Martin Kay
dataBased Intelligence, Inc.