ADS and DATA DICTIONARY

[lucasdebeltran]

Hello,

When working with tables in a Data Dictionary, how do you reindex and create indexes?.

Thank you very much.

Best regards,

Lucas

[Marcelo Via Giglio]

Hello,

you can use a Store Procedure to do that


sp_CreateIndex
sp_CreateIndex90

regards

Marcelo

[lucasdebeltran]

Hello Marcelo,

thanks for reply.

But you don´t use INDEX ON TO..?.


Please, can I see a piece of code?.


Thank you very much.

[Marcelo Via Giglio]

Hola,

from the ADS help file


sp_CreateIndex( TableName,CHARACTER,515,
FileName,CHARACTER,515,
TagName,CHARACTER,128,
Expression,CHARACTER,510,
Condition,CHARACTER,510,
Options,INTEGER,
PageSize,INTEGER );

[lucasdebeltran]

Hello,

And how do you reindex?.

Thank you

[Marcelo Via Giglio]

From the help file too, and with sample at the end of the page

Rebuilds all auto-open indexes associated with the given table.



Syntax

sp_Reindex( TableName,CHARACTER,515,

PageSize, INTEGER )



Parameters

TableName (I)
Name of table to rebuild indexes on. This can include path information for free tables if the table is in a different folder from the connection path.

PageSize (I)
The page size to use when rebuilding indexes on ADT tables. This is ignored for other table types. If the value 0 is given, the existing page size is used. Valid page sizes can be any value from 512 to 8192. Refer to Index Page Size and Index Key Size and Page Size Relationships for more information. Note that if the table is in an Advantage Data Dictionary, then only the administrator connection can be used to change the page size when reindexing a table.




Remarks

sp_Reindex rebuilds all auto-open indexes associated with the given table. Because the reindex operation requires exclusive access to the table, no other connections can have the table open when this is called.



Example



-- Rebuild all auto-open indexes on mytable with a page size of 1024

EXECUTE PROCEDURE sp_Reindex( 'mytable.adt', 1024 );

[Patricio Avalos Aguirre]

Lukas

Si las tablas ya tienen index puedes usar esto

Code: Select all  Expand view

    aIndex := AdsDirectory()

    for i := 1 to len( aIndex )
        cDbf := Left( aIndex[i], len( aIndex[i] )-1 )
        if Netuse( cDbf, .f. ) //abrimo la tabla exclusivo
           PACK
           if ADSGETNUMINDEXES() > 0 // revisamos si la tabla tiene indice
              AdsReindex()  //reindexamo
           endif
        endif
        USE
    next i

[carlos vargas]

esto lo hice a partir del ejemplo anterior.

Code: Select all  Expand view

PROCEDURE Reorganizar()
   LOCAL cTabla
   LOCAL aTablas
   LOCAL oError

   IF !MsgNoYes( "Para realizar este proceso, ningun usuario debe estar conectado al programa, desea continuar?" )
      RETURN
   ENDIF

   IF !ADSRunSQL( "TABLAS", "SELECT * FROM SYSTEM.TABLES" )
      RETURN
   ELSE
      aTablas := {}
      TABLAS->( DBEval( {|| AAdd( aTablas, RTrim( FIELD->NAME ) ) } ), DBCloseArea() )
      IF Len( aTablas ) = 0
         TABLA->(dbclosearea())
         RETURN
      ENDIF
   ENDIF

   WaitOn( "Cerrando tablas..." )
   DbCloseAll()
   WaitOff()

   WaitOn( "Reorganizando datos..." )
   CursorWait()

   FOR EACH cTabla IN aTablas
      TRY
         USE (cTabla) NEW EXCLUSIVE
         IF !NetErr()
            IF AdsGetNumIndexes() > 0
               IF !AdsReindex()
                  MsgInfo( AdsGetLastError(), "Usuario" )
               ENDIF
            ENDIF
         ENDIF
         DBCloseArea()
      CATCH oError
         MsgAlert( "Error reorganizando información en tabla en: " + Upper( cTabla ) + FINL + oError:Description )
      END
   NEXT

   CursorArrow()
   WaitOff()

   MsgRun( "Espere un momento, abriendo nuevamente tablas de datos.", "CREDICOM", {|| Conectar_AbrirTablas( TRUE, TRUE ) } )

RETURN
 

[lucasdebeltran]

Hello,

Thank you both very much, Patricio and Carlos.

The point is that with a Data Dictionary INDEX ON TO.. does not work. AdsReindex() should be used instead. But I could not find this nor in the documentation nor in Rene Flore´s manual.

Using Remote or Local server works the same as RDDCDX, but not when using a data dictionary.

Thanks a lot!!!

[carlos vargas]

en un diccionario es necesario usar la funciones

EXECUTE PROCEDURE sp_CreateIndex90(
'caja',
'caja.adi',
'NUM_CIUD',
'num_ciud',
'',
2,
512,
'' );

[carlos vargas]

creacion por codigo de un dd, talas e indices.

salu2

Code: Select all  Expand view

/*
    Demo of ADS Connection handling and Data Dictionaries
*/


#include "ads.ch"
REQUEST ADS

FUNCTION MAIN
   local n
   local cErr, cStr
   local aStru := {{ "ID", "A", 1, 0}, {"Name", "C", 50, 0}, {"address", "C", 50, 0}, {"city", "C", 30, 0}, {"Age", "n", 3, 0}}
   local hConnection1, hConnection2
   local lIsDict := .f.

   CLS

   RddSetDefault("ADT")
   AdsSetServerType ( 7 )
   SET Filetype to ADT

   ? "Default connection is 0:", adsConnection()

   fErase("xharbour.add")
   fErase("xharbour.ai")
   fErase("xharbour.am")
   fErase("Table1.adt")
   fErase("Table1.adi")
   fErase("Table2.adt")
   fErase("Table2.adi")

   // now Create a Data dictionary and the files if not exist
   IF !File("xharbour.add")
      ADSDDCREATE("xharbour.add",, "Xharbour ADS demo for data dictionary")
      // This also creates an Administrative Handle that is set as the default
      ? "Default connection is now this admin handle:", adsConnection()
      AdsDisconnect()   // disconnect current default.
                        // if you wanted to retain this connection for later, you could use
                        // hAdminCon := adsConnection(0)
                        // This get/set call would return the current connection, then set it to 0

      ? "Default connection is now this handle (zero):", adsConnection()

      // now create two free tables with same structure
      DbCreate("Table1", aStru)
      DbCreate("Table2", aStru)
      //now create an index
      USE table1 new
      INDEX ON id TAG codigo
      USE

      USE table2 new
      INDEX ON id TAG codigo
      USE
   ENDIF

   // now the magic
   IF adsConnect60("xharbour.add", 7/* All types of connection*/, "ADSSYS", "", , @hConnection1 )
      // The connection handle to xharbour.add is now stored in hConnection1,
      // and this is now the default connection

      ? "Default connection is now this handle:", adsConnection()
      ? "   Is it a Data Dict connection?  (ADS_DATABASE_CONNECTION=6, "
      ? "      ADS_SYS_ADMIN_CONNECTION=7):", AdsGetHandleType()

      // Add one user
      AdsDDCreateUser(, "Luiz", "papael", "This is user Luiz")


      IF adsddGetUserProperty("Luiz", ADS_DD_COMMENT, @cStr, hConnection1)
         ? "User comment:", cStr
      ELSE
         ? "Error retrieving User comment"
      ENDIF


      ? "Add the tables"
      AdsDDaddTable("Table1", "table1.adt", "table1.adi")
      ?
      IF ! AdsDDaddTable("Customer Data", "table2.adt", "table2.adi")
         // notice the "long table name" for file Table2.adt.  Later open it with "Customer Data" as the table name
          ? "Error adding table:", adsGetLastError(@cErr), cErr
      ENDIF
      ? "Set new admin pword on default  connection:", AdsDDSetDatabaseProperty( ADS_DD_ADMIN_PASSWORD, "newPWord"  )
      ? "Set new admin pword on explicit connection:", AdsDDSetDatabaseProperty( ADS_DD_ADMIN_PASSWORD, "newPWord", hConnection1  )
      ? "Clear admin pword:", AdsDDSetDatabaseProperty( ADS_DD_ADMIN_PASSWORD, ""  )


   ELSE
      ? "Error connecting to xharbour.add!"
   ENDIF
   AdsDisconnect(hConnection1)
   hConnection1 := nil     // you should always reset a variable holding a handle that is no longer valid

   ? "Default connection is back to 0:", adsConnection()
   ? "Is a Data Dict connection? (AE_INVALID_HANDLE = 5018):", AdsGetHandleType()

   // now open the tables and put some data

   IF AdsConnect60("xharbour.add", 7/* All types of connection*/, "Luiz", "papael", , @hConnection1)
      ? "Default connection is now this handle:", adsConnection()
      ? "Connection type?", AdsGetHandleType()

      FOR n := 1 TO  100
         IF AdsCreateSqlStatement("Data2", 3)
            AdsExecuteSqlDirect(" insert into Table1( name,address,city,age) VALUES( '" + strzero(n)+"','"+strzero(n)+"','"+strzero(n)+"'," +str(n)+ ")" )
            USE
         ENDIF
      NEXT

      FOR n := 1 TO 100
         IF AdsCreateSqlStatement("Data1", 3)
            AdsExecuteSqlDirect(" insert into " +'"Customer Data"'+"( name,address,city,age) VALUES( '"+ strzero(n)+"','"+strzero(n)+"','"+strzero(n)+"'," +str(n)+")" )
            USE
         ENDIF
      NEXT


      // AdsUseDictionary(.t.)  this function no longer is needed; the system knows if it's using a Data Dictionary connection

      // Open the "long table name" for Table2
      DbUseArea(.t.,, "Customer Data", "custom", .t., .f.)
      ? "Press a key to browse", alias()
      inkey(0)
      Browse()
      USE
      USE table1 new
      Browse()
      USE
   ENDIF

   AdsDisconnect(hConnection1)
   @ 24, 0 say ""

RETURN NIL