cQueryCharacterRequired
SQL query containing ? placeholders for values that will be bound later.
CLASSPublished
FWExecStatement
FWExecStatement():New( <cQuery> ) --> oStatementExecutes parameterized SQL queries with value binding, alias-based result sets, and methods inherited from FWPreparedStatement.
Overview
FWExecStatement derives from FWPreparedStatement and encapsulates the Framework query execution/cache concepts. It lets you build SQL with ? placeholders, bind typed values, and execute through OpenAlias() or ExecScalar(). Official documentation states that it is available starting with LIB label 20211116 and that its bind behavior follows TCGenQry2 rules and limitations.
Syntax
FWExecStatement():New( <cQuery> ) --> oStatementParameters
Methods
OpenAlias()Executa a consulta e retorna o alias aberto para navegação do result set.oStatement:OpenAlias( [cAlias], [cLifeTime], [cTimeout] ) --> cAlias
Parameters
NameFormatRequiredDescriptionNotes
cAliasCaractereOptionalAlias que será criado para o resultado.
cLifeTimeCaractereOptionalConfiguração de tempo de vida da consulta no cache da DBAPI.
cTimeoutCaractereOptionalConfiguração de timeout relacionada ao cache da consulta.
Return value
cAlias, caractere. Alias em que o resultado foi aberto.
ExecScalar()Executa uma consulta escalar e retorna diretamente o valor da coluna informada.oStatement:ExecScalar( <cColumn>, [cLifeTime], [cTimeout] ) --> xValue
Parameters
NameFormatRequiredDescriptionNotes
cColumnCaractereRequiredNome da coluna que deve ser retornada.
cLifeTimeCaractereOptionalConfiguração de tempo de vida no cache da DBAPI.
cTimeoutCaractereOptionalConfiguração de timeout relacionada ao cache.
Return value
xValue, variante. Valor obtido da coluna informada.
SetString()Método herdado de FWPreparedStatement para associar um valor caractere ao marcador indicado.oStatement:SetString( <nParam>, <cValue> )
SetDate()Método herdado para associar uma data AdvPL ao marcador indicado.oStatement:SetDate( <nParam>, <dDate> )
SetBoolean()Método herdado para associar um valor lógico ao marcador indicado.oStatement:SetBoolean( <nParam>, <lValue>, [lProtheus] )
SetIn()Método herdado para associar um array a um marcador utilizado em cláusula SQL IN.oStatement:SetIn( <nParam>, <aValues> )
SetUnsafe()Insere um valor sem o tratamento seguro normal. Use somente quando o valor for controlado e não vier de entrada externa.oStatement:SetUnsafe( <nParam>, <xValue> )
GetFixQuery()Retorna a consulta após o tratamento dos parâmetros, útil para diagnóstico.oStatement:GetFixQuery() --> cQuery
Destroy()Libera o objeto após o uso.oStatement:Destroy()
Return value
The constructor returns a FWExecStatement object. Query execution occurs later through OpenAlias() or ExecScalar().
Parameterized query with OpenAlias()
#Include "TOTVS.ch"
User Function ExFWExec()
Local cQuery := ""
Local cAlias := ""
Local oStmt
// A1_COD means “Code”, A1_NOME means “Name”, and A1_FILIAL means “Branch”.
cQuery := "SELECT A1_COD, A1_NOME FROM " + RetSqlName("SA1")
cQuery += " WHERE A1_FILIAL = ? AND A1_COD = ? AND D_E_L_E_T_ = ' '"
cQuery := ChangeQuery(cQuery)
oStmt := FWExecStatement():New(cQuery)
// Parameters start at 1 and follow the order of the ? placeholders.
oStmt:SetString(1, xFilial("SA1"))
oStmt:SetString(2, "000001")
cAlias := oStmt:OpenAlias()
While !(cAlias)->(Eof())
ConOut((cAlias)->A1_COD + " - " + (cAlias)->A1_NOME)
(cAlias)->(DbSkip())
EndDo
// Close the alias first, then release the statement object.
(cAlias)->(DbCloseArea())
oStmt:Destroy()
ReturnExpected result
The query binds branch and code values to the placeholders and opens the result in a WorkArea.
List filter with SetIn()
User Function ExFWExecIn()
Local cQuery := "SELECT A1_COD, A1_NOME FROM " + RetSqlName("SA1")
Local cAlias := ""
Local oStmt
Local aCodes := {"000001", "000003"}
// A1_COD means “Code”; A1_FILIAL means “Branch”.
cQuery += " WHERE A1_FILIAL = ? AND A1_COD IN (?) AND D_E_L_E_T_ = ' '"
cQuery := ChangeQuery(cQuery)
oStmt := FWExecStatement():New(cQuery)
oStmt:SetString(1, xFilial("SA1"))
// SetIn() is inherited from FWPreparedStatement and receives an array.
oStmt:SetIn(2, aCodes)
cAlias := oStmt:OpenAlias()
// Alias processing is omitted to emphasize parameter binding.
(cAlias)->(DbCloseArea())
oStmt:Destroy()
ReturnExpected result
SetIn() binds the code list to the second query placeholder.
Scalar value with ExecScalar()
User Function ExFWScalar()
Local cQuery := "SELECT COUNT(*) QTY FROM " + RetSqlName("SA1")
Local oStmt
Local nQty := 0
// A1_FILIAL means “Branch”.
cQuery += " WHERE A1_FILIAL = ? AND D_E_L_E_T_ = ' '"
cQuery := ChangeQuery(cQuery)
oStmt := FWExecStatement():New(cQuery)
oStmt:SetString(1, xFilial("SA1"))
// ExecScalar() avoids opening an alias when only one column/value is needed.
nQty := oStmt:ExecScalar("QTY")
oStmt:Destroy()
ConOut("Quantity: " + CValToChar(nQty))
ReturnExpected result
ExecScalar() directly returns the aggregate column value.
- Parameters are positional and start at 1; Set* calls must follow the order of the ? placeholders.
- Use RetSqlName(), xFilial(), and ChangeQuery() when appropriate for the Protheus query context.
- Close the alias returned by OpenAlias() and call Destroy() after processing.
- SetIn() is inherited from FWPreparedStatement and supports lists used in SQL IN clauses.
- The class is documented by TOTVS as available starting with LIB label 20211116.
- Do not add SQL quotes around a placeholder filled by SetString(); pass the plain value to the method.
- Do not use SetUnsafe() with user input, HTTP request values, or any uncontrolled input; official documentation warns about SQL Injection.
- Do not forget to close the WorkArea opened by OpenAlias() before destroying the object.
- Do not assume FWExecStatement automatically replaces every query technique; choose TCQUERY, Embedded SQL, or parameterized statements according to the scenario.
Comments
There are no approved comments yet.
Sign in with Google or Microsoft to comment.