topic-30742

Board index FiveWin for Harbour/xHarbour ADO Filtering - Mr. Nages and others

Posts: 1303

Joined: Tue Jul 21, 2009 08:12 AM

ADO Filtering - Mr. Nages and others
Posted: Sat May 23, 2015 11:00 AM

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 (fw): Select all Collapse

  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 (fw): Select all Collapse

       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.

Muchas gracias. Many thanks.

Un saludo, Best regards,

Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]

Implementando MSVC 2010, FWH64 y ADO.

Abandonando uso xHarbour y SQLRDD.

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: ADO Filtering - Mr. Nages and others
Posted: Sat May 23, 2015 11:51 AM

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.

Regards

G. N. Rao.

Hyderabad, India

lucasdebeltran

Posts: 1303

Joined: Tue Jul 21, 2009 08:12 AM

Re: ADO Filtering - Mr. Nages and others
Posted: Sat May 23, 2015 11:54 AM

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 (fw): Select all Collapse

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

DBSETFILTER( &bFiltro, cFiltro )

Any other approach?.

Thank you.

Muchas gracias. Many thanks.

Un saludo, Best regards,

Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]

Implementando MSVC 2010, FWH64 y ADO.

Abandonando uso xHarbour y SQLRDD.

lucasdebeltran

Posts: 1303

Joined: Tue Jul 21, 2009 08:12 AM

Re: ADO Filtering - Mr. Nages and others
Posted: Sat May 23, 2015 01:36 PM

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

Muchas gracias. Many thanks.

Un saludo, Best regards,

Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]

Implementando MSVC 2010, FWH64 y ADO.

Abandonando uso xHarbour y SQLRDD.

FiveTech Support Forums

Continue the discussion