DBF How to obtain an array containing record number

[MarcoBoschi]

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: Select all  Expand view

   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
 

[karinha]

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.

[Enrico Maria Giordano]

Something like this?

Code: Select all  Expand view

#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

[MarcoBoschi]

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.

[karinha]

Esto?

Code: Select all  Expand view

#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.

[carlos vargas]

Code: Select all  Expand view

LOCAL aRec := CUSTOMER->( RecsToArray() )
...
CUSTOMER->(ArrayToRecs(aRec))
...
 

Code: Select all  Expand view

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

 

[Marc Venken]

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: Select all  Expand view

#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


 

[MarcoBoschi]

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

[nageswaragunupudi]

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.

[MarcoBoschi]

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.

[Enrico Maria Giordano]

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

[Enrico Maria Giordano]

And what do you think about this?

Code: Select all  Expand view

#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

[MarcoBoschi]

simply fantastic Enrico

[MarcoBoschi]

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

[Enrico Maria Giordano]

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