FUNCTIONPublished

TCSetField

TCSetField( <cAlias>, <cField>, <cType>, [nSize], [nPrecision] )

Adjusts the type, size and precision of a column returned by a DBAccess query.

SQL and TopConnDBAccessQueryData typesTCGenQryDatabase

01 · OVERVIEW

Overview

Queries opened through TCGenQry() return numeric fields as 15,8 by default and treat other fields as character data. TCSetField() redefines how date, logical and numeric columns are exposed by the query alias. This is especially useful for ERP dates physically stored as CHAR or VARCHAR in YYYYMMDD format. It changes the logical cursor definition, not the data or physical table structure.

02 · SYNTAX

Syntax

TCSetField( <cAlias>, <cField>, <cType>, [nSize], [nPrecision] )

Parameters

cAlias

CharacterRequired

Alias opened for the query; a missing alias causes a fatal error.

cField

CharacterRequired

Cursor column to redefine.

cType

CharacterRequired

AdvPL type: D, N or L.

nSize

NumericOptional

Total digits for N, from 1 to 18.

Default: 0

nPrecision

NumericOptional

Decimal places for N, from 0 to nSize - 1.

Default: 0

Return value

The documentation does not define an application return value. The relevant effect is the updated column treatment.

03 · PRACTICAL EXAMPLE

Adjust date, logical and numeric columns

#Include "TOTVS.ch"
#Include "TopConn.ch"

User Function ExQueryTypes()
    Local cAlias := GetNextAlias()
    Local cQuery := ""
    Local dIssueDate
    Local lActive
    Local nAmount

    cQuery := " SELECT C5_EMISSAO, C5_TIPO, C5_VALOR "
    cQuery += " FROM " + RetSqlName("SC5")
    cQuery += " WHERE D_E_L_E_T_ = '' "
    cQuery := ChangeQuery(cQuery)

    DbUseArea(.T., "TOPCONN", TCGenQry(,,cQuery), cAlias, .F., .T.)
    TCSetField(cAlias, "C5_EMISSAO", "D")
    TCSetField(cAlias, "C5_TIPO", "L")
    TCSetField(cAlias, "C5_VALOR", "N", 15, 2)

    dIssueDate := (cAlias)->C5_EMISSAO
    lActive := (cAlias)->C5_TIPO
    nAmount := (cAlias)->C5_VALOR
    (cAlias)->(DbCloseArea())
Return {dIssueDate, lActive, nAmount}

Expected result

The columns are read with the configured AdvPL types. Validate actual dictionary fields and logical content.

04 · PRACTICAL EXAMPLE

Reuse the table structure

Local aStructure := CT2->(DBStruct())
Local nField

For nField := 1 To Len(aStructure)
    If aStructure[nField, 2] $ "DNL"
        TCSetField("CT2TMP", aStructure[nField, 1], ;
            aStructure[nField, 2], aStructure[nField, 3], ;
            aStructure[nField, 4])
    EndIf
Next nField

Expected result

D, N and L fields guide equivalent cursor columns.

BEST PRACTICES

Call it after opening the cursor and before reading the column.
Use D only for YYYYMMDD content.
Use L for T or F content.
Validate numeric size and precision.
Use DBStruct() when matching ERP table fields.
Use the exact cursor alias.

COMMON PITFALLS

Invalid content becomes an empty date, zero or .F..
Invalid alias, size or precision may cause a fatal error.
Other types are ignored and logged as warnings.
D always uses 8,0 and L uses 1,0.
The TDN sample has a CT2TMP/CT2TRB alias mismatch; this page corrects it.

TCSetField

Overview

Syntax

Parameters

Return value

Adjust date, logical and numeric columns

Reuse the table structure

Related content