best way to update indexes

[gkeramidas]

i have an application that uses a database that's updated every day from an external program. if my app is run requesting data from the last day, no data is found unless i manually update the indexes first.

i tried an action script and it generates this code:
for i = 1 to count
statusbar.percent(i,count)
table_i = word(tables_to_update,i,crlf())
tbl = table.open(table_i)
tbl.update_production_index()
tbl.close()
next i

but nothing happens when this is run. there are over a million records in this db, so i know it doesn't happen that fast. it takes a minute or two for each index when i run it manually. i just need a way that it's updated when the user starts the app or clicks a menu item.

i also tried index.update() and it didn't seem to work. i have 2 indexes, Name and Acct.

thanks

[Ohlen Cartmell]

Look under database utilities in action scripting. There is an action script to update the indexes in an application.

[Peter.Wayne]

You have to supply a list of the tables you want to update. If you want to update indexes for all the tables in your database, you can change the script to

tables_to_update=a5_table_enum_actual()
for i = 1 to count
statusbar.percent(i,count)
table_i = word(tables_to_update,i,crlf())
tbl = table.open(table_i)
tbl.update_production_index()
tbl.close()
next i

but if you want to update indexes for only a few tables, you can write
tables_to_update=<<%lst%
bigTable
smallTable
justRightTable
%lst%
for i = 1 to count
statusbar.percent(i,count)
table_i = word(tables_to_update,i,crlf())
tbl = table.open(table_i)
tbl.update_production_index()
tbl.close()
next i

You probably don't need to update indexes on all the tables, just the one(s) receiving external information.

[Peter.Wayne]

Whoops, Ohlen, I made a mistake by not initializing "count". To rewrite:

tables_to_update=a5_table_enum_actual()
count=w_count(tables_to_update,crlf())
for i = 1 to count
statusbar.percent(i,count)
table_i = word(tables_to_update,i,crlf())
tbl = table.open(table_i)
tbl.update_production_index()
tbl.close()
next i

and
tables_to_update=<<%lst%
bigTable
smallTable
justRightTable
%lst%
count=w_count(tables_to_update,crlf())
for i = 1 to count
statusbar.percent(i,count)
table_i = word(tables_to_update,i,crlf())
tbl = table.open(table_i)
tbl.update_production_index()
tbl.close()
next i

- Peter

[gkeramidas]

thanks for the replies, like i mentioned, action scripiting generates the code, i didn't paste it all becasue i figured it was a5 generated. but here it is, it looks just like yours but nothing happens. there is only one table and 2 indexes.

'Update indexes for all of the tables in the Database.
omit_tables_short = <<%list%
custpts
%list%
'convert short table names to fully qualified table names.
omit_tables = *for_each(x,table.filename_get(x),omit_tables_short)


dim table_list as c
table_list = <<%list%
custpts
%list%

'convert table_list to long file names
table_list = *for_each(x,table.filename_get(x),table_list)
'check that all tables in the table_list exist
check_exist = *for_each(x,x+"|" + file.exists(x),table_list)
missing = filter_string(check_exist,"|False",crlf())
if missing <> "" then
msg = "Indexes in the following tables will not be updated because the tables cannot be found: " + crlf() + stritran(missing,"|False","")
ui_msg_box("Warning",msg,UI_ATTENTION_SYMBOL)
table_list = filter_string(check_exist,"|True",crlf())
if table_list = "" then
ui_msg_box("Error","No tables for which to update indexes.",UI_STOP_SYMBOL)
end
else
table_list = stritran(table_list,"|True","")
end if
end if


if omit_tables <> "" then
tables_to_update = word_subtract(table_list,omit_tables,crlf())
else
tables_to_update = table_list
end if

dim count as n
count = w_count(tables_to_update,crlf())
dim i as n
dim tbl as p
for i = 1 to count
statusbar.percent(i,count)
table_i = word(tables_to_update,i,crlf())
tbl = table.open(table_i)
tbl.update_production_index()
tbl.close()
next i

[Peter.Wayne]

You have chosen to omit the same table you want to update. Remove the section on omit_tables, or just scrap the action script and modify the script I wrote.

[gkeramidas]

thanks, DR.


in action scripting, i must have checked the db in the first dialog assuming it said you want to update instead of select the db you do NOT want to update