I've got a VFP5/MSSQL6.5 application that has concurrent development in both. We decided to go with numerics for "logical" fields, instead of dealing with the differences between VFP logical type and SS bit type.
In SS we decided on numeric(1,0) as the datatype rather than integer to just store 1 instead of 4. Not really an issue, but the client had a hangup about disk space.
Anyway, we just started noticing that there is some funkiness in the way the ODBC updates these fields.
e.g.
-- mytable.nLogic - initial value of 1
-- REPLACE nLogic WITH 2
-- TABLEUPDATE()
-- in SQL Trace, the ODBC UPDATE statement reads as follows:
UPDATE mytable SET nLogic = 3 WHERE iid = 1 AND nLogic = 2
The "old" value in the update reads as 2 - no one else has changed this value!
The "new" value in the update reads as 3 - the value entered was 2.
If we change the nLogic field to numeric(2,0) it works as desired.
Does SQL and/or the ODBC driver have a problem with (1,0) as a field size for numeric type? Does it reserve a byte for the decimal point?
We wanted to go with integers, so we may convert to integers anyway now.
Insanity: Doing the same thing over and over and expecting different results.