Is anyone aware of an ODBC bug that causes blank data to be sent to SQL server through a VFP remote view?

In other words, you USE a parameterized view to retrieve one record from SQL Server. Most of the fields contain data; most fields are not blank. You make changes to a handful of fields. You then issue a TABLEUPDATE(). And then you look at the server. Many of the fields that used to contain data are now blank. But you didn't blank them out.

Has anyone heard about this, or experienced it firsthand?

If so, I'D REALLY APPRECIATE YOUR HELP. I'm going crazy trying to figure out why this is happening and am now considering whether this is an ODBC related issue.

Here's some more info…

**************************
SQL TRACE FILE
**************************
We've asked our client experiencing this problem to turn on SQL trace. And we're seeing weird stuff like the following (notice the blanks towards the end):

exec sp_executesql N'UPDATE GEN SET
LOAN_NUM=@P1,BORROW_LN=@P2,BORROW_FN=@P3,BORROW_SSN=@P4,
PROP_NO=@P5,PROP_STREET=@P6,PROP_CITY=@P7,PROP_STATE=@P8,PROP_ZI
P=@P9,LOAN_AMT=@P10,INT_RATE=@P11,APPRAS_VAL=@P12,ORIG_APPRA
S=@P13,LTV=@P14,STAGE=@P15,PURPOSE=@P16,CASH_OUT=@P17,DOC_TY
PE=@P18,BUYDOWN=@P19,IMPOUNDS=@P20,MARGIN=@P21,CAP=@P22,INDE
X_VAL=@P23,APP_DATE=@P24,BROKERS_ID=@P25,BRANCH_TYPE=@P26,LO
_REP=@P27,PROGRAMS_ID=@P28,INV_LOAN_NO=@P29,IMPOUND_TOT=@P30
,REQ_SETUP=@P31,WIRE_AMT=@P32,ODD_DAYS=@P33,PER_DIEM=@P34,OD
D_DAYS_BASIS=@P35,ODD_DAYS_INT=@P36,DEDUCT_SUB=@P37,OINT_FRO
M=@P38,OINT_TO=@P39,MAIL_TO=@P40,MAIL_STREET=@P41,MAIL_CITY=
@P42,MAIL_STATE=@P43,MAIL_ZIP=@P44,P_AND_I=@P45,IMP_PMT=@P46
,TOT_PMT=@P47,SITE_ID=@P48,VERIFDISABLED=@P49,INITIAL_CAP=@P
50,SUBSEQ_CAP=@P51,RATE_ADJ_PERIOD=@P52,FIRST_RATE_ADJ=@P53,
NEXT_RATE_ADJ=@P54,PAY_ADJ_PERIOD=@P55,FIRST_PAY_ADJ=@P56,NE
XT_PAY_ADJ=@P57,LOAN_TERM=@P58,AMORT_TERM=@P59,CREATED_BY=@P
60,CREATED_ON=@P61,INDEX_TYPE=@P62,ACTION_TYPE=@P63,ACTION_D
ATE=@P64,FLOOR_RATE=@P65,FIRST_PMT=@P66,WEBTRAC_DT=@P67,INV_
LOCK=@P68,SUB_RATE_ADJ=@P69,SUB_PAY_ADJ=@P70,GROSS_MARGIN=@P
71,YSP_TO_WIRE=@P72,VAFF_TO_WIRE=@P73,EST_CLOSING_COSTS=@P74
,PREPAID_ESTIMATED=@P75,REFI_AMOUNT=@P76,BORR_COSTS_PAID_BY_
SELLER=@P77,ESTATE_HELD=@P78,BORROWER_PAID_DISC=@P79,EST_CLO
SING_OVERW=@P80,PREPAID_EST_OVERW=@P81 WHERE FILE_ID=@P82',
N'@P1 char(15),@P2 char(60),@P3 char(20),@P4 char(11),@P5
char(15),@P6 char(30),@P7 char(30),@P8 char(2),@P9
char(10),@P10 decimal(10,2),@P11 decimal(6,3),@P12
decimal(10,2),@P13 decimal(10,2),@P14 decimal(7,3),@P15
char(2),@P16 char(1),@P17 bit,@P18 char(15),@P19
char(3),@P20 bit,@P21 decimal(7,3),@P22 decimal(7,3),@P23
decimal(7,3),@P24 char(23),@P25 char(3),@P26 char(1),@P27
char(3),@P28 char(3),@P29 char(12),@P30 decimal(9,2),@P31
decimal(9,2),@P32 decimal(10,2),@P33 decimal(3,0),@P34
decimal(10,4),@P35 char(3),@P36 decimal(8,2),@P37
decimal(10,2),@P38 char(23),@P39 char(23),@P40 char(30),@P41
char(35),@P42 char(30),@P43 char(2),@P44 char(10),@P45
decimal(12,2),@P46 decimal(9,2),@P47 decimal(12,2),@P48
char(4),@P49 bit,@P50 decimal(7,3),@P51 decimal(7,3),@P52
decimal(3,0),@P53 char(23),@P54 char(23),@P55
decimal(3,0),@P56 char(23),@P57 char(23),@P58
decimal(3,0),@P59 decimal(3,0),@P60 char(3),@P61
char(23),@P62 char(15),@P63 char(1),@P64 datetime,@P65
decimal(6,3),@P66 char(23),@P67 char(23),@P68 char(23),@P69
decimal(3,0),@P70 decimal(3,0),@P71 decimal(9,6),@P72
bit,@P73 bit,@P74 decimal(9,2),@P75 decimal(9,2),@P76
decimal(10,2),@P77 decimal(10,2),@P78 char(1),@P79
decimal(9,2),@P80 bit,@P81 bit,@P82 char(5)', '
', '
', ' ', ' ', ' ',
' ', '
', ' ', ' ', 0.00, 0.000, 0.00, 0.00, 0.000, '2 ',
'P', 0, ' ', ' ', 0, 0.000, 0.000, 0.000, '
', ' ', 'B', ' ', ' ', ' ', 793.74, 793.74,
347842.64, 0, 0.0000, '360', 0.00, 9778.00, '
', ' ', '
', ' ', '
', ' ', ' ', 0.00, 264.58, 2657.25, ' ', 0,
0.000, 0.000, 0, ' ', '
', 0, ' ', ' ',
0, 0, ' ', ' ', ' ', '
', NULL, 0.000, ' ', '
', ' ', 0, 0, 0.000000, 0, 1, 8825.80,
2379.36, 0.00, 0.00, ' ', 5400.00, 0, 0, 'AAC77'

Again, notice the blank fields being sent to SQL Server towards the end. Now, the weird thing is that we know these fields are not blank in the view on the client side. How do we know this? Because of the way we save our data.

We buffer the data in our application (it's a remote view). When a user wants to save all changes, they click a save button on a toolbar. At that time we look at each field in the "GEN" view to see if the current value for each field matches the OLDVAL() on the server. If the field has been modified, we make a note of it in a transaction log. We store the user who made the change, the old value and the new value. So, we always have a history of who changed what field, what they changed it from, and what they changed it to.

If the user were blanking out the fields, it would show what the old value used to be, and then a blank value. We're not seeing this. Instead, the log shows the user changing just a few fields to other values and then they save. So we know the view contains good (non-zero, non-blank) data.

So how come the data is getting blanked out on the server after the TABLEUPDATE? How come the trace file shows an UPDATE statement with empty strings and 0's? Go figure!?

**************************
BACKGROUND DETAIL
**************************
Our application is written in VFP 7.0 SP1. It uses remote views to connect to SQL Server 2000 SP3.

We have some 250 clients all running the same application with no problems. However, we have one client that is experiencing the above described save issue. It's not isolated to one particular machine and it only happens periodically (in the order of once per month).

Our remote view is called "GEN" and is defined as follows:

CREATE SQL VIEW "GEN" ;
REMOTE CONNECT "TracSQL1" ;
AS Select * From gen gen Where gen.FILE_ID = ?cCurFile

DBSetProp('GEN', 'View', 'UpdateType', 1)
DBSetProp('GEN', 'View', 'WhereType', 1)
DBSetProp('GEN', 'View', 'FetchMemo', .T.)
DBSetProp('GEN', 'View', 'SendUpdates', .T.)
DBSetProp('GEN', 'View', 'UseMemoSize', 255)
DBSetProp('GEN', 'View', 'FetchSize', 100)
DBSetProp('GEN', 'View', 'MaxRecords', -1)
DBSetProp('GEN', 'View', 'Tables', 'GEN')
DBSetProp('GEN', 'View', 'Prepared', .F.)
DBSetProp('GEN', 'View', 'CompareMemo', .F.)
DBSetProp('GEN', 'View', 'FetchAsNeeded', .F.)
DBSetProp('GEN', 'View', 'FetchSize', 100)
DBSetProp('GEN', 'View', 'Comment', "")
DBSetProp('GEN', 'View', 'BatchUpdateCount', 1)
DBSetProp('GEN', 'View', 'ShareConnection', .T.)

*!* Field Level Properties for GEN
* Props for the GEN.file_id field.
DBSetProp('GEN.file_id', 'Field', 'KeyField', .T.)
DBSetProp('GEN.file_id', 'Field', 'Updatable', .T.)
DBSetProp('GEN.file_id', 'Field', 'UpdateName', 'GEN.FILE_ID')
DBSetProp('GEN.file_id', 'Field', 'Caption', "File No.")
DBSetProp('GEN.file_id', 'Field', 'DataType', "C(5)")
* Props for the GEN.loan_num field.
DBSetProp('GEN.loan_num', 'Field', 'KeyField', .F.)
DBSetProp('GEN.loan_num', 'Field', 'Updatable', .T.)
DBSetProp('GEN.loan_num', 'Field', 'UpdateName', 'GEN.LOAN_NUM')
DBSetProp('GEN.loan_num', 'Field', 'Caption', "Loan Number")
DBSetProp('GEN.loan_num', 'Field', 'DataType', "C(15)")
* Props for the GEN.borrow_ln field.
DBSetProp('GEN.borrow_ln', 'Field', 'KeyField', .F.)
DBSetProp('GEN.borrow_ln', 'Field', 'Updatable', .T.)
DBSetProp('GEN.borrow_ln', 'Field', 'UpdateName', 'GEN.BORROW_LN')
DBSetProp('GEN.borrow_ln', 'Field', 'Caption', "Borrower Last Name")
DBSetProp('GEN.borrow_ln', 'Field', 'DataType', "C(60)")

There's a total of 177 fields. I didn't list the rest to save space.

Again, any input/feedback you might have is GREATLY APPRECIATED!

Shawn Nelson
Del Mar Database