TOTVS Technology

Home

TOTVS Technology

Child pages
  • Embedded SQL - Queries Enabler
The goal of Embedded SQL is to enable the writing and reading of queries. To accomplish this, we defined a syntax to write the query directly in the AdvPL code, without the need to concatenate string parts to compose the final string.

 

Scope

ERP 10 and 11


Resource Availability

This resource is available in products Microsiga Protheus 10 and 11.

We divide the use of Embedded SQL in:

  • Source-code compilation.
  • Source-code execution.

To compile a source-code with code written in Embedded format, you must use the Microsiga Protheus product with build equal to or above 7.00.050721P, in an environment with repository for TOTVS | DBAccess (RPODB=TOP). The use of this resource also depends on updating the LIB.

  

Basic Example - Current Source-Code

cQuery : 'SELECT SE2.E2_PREFIXO,SE2.E2_NUM ' cQuery += 'FROM '+RetSqlTable('SE2')+' SE2,'+RetSqlTable('QEK')+' QEK ' cQuery += 'WHERE SE2.E2_FILIAL= '+xfilial('SE2')+' AND '         cQuery += 'SE2.E2_PREFIXO<> ''+cPrefixo+'' AND ' cQuery += 'SE2.D_E_L_E_T_ = ' ' ' cQuery += 'ORDER BY '+RetSqlOrder('SE2')  dbUseArea(.T.,'TOPCONN',TcGenQry(,,cQuery),'E2TEMP',.T.,.T.)TCSetField('E2TEMP','E2_EMISSAO','D',8,0) 

 

Basic Example - Using Embedded SQL

BeginSql alias 'E2TEMP'    column E2_EMISSAO as Date    %noparser%    SELECT SE2.E2_PREFIXO,SE2.E2_NUM    FROM %table:SE2% SE2,%table:QEK% QEK           WHERE SE2.E2_FILIAL= %xfilial:SE2% AND                          SE2.E2_PREFIXO<> %exp:cPrefixo% AND          SE2.%notDel%    ORDER BY %Order:SE2% EndSql

 

Operational Characteristics - Syntax

  • You must start the code block in which to write the SELECT command with BeginSQL Alias and end it with EndSQL.
  • Parts of source-code that must be replaced appear between % (percentage) signs. During execution, these expressions have special procedures.
  • Any instruction placed between BeginSQL ... EndSQL that is not an expression %...% will be entered in the query to be sent to the database, in literal form.
  • Variables, expressions and functions start with %exp:%.
  • In column you must specify query fields of types date, logical or numeric (DATE, LOGIC, NUMBER). This line is switched for calls to function TCSetField().
  • %noparser% indicates the query must not go through function ChangeQuery() before being sent to the database. If not specified, by default the query string is automatically sent through function ChangeQuery().
  • %table:% is replaced by RetSqlName().
  • %notDel% is replaced by D_E_L_E_T_=' '.
  • %Order:% is replaced by SqlOrder (->(IndexKey())).

 

3 options exist for %Order:


 1st Option:

%Order:  %   translated to  SqlOrder(->(IndexKey()))

 

2nd Option:

%Order: , %  translated to  SqlOrder(->(IndexKey()))

 

3rd Option:

%Order: , %  translated to  SqlOrder(->(DBNickIndexKey()))

 

Limitation

You cannot add functions in the middle of embedded code. If you must, keep the value in a variable before the start of BeginSQL.

Example

tam_cp := GetE2ValorSize() BeginSql alias 'E2TEMP'   column E2_EMISSAO as Date, E2_VALOR as Numeric(tam_cp,2)   . . .  EndSql

 

Compilation Errors

If you use some invalid argument to specify the columns or syntax errors in the expressions to be transformed for the query assembly, the compilation of the source-code is interrupted with the occurrence Syntax Error, notifying the line in which the first occurrence was found.

 

EndSQL (Error C2001 Syntax error:)

If the occurrence of compilation directly points to the source-code line in which the EndSQL instruction is written, check whether any blank space or tabulation exists, from the beginning of the line, before the EndSQL instruction. The current version of this environment does not support this statement, demanding that the EndSQL instruction be aligned to the left of the source-code, without space or tabulation.

 

Execution Errors

  • Query Argument Error: Alias [XXX] already in use.
    If the BeginSQL instruction specifies an alias already open (in use), the application is aborted with fatal error occurrence, notifying in XXX the alias used.
     
  • Query Argument Error: Invalid Value Type [X]
    If any expression entered in the query, through tag %exp:...%, returns a value other than C (Character), D (Date), N (Numeric) or L (Logical), the application is aborted with error occurrence, in which the unexpected argument type is displayed in [X].
     
  • Type Mismach on +
    This occurrence, if reproduced, notifies in the call stack the number of the source-code line corresponding to the instruction EndSQL. This occurs if some intermediary function of the engine of Embedded SQL, excepting the functions specified in the query with syntax %exp:...%, returns a non-character content which is added to the query. This occurrence is more difficult to locate. In these cases, it pays to analyze the temporary file created by TOTVS | Development Studio, during compilation.
     
  • Help NOFUNCW - Function __EXECSQL
    If you execute an Embedded SQL source-code in a repository that is outdated or not a repository for the TOTVS | DBAccess environment (RPODB=TOP), the application displays this occurrence, indicating that the internal execution function of the query is missing from the environment. Check whether the LIB is up-to-date and whether the RPO in use belongs to a TOTVS | DBAccess environment.

 

Operational Characteristics - Debugging

Given the query assembly, you cannot debug the codeblock found between instructions BeginSQL and EndSQL, not taking into account debugging BreakPoints, if placed in this interval of code. You must place breakpoints before or after this block.

 

Auxiliary Function - GetLastQuery()

After opening the cursor in the specified alias, function GetLastQuery() returns an array, with 5 elements, in which the following information is available on the executed query.

[1] cAlias - Alias used to open the cursor.
[2] cQuery - executed Query
[3] aCampos - Array of fields with specified conversion criterion.
[4] lNoParser - If true (.T.), function ChangeQuery() was not used in the original string.
[5] nTimeSpend - Time, in seconds, used to open the cursor.

 

More Complete Example

AdvPL

BeginSql alias 'E2TEMP'   column E2_EMISSAO as Date, E2_VALOR as Numeric(tam_cp,2)   column  QEK_SKLDOC As Logical          %noparser%   SELECT SE2.E2_PREFIXO,SE2.E2_NUM, SE2.E2_FORNECE, SE2.E2_LOJA,SE2.E2_VALOR, SE2.D_E_L_E_T_ DEL1, QEK.D_E_L_E_T_ DEL2 , QEK.QEK_SKLDOC, SE2.R_E_C_N_O_ SE2RECNO    FROM %table:SE2% SE2,%table:qeK% QEK           WHERE SE2.E2_FILIAL= %xfilial:SE2% AND                             qek.%notDel%  and            SE2.E2_PREFIXO<> %exp:cPrefixo% AND             SE2.E2_NUM<> %exp:(cAlias)->M0_CODIGO% AND             SE2.E2_NUM<>45        AND             SE2.E2_FORNECE=%exp:Space(Len(SE2->E2_FORNECE))% AND             SE2.E2_EMISSAO<>%exp:MV_PAR06% AND             SE2.E2_LOJA<>%exp:MV_PAR05% AND             SE2.E2_VALOR<>%exp:MV_PAR04% AND                             qek.QEK_SKLDOC<>%exp:MV_PAR03%   And            SE2.%notDel%   ORDER BY %Order:SE2,1% EndSql

 

Source-code created by pre-compiler (PPO)

DEL2 , QEK.QEK_SKLDOC, SE2.R_E_C_N_O_ SE2RECNO FROM  '+RetSqlName('SE2')+' SE2, '+RetSqlName('QEK')+' QEK WHERE SE2.E2_FILIAL=  '' +xFilial
('SE2')+''  AND qek.D_E_L_E_T_= ' ' and SE2.E2_PREFIXO<>  '+___SQLGetValue(CPREFIXO)+' AND SE2.E2_NUM<>  '+___SQLGetValue((CALIAS)
->M0_CODIGO)+' AND SE2.E2_NUM<>45 AND SE2.E2_FORNECE= '+___SQLGetValue(SPACE(LEN(SE2->E2_FORNECE)))+' AND SE2.E2_EMISSAO<> 
'+___SQLGetValue(MV_PAR06)+' AND SE2.E2_LOJA<> '+___SQLGetValue(MV_PAR05)+' AND SE2.E2_VALOR<> '+___SQLGetValue(MV_PAR04)+' AND 
qek.QEK_SKLDOC<> '+___SQLGetValue(MV_PAR03)+' And SE2.D_E_L_E_T_= ' ' ORDER BY  '+ 
SqlOrder(SE2->(IndexKey(1))),{{'E2_EMISSAO','D',8,0},{'E2_VALOR','N',tam_cp,2},{'QEK_SKLDOC','L',1,0}},.T.)
  • No labels