ADO Filtering - Mr. Nages and others

[lucasdebeltran]

Hello,

We are almost finishing ADORDD, but there is a problem converting some filters from Clipper sinatx to ADO Filter command:

The problem is with the vars (oData:Codigo, cName)...:

Code: Select all  Expand view  RUN

  SET FILTER TO FIELD->NAME = cName
  SET FILTER TO AUXILIAR->RELACION  == alltrim(  STR( oData:Codigo ))
 
  cCondicion := "CONTA->CUENTA = " + chr( 34 ) + alltrim( cValToChar( cCuentaoConcepto ) ) + chr( 34 )

  cCondicion := "CONTA->CONCEPTO = " + chr( 34 ) + alltrim( cValToChar( cCuentaoConcepto ) ) + chr( 34 )

 

This is the parser to ADO:

Code: Select all  Expand view  RUN

       oRecordSet:Filter := SqlTranslate(aFilterInfo[ UR_FRI_CEXPR ])


STATIC FUNCTION SQLTranslate( cFilter )
  local cWhere
  local nAt, nLen, cToken, cDate, n
  local afunctions := {"STR(","VAL(","CVALTOCHAR(",;
                       'SOUNDEX(', "ABS(","ROUND(","LEN(","ALLTRIM(","LTRIM(","RTRIM(",;
                       "UPPER(","LOWER(","SUBSTR(",;
                       "SPACE(","DATE(","YEAR(","MONTH(",;
                       "DAY(","TIME(","IF("}
  local areplaces := { "","",""," LIKE ","","","","","","","","","","","","","","","",""}

   cWhere      := Upper( cFilter )
   cWhere      := StrTran( StrTran( cWhere, "'", "''" ), '"', "'" )
   cWhere      := StrTran( StrTran( cWhere, ".AND.", "AND" ), ".OR.", "OR" )
   cWhere      := StrTran( StrTran( cWhere, ".T.", "1" ), ".F.", "0" )
   cWhere      := StrTran( cWhere, "==", "=" )
   cWhere      := StrTran( cWhere, "!=", "<>" )
   cWhere      := StrTran( cWhere, "!", " NOT " )
   cWhere      := StrTran( cWhere, Alias()+"->", "" )
   if At( "!DELETED()", cWhere ) == 1; cWhere   := LTrim( SubStr( cWhere, 11 ) ); endif
   if At( "AND", cWhere ) == 1; cWhere := LTrim( SubStr( cWhere, 4 ) ); endif
   if At( "OR", cWhere ) == 1; cWhere := LTrim( SubStr( cWhere, 3 ) ); endif

   if  At("$",cWhere) > 0
      cWhere := InvertArgs(cWhere,"$")
   endif

   // Now handle dates its adpated from adofuncs because it was only considering one occurrence
   do while .t.

      for each cToken in { "STOD(", "CTOD(", "HB_STOT(", "HB_CTOT(", "STOT(", "CTOT(", "{^" }
          nAt    := At( cToken, cWhere )
          if nat > 0
             exit
          endif
       next

       if nAt = 0
          exit
       endif

       for each cToken in { "STOD(", "CTOD(", "HB_STOT(", "HB_CTOT(", "STOT(", "CTOT(", "{^" }
           nAt    := At( cToken, cWhere )
           if nAt > 0
              if Left( cToken, 1 ) == "{"
                 nLen  := At( "}", SubStr( cWhere, nAt ) )
              else
                 nLen  := At( ")", SubStr( cWhere, nAt ) )
              endif
              cDate := SubStr( cWhere, nAt, nLen )
#ifdef __XHARBOUR__
              if Left( cDate, 3 ) == "HB_"; cDate := SubStr( cDate, 4 ); endif
#else
              if Left( cDate, 5 ) $ "STOT(,CTOT("
                 cDate    := "HB_" + cDate
              endif
              if Left( cDate, 2 ) = "{^"
                 cDate    := LTrim( SubStr( cDate, 3 ) )
                 cDate    := If( ':' $ cDate, "HB_STOT('", "HB_STOD('" ) + cDate + "')"
                 cDate    := CharRem( "/-:} ", cDate )
              endif

#endif
              cDate  := &cDate
              cWhere := Stuff( cWhere, nAt, nLen,  DateToADO( cDate ) )
           endif
       next
   enddo

   for n:= 1 to len(afunctions)
       cWhere := StrTran( cWhere, afunctions[n], areplaces[n] )

   next

   cWhere      := StrTran( cWhere, ")", "" )


return cWhere


 

Any clue please?.

Thank you.

[nageswaragunupudi]

Translating a filter condition when the condition contains harbour variables seems to be difficult. I could not find a way. So the present FW_Filter2Where() function returns valid condition only if the filter condition contains constants only and not variables.

The author of SQLRDD did an extremely wonderful job. It may be worth seeing how did he handle this.

[lucasdebeltran]

Mr. Nages,

Thank you.

Are you refering to SQLRDD from xHarbour.com?. I am afraid the source code is not available.

SO far, the only way would be to rewrite all filters to this:

Code: Select all  Expand view  RUN

cFiltro := "NIF = '" + ALLTRIM( cNif) + "'"
bFiltro := "{|| " + cFiltro +"}"

DBSETFILTER( &bFiltro, cFiltro )

 

Any other approach?.

Thank you.

[lucasdebeltran]

Hello,

I also posted on the Harbour and xHarbour developers group to get their kind help:

https://groups.google.com/forum/#!topic ... bfq-Zydz0I

https://groups.google.com/forum/#!topic ... sz0_vb8hh8