I am looking for a network-efficient way of dealing with Lookups.

I have a database app with lookup fields. Most of these are highly static, but of course
some change, and users need to see the changes.

I am thinking about doing something like this:

- Create a View on the Remote server for each Lookup.
- Copy this View to the local machine in a local DB once.
- Use the local View in the application, but test whether the remote View has changed
compared to the local view, and update if necessary.

--

The replication feature of EDB (or a function??) may allow me to do this.

Is this a sensible strategy, or do other people do it totally differently?
Does anyone have example code they can point me to for the "update if necessary"
functionality?

Thanks in advance.

Adam Brett

Adam,

<< I am thinking about doing something like this:

- Create a View on the Remote server for each Lookup.
- Copy this View to the local machine in a local DB once.
- Use the local View in the application, but test whether the remote View
has changed compared to the local view, and update if necessary. >>

Views are simply a stored SQL SELECT statement.  You can't really "copy"
them and have the data copied also.

<< The replication feature of EDB (or a function??) may allow me to do this.
>>

The replication will allow you to replicate tables, but not views, because
of the above-mentioned reasons.  But yes, you can use the replication as a
way of handling lookup tables in an application.  The replication is ideal
for situations where you have a local application that needs to periodically
update its data from a central source.

<< Does anyone have example code they can point me to for the "update if
necessary" functionality? >>

It's very easy with the stores functionality.  You would simply use this
pseudo-code:

1) Create a remote store in the local application that points to a store on
the ElevateDB Server.
2) Use the SET UPDATES STORE statement to set the current updates store to
this remote store.
3) Issue a SELECT statement on the Configuration.Updates table to get a list
of the update file that are present in the remote store.
4) If there are any update files present whose creation date is greater than
the last loaded updates file's creation date, then go ahead and copy all
update files that quality to a local store using the COPY FILE statement,
and then proceed to load them using the LOAD UPDATES statement.
5) Record the creation date for the last updates file that you loaded to use
with the next check.

You can create a job on the ElevateDB Server that will dump out new update
files into the store that is polled by all of the client applications every
night or every hour based upon whatever criteria you wish.

--
Tim Young
Elevate Software
www.elevatesoft.com