----------------------------------------------------------------------------------------------
[BUG/PRB.] VFP 9.0 FIX - VARCHAR(MAX) FIELDS IN SQL SERVER ARE MAPPED TO C(0) FIELDS
January 2024
----------------------------------------------------------------------------------------------
CCB
1. BUG:
Microsoft has said for years that TEXT field type will be deprecated in SQL Server and VARCHAR(MAX) is the recommended replacement.
But in vfp9, VARCHAR(MAX) fields in SQL Server are mapped to C(0) fields, so it always returns an empty string.
The bug was reported by John Ryan.
2. CAUSE:
There are some BUGs in the following code.
3. RESOLUTION:
We can write some code to fix the BUG.
Fun88a599 :: ; proc near
push ebp ;0x0088a599 : 55
mov ebp , esp ;0x0088a59a : 8bec
push ecx ;0x0088a59c : 51
push ebx ;0x0088a59d : 53
push esi ;0x0088a59e : 56
push edi ;0x0088a59f : 57
mov edi , ecx ;0x0088a5a0 : 8bf9
lea ecx , dword ptr [ ebp - 4 ] ;0x0088a5a2 : 8d4dfc
imul edi , edi , 0158h ;0x0088a5a5 : 69ff58010000
push ecx ;0x0088a5ab : 51
xor ebx , ebx ;0x0088a5ac : 33db
push ebx ;0x0088a5ae : 53
push ebx ;0x0088a5af : 53
push ebx ;0x0088a5b0 : 53
mov esi , eax ;0x0088a5b1 : 8bf0
mov eax , dword ptr [ Data93b94c ] ;0x0088a5b3 : a14cb99300
mov eax , dword ptr [eax] ;0x0088a5b8 : 8b00
pushd 06h ;0x0088a5ba : 6a06
push dword ptr [ ebp + 8 ] ;0x0088a5bc : ff7508
push dword ptr [ edi + eax + 4 ] ;0x0088a5bf : ff740704
call dword ptr [ Data947b14 ] ;0x0088a5c3 : ff15147b9400
mov ecx , eax ;0x0088a5c9 : 8bc8
push ecx ;0x0088a5cb : 51
call Fun880072 ;0x0088a5cc : e8a15affff
test eax , eax ;0x0088a5d1 : 85c0
jne Label88a660 ;0x0088a5d3 : 0f8587000000
mov eax , dword ptr [ ebp - 4 ] ;0x0088a5d9 : 8b45fc
lea ecx , dword ptr [ ebp - 4 ] ;0x0088a5dc : 8d4dfc
push ecx ;0x0088a5df : 51
push ebx ;0x0088a5e0 : 53
push ebx ;0x0088a5e1 : 53
push ebx ;0x0088a5e2 : 53
mov dword ptr [ esi + 8 ] , eax ;0x0088a5e3 : 894608
mov eax , dword ptr [ Data93b94c ] ;0x0088a5e6 : a14cb99300
mov eax , dword ptr [eax] ;0x0088a5eb : 8b00
pushd 09h ;0x0088a5ed : 6a09
push dword ptr [ ebp + 8 ] ;0x0088a5ef : ff7508
push dword ptr [ edi + eax + 4 ] ;0x0088a5f2 : ff740704
call dword ptr [ Data947b14 ] ;0x0088a5f6 : ff15147b9400
mov ecx , eax ;0x0088a5fc : 8bc8
push ecx ;0x0088a5fe : 51
call Fun880072 ;0x0088a5ff : e86e5affff
test eax , eax ;0x0088a604 : 85c0
jne Label88a660 ;0x0088a606 : 7558
cmp dword ptr [ ebp - 4 ] , ebx ;0x0088a608 : 395dfc
lea ecx , dword ptr [ ebp + 10 ] ;0x0088a60b : 8d4d0a
push ecx ;0x0088a60e : 51
setne al ;0x0088a60f : 0f95c0
xor al , byte ptr [ esi + 18 ] ;0x0088a612 : 324612
lea ecx , dword ptr [ esi + 16 ] ;0x0088a615 : 8d4e10
push ecx ;0x0088a618 : 51
and al , 01h ;0x0088a619 : 2401
xor byte ptr [ esi + 18 ] , al ;0x0088a61b : 304612
mov eax , dword ptr [ Data93b94c ] ;0x0088a61e : a14cb99300
mov eax , dword ptr [eax] ;0x0088a623 : 8b00
lea ecx , dword ptr [ esi + 12 ] ;0x0088a625 : 8d4e0c
push ecx ;0x0088a628 : 51
add esi , 04h ;0x0088a629 : 83c604
push esi ;0x0088a62c : 56
lea ecx , dword ptr [ ebp + 18 ] ;0x0088a62d : 8d4d12
push ecx ;0x0088a630 : 51
push dword ptr [ ebp + 16 ] ;0x0088a631 : ff7510
push dword ptr [ ebp + 12 ] ;0x0088a634 : ff750c
push dword ptr [ ebp + 8 ] ;0x0088a637 : ff7508
push dword ptr [ edi + eax + 4 ] ;0x0088a63a : ff740704
call dword ptr [ Data947ba4 ] ;0x0088a63e : ff15a47b9400
mov ecx , eax ;0x0088a644 : 8bc8
push ecx ;0x0088a646 : 51
call Fun880072 ;0x0088a647 : e8265affff
test eax , eax ;0x0088a64c : 85c0
jne Label88a660 ;0x0088a64e : 7510
;
; -----------------------------------------------------------------------------------
; VFP 9.0 FIX - VARCHAR(MAX) FIELDS IN SQL SERVER ARE MAPPED TO C(0) FIELDS
; September 2018
; -----------------------------------------------------------------------------------
; CCB
;
; In vfp9, VARCHAR(MAX) fields in SQL Server are mapped to C(0) fields,
; VARBINARY(MAX) fields in SQL Server are mapped to Q(0) fields (CURSORSETPROP("MapBinary",.T.)) or Memo (Binary) fields (CURSORSETPROP("MapBinary",.F.)),
; XML fields in SQL Server are mapped to C(0) fields.
; In VFP Advanced, VARCHAR(MAX) fields in SQL Server are mapped to Memo fields,
; VARBINARY(MAX) fields in SQL Server are mapped to Blob fields (CURSORSETPROP("MapBinary",.T.)), General fields or Memo (Binary) fields (CURSORSETPROP("MapBinary",.F.)),
; XML fields in SQL Server are mapped to Memo fields.
;
; 2018/9/30, by ccb
;
; SQL_VARCHAR (12)
cmp word ptr [esi] , 0Ch
je Label88a650_varchar
; SQL_WVARCHAR (-9)
cmp word ptr [esi] , 0FFF7h
je Label88a650_nvarchar
; SQL_VARBINARY (-3)
cmp word ptr [esi] , 0FFFDh
je Label88a650_varbinary
; SQL_SS_XML (-152)
cmp word ptr [esi] , 0FF68h
je Label88a650_xml
jmp Label88a650
Label88a650_varchar ::
cmp dword ptr [esi+4] , 00h
jne Label88a650
cmp dword ptr [esi+8] , 00h
jne Label88a650
mov dword ptr [esi+4] , 07FFFFFFFh
mov dword ptr [esi+8] , 07FFFFFFFh
cmp dword ptr vfpa_sys9135_data,00h
je Label88a650
; SQL_LONGVARCHAR (-1)
mov word ptr [esi] , 0FFFFh
jmp Label88a650
Label88a650_nvarchar ::
cmp dword ptr [esi+4] , 00h
jne Label88a650
cmp dword ptr [esi+8] , 00h
jne Label88a650
mov dword ptr [esi+4] , 07FFFFFFFh
mov dword ptr [esi+8] , 07FFFFFFFh
cmp dword ptr vfpa_sys9135_data,00h
je Label88a650
; SQL_WLONGVARCHAR (-10)
mov word ptr [esi] , 0FFF6h
jmp Label88a650
Label88a650_varbinary ::
cmp dword ptr [esi+4] , 00h
jne Label88a650
cmp dword ptr [esi+8] , 00h
jne Label88a650
mov dword ptr [esi+4] , 07FFFFFFFh
mov dword ptr [esi+8] , 07FFFFFFFh
cmp dword ptr vfpa_sys9136_data,00h
je Label88a650
; SQL_LONGVARBINARY (-4)
mov word ptr [esi] , 0FFFCh
jmp Label88a650
Label88a650_xml ::
cmp dword ptr [esi+4] , 00h
jne Label88a650
cmp dword ptr [esi+8] , 00h
jne Label88a650
mov dword ptr [esi+4] , 03FFFFFFFh
mov dword ptr [esi+8] , 03FFFFFFFh
cmp dword ptr vfpa_sys9137_data,00h
je Label88a650
; SQL_WLONGVARCHAR (-10)
mov word ptr [esi] , 0FFF6h
jmp Label88a650
Label88a650 ::
mov esi , dword ptr [ ebp + 20 ] ;0x0088a650 : 8b7514
cmp esi , ebx ;0x0088a653 : 3bf3
je Label88a660 ;0x0088a655 : 7409
cmp word ptr [ ebp + 10 ] , bx ;0x0088a657 : 66395d0a
setne al ;0x0088a65b : 0f95c0
mov dword ptr [esi] , eax ;0x0088a65e : 8906
Label88a660 ::
pop edi ;0x0088a660 : 5f
pop esi ;0x0088a661 : 5e
mov ax , cx ;0x0088a662 : 668bc1
pop ebx ;0x0088a665 : 5b
leave ;0x0088a666 : c9
ret 010h ;0x0088a667 : c21000
Now in VFP Advanced, VARCHAR(MAX) fields in SQL Server are mapped to Memo fields,
VARBINARY(MAX) fields in SQL Server are mapped to Blob fields (CURSORSETPROP("MapBinary",.T.)), General fields or Memo (Binary) fields (CURSORSETPROP("MapBinary",.F.)),
XML fields in SQL Server are mapped to Memo fields.
4. APPLIES TO:
VFP 6.0.8167.0
VFP 6.0.8961.0 (SP5)
VFP 7.0.0.9262
VFP 7.0.0.9465 (SP1)
VFP 8.0.0.2521
VFP 8.0.0.3117 (SP1)
VFP 9.0.0.2412
VFP 9.0.0.3504 (SP1)
VFP 9.0.0.4611 (SP2)
VFP 9.0.0.5015 (SP2)
VFP 9.0.0.5411 (SP2)
VFP 9.0.0.5721 (SP2)
VFP 9.0.0.5815 (SP2)
VFP 9.0.0.6303 (SP2)
VFP 9.0.0.6602 (SP2)
VFP 9.0.0.7423 (SP2)
The bug has been fixed in VFP Advanced.
5. REFERENCE WEBSITES:
1, baiyujia.com:
http://www.baiyujia.com
http://www.baiyujia.com/vfpdocuments/f_vfp9fix273.asp
http://www.baiyujia.com/vfpdocuments/f_vfp9fix274.asp
http://www.baiyujia.com/vfpdocuments/f_vfp9fix275.asp
2, foxite.com:
https://www.foxite.com/archives/sql-varcharmax-vs-memo-0000433414.htm
https://www.foxite.com/archives/warning-possible-bug-sql-server-0000239346.htm
https://www.foxite.com/archives/sql-server-2008-varbinarymax-0000410009.htm
3, microsoft.com:
https://technet.microsoft.com/en-us/library/ms171931(v=sql.105).aspx
https://social.msdn.microsoft.com/Forums/en-US/ee9c97ea-0dfb-4337-877b-37db710885d5/refresh-function-and-recordrefresh-does-not-refresh-memo
4, tek-tips.com:
https://www.tek-tips.com/viewthread.cfm?qid=1776880
5, easysoft.com:
https://www.easysoft.com/developer/languages/c/examples/ListDataTypes.html
6. OTHER:
For reference only, there is no guarantees.
Any questions or suggestions, please send me an email at ccb2000@163.com.
|