TrimRightSpace

Enables the removal of the spaces in blank on the right in character fields.

Valid values

Notes

• This configuration is read during the connection with the SGBD and is not changed after use.
• The key only affects the fiels of the type caractere (C).
• In case a character field has a single key, you cannot add values equal with and without space in blank on the right (for example: "A" e "A "); therefore, if there is no single key or only a common index, insertion is allowed.
• The SGBDs Oracle, Tibero and PostgreSQL, even if the character field has a single key enable the insertion of equal values with and without space in blank on the right. Then, to keep the same behavior in SGBDs configuration TrimRightSpace for Oracle, Tibero and PostgreSQL operates by field of table, that is, after the creation of a table, if a field is created with RTrim, every writing will be with RTrim, even if the key is turned off after the creation of the field; if created without RTrim, every writing will be without RTrim. This behavior is also valid for the case of the modification of the table structure.
• If the key is linked and is a string is written in blank (""), the string is switched to a string with 1 space (" "), to keep the compatibility when handling the strings in all SDBDs.

Side effects

Both the standard ERP in relation to customizations, the queries assembled by the application of the AdvPL, or filters with expression SQL, if they use concatenation of fields compared to constant values, where a field on the left of the expression has a smaller content than the total size of the field, the use of the key leads to modification of behavior in the result of the Query, then, in the operationof the application.

// If the fields are alwasy "fully" fed, everything works 
insert into table fields( CPO1, CPO2 ) values( "000101", "0001" )
select * from tabela where CPO1 || CPO2 = "0000010001"

// Now, it will not work ...
insert into table fields( CPO1, CPO2 ) values( "101", "0001" )
select * from table where CPO1 || CPO2 = "101   0001"

The proper approach for this situation is compare field by field. It is actually a good practice, as it benefits the SGDB, the use of an execution plan that uses better the existing indexes in the table for the resolution of the Query.

select * from tabela where CPO1 = "101" AND CPO2 = "0001"

Examples

[oracle]
TrimRightSpace=0
; All environments / ORACLE connections will not remove the spaces on the right in character fields.

[oracle/homologa]
TrimRightSpace=1
; However, the connection with the environment ractification will perform removal.

Considere the AdvPL code below:

static cRDD := "TOPCONN"
static cTblTrim := "TBLTRIM"
static cDB := "MSSQL/DSNP11-MSSQL"

user function addTbl()
  Local nHandle := -1
  
  nHandle := TCLink( cDB, "127.0.0.1", 7890 )
  if nHandle < 0
    conout( "Connection error" )
    return
  endif
  
  // Creates table
  DBCreate( cTblTrim, { { "CPOC", "C", 10, 0 } }, cRDD )
  
  TCUnlink( nHandle )
return

user function unqIdx()
  Local nHandle := -1
  Local nRet := 0
  
  nHandle := TCLink( cDB, "127.0.0.1", 7890 )
  if nHandle < 0
    conout( "Erro de conexao" )
    return
  endif
  
  // Creates a single index in the CPOC field
  DBUseArea( .T., cRDD, cTblTrim, (cTblTrim), .F., .F. )
  nRet := TCUnique( cTblTrim, "CPOC" )
  if nRet <> 0
    conout( "Erro TCUnique: " + cValToChar( nRet ) )
    return
  endif
  DBCloseArea()
  
  TCUnlink( nHandle )
return

user function insRec()
  Local nHandle := -1
  
  nHandle := TCLink( cDB, "127.0.0.1", 7890 )
  if nHandle < 0
    conout( "Connection error" )
    return
  endif
  
  DBUseArea( .T., cRDD, cTblTrim, (cTblTrim), .F., .F. )
  
  // insere registro em branco
  DBAppend( .F. )
  DBCommit()
  
  DBCloseArea()
  
  TCUnlink( nHandle )
return

user function insA()
  Local nHandle := -1
  
  nHandle := TCLink( cDB, "127.0.0.1", 7890 )
  if nHandle < 0
    conout( "Connection error" )
    return
  endif
  
  DBUseArea( .T., cRDD, cTblTrim, (cTblTrim), .F., .F. )
  
  // inserts letter "A"
  DBAppend( .F. )
  CPOC := "A"
  DBCommit()
  
  DBCloseArea()
  
  TCUnlink( nHandle )
return

If the sequence of execution of the DBAccess and the function is:

1. Upload the DBAccess with key TrimRightSpace off
2. Executes function u_addTbl
3. Executes function u_insRec
4. Executes function u_insA
5. Restart the DBAccess switching on the key TrimRightSpace
6. Executes function u_insA records that are in the TBLTRIM table are:

If this same sequence of executions were executed in a database Oracle, Tibero or PostgreSQL, the records would be:

If the sequence of executions are:

1. Upload the DBAccess with key TrimRightSpace on
2. Executes the function u_addTbl
3. Executes the function u_insRec
4. Executes the function u_insA
5. Restart the DBAccess turning off the key TrimRightSpace
6. Executes the function u_insA

The records stored in the SGBD are:

But in Oracle, Tibero or PostgreSQL the records are:

Now, if the sequence is:

1. Upload the DBAccess with key TrimRightSpace turned off
2. Executes the function u_addTbl
3. Executes the function u_unqIdx
4. Executes the function u_insRec
5. Executes the function u_insA
6. Restart the DBAccess turning on the key TrimRightSpace
7. Executes the function u_insA

The second call of the function u_insA would result error, as the SGBD, when comparing two strings, disregards the blank spaces on the right, which leads to violation of the single key created by the function u_unqIdx.