Index create, add, update

[Antonio Wiesner]

I am confused about the XBasic methods for indexes. In Xbasic manual pages 201.. we see Table.index_create_begin() and Table.index_add(), and for other methods there is a different syntax with a pointer such as .index_create_end or .index_primary._put() In examples we also see expressions like
tbl= table.current()
..create..
index_pointer=tbl.index_create_end()
Questions: does this last line assume that a variable index_pointer has been declared? Or more generally, what would be a simple, safe syntax for creating a new production index: should I use the line with the pointer or tbl.index_create_end() alone will do? Same for adding - updating indexes and setting primary index. ASSUME TABLE IS IN SINGLE USER SESSION. Thanks for your kind help.
Antonio Wiesner

[JerryBrightbill]

RE: Index create, add, update

Antonio,

I agree that the index create method in xbasic in not very intuitive, so let me see if I can answer your questions.

tbl. and idx. are pointers you set that refer to a table and index respectively. Therefore, the line

index_pointer=tbl.index_create_end()

actually sets the index pointer. It also assumes that a table pointer tbl. has already been set. Therefore the need for the line

tbl = table.current()

which sets a pointer tbl. as the pointer for the current table. You actually don't have to use tbl and idx as the pointer names. You could use table1. or index1. or something else. You just can't set a pointer as table. or index. or Alpha will be confused. The use of table. in the index create expression is actually a dot variable used by alpha, not one you set.

Now for your other questions. To create an index in xbasic, you must first use table.index_create_begin() to set the information for the first index. Then use table.index_add to add a second, third, fourth index, etc. Finally, you have to have a table pointer assigned for the table that will contain the indexes. This can be set before or after the index creation lines. Then use the table_pointer.index_create_end() to send the information to the table. Here is an example. Be aware that the indexname (tag name)and index expression must be expressed as character expressions and enclosed in quotes. Filters and flags are optional and are also character expressions.

table.index_create_begin("lastname","lname+fname")
table.index_add("city","city")
table.index_add("zipcode","zipcode")
tbl = table.open("customers")' this line assumes that the customer table is not open.
tbl.index_create_end()
tbl.close() ' this line closes the customer table

This will replace all existing indexes for the customers table with three new indexes named "lastname","city", and "zipcode".

Now for a variation. Lets assume that the customers table is already open, you only want to create one index, and you want to set the primary index that index "lastname" and create an index pointer for the index.

tbl2 = table.current()
table.index_create_begin(�lastname�,�lname+fname�)
tbl2.index_create_end()

idx2 = tbl2.index_primary_put("lastname")

The use of pointers and dot variables is very confusing at times. There is a good article at http://www.learn alpha.com/ which does a much better job of explaining this than I have.

Jerry

[Antonio Wiesner]

RE: Index create, add, update

Thanks, Jerry, your info very useful.
The reason I am rebuilding indexes is that my table 'clientes' tends to malfunction for some unknown reason: when updating there is a crash sometimes. I managed to avoid the crash by first removing indexes (that is, index_create..with just one customer_No index),then performing the update operation and later rebuilding the several indexes required for the table, with another index_create.. plus index_add.. commands. All this is rather awkward and I would prefer to spare all the index manipulation. So another question comes:
Perhaps if I recreate the table from scratch (new table) my problems would be gone, but I need to keep the file name 'clientes.dbf' for all layouts and scripts,. How can I drop or delete existing table and create a new one with same name, dictionary, layouts and data safely? Thanks in advance for any help.

[Ohlen Cartmell]

RE: Index create, add, update

FWIW
If reindexing causes a crash, I suspect one of the fields the index definition is corrupted in one or more records. If this the case, I'd define a new table and transfer half the records to it. Then run and index update on the new table. If you keep doing this, eliminating or including half the records at a time, you can isolate a bad record quickly.

[Antonio Wiesner]

RE: Index create, add, update

Elsewhere in this board conributors point to serious potential problems with filtered indexes. In our application such indexes exist that may have caused trouble, and I have inserted scripts updating production indexes to avoid crashes. Ohlen Cartmell's suggestion that corrupted records could be generating errors made me review one of my main tables (clientes.dbf), customer table.When re-evaluating field rules with "mark invalid records" option, the caption shows a number of records marked as invalid, but when looking at the table no record is actually marked. The reason for invalid records was corrected (conflict between imported data and validation/data entry rules), but it would be great to have a way to detect and identify invalid records -really marking them when revaluating rules.

Anyway, after finding and correcting errors in several tables via field rule re-evaluation and editing, I hope the application becomes more stable. What other evidence of record corruption there might be? perhaps corrupted (invalid) records are a major cause of index-related crashes?