BUG: Record Inserted Even Though the Field Does Not Accept NULL (257608)

RESOLUTION

The workaround for this behavior is to create an error trapping routine to capture the error generated from the insertion of the .NULL. values and revert the table records. The following code illustrates how to accomplish this:

Paste the code below into a Visual FoxPro Program (.PRG) and run it:

CLEAR
SET MULTILOCKS ON
ON ERROR DO nullerror

CREATE TABLE myinsert ;
       (col1 i    NOT NULL, ;
	col2 c(5) NOT NULL, ;
	col3 c(5) NOT NULL)
CURSORSETPROP('Buffering', 3, 'MyInsert') && Enable Record Level Buffering

*!* These insertions will be reverted after they generate Error 1581
INSERT INTO myinsert VALUES (1,     NULL, 'xxx')
INSERT INTO myinsert VALUES (NULL, 'xxx', 'xxx')
INSERT INTO myinsert VALUES (1,    'xxx',  NULL)

ON ERROR

PROCEDURE nullerror

AERROR(aerrorarray)     && Creates Array with Error details
IF aerrorarray(1) = 1581
	WAIT aerrorarray(2) WINDOW TIMEOUT 2
	TABLEREVERT()
ENDIF
				

CREATE TABLE myInsert ;
	(col1 I    NOT NULL, ;
	 col2 C(5) NOT NULL, ;
	 col3 C(5) NOT NULL)

*!* Inserts a record with a value in col1, but not col2 or col3
INSERT INTO myInsert VALUES (1,     NULL, 'xxx')  

*!* Does not insert a record
INSERT INTO myInsert VALUES (NULL, 'xxx', 'xxx')  

*!* Inserts a record with values in col1 and col2, but not col3.
INSERT INTO myInsert VALUES (1,    'xxx',  NULL)