The sample database described in the Product Availability and Resources section contains two agents, WebGeneralQuery and WebDateQuery, together with a script library containing useful functions for reuse in Web Query agents. Both agents have two sets of parameters: the ODBC connection details from a profile document called Settings and the four parameters which make up the query itself -- Tablename, Columns, Query, Order -- supplied as part of the URL. These agents are set to query any table from any database for which the Domino server has an ODBC connection. The parameters for these queries can be defined as static within the Excel Web query definition, or selected by the Excel user as a dynamic parameter when running the query. Either way, hundreds of queries for many different corporate databases can all be run using a single Domino Web agent.

The agents also implement simple intranet security. You could password-protect the database containing the agents and force users to log in each time. However, to prevent that inconvenience to users, you can alternately set up a list of TCP/IP networks (IP numbers or alias names) in the Settings document which are to be allowed access, as shown in Figure D. Any user trying to gain access from outside the list of permitted IP numbers or names will receive a polite security denial message. A function called CheckAddress() is supplied in the database's script library to simplify this check.

FIGURE D


The settings profile document in sample database controls ODBC and intranet security. Roll over picture for a larger image.

The need for a separate date query is to simplify entry of SQL parameters. Dates usually must be entered using an RDBMS specific formula, for example TODATE('31/10/98','dd/mm/yy'), which is cumbersome for users. The date-specific query replaces this with three parameters: one which specifies the column of the database to be queried on a date basis, and the other two which specify the start and end dates. This isn't necessary, but it does make life much easier for both your Excel users and for anyone writing Web query files.

While these are generic agents for maximum flexibility, they also serve as the basis for custom queries for your own projects. For example, you may wish to replace the generic Query parameter with specific columns to be queried, or format the resulting data in particular ways. To make writing your agents quicker and easier, reuse the ParseCGiString$() and CGITranslate$() functions from the database script library.