tdatabase Filter error

[Silvio.Falconi]

I have a problem on a set filter , I'm converting from old application

oReservation:=TDatabase():Open( , cDbfPath+"Reserva", "DBFCDX", .T. )
oReservation:setorder(1)


cFilter := "trim(TYPE) == '" + AllTrim( cTypeRoom ) +;
"' .and. num== '" + AllTrim(str(nElemento)) +;
"' .and. '"+ dtoc(dDataIniziale)+"'<= dtoc(CHECK_OUT)"+;
" .and. '"+ dtoc(dDataFinale)+"'=> dtoc(CHECK_IN)"


oReservation:setFilter(cFilter)
oReservation:gotop()

xbrowser oReservation


the original was

SET FILTER TO AllTrim( RE->TYPE ) == AllTrim( cTypeRoom ) .and. RE->NUM = nElemento ;
.AND. ( dDataIniziale <= RE->CHECK_OUT .AND. dDataFinale >= RE->CHECK_IN )

RE->(DbGoTop())


give me this error

Code: Select all  Expand view  RUN

Application
===========
   Path and name: C:\Work\Errori\image_su_btnbmp\test2.Exe (32 bits)
   Size: 3,953,152 bytes
   Compiler version: Harbour 3.2.0dev (r1703231115)
   FiveWin  version: FWH 19.03
   C compiler version: Borland/Embarcadero C++ 7.0 (32-bit)
   Windows version: 6.2, Build 9200

   Time from start: 0 hours 0 mins 5 secs
   Error occurred at: 23-04-2019, 19:09:38
   Error description: Error BASE/1449  Syntax error: &
   Args:
     [   1] = C   {||trim(TYPE) == '01' .and. num== '1' .and. '01-07-2018'<= dtoc(CHECK_OUT) .and. '07-08-2018'=> dtoc(CHECK_IN)}

Stack Calls
===========
   Called from: .\source\classes\DATABASE.PRG => COMPILE( 1133 )
   Called from: .\source\classes\DATABASE.PRG => TDATABASE:SETFILTER( 1351 )

Someone can Help me pls

[nageswaragunupudi]

Change this

Code: Select all  Expand view  RUN

" .and. '"+ dtoc(dDataFinale)+"'=> dtoc(CHECK_IN)"
 

as

Code: Select all  Expand view  RUN

" .and. '"+ dtoc(dDataFinale)+"' >= dtoc(CHECK_IN)"
 

Next:
You should be knowing that you should not compare DTOC(). This gives the wrong results.
Example:

Code: Select all  Expand view  RUN

  ? "30-06-2018" < "01-07-2018" // --> .F.
 

This gives false but you expect true

Instead, you should use DTOS()

[Silvio.Falconi]

Now I made

Code: Select all  Expand view  RUN

  oReservation:setorder(1)
 cFilter := "trim(TYPE) == '" + AllTrim( cTypeRoom ) +;
                 "' .and. num= '" + AllTrim(str(nElemento)) +;
                 "' .and. '"+ dtoc(dDataIniziale)+"' <= dtoc(CHECK_OUT)"+;
                 " .and. '"+ dtoc(dDataFinale)+"' >= dtoc(CHECK_IN)"
 oReservation:setFilter(cFilter)
   oReservation:gotop()
 

give me this error

Code: Select all  Expand view  RUN

Application
===========
   Path and name: C:\Work\Errori\image_su_btnbmp\test2.Exe (32 bits)
   Size: 3,953,664 bytes
   Compiler version: Harbour 3.2.0dev (r1703231115)
   FiveWin  version: FWH 19.03
   C compiler version: Borland/Embarcadero C++ 7.0 (32-bit)
   Windows version: 6.2, Build 9200

   Time from start: 0 hours 0 mins 3 secs
   Error occurred at: 24-04-2019, 10:57:41
   Error description: Error BASE/1071  Argument error: =
   Args:
     [   1] = N   2
     [   2] = C   1
Stack Calls
===========
   Called from: .\source\classes\DATABASE.PRG => (b)COMPILE( 1133 )
   Called from:  => DBGOTOP( 0 )
   Called from: .\source\classes\DATABASE.PRG => (b)TDATABASE( 189 )
   Called from: .\source\classes\DATABASE.PRG => TDATABASE:GOTOP( 0 )
   Called from: test2.prg => MOSTRA_ELEMENTI( 367 )
   Called from: test2.prg => LOAD( 257 )
   Called from: test2.prg => (b)TEST( 143 )
   Called from: .\source\classes\DIALOG.PRG => TDIALOG:INITIATE( 864 )
   Called from: .\source\classes\DIALOG.PRG => TDIALOG:HANDLEEVENT( 1120 )
   Called from:  => DIALOGBOXINDIRECT( 0 )
   Called from: .\source\classes\DIALOG.PRG => TDIALOG:ACTIVATE( 304 )
   Called from: test2.prg => TEST( 143 )
   Called from: test2.prg => MAIN( 36 )

System
======
   CPU type: Intel(R) Atom(TM) x5-Z8350  CPU @ 1.44GHz 1440 Mhz
   Hardware memory: 3961 megs

   Free System resources: 90 %
        GDI    resources: 90 %
        User   resources: 90 %

   Windows total applications running: 5
      1 , C:\Windows\WinSxS\x86_microsoft.windows.common-controls_6595b64144ccf1df_6.0.15063.0_none_583b8639f
      2 , C:\Work\Errori\image_su_btnbmp\test2.Exe                                                            
      3 DDE Server Window, C:\Windows\System32\OLE32.DLL                                                                      
      4 G,                                                                                                    
      5 G, C:\Windows\WinSxS\x86_microsoft.windows.gdiplus_6595b64144ccf1df_1.1.15063.0_none_d802f55807fa1ec7\

Variables in use
================
   Procedure     Type   Value
   ==========================
   (b)COMPILE
     Param   1:    O    Class: ERROR
   DBGOTOP
     Local   1:    N    2
     Local   2:    C    "1"
     Local   3:    U    
     Local   4:    U    
   (b)TDATABASE
   TDATABASE:GOTOP
     Param   1:    O    Class: TDATABASE
     Local   1:    N    3
   MOSTRA_ELEMENTI
   LOAD
     Param   1:    A    Len:   20
     Param   2:    A    Len:   20
     Local   1:    N    2
     Local   2:    N    1
     Local   3:    N    2
     Local   4:    N    1
     Local   5:    N    50
     Local   6:    C    "trim(TYPE) == '01' .and. num= '1' .and. '01-07-2018' <= dtoc(CHECK_OUT) .and. '07-08-2018' >= dtoc(CHECK_IN)"
     Local   7:    U    
     Local   8:    U    
     Local   9:    U    
     Local  10:    U    
     Local  11:    U    
     Local  12:    U    
     Local  13:    U    
     Local  14:    U    
     Local  15:    U    
   (b)TEST
     Param   1:    O    Class: TDIALOG
     Param   2:    N    20
     Param   3:    N    40
     Param   4:    A    Len:   20
     Param   5:    A    Len:   20
     Local   1:    L    .T.
   TDIALOG:INITIATE
     Param   1:    O    Class: TDIALOG
   TDIALOG:HANDLEEVENT
     Param   1:    N    1050216
     Param   2:    N    1050216
     Local   1:    L    .T.
     Local   2:    U    
     Local   3:    U    
     Local   4:    L    .F.
     Local   5:    U    
     Local   6:    U    
   DIALOGBOXINDIRECT
     Param   1:    N    272
     Param   2:    N    1050216
     Param   3:    N    1050216
   TDIALOG:ACTIVATE
     Param   1:    N    4194304
     Param   2:    C    "€ È€          Œo
   M i n i   B e a c h   t e s t     "
     Param   3:    N    0
     Param   4:    O    Class: TDIALOG
   TEST
     Param   1:    U    
     Param   2:    U    
     Param   3:    U    
     Param   4:    L    .T.
     Param   5:    U    
     Param   6:    L    .T.
     Param   7:    B    {|| ... }
     Param   8:    U    
     Param   9:    U    
     Param  10:    U    
     Param  11:    U    
     Param  12:    L    .F.
     Param  13:    O    Class: TDIALOG
     Param  14:    U    
     Local   1:    N    0
     Local   2:    S    
     Local   3:    O    Class: TDIALOG
   MAIN
     Local   1:    N    800
     Local   2:    O    Class: TFONT
     Local   3:    N    48.6
     Local   4:    N    165
     Local   5:    N    1303.5
     Local   6:    N    733.86
     Local   7:    C    ""
     Local   8:    N    50

Linked RDDs
===========
   DBF
   DBFFPT
   DBFBLOB
   DBFCDX
   DBFNTX

DataBases in use
================

  1: => TDF00002                           RddName: DBFCDX
     ==============================
     RecNo    RecCount    BOF   EOF
          1           22      .F.   .F.

     Indexes in use                        TagName
        => ROOMS_ID + DToS( CHECK_IN )        RE001
           ROOMS_ID                           RE002

     Relations in use

  2:    TDF00003                           RddName: DBFCDX
     ==============================
     RecNo    RecCount    BOF   EOF
         41          800      .F.   .F.

     Indexes in use                        TagName
        => ELEMENTO                           SPCAM
           ELEMENTO+SETTORE                   ID

     Relations in use

Classes in use:
===============
     1 ERROR
     2 HBCLASS
     3 HBOBJECT
     4 TINI
     5 TDATABASE
     6 TFONT
     7 TWINDOW
     8 TDIALOG
     9 TBRUSH
    10 TCONTROL
    11 TXBROWSE
    12 TREG32
    13 TXBRWCOLUMN
    14 TBTNBMP
    15 TSCROLLBAR
    16 TCLIPBOARD
    17 TRECT
    18 TSTRUCT

Memory Analysis
===============
      585 Static variables

   Dynamic memory consume:
      Actual  Value:    1507328 bytes
      Highest Value:    1507328 bytes


 

I saw the problem is on

"' .and. num= '" +ltrim(str(nElemento)) +;

nElemento is a Number and on dbf I have num is a number field


I made a small test to try

Code: Select all  Expand view  RUN

#include"fivewin.ch"
#include "constant.ch"

REQUEST DBFCDX
static cDbfPath

Function test()
Local oreservation
Local cFilter

Local cTypeRoom:="01"
Local nElemento:= 2
Local dDataIniziale  := ctod("01/07/2018")
Local dDataFinale    := ctod("07/08/2018")

 RDDSetDefault( 'DBFCDX' )
 cDbfPath    := cFilePath(GetModuleFileName( GetInstance() ))  + "Data\"

   oReservation:=TDatabase():Open( , cDbfPath+"Reserva", "DBFCDX", .T. )
   oReservation:setorder(1)
   oReservation:gotop()

      cFilter := "trim(TYPE) == '" + ( cTypeRoom )
      cFilter += "' .and. num= '" +ltrim(str(nElemento))
      cFilter += "' .and. '"+ dtoc(dDataIniziale)+"' <= dtos(CHECK_OUT)"
      cFilter += " .and. '"+ dtoc(dDataFinale)+"' >= dtos(CHECK_IN)"


   ? cFilter
   oReservation:setFilter(cFilter)
   oReservation:gotop()

xbrowser oReservation

return nil

[nageswaragunupudi]

What datatypes your dbf has you should be knowing. I do not know.
I can only point out obvious errors in your code.

[Silvio.Falconi]

sorry I forget it

structure of Reserva.dbf

Code: Select all  Expand view  RUN

 DbCreate(cDir+'RE',{ { "date"      , "D",   8, 0 },;
                           { "rooms_id"  , "C",   4, 0 },;
                           { "check_in"  , "D",   8, 0 },;
                           { "check_out" , "D",   8, 0 },;
                           { "status"    , "C",   2, 0 },;
                           { "type"      , "C",   2, 0 },;
                           { "num"       , "N",   4, 0 },;
                           { "guest"     , "C",  30, 0 },;
                           { "phone"     , "C",  14, 0 },;
                           { "invoice"   , "C",  10, 0 },;
                           { "payed"     , "L",   1, 0 },;
                           { "totale"    , "N",  12, 2 },;
                           { "acconto"   , "N",  12, 2 },;
                           { "dataPayed" , "D",   8, 0 },;
                           { "dasaldare" , "N",  12, 2 },;
                           { "num_giorni", "N",   3, 0 },;
                           { "morning"   , "C",   1, 0 } }, 'DBFCDX')

index

INDEX ON ROOMS_ID + DToS(CHECK_IN ) TAG room_in EVAL (oProgress:SetPos(nProgress++), Sysrefresh()) EVERY 1 FOR !Deleted()

I have to filter by room type ( field type) by number ( field num) and by date initial and final ( fields check_in and check_out)

[nageswaragunupudi]

field NUM is numeric and you are comparing with a string value.
You should check yourself such mistakes at your end instead of posting them here

[Silvio.Falconi]

Sorry Mr Rao I correct it but I have also a problem because it not execute the filter well

I have this dbf



and I make this small test to try

Code: Select all  Expand view  RUN

#include"fivewin.ch"
#include "constant.ch"

REQUEST DBFCDX
static cDbfPath

Function test()
Local oreservation
Local cFilter

Local cTypeRoom:="01"
Local nElemento:= 2
Local dDataIniziale  := ctod("01/07/2018")
Local dDataFinale    := ctod("07/08/2018")

 RDDSetDefault( 'DBFCDX' )
 cDbfPath    := cFilePath(GetModuleFileName( GetInstance() ))  + "Data\"

   oReservation:=TDatabase():Open( , cDbfPath+"Reserva", "DBFCDX", .T. )
   oReservation:setorder(1)
   oReservation:gotop()


      cFilter := "trim(TYPE) == '" + ( cTypeRoom )
      cFilter += "' .and. num= " +ltrim(str(nElemento))
      cFilter += " .and. '"+ dtoc(dDataIniziale)+"' <= dtoc(CHECK_OUT)"
      cFilter += " .and. '"+ dtoc(dDataFinale)+"' >= dtoc(CHECK_IN)"

   ? cFilter
   oReservation:setFilter(cFilter)
   oReservation:gotop()

xbrowser oReservation

return nil

the archive index is
ROOMS_ID + DToS( CHECK_IN )

I set
Local cTypeRoom:="01"
Local nElemento:= 2
Local dDataIniziale := ctod("01/07/2018")
Local dDataFinale := ctod("07/08/2018")

If you see the picture I have the record ( is the first) with NUM = 2 and the chech_in 16 july 2018 and check_out 16 july 2018
why I not see this record ?

I tried also with

Code: Select all  Expand view  RUN

cFilter := "trim(TYPE) == '" + trim( cTypeRoom )
      cFilter += "' .and. num= " +ltrim(str(nElemento))
      cFilter += " .and. '"+ dtos(dDataIniziale)+"' <= dtos(CHECK_OUT)"
      cFilter += " .and. '"+ dtos(dDataFinale)+"' >= dtos(CHECK_IN)"
 

the string we have is

trim(TYPE) == '01' .and. num= 2 .and. '20180107' <= dtos(CHECK_OUT) .and. '20180708' >= dtos(CHECK_IN)

where is the error ?

but I not see that record

[nageswaragunupudi]

What do you want to see?
All Rooms fully vacant between dDataIniziale and dDataFinale?
Or
All Rooms occupied anytime between dDataIniziale and dDataFinale?

[Silvio.Falconi]

Sorry Mr Rao

I need to have All rooms occupied at any time between dDataIniziale and dDataFinale

the end user selects the dates (dDataIniziale and dDataFinale)

after making the filter (cfilter) I made I cycle on the archive reserva.dbf


DO WHILE !oReservation:EoF()
nStatus := Val( oReservation:STATUS )

to take the first record and have the value of the variable nStatus

later with this value I can change the image to btnbmp

IF nstatus=5 //free
aBmps[nY,nX]:SetFile(aElementi[nKE][8])
else // not free
aBmps[nY,nX]:SetFile(aElementi[nKE][9])
endif




this night I try with

cFilter := "trim(TYPE) == '" + ( cTypeRoom )
cFilter += "' .and. num= " +ltrim(str(nElemento))
cFilter += " .and. '"+ dtoc(dDataIniziale)+"' <= dtoc(CHECK_OUT)"
cFilter += " .and. '"+ dtoc(dDataFinale)+"' >= dtoc(CHECK_IN)"

and also not run ok

[nageswaragunupudi]

Mr. Silivio

You said your original filter with DBF is working. I am not going into the logic.
I am giving here conversion of your old DBF filter to be used here

Code: Select all  Expand view  RUN

// Values of the variables which the User entered
// We are using constants here for example

cTypeRoom      := "01"
nElemento      := 1
dDataIniziale  := STOD( "20180701" )
dDataFinale    := STOD( "20180807" )

// Converting the Filter Expression
// Start with original filter expression without alias names

cFilter  := "AllTrim( TYPE ) == AllTrim( cTypeRoom ) .and. NUM = nElemento .AND. " + ;
               "( dDataIniziale <= CHECK_OUT .AND. dDataFinale >= CHECK_IN )"

cFilter  := StrTran( StrTran( StrTran( StrTran( cFilter,        ;
               "cTypeRoom",     VALTOPRGEXP( cTypeRoom )     ), ;
               "nElemento",     VALTOPRGEXP( nElemento )     ), ;
               "dDataIniziale", VALTOPRGEXP( dDataIniziale ) ), ;
               "dDataFinale",   VALTOPRGEXP( dDataFinale )   )

? cFilter

oReservation:SetFilter( cFilter )

 

The filter expression after applying the values of the variables looks like this

Code: Select all  Expand view  RUN

AllTrim( TYPE ) == AllTrim( "01" ) .and. NUM = 1 .AND. ( 0d20180701 <= CHECK_OUT .AND. 0d20180807 >= CHECK_IN )
 

If you use this code, you will get the results exactly as in your old program.

[Silvio.Falconi]

thanks
the test run ok I see the record right now



I select from datepick control the dDataIniziale and dDataFinale and I have set date Italian
on dbf I have the date as 16/07/18 how I can make to have and use as your test
dDataIniziale := STOD( "20180701" ) ?

[nageswaragunupudi]

Do not make any changes.
Use this logic as it is.

In fwh 1904, we are going to make this much simpler.
Are you now using 1903 and will you be downloading FWH1904 when released?

[Silvio.Falconi]

ok
Now run ok

[nageswaragunupudi]

Mr. Silvio

Hereafter, you do not take all the above trouble to convert your old DBF filters for use with TDatabase. I suggest you a very simple way.

This is your original filter

Code: Select all  Expand view  RUN

SET FILTER TO AllTrim( RE->TYPE ) == AllTrim( cTypeRoom ) .and. RE->NUM = nElemento ;
.AND. ( dDataIniziale <= RE->CHECK_OUT .AND. dDataFinale >= RE->CHECK_IN )
 

We can not use alias names, so let us replace RE-> with FIELD->
This is the new filter expression

Code: Select all  Expand view  RUN

SET FILTER TO AllTrim( FIELD->TYPE ) == AllTrim( cTypeRoom ) .and. FIELD->NUM = nElemento ;
.AND. ( dDataIniziale <= FIELD->CHECK_OUT .AND. dDataFinale >= FIELD->CHECK_IN )
 

Now, do not bother to replace local variables with their values. Just use Exec() method like this, using exactly the same expession without any conversions:

Code: Select all  Expand view  RUN

oReservation:Exec( < ||
   SET FILTER TO AllTrim( FIELD->TYPE ) == AllTrim( cTypeRoom ) .and. FIELD->NUM = nElemento ;
   .AND. ( dDataIniziale <= FIELD->CHECK_OUT .AND. dDataFinale >= FIELD->CHECK_IN )
   return nil
   > )
oReservation:GoTop()
 

This approach will save you a lot of time in converting your old filter expressions.

[Silvio.Falconi]

thanks
it no need to converte the date ?

to clear the filter ( set filter to)
I can make ?

Code: Select all  Expand view  RUN

oReservation:Exec( < ||
    SET FILTER TO
   return nil
   > )