oDbf:Delete() error

[Silvio.Falconi]

I have two database

I must delete the User and all permission of this user


The user is deleted as you can see here

USERS



The permissions are not deleted ( sometimes two or three records are deleted)




I not understood why


the function to delete is this

Code: Select all  Expand view

Static Function UtBorra(oUtenti,oPermessi,oGrid2,oGrid3,oBtnCambia)
   local cClave:= alltrim(oUtenti:Clave)  
   IF lABC .AND. !oUtenti:EoF()
      IF MsgYesNo("Cancellare i permessi dell'utente "+Alltrim(oUtenti:Nombre)+"?","Confermare!")
         oPermessi:Seek(cClave)
                DO WHILE !oPermessi:EoF()
            IF alltrim(oPermessi:Usuario)==cClave
                  oPermessi:Delete()
            ENDIF
            oPermessi:Skip()
         ENDDO
            oUtenti:Delete()
            oGrid2:GoUp()
            oGrid2:Refresh()
            oGrid2:SetFocus()
          Rinfresca(nMod,oUtenti,oPermessi,oGrid2,oGrid3,oBtnCambia)
      ENDIF
   ELSE
   ENDIF
   oGrid2:SetFocus()
RETURN (NIL)
 

the key and the name of the user is always 8 characters but I put an alltrim () because it can be even less than 8 characters like for example "TEST"

on the oPermessi there is a SetScope
static cKey
cKey:=oUtenti:Clave
oPermessi:setscope(cKey)

I did not understand why sometimes some records are cacenalled and other times none and other times only one,

Another test made this morning




yet putting an xbrowser after the seek makes me see all the modules of that user but then does not delete them all


Any solution ?

a test sample to try the problem

Code: Select all  Expand view

#include "FiveWin.ch"

static cKey,lABC

request dbfcdx
request dbffpt

Function test()

local ctest:="Test"

RddSetDefault( "DBFCDX" )
     oUtenti:= TUtenti():New()
     oModuli:= TModuli():New()
     oPermessi:=TPermessi():New()

     lABC :=.t.

      xbrowser oUtenti
      xbrowser oPermessi

         oPermessi:SetOrder(2)
         cKey:=oUtenti:Clave
        oPermessi:setscope(cKey)
        xbrowser oPermessi

        UtBorra(oUtenti,oPermessi)

           xbrowser oPermessi
return nil


Static Function UtBorra(oUtenti,oPermessi)
   local cClave:= alltrim(oUtenti:Clave)  // può essere meno di 8 caratteri  data errore
   IF lABC .AND. !oUtenti:EoF()
      IF MsgYesNo("Cancellare i permessi dell'utente "+Alltrim(oUtenti:Nombre)+"?","Confermare!")
         oPermessi:Seek(cClave)
         xbrowser oPermessi
         DO WHILE !oPermessi:EoF()
            IF alltrim(oPermessi:Usuario)==cClave
                  oPermessi:Delete()
            ENDIF
            oPermessi:Skip()
         ENDDO
            oUtenti:Delete()
            *oGrid2:GoUp()
            *oGrid2:Refresh()
            *oGrid2:SetFocus()
          *Rinfresca(nMod,oUtenti,oPermessi,oGrid2,oGrid3,oBtnCambia)
      ENDIF
   ELSE
   ENDIF
  * oGrid2:SetFocus()
RETURN (NIL)






CLASS TXData from TDataBase
       DATA cExePath  init cFilePath(GetModuleFileName( GetInstance() ))
     ENDCLASS


CLASS TUtenti from TXData
   METHOD New()
ENDCLASS

METHOD New( lShared ) CLASS TUtenti
   Default lShared := .t.
   ::super:Open(,::cExePath + "Utenti" ,, lShared)
   if ::use()
      ::setOrder(1)
      ::gotop()
   endif
RETURN Self
//----------------------------------------------------------------------moduli
CLASS TModuli from TXData
   METHOD New()
ENDCLASS

METHOD New( lShared ) CLASS TModuli
   Default lShared := .t.
   ::super:Open(,::cExePath + "Moduli" ,, lShared)
   if ::use()
      ::setOrder(1)
      ::gotop()
   endif
RETURN Self
//----------------------------------------------------------------------Permessi
CLASS TPermessi from TXData
   METHOD New()
ENDCLASS

METHOD New( lShared ) CLASS TPermessi
   Default lShared := .t.
   ::super:Open(,::cExePath + "Permessi" ,, lShared)
   if ::use()
      ::setOrder(1)
      ::gotop()
   endif
RETURN Self



 

write to me at silvio[dot]falconi[at]gmail[dot]com if you need the dbfs to test it

[nageswaragunupudi]

When you want to delete multiple records for a given condition

oDbf:Lock()
oDbf:Exec( { || DELETE FOR <cond> } )
oDbf:Unlock()

[Silvio.Falconi]

Nages,
someone (an american boy) sad me not use lock and unlock (and commit) because
tdatabase make it auto.

[nageswaragunupudi]

Nages,
someone (an american boy) sad me not use lock and unlock (and commit) because
tdatabase make it auto.

Not FLOCK

[Silvio.Falconi]

then

DO WHILE !oPermessi:EoF()
oPermessi:Lock()
oPermessi:Exec( { || DELETE FOR oPermessi:Usuario==oUtenti:Clave } )
oPermessi:Unlock()

oPermessi:Skip()
ENDDO

is it right ?

make me error
source\test.prg(688) Error E0030 Syntax error "syntax error at 'FOR'"
1 error

[Marc Venken]

then

DO WHILE !oPermessi:EoF()
oPermessi:Lock()
oPermessi:Exec( { || DELETE FOR oPermessi:Usuario==oUtenti:Clave } )
oPermessi:Unlock()

oPermessi:Skip()
ENDDO

is it right ?

make me error
source\test.prg(688) Error E0030 Syntax error "syntax error at 'FOR'"
1 error

No, it's not

As i see it, the

oPermessi:Lock() // is actually a Flock and therefore not needed in a loop
oPermessi:Exec( { || DELETE FOR oPermessi:Usuario==oUtenti:Clave } ) // block that run all data inside the exec(), so not in a loop

Only the 3 lines from Mr. Rao seems to be needed.

the <cond> is the tricky part... what code is needed

// other post :

If you want to delete all records where the field INVNUM == nInvoice, i.e., "1234567890128", then:
[code]
Local nInvoice := "1234567890128"

oPartres:Exec( <||
DELETE ALL FOR ALLTRIM(FIIELD->INVNUM) == nInvoice
> )
[code]

viewtopic.php?f=3&t=37616&hilit=delete+for

[Marc Venken]

Maybe this will do it almost...

Code: Select all  Expand view

nInvoice := "1234567890128"
oPermessi:Lock()

oPermessi:Exec( <|| DELETE ALL FOR ALLTRIM(FIELD->INVNUM) == nInvoice  > )  // here your code

oPermessi:Unlock()

 

[Silvio.Falconi]

Maybe this will do it almost...

Code: Select all  Expand view

nInvoice := "1234567890128"
oPermessi:Lock()

oPermessi:Exec( <|| DELETE ALL FOR ALLTRIM(FIELD->INVNUM) == nInvoice  > )  // here your code

oPermessi:Unlock()

 

usuario and clave are 8 cr

DO WHILE !oPermessi:EoF()
oPermessi:Lock()
oPermessi:Exec( { || DELETE FOR alltrim(oPermessi:Usuario)==oUtenti:Clave } )
oPermessi:Unlock()
oPermessi:Skip()
ENDDO

I tried also with oPermessi:Exec( { || DELETE FOR alltrim(FIELD->Usuario)==oUtenti:Clave } )
give me an error on compilation

[Silvio.Falconi]

Now run ok

DO WHILE !oPermessi:EoF()
oPermessi:Lock()
oPermessi:Exec( { ||
DELETE FOR ALLTRIM(oPermessi:Usuario)==ALLTRIM(oUtenti:Clave)
} )
oPermessi:Unlock()
oPermessi:Skip()
ENDDO


and it delete all records but only some records because on Permessi dbf there is a filter, it deleted only that record have M = 5

[Silvio.Falconi]

Now run ok

Code: Select all  Expand view

Static Function UtBorra(oUtenti,oPermessi,oGrid2,oGrid3,oBtnCambia)
   local cClave:= oUtenti:Clave
      cKey:=(oUtenti:Clave)
   IF lABC .AND. !oUtenti:EoF()
      IF MsgYesNo("Cancellare i permessi dell'utente "+Alltrim(oUtenti:Nombre)+"?","Confermare!")
         oPermessi:Seek(cClave)
         oPermessi:SetScope(cKey)
          DO WHILE !oPermessi:EoF()
             oPermessi:Lock()
             oPermessi:Exec( { ||
             DELETE FOR ALLTRIM(oPermessi:Usuario)==ALLTRIM(oUtenti:Clave)
          } )
            oPermessi:Unlock()
            oPermessi:Skip()
         ENDDO
            oUtenti:Delete()
            oGrid2:GoUp()
            oGrid2:Refresh()
            oGrid2:SetFocus()
          Rinfresca(nMod,oUtenti,oPermessi,oGrid2,oGrid3,oBtnCambia)
      ENDIF
   ELSE
   ENDIF
   oGrid2:SetFocus()
RETURN (NIL)

 

[Marc Venken]

Hey Silvio,

Great that you have it working !

Could you please try this function (1 loop less that yours) for my personel curiossity ?
I would like to know if

oPermessi:Exec( { ||
DELETE FOR ALLTRIM(oPermessi:Usuario)==ALLTRIM(oUtenti:Clave)
} )

acts like a dbeval and processes all scopes records...

Code: Select all  Expand view

Static Function UtBorra(oUtenti,oPermessi,oGrid2,oGrid3,oBtnCambia)
   local cClave:= oUtenti:Clave
      cKey:=(oUtenti:Clave)
   IF lABC .AND. !oUtenti:EoF()
      IF MsgYesNo("Cancellare i permessi dell'utente "+Alltrim(oUtenti:Nombre)+"?","Confermare!")
         oPermessi:Seek(cClave)
         oPermessi:SetScope(cKey)
         oPermessi:Lock()
          oPermessi:Exec( { ||
             DELETE FOR ALLTRIM(oPermessi:Usuario)==ALLTRIM(oUtenti:Clave)
          } )
           oPermessi:Unlock()
            oUtenti:Delete()
           oGrid2:GoUp()
           oGrid2:Refresh()
           oGrid2:SetFocus()
          Rinfresca(nMod,oUtenti,oPermessi,oGrid2,oGrid3,oBtnCambia)
      ENDIF
   ELSE
   ENDIF
   oGrid2:SetFocus()
RETURN (NIL)

 

[nageswaragunupudi]

Code: Select all  Expand view

        oPermessi:Lock()
          oPermessi:Exec( { ||
             DELETE FOR ALLTRIM(Usuario)==ALLTRIM(oUtenti:Clave)
          } )
 

The DELETE FOR statement should be PURE RDD statement,

[James Bott]

Maybe try this:

Code: Select all  Expand view

          oPermessi:Lock()
          oPermessi:Exec( { |oUtenti|
             DELETE FOR ALLTRIM(Usuario)==ALLTRIM(oUtenti:Clave)
          } )

[nageswaragunupudi]

Maybe try this:

Code: Select all  Expand view

          oPermessi:Lock()
          oPermessi:Exec( { |oUtenti|
             DELETE FOR ALLTRIM(Usuario)==ALLTRIM(oUtenti:Clave)
          } )

This gives wrong results.
Exec method evaluates the codeblock with Self as parameter.

Code: Select all  Expand view

  METHOD Exec( bBlock )      INLINE ( ::cAlias )->( Eval( bBlock, Self ) )
 

So, the variable oUtenti gets the value of oPermiessi inside this codeblock.

[Silvio.Falconi]

Hey Silvio,

Mark,
on my procedure when I delete a User I must delete all permissions of that user , but when I call ut_borra() function to delete that use I have on Permission dbf a set scope
Now with the new fwh ( april 2021) I can erase that setscope with setscope(nil), then I can delete all the permissions of that user with

Code: Select all  Expand view

DO WHILE !oPermessi:EoF()
             oPermessi:Lock()
             oPermessi:Exec( { ||
             DELETE FOR ALLTRIM(oPermessi:Usuario)==ALLTRIM(oUtenti:Clave)
          } )
            oPermessi:Unlock()
            oPermessi:Skip()
         ENDDO

then I can delete the user with oUtenti:Delete()

before it is not working because need a Flock (1) and I had the setscope on Usuario field ( user) (2)
with these two problems only some records were deleted, but now it seems to work fine





I didn't see the deleted records and I trusted them
I did not see them because the dbf was indexed with condition! Deleted ()
and then there was the scope on the user and on the number of menus (green box)
so if I selected a user in the xbrowse above I could not see the records in the xbrowse that it was below,
I didn't select it because the user had been deleted but his permissions were still in the archive