Wiki Home

VFP Gotchas Database


Namespace: VFP
Remote views can eat data. Even though a view and field was updateable, an improper UpdateName can cause the data to disappear without any error message. It is the lack of error that makes this a gotcha.

In the following code, a db is created on the server, a dbc and view are created, a record is added, 2 values are assigned (123 and "ABC"), TableUpdate() reports that all is well, but looking at what was sent to the server with Sql Profiler, you can see that the ABC is nowhere to be found.

#DEFINE MYCONSTR "DRIVER=SQL Server;Network=DBMSSOCN;SERVER=dev.personnelware.com;uid=sa;pwd=abc"

lnX = SQLStringConnect( MYCONSTR )
SQLExec( lnX, "drop table foo" )
SQLExec( lnX, "create table foo (kFoo_pk int primary key, cFid1 char(10) null )" )
SQLDisconnect(lnx)

Create Database foo
Create Connection dbccon connstring MYCONSTR

CREATE SQL VIEW v_foo REMOTE CONNECTION dbccon AS ;
		select kFoo_pk, cFid1 from foo

DBSetProp("v_foo", 'View', 'SendUpdates',.T.)
DBSetProp("v_foo", 'View', 'Tables',[dbo.foo])
DBSetProp("v_foo.kFoo_pk",'Field','Updatable',.T.)
DBSetProp("v_foo.kFoo_pk",'Field','UpdateName',[dbo.foo.kFoo_pk])
DBSetProp("v_foo.cFid1",'Field','Updatable',.T.)
DBSetProp("v_foo.cFid1",'Field','UpdateName',[foo.cFid1])

Use v_foo
Append Blank
Replace kFoo_pk with 123, cFid1 with "ABC"
? TableUpdate()  &&.T. - Sent to server: exec sp_executesql N'INSERT INTO dbo.foo (kFoo_pk) VALUES (@P1)', N'@P1 int', 123
? Refresh() && 1
? "cFid1:", cFid1  && ABC
? Requery() && 1
? "cFid1:", cFid1 && .Null.

Return


Another problem - misleading Error message:
Create Database footables
Create Table foo ( kfootb_pk int primary key, cFid1 char(1) )

Create Database fooviews
Create View v_foo as select kfootb_pk, cFid1 from footables!foo

DBSetProp("V_FOO", 'View', 'SendUpdates',.T.)
DBSetProp("V_FOO", 'View', 'Tables',[foodb!footb])

DBSetProp("V_FOO.kfootb_pk",'Field', 'UpdateName',[fooviews!footb.kfootb_pk])
DBSetProp("V_FOO.cfid1",'Field', 'UpdateName',[fooviews!foodb.cfid1])

Open Database footables
Open Database fooviews
Use v_foo
Append Blank
replace cFid1 with "A"

? CursorGetProp("Tables") && foodb!footb
? TableUpdate() && .f.
? Message()  && No update tables are specified. Use the Tables property of the cursor.

? TableRevert()
Close Databases all
Delete Database footables deletetables
Delete Database fooviews

return

Carl Karsten
SetFldState() Doesn't do anyting usefull. It just changes what GetFldState() returns, but dosn't change what VFP thinks about the state of the buffer. Hack Fox has a pretty good description of this. - cfk

Corrupt record(s) + PACK = lost indices...!!

Gotcha: Beware of PACK'ing or REINDEX'ing after a server crash (or any time for that matter, unless you're prepared for what follows). If you have record corruption, Primary and Candidate keys may not be able to be rebuilt by using PACK or REINDEX. If you have a few corrupt records where the PK or Candidate key fields evaluate as duplicates across records, the PACK or REINDEX process will err out during the PK or Candidate key generation.

Result: Any PK or Candidate key, along with any other tags that were not generated prior to the "Uniqueness of index violation" error, will be missing from the table. The database container will still know about the indices that are now missing from the table resulting in an invalid .DBC

Moral of the story: After a server crash, if you suspect ANY record corruption, prior to performing a PACK or a REINDEX, at a minimum, perform the following:

1. Check for Primary and Candidate Key validity.
a. Use the NOOPTIMIZE parameter of the LOCATE command (I.E. LOCATE FOR EMPTY(MyPKField) OR MyPKField < 1 NOOPTIMIZE ).
2. Delete or attempt to recover any corrupt records that would cause a PK or candidate key reindex to fail.

Perhaps even copy the table files off for safe keeping before performing the PACK or REINDEX. Then restore them in the event of an error building the indices.

It all boils down to the realization that PACK or REINDEX should not be performed in a production application without performing some serious validation and taking defensive measures beforehand. There may be leftover record corruption that occurred some time in the distant past waiting to ruin your day. This behavior is not documented, nor does the error mention anything about tags being dropped.

If the behavior of dropping tags were removed, we could deal with the error directly by eliminating the corrupt records and reissueing the PACK or REINDEX. As it stands now, we have to proactively deal with record corruption prior to PACK'ing or REINDEX'ing, or deal with regenerating index tags afterwards.

- William Fields

Error# 1489, "General fields cannot be used in the WHERE condition of an update statement. Change the WhereType property of the view."

Something is fishy. Why is the data engine concerned with what the data is?
Create Database BatDbc
 Create Table BatDbf ( kBatKey i, cBatField c(1) )
Create sql View BatView as select * from BatDbf
DBSetProp( "BatView","VIEW","SendUpdates",.t.)
USE BatView
Append Blank
? "T1", TableUpdate()
AError( laE )
? laE[1,1], lae[1,2]  && Error# 1547, "Cannot insert an empty row from a view into its base table(s)."
replace cBatField with ""
? "T2", TableUpdate()

Q: So, why can't one insert the empty row?
A: Cause that's the way it works. Apparently it is a built in business rule.

[Walter Nicholls] There are two issues being dealt with in this Wiki page: why one "Cannot insert an empty row from a view into its base table(s)", and whether null support in VFP is broken or not. Well, the answer to the first question has nothing at all to do with either nulls or blanks. "Cause that's the way it works" isn't a bad answer, but there's a sane answer. It's much more obvious if you think about a remote back-end, but the same test is being applied for local views (consistent, at least)

When you APPEND BLANK, your view acquires a new row, but you have not yet modified any fields. Thus each field has the state of 3 "Field in an appended record has not been modified". YOu can see that with GETFLDSTATE():
APPEND BLANK
? GETFLDSTATE(-1) && prints "333"

Now when you call TABLEUPDATE() (or UNLOCK, UNLOCK ALL, USE, CLOSE DATA - the error also occurs!), VFP needs to build a SQL statement or statements with which to update the back-end database. Since this is a new row, it starts with INSERT INTO batdbf ...
THen it collects up the fields which have modified, but none have! So VFP is left with INSERT INTO batdbf () values (). This isn't valid SQL, and it rightly raises an error. Error 1547 is for exactly this problem - the help states "... a new empty record (with no modified fields) is being inserted ..."

When you change one of the fields - REPLACE cBatField with "" the modification state of the field is changed, and GETFLDSTATE(-1) now returns "334". VFP can now build a valid statement INSERT INTO batdbf (cBatField) values ("").

You can argue this all you like, but I'll go with "that's the way it works". I've never had this error signal anything but a bug - usually in multi-table views because I forgot to mark one of the base tables as read-only (no NEED to update back-end).

Many other databases insert NULL values in new records as the default when another default has not been defined. I am not sure, but I believe that xBase databases are the only ones to insert Empty values.

I don't know about other languages, but VFP's support for null is kind of rocky. It might be that null in general is a rocky concept, so there is no elegant implementation. Or it might be that even the most elegant implementation involving null will still confuse most users, so it doesn't get much use, therefore it doesn't get much attention from the get go, and suffers. - ?cfk

Sorry, what's so rockey about VFP's NULL support? To me, many other languages are wrong. For example, in C++, NULL == 0. In many database applications, if you say (the equivilent of:) REPLACE FieldX WITH "", what do you get? NULL - so NULL == Empty. -- Peter Crabtree

Peter - NULL is not the same thing as an empty string nor is it the same as 0. NULL means that the value is undefined. -- ?df

I know that. VFP knows that. C++ doesn't. Many databases don't. That's what I'm saying. -- Peter Crabtree

Peter - I'm sorry, but from your first statement above, you really don't know what NULL is. In VFP NULL is not == "". NULL is not = 0. NULL in database terms means "I Don't know What The Value Is".

create table x1 ( i1 i null, i2 i null, c1 c(10) null )
insert into x1 values ( null, null, null )

? x1.i1 = x1.i2 && prints NULL VFP doesn't know
? x1.i1 == x1.i2 && same result
? x1.i1 = x1.c1 && same result, even though the two columns are of a different type
? isnull( x1.i1 ) && prints .t. VFP does know that this field has a NULL value
replace x1.i1 with 0
? isnull( x1.i1 ) && prints .f., the field is no longer NULL
? x1.i1 = x1.i2 && prints NULL, the comparison of a non-null value to a NULL is not defined the result is null
? x1.c1 + "test" && prints NULL a NULL value used in any numeric or string operation results in a NULL value for the expression

VFP, SQL Server, Sybase and Oracle know what NULL is. A NULL field value is compatible and understood equally well by all these databases. We don't care what the particular binary representation of NULL is in any of these databases.

C++ knows what NULL is, it has a very specific definition. The C++ definition of NULL is pretty radically different than the database definition of NULL. In C++ NULL is a constant, you can specifically test a variable to see if it is equal to the NULL constant.

-- ?df

I am fully aware of what a NULL is! And I am fully aware, that in VFP, SQL Server, and some other languages/database systems, NULL is not equal to anything, including NULL. I am also fully aware of C++'s definition of NULL. In C++, NULL == 0. So this is useless to a database. Perhaps the majority of languages do get this right. But many of the databases I've worked with (low-end), misinterpret NULL - it's equal to Empty, though VFP gets it right. All I'm saying is that other languages get it wrong, from the viewpoint of a database, but VFP gets it right, so what's wrong with "VFP's support for null"? -- Peter Crabtree

There is nothing wrong with VFP's support of NULL. These other lowend databases you are bringing into the discussion by definition do not have NULL support. -- ?df

Right. Exactly. Glad you agree. (Notice my first "post": "Sorry, what's so rockey about VFP's NULL support?" - I didn't see anything wrong. I was giving examples of doing it WRONG.) -- Peter Crabtree

Hmm, I think I misposted this. Now that I think about it, VFP's data enginie handles Null's just fine. If you store null to a field, it is there when you look at it later. From everything I have seen, you can detect null values in a table just fine.

So why did I post? VFP's UI has troubles with Null. to set a checkbox.value to null, the user hits ^0 - who came up with that? and I think .value only gets null if it is a logical - if it is numeric, you get 0,1,2 where 2=null. eh?

If you have a better idea of how to support nullable columns in the UI you are quite free to add code to your lowest level classes and handle it some other way. -- ?df

Anytime I work with nulls, I find my self having to have lots of isnull() cases to keep the nulls away from the not so null friendly UI things.

Also, the fact that dates can be either blank or null causes problems. not sure what they are, but thats part of the problem. ;) -- ?cfk

Thank you! This is what I was asking. I haven't worked with (real) nulls much, as I have had little use for them. But this outlines what you feel is wrong with VFP's .NULL. handling. -- Peter Crabtree
I tend to agree with Carl - Nulls are pretty much evil and pointless. At least, I've never found a use to make something NULL except for garbage collecting and storing "empty" dates in SQL Server (only because 12/31/1999 is not an elegant solution either.) We make sure ALL other data types in SQL have a default so they are initialized even if we don't initialize on the front-end. -- Randy Jean

Well, the most useful thing for NULL, besides object referances, is statistics. Let's say you had a database full of seniors (in this case > 65 years old) - and you had a numeric age field. If they did not answer their age, and you stored that as 0, you would have an intresting time calculating statistics. The following would have to be rewritten:
SELECT ALL AVG(age), SUM(balance), AVG(balance) FROM people
Though I agree - the use of NULLs is usually limited. Though if you did store an unkown age as 0, since VFP does support NULLs:
SELECT ALL AVG(EVL(age,.NULL.)), SUM(balance), AVG(balance) FROM people
-- Peter Crabtree
NULLs are not evil. If you have a column that is nullable, then yes, you have to write code to deal with it, it's one of those database design questions that you need to figure out early on.

I think the place where NULLs are most useful is in foreign key columns. Say you have Client, Address and Company tables. With these constraints: client may or may not have an address, a client may or may not have a company, a company must have an address. So Client.AddressNo is nullable and has a foreign key constraint to Address. Client.CompanyNo is nullable and has a foreign key constraint to Company. Company.Addressno is not nullable and has a foreign key constraint to Address.

If these columns are not nullable how do you handle a client that has no address? Do you do something hokey like set AddressNo = 0 or -1 and have a special case row in Address to support it? NULL is a much better solution. -- ?df

I really don't know, because I know we have similar situations such as this in our apps, but we don't handle with allowing NULL to be stored. The app I'm thinking of in particular does not use integer keys, either, rather character. I just allow the foreign key to be empty (string) and handle the RI in the middle or data tier vs. doing a "hard" RI in the database. I think we do have relations defined for these keys, just without the constraints. Not something that comes up much in our apps. There are ALWAYS other ways to handle things (and some may argue "best practice" but sometimes best practice doesn't meet reality), but I'll buy your reasons for the existence of NULLs, I just prefer not dealing with them (ie. prevent them from occuring) if I can help it. I know there are times in outer joins, etc. that we must expect that there could be NULL in the result set, I just don't want to store them in every field by default because if you add business logic and don't ALWAYS check for the existence of NULL, you could get unpredictable/undesirable results and things can start slipping by the business rules due to improper logical comparisons.
The statistical analysis argument is an interesting one, too, although, in the few places we do aggregate queries, we usually have additional critiria to qualify the records we want averaged, summed, etc.
I'll admit, I'm overall ignorant about why I would NEED nulls (vs. want), but even when I learn why I MAY want to use them, I still tend to think they are more trouble than they are worth in the application development domain. "Real" database experts (of which I am not) may disagree, and I respect that. -- Randy Jean

[Walter Nicholls] In my opinion, it isn't VFP's implementation of nulls which is broken, but more its implementation of blanks. This is a historical thing. Once upon a time, we didn't have null support in FoxPro, and so empty/blank values were used for much the same purpose. Now we have nulls available, but (V)FP behaviour in a number of areas has not changed (for backward compatibility reasons) to make use of them as it 'should'. Here's a number of examples However VFP does get things right in outer joins.

Really, there is nothing wrong intrinsically wrong with the way that null is supported in VFP. Sure, you need to use ISNULL() and NVL() appropriately - but in what way is that different to using EMPTY() and EVL() (or prior to VPP8 iif(empty(x),x,y))? Same goes with the .NullDisplay property of some VFP controls. Set this to " " (note the space) and you can't see the difference between empty and null strings, but can also set to "None", "n/a", "Forever"...
FWIW, I think NULL is less useful for string fields but an excellent habit for date/datetime fields for the simple reason that certain C/S databases cannot store a blank date. Might as well cater for upsizing from the start...

See also: Table Has No Index Order

Another problem with calculated fields in queries. AVG(integer) returns integer, when you may not want it to:
SELECT ALL AVG(age), AVG(balance) FROM people

You can fix it in VFP 9 with a CAST:
SELECT AVG( CAST(age AS N(4,0))), AVG(balance) FROM people

Consider NULL processing too. -- Craig Roberts

Category Needs Refactoring
( Topic last updated: 2008.02.07 03:51:12 PM )