----------------------------------------------------------------------------------------------
                      [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.