FiveTech Support Forums

FiveWin / Harbour / xBase community
Board indexActive topicsAll discussionsLogin (GitHub)
Board index FiveWin for Harbour/xHarbour DBF How to obtain an array containing record number
MarcoBoschi
Posts: 1091
Joined: Thu Nov 17, 2005 11:08 AM

DBF How to obtain an array containing record number

Posted: Wed Oct 09, 2024 01:57 PM
Hi to all,
I'm wondering which is the best/fastest way to load an array containing all records numbers in a dbf table indexed by a particular order
Obviously not this one ot this one because as written in my previous post it is very slow if opened by other users.
I don't know how cdx are structured but I imagine that there is an association key-record number
Many thanks
Marco
Code (fw): Select all Collapse
   FUNCTION MAIN()
    LOCAL aRecno := {}
    SELECT 0
    USE customers
    SET INDEX TO customer
    INDEX ON field->last TAG LAST TO customer
    GO TOP
    DO WHILE !EOF()
          AADD( aRecno , RECNO())
    ENDDO

RETURN NIL
Marco Boschi
info@marcoboschi.it
karinha
Posts: 8523
Joined: Tue Dec 20, 2005 07:36 PM

Re: DBF How to obtain an array containing record number

Posted: Wed Oct 09, 2024 02:19 PM

Good morning Marco, I didn't quite understand your question, but I think the correct thing would be: nPos := OrdKeyNo()

Explain better what you want to do and what the purpose is, pls.

Buenos días Marco, no entendí bien tu pregunta, pero creo que lo correcto sería: nPos := OrdKeyNo()

Explique mejor lo que quiere hacer y cuál es el propósito, por favor.

Gracias, tks.

Regards, saludos.

João Santos - São Paulo - Brasil - Phone: +55(11)95150-7341
Enrico Maria Giordano
Posts: 9022
Joined: Thu Oct 06, 2005 08:17 PM

Re: DBF How to obtain an array containing record number

Posted: Wed Oct 09, 2024 02:20 PM
Something like this?
Code (fw): Select all Collapse
#include "Fivewin.ch"


FUNCTION MAIN()

    LOCAL aRec := {}

    USE CUSTOMER

    DO WHILE !EOF()
        AADD( aRec, { RECNO(), FIELD -> last } )
        SKIP
    ENDDO

    ASORT( aRec, , , { | aItem1, aItem2 | aItem1[ 2 ] < aItem2[ 2 ] } )

    ? LEN( aRec ), aRec[ 1, 1 ], aRec[ 1, 2 ]

    RETURN NIL
Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg
MarcoBoschi
Posts: 1091
Joined: Thu Nov 17, 2005 11:08 AM

Re: DBF How to obtain an array containing record number

Posted: Wed Oct 09, 2024 03:08 PM

Very interesting Enrico!

Karinha I want an array containing all record numbers because the real speed of a cycle that

FOR i := 1 TO LEN( aRec ) 

  GOTO  aRec[ i , 1 ]

NEXT i

and not by this one that is very slow if cdx is opened by other person

DO WHILE !EOF()

  SKIP

ENDDO

But I would hope that by reading the cdx file directly I can obtain the list of records one by one.

I remember you that If the cdx file is opened by another person in my lan the speed going down tremendously.

If you could with some basic function obtain this array (the records) by reading only the cdx file it would be a great thing.

Marco Boschi
info@marcoboschi.it
karinha
Posts: 8523
Joined: Tue Dec 20, 2005 07:36 PM

Re: DBF How to obtain an array containing record number

Posted: Wed Oct 09, 2024 03:42 PM
Esto?
Code (fw): Select all Collapse
#include "Fivewin.ch"

ANNOUNCE RDDSYS
REQUEST OrdKeyNo, OrdKeyCount, OrdCreate, OrdKeyGoto
REQUEST DBFCDX, DBFFPT

FUNCTION Main()

   LOCAL aRec := {}

   rddSetDefault( "DBFCDX" )
   rddRegister( "DBFCDX", 1 )

   USE CUSTOMER NEW

   INDEX ON FIELD->LAST TAG 01 TO CUSTOMER FOR .NOT. DELETED()

   SET INDEX TO CUSTOMER

   GO TOP

   WHILE .NOT. EOF()

      SYSREFRESH()

      AAdd( aRec, { RecNo(), FIELD->LAST } )

      SKIP

   ENDDO

   ? LastRec(), RecCount()

   XBROWSE( aRec )

RETURN NIL
Regards, saludos.
João Santos - São Paulo - Brasil - Phone: +55(11)95150-7341
carlos vargas
Posts: 1789
Joined: Tue Oct 11, 2005 05:01 PM

Re: DBF How to obtain an array containing record number

Posted: Wed Oct 09, 2024 08:51 PM
Code (fw): Select all Collapse
LOCAL aRec := CUSTOMER->( RecsToArray() )
...
CUSTOMER->(ArrayToRecs(aRec))
...
Code (fw): Select all Collapse
FUNCTION RecsToArray()
   LOCAL nField := FCount()
   LOCAL nCount := RecCount()
   LOCAL aArray := Array( nCount, nField )
   LOCAL i, x

   FOR i := 1 TO nCount
      FOR x := 1 TO nField
         aArray[ i, x ] := FieldGet( x )
      NEXT
      dbSkip()
   NEXT

RETURN aArray

/*----------------------------------------------------------------------------------*/

FUNCTION ArrayToRecs( aArray )
   LOCAL nCount  := Len( aArray )
   LOCAL nFields := 0
   LOCAL nRecAdd := 0
   LOCAL i, x

   IF nCount == 0
      RETURN nRecAdd
   ENDIF

   nFields  := Len( aArray[ 1 ] )

   FOR i := 1 TO nCount
      DBAppend()
      IF !NetErr()
         FOR x:=1 TO nFields
            FieldPut( x, aArray[ i, x ] )
         NEXT
         ++nRecAdd
      ENDIF
   NEXT
Salu2

Carlos Vargas

Desde Managua, Nicaragua (CA)
Marc Venken
Posts: 1487
Joined: Tue Jun 14, 2016 07:51 AM

Re: DBF How to obtain an array containing record number

Posted: Wed Oct 09, 2024 09:59 PM
Sql can be used with DBF... (partly forum code)

Not sure if the speed is better in Lan than with CDX used by more people.
How to get recno() with SQL is not known by me. ChatGPT gave 2 options, but not working with my small knowledge of SQL
Code (fw): Select all Collapse
#include "fivewin.ch"
#include "xbrowse.ch"
#include "hbcompat.ch"

function Main()

   local nAvgAge, aData, cSql
   local cFolder:= "c:\fwharb\samples\"

   TEXT INTO cSql
     SELECT LAST,STATE FROM customer
   ENDTEXT

   aData    := FW_DbfSqlQuery( cFolder, cSql )

   XBROWSER aData TITLE "Result" ;
      SETUP (  oBrw:cHeaders := { "Last", "State"} )
return nil
Marc Venken

Using: FWH 23.08 with Harbour
MarcoBoschi
Posts: 1091
Joined: Thu Nov 17, 2005 11:08 AM

Re: DBF How to obtain an array containing record number

Posted: Thu Oct 10, 2024 08:07 AM

Thanks friends

You are all very good. Excellent programmers.

Let's see if I can explain myself

Let's just focus on the customer.cdx file

Let's forget about customer.dbf for a moment

I imagine that for the FIELD -> last key there is written somewhere the number of a record or more records that contain for example "Bailey"

I was wondering if there was the possibility of obtaining this information without necessarily scrolling the dbf table

Are there any functions among all index functions that permits to me to obtain the record number passing the key value? Only reading cdx files?

I ask this because from my tests the DO WHILE !EOF() SKIP ENDDO loop is very very slow if the table is opened by others

Sorry but I'm not happy with this matter

have a nice day!

Marco

Marco Boschi
info@marcoboschi.it
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 05:22 AM

Re: DBF How to obtain an array containing record number

Posted: Thu Oct 10, 2024 08:24 AM

This is exactly what I am looking for.

Can we open cdx file alone (raw file) and retrieve a list of record numbers of a specified Tag.

One should know the structure of CDX file very well.

Only great Experts like Mr. Enrico can help us.

And surely that will be a great help to all of us.

Regards



G. N. Rao.

Hyderabad, India
MarcoBoschi
Posts: 1091
Joined: Thu Nov 17, 2005 11:08 AM

Re: DBF How to obtain an array containing record number

Posted: Thu Oct 10, 2024 08:36 AM
nageswaragunupudi wrote:This is exactly what I am looking for.
Can we open cdx file alone (raw file) and retrieve a list of record numbers of a specified Tag.
One should know the structure of CDX file very well.

Only great Experts like Mr. Enrico can help us.
And surely that will be a great help to all of us.
8)
Marco Boschi
info@marcoboschi.it
Enrico Maria Giordano
Posts: 9022
Joined: Thu Oct 06, 2005 08:17 PM

Re: DBF How to obtain an array containing record number

Posted: Thu Oct 10, 2024 08:37 AM

The structure of the CDX index file is too complex, sorry.

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg
Enrico Maria Giordano
Posts: 9022
Joined: Thu Oct 06, 2005 08:17 PM

Re: DBF How to obtain an array containing record number

Posted: Thu Oct 10, 2024 11:14 AM
And what do you think about this?
Code (fw): Select all Collapse
#include "Fivewin.ch"


FUNCTION MAIN()

    LOCAL aRec := {}

    LOCAL i

    USE CUSTOMER

    FOR i = 1 TO LASTREC()
        GOTO i
        AADD( aRec, { RECNO(), FIELD -> last } )
    NEXT

    ASORT( aRec, , , { | aItem1, aItem2 | aItem1[ 2 ] < aItem2[ 2 ] } )

    ? LEN( aRec ), aRec[ 1, 1 ], aRec[ 1, 2 ]

    RETURN NIL
Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg
MarcoBoschi
Posts: 1091
Joined: Thu Nov 17, 2005 11:08 AM

Re: DBF How to obtain an array containing record number

Posted: Thu Oct 10, 2024 11:31 AM
simply fantastic Enrico :idea:
Marco Boschi
info@marcoboschi.it
MarcoBoschi
Posts: 1091
Joined: Thu Nov 17, 2005 11:08 AM

Re: DBF How to obtain an array containing record number

Posted: Thu Oct 10, 2024 11:45 AM

Some benchmarks using a table opened by other user containing 6000 records indexed in a lan

0.06 seconds using Enrico's technique and 11.57 seconds using a normal DO WHILE !EOF() ; SKIP ; ENDDO cicle

If table is opened only by this test program the speed is the same 0.06 seconds

Thanks Again

marco

Marco Boschi
info@marcoboschi.it
Enrico Maria Giordano
Posts: 9022
Joined: Thu Oct 06, 2005 08:17 PM

Re: DBF How to obtain an array containing record number

Posted: Thu Oct 10, 2024 12:20 PM

Great, but it's you who had the idea about using GOTO.

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg
Static archive · New replies & topics via GitHub Discussions