We did something similar using the cursoradapter for another customer who was using a backend that didn't have a bit data type.

This works for me using SQL Server. Use my code as an example, you may have to change it some to get it to work for your backend.

Here is the SQL script to create the table, I am using a database called testdb:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testtab1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[testtab1]
GO

CREATE TABLE [dbo].[testtab1] (
	[ikey] [int] NOT NULL ,
	[nFld] [numeric](18, 2) NULL ,
	[nFld2] [numeric](18, 2) NULL 
) ON [PRIMARY]
GO

insert into [dbo].[testtab1] (ikey, nFld, nFld2) values (1, 1, 0)
insert into [dbo].[testtab1] (ikey, nFld, nFld2) values (2, 0, 1)
insert into [dbo].[testtab1] (ikey, nFld, nFld2) values (3, 1, 0)
insert into [dbo].[testtab1] (ikey, nFld, nFld2) values (4, 0, 1)

Here is the CA code. The CA has is a member array that contains the fields to convert. We call a function from AfterCursorFill() that spins through the array, detaches from the original cursor, and makes a converted cursor which the CA attaches to. Note that CursorRefresh() will not work, you need to call CursorFill() again to refresh the cursor. The comments in the code should be sufficient to explain what we did.

#define SERVERNAME '(local)'
lcServerName = SERVERNAME
lcDatabase = "testdb"  
lcConstr = "Driver=SQL Server;SERVER="+lcServername+";DATABASE="+lcDatabase

set multilocks on 
CLOSE DATABASES ALL 
CLEAR 
LOCAL loXCursor as cursoradapter 
loXcursor = CREATEOBJECT('mycursoradapter', lcConstr )
if !loxCursor.CursorFill()
 AError(atest)
 list memory like atest
endif
browse
?TABLEUPDATE(1)
release loXcursor 
close databases all

loXcursor = CREATEOBJECT('mycursoradapter', lcConstr )

if !loxCursor.cursorfill()
 AError(atest)
 list memory like atest
endif
browse 
return 

Define Class Mycursoradapter As cursoradapter
* member array containing list of fields to convert
 dimension acFieldList[2]
 acFieldList[1] = 'nFld'
 acFieldList[2] = 'nFld2'

 Procedure Init(lcConStr)
    This.SendUpdates = .T.
    This.DataSourceType = "ODBC"
    This.Datasource = Sqlstringconnect(lcConStr)
    This.Tables = "testtab1"
    This.KeyFieldList = "ikey"
    This.Alias = "curtesttab1"
    This.FetchMemo = .T. && Default
    This.CompareMemo = .T. && Default
    This.WhereType = 3 && Key and modified - Default
    Store [ikey, nfld, nfld2] ;
        TO This.UpdatableFieldList
       
   Store [iKey testtab1.iKey, ] ;
      + [nFld testtab1.nFld, ] ;
      + [nFld2 testtab1.nFld2 ] ;
      TO This.UpdateNameList

    This.BufferModeOverride = 5
   This.SelectCmd = "SELECT * FROM testtab1"
  endproc
 procedure aftercursorfill()
    LPARAMETERS lUseCursorSchema, lnoDataOnLoad, cSelectCmd, lResult
  this.convertfields()
 endproc
 
 procedure convertfields()
  * if 1st array element is .f., assume no conversions needed
    if Type("this.acFieldList[1]") = "L"
     return
    else
     local lcFieldList, lnFldCount,aFlds[1],lcField,lcTempAlias
     lcTempAlias = Sys(2015)
   this.Cursordetach()
   lcFldList = " "
   lnFldCount = AFields(aFlds,this.Alias)
   * Concatenate field list from aFlds alias
   for lni = 1 to lnFldCount
    lcFldList = lcFldList + " " + aFlds[lni,1] + ','
   endfor
   * spin through array and convert each field
   for each lcField in this.acFieldList
    lcField = Upper(lcField)
    * look for trailing comma in case you have names like field, field1
    lcFldList = Strtran(lcFldList, lcField+",", ;
     "iif("+lcField+" = 0, .f., .t.) as "+lcField+",")
   endfor
   * strip off last trailing comma, replace with space
   lcFldList = Substr(lcFldList,1,Len(lcFldList)-1) + " "
   * make temp cursor with logical data type in fields in array
   select &lcFldList from (this.Alias) into cursor (lcTempAlias);
        nofilter readwrite
   * close original cursor
   use in this.alias
   * select into new cursor of original name from temp cursor
   select * from (lcTempAlias) into cursor (this.alias) nofilter readwrite
   * close temp cursor
   use in (lcTempAlias)
   * reattach new cursor
   this.Cursorattach(this.alias)
  endif
 endproc
Enddefine

Please don't treat this as application-ready code, but rather as an example of a possible technique to implement what you are trying to do.

I hope this helps!



>I am working on a conversion to a backend database where I have to store 0/1 to represent .F./.T.
>
>I am using a CursorAdapter class and hoped if I set up a cursor schema that VFP could translate a backend database integer type into a logical field in the generated cursor but it does not. I get a Type conversion error.
>
>Is there a way this can be done with the CA class?
>
>When I use SPT like so, it translate Logical to Integer:
>
>vTF = .T.
>? SQLEXEC(m.iConn, "select * from TABLE where FIELD = ?vTF")