dbAppend to top dbf

[trepao2...@gmail.com]

Hello, 

I would need an append from but that would not add at the end but would add from the first record, crushing the existing ones and in some way being able to know how many have been added to know how many I have to delete. 

The idea is that like a .dbf it cannot be copied if it is in use, however you can make an append from (cFile). A delete all and an append are not valid because they generate too many deleted records which slows down the system

regards

[cod...@outlook.com]

Hi

I understand you post this way:

You have one file, say items.dbf, with n records for example. This file is in USE.

Then you have another file, say temp.dbf, with m records. It has same structure as items.dbf. It is not opened (not in USE).

You want to add m records from temp.dbf to items.dbf, but at the beginning of items.dbf, and before adding you want to delete existing n records in items.dbf. The result you want is to have m records in items.dbf. You want to know number of records you added, and number of records you deleted.

Did I understand it right way ?

Also, is first file (items.dbf in example) opened exclusive or shared ? Is app multiuser ? Can you temporary open first file exclusive ? Some more information will be helpful.

Regards,

Simo. 

[trepao2...@gmail.com]

Mr, Simo,

So:

[trepao2...@gmail.com]

Better sample so:

[jmcqu...@gmail.com]

Once you can't use ZAP, APPEND FROM, Create yout own routine.

May be anything like this

   USE ( cSource ) NEW
   SET INDEX TO ( cSource )
   SET ORDER TO 0
   nSource := Select()
   USE ( cTarget ) NEW
   SET INDEX TO ( cTarget )
   SET ORDER TO 0
   nTarget := Select()
   

SELECT ( nSource )
   DO WHILE ! Eof()
      SELECT ( nTarget )
      IF ( nSource )->( RecNo() ) > ( nTarget )->( LastRec() )
         RecAppend()
      ELSE
         GOTO ( ( nSource )->( RecNo() ) )  
      ENDIF
      RecLock()
      IF Deleted()
         RECALL
      ENDIF
      FOR nCont = 1 TO FCount()
         FieldPut( nCont, ( nSource )->( FieldGet( nCont ) ) )
      NEXT
      IF ( nSource )->( Deleted() )
         DELETE
      ENDIF
      RecUnlock()
      SELECT ( nSource )
      SKIP
   ENDDO
   IF ( nSource )->( RecNo() ) < ( nTarget )->( LastRec() )
      SELECT ( nTarget )
      GOTO ( ( nSource )->( LastRec() ) + 1 )
      DELETE WHILE ! Eof() .AND. RecLock()
   ENDIF

   RETURN Nil

FUNCTION RecAppend()
   
   APPEND BLANK
   
   RETURN Nil
   
FUNCTION RecLock()

   DO WHILE ! RLock()
      Inkey(0.3)
   ENDDO

   RETURN Nil

FUNCTION RecUnlock()

   SKIP 0  
   UNLOCK
   
   RETURN Nil

José M. C. Quintas

[cod...@outlook.com]

Hi

As I can see you want to append DbfA with m non-deleted (active) records from DbfB, putting them in the place of first m records (positions) in DbfA,  deleted or not in DbfA. And then you want to delete all non-deleted record from DbfA on positions greater then m.

In this way you minimize number of deletions, but it requires recalling deleted records in DbfA that exists in first m positions in DbfA.

José M. C. Quintas gave you one solution. I am sending you another one I used it long ago, with some old style coding. 

// DbfA and DBfB must have same record strukture

LOCAL m:=0,n:=0,rRecs:={},aRecs:={},i

USE DbfA NEW SHARED  // or it is already opened

// First, gather all active record from DbfB into array
// I like to work with arrays
aRecs:={}  // array of active (non-deleted) records from DbfB
USE DbfB NEW SHARED READONLY
GO TOP
DO WHILE ! EOF()
     IF DELETED()
          SKIP
          LOOP
     ENDIF
     rRecs:={}     //  array of fields from one (current) record in DbfB
     FOR i=1 TO FCOUNT()
           AADD(rRecs,FIELDGET(i))
     NEXT i
     AADD(aRecs,rRecs)
     SKIP
ENDDO
m:=LEN(aRecs)  // number of records to place at beginning of DbfA
CLOSE DbfB

//Second, place records from DbfB at beginning of DbfA
SELECT DbfA
GO TOP
n:=0
DO WHILE ! EOF()
     n++  // record position in DbfA
     IF ! RLOCK()  // error, can not lock deleted record,  must be serviced in proper way
        SKIP
        LOOP
     ENDIF
     // RLOCK required for any use command
     IF n <= m
        IF DELETED()
           RECALL
        ENDIF
        FOR i=1 TO LEN(aRecs[n])
            FIELDPUT(i, aRecs[n,i])
        NEXT i
        SKIP
        LOOP
     ENDIF
     *
     IF ! DELETED()
        DELETE
     ENDIF
     *
     SKIP
ENDDO
CLOSE DbfA

QUIT

Regards,

Simo.

[trepao2...@gmail.com]

Thank you mr. Simo and mr. Quintas

There is here my AppendMachacante() function

Please give me your opinion

//-------------------------------------------------------------------------//
/*
   // Use:

   FUNCTION Main()

   (cAliTarget)-> ( AppendMachacante(cAliFrom) )

   RETURN NIL

*/
FUNCTION AppendMachacante(cAliFrom)
Local cAli:= Alias()
Local a:= SEntorno({cAli, cAliFrom})
Local lSet:= Set(_SET_DELETED, .f.)
Local nPos:= 1
Local aPar
Local nMaxRec:= (cAli)-> (LastRec())
Local nCopied:= 0
*
SELECT (cAliFrom)
SET ORDER TO 0
GO TOP
DO WHILE !Eof()
   IF !Deleted()
      IF nPos <= nMaxRec
         (cAli)-> (dbGoto(nPos))
         (cAli)-> (Rec_Lock(0))
         IF (cAli)-> ( Deleted() )
            (cAli)-> ( dbRecall() )
         ENDIF
      ELSE
         (cAli)-> ( Add_Rec(0) )
      ENDIF
      //FLOGMSG_("ANADIENDO", cAli, cAliFrom, (cAli)-> (dbInfo(10)), (cAliFrom)-> (dbInfo(10)), aPar, nMaxRec, nPos)
      aPar:= CopReg5(cAliFrom, cAli, aPar)
      nCopied++
      UNLOCK
      *
      nPos++
   ENDIF
   *
   SKIP
ENDDO
*
IF nCopied > 0
   nPos--
   *
   IF nPos < nMaxRec
      *
      nPos++
      *
      SELECT (cAli)
      SET ORDER TO 0
      dbGoto(nPos)
      //FLOGMSG_("BORRETEO", cAli, cAliFrom, (cAli)-> (dbInfo(10)), (cAliFrom)-> (dbInfo(10)), aPar, nMaxRec, nPos)
      DO WHILE !Eof()
         Rec_Lock(0)
            DELETE
         UNLOCK
         SKIP
      ENDDO
      *
   ENDIF
ENDIF
*
Set(_SET_DELETED, lSet)
REntorno(a)
RETURN NIL
*
//-------------------------------------------------------------------------//
// Esta funcion es tolerante a estructuras distintas de origen y destino
STATIC FUNCTION CopReg5(cAliOri, cAliDes, aPar)
Local nI
Local nFCount
Local nPosOri
Local cCampo
*
IF aPar == NIL
   aPar:= {}
   *
   nFCount:= (cAliDes)-> (FCount())
   *
   #Define FG_(cAlias, nPos) ((cAlias)-> (FieldGet(nPos)))
   FOR nI:= 1 TO nFCount
      cCampo:= (cAliDes)-> (FieldName(nI))
      nPosOri:= (cAliOri)-> ( FieldPos(cCampo))
      *
      IF nPosOri != 0
         (cAliDes)-> ( FieldPut(nI, FG_(cAliOri, nPosOri)))
         *
         Aadd(aPar, {nI, nPosOri})
      ENDIF
   NEXT
   *
ELSE
   // Con el parametro de la primera vez es posible hacerlo mas rapido.
   nFCount:= Len(aPar)
   FOR nI:= 1 TO nFCount
      (cAliDes)-> ( FieldPut(aPar[nI, 1], FG_(cAliOri, aPar[nI, 2])))
   NEXT
ENDIF

RETURN aPar

[cod...@outlook.com]

Hi

I assume that you have tested your function, and that it works good.

That is only important. Everybody have its own style of coding. Every code that works what I want and with desired performances is good for me. 

If you need more tests, I can do it but you must send your code for functions SEntorno, REntorno, Add_Rec, Rec_Lock.

I am curious what is the story behind this way of solving problem. Why do you  allways replace whole content of DbfA with new content of DbfB, and then continue to use DbfA ? May be to use DbfB without transfering its content to DbfA ? If you wish let us explain real situation.

Regards,

Simo.

[ivanil.m...@gmail.com]

If the issue is speed, perhaps this works better;

Local cCod:="000001"
While x->(Dbseek(cod))
    x->codigo:="DELETED"
enddo

//Importação
A->(DbSeek(cCod))
While !A->(Eof()) .and. a->Codigo=cCod
if x->(DbSeek("DELETED")
           x->(rlock())
        else
           x->(DbAppend())
        endif
        x->... := a->...
        a->(DbSkip())
Enddo

[cod...@outlook.com]

Hi

I have tested your function replacing Add-rec with Dbappend() and Rec_lock with RLOCK(). 

Functions SEntorno and  REntorno I did not used, I make them as comment lines. Your AppendMachacante  function works without them.

I made few tests, with different number of records in DbfA and DbfB, and different number of deleted records inside them.

Result is that your function works good !

As opinion:

For better function readability you can make variable names for file and aliases more clearer, as all of them begins with cAli, like cAliFrom, cAli, cAliOri, cAlides. For someone who did not wrote function while reading code sometimes it can be unclear is it file which gives records or is it file which receives records. Or you can add comments on some lines of code. I put lot of comments on lines of code. Many times it was big help when after few years I must change part of old code. 

Regards,

Simo.

[trepao2...@gmail.com]

Thank you very much Mr. Simo for your comments and tests. It is increasingly difficult to open a dbf exclusively to make a pack because sometimes it is not possible because the users are using the program and other times it is because there are one or some processes that run regularly every night. 

1st. As it is, not being able to make a PACK results in the programs becoming slower and slower. Part of this slowness can be solved by putting all the live records at the beginning of the .dbf and the deleted ones at the end because this way, at least at the time of the USE (cDbf) that seeks to position itself in the first live record, it becomes more fast. AppendMachacante() can fix it.

2nd. Another application is to "replace" files that are in use. Imagine: I have taken a .dbf, I have worked on it (replaces, deletes) and I want to put it back in its original place. If the file is in use it will not be possible but AppendMachacante() will do the job. 

Greetings

[Daniel Aisenberg]

CREATE TABLE TAB1 ( CAMPO1 N(5), CAMPO2 C(5))
CREATE TABLE TAB2 ( CAMPO1 N(5), CAMPO2 C(5))

INSERT INTO TAB1 (CAMPO1, CAMPO2) VALUES (1, "1")
INSERT INTO TAB1 (CAMPO1, CAMPO2) VALUES (2, "2")
INSERT INTO TAB1 (CAMPO1, CAMPO2) VALUES (3, "3")
INSERT INTO TAB1 (CAMPO1, CAMPO2) VALUES (4, "4")
INSERT INTO TAB1 (CAMPO1, CAMPO2) VALUES (5, "5")

/* TO TEST
SELECT TAB1
GO 2
DELETE
*/


INSERT INTO TAB2 (CAMPO1, CAMPO2) VALUES (1, "1")
INSERT INTO TAB2 (CAMPO1, CAMPO2) VALUES (2, "2")

SELECT TAB2
GO 2
DELETE

SELECT TAB1

SET RELATION TO RECNO() INTO TAB2
DELETE FOR EOF("TAB2")
RECALL FOR !EOF("TAB2") AND !DELETED("TAB2")

// NOTE: syntax is vfp.. must translate to harbour

--
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: https://groups.google.com/group/harbour-users
---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/harbour-users/e4607fa0-9b9e-433c-9936-ea49a67f24a3n%40googlegroups.com.