Overview
OTProvide is a add on module for Improv that allows you to insert information stored in a relational database into your Improv worksheet. The Beta version of this product provides access to Sybase databases only.
OTProvide is a database link and query manager that allows you to create, store, and associate sets of queries with a specific Improv models. Each query is associated with a specific worksheet within the model. OTProvide supplies two methods of creating and modifying queries. A point and click interface allows you to easily design the queries to retrieve the data you need. For users familiar with the database query specification language, SQL, an editor is supplied to allow direct SQL manipulation. This group of easy to use tools enable novice and casual users, as well as database experts, to employ database information in their Improv models.
Results of queries are retrieved from Sybase using the login information you specify on the OTProvide Preferences panel. The results are placed in the worksheet and model associated with the query and its query set respectively. This worksheet is called the destination worksheet.
Results of a query are presented in a two category worksheet. The rows returned from Sybase are stored, by default, in the category named "Query_Rows"; the columns are stored in the category named "Query Columns". The row item names are, by default, numeric (e.g. 1,2,3). The column item names are the column names returned from Sybase, automatically adjusted to maintain uniqueness.
In order to be able to refresh the destination worksheet with new data by re-executing a query, certain restrictions exist on the way the worksheet may be modified. For example, if you add a third category to the destination worksheet, it may not be re-used. Further restrictions will be presented in the context of using many of OTProvide's features described below.
Improv, unlike other spreadsheet applications, was designed to utilize multiple worksheets to form a model. OTProvide has been designed with that approach in mind. The general approach for building models based on data retrieved from the database through OTProvide, is to bring data into "data sheets". You then build your model in additional sheets which utilize the data in the "data sheets".
Query Viewer
The majority of the work you will do with OTProvide centers around the Query Viewer. The Query Viewer allows you to create, manage and destroy queries. The Query Viewer works much like the File Viewer provided by NeXT within the Workspace application. The Query Viewer is displayed by selecting the Query Viewer menu item within the Tools item in the OTProvide menu.
The bottom region provides a browser. In the first column of the browser, the Query Sets are listed. A Query Set is a folder containing any number of Queries. OTProvide associates a Query Set to the Improv model of the same name. To execute any of the Queries within a Query Set, the Improv model of the same name must be open inside of Improv. The second column lists the Queries within a selected Query Set. OTProvide associates a Query with the worksheet of the same name, contained within the model of the same name as the selected Query Set. In this way, there is a direct mapping of Query Sets and Queries to models and worksheets respectively.
The third column contains summary information about the selected Query. It contains the time the Query was last executed, whether or not the execution was successful, and how many rows of information were retrieved. Below the summary information are four buttons. From left to right, the buttons are delete, copy, new, and execute. The delete button deletes the selected Queries or Query Sets. You will be asked to confirm any deletions before they occur. The copy button duplicates the current selection. If there is no selected Query Set, the new button will create a new Query Set. If a Query Set is selected, the new button will create a new Query. The execute button will execute the selected Query.
The middle region is a path describing the current selections in the browser. The path contains icon representations of the Query Set and/or Query specified in the browser below it. The icons provide an interface for directly manipulating the Query Sets and Queries using the mouse.
The top region is a shelf. The shelf is made up of icon sized regions that behave like "programmable radio" buttons. You can drag any icon from the path over one of these buttons. A faded version of the icon you are dragging will appear in the button activated by the icon above it. Releasing the icon will attach the icon to the button. The button has now been programmed. Once programmed, the icons behave exactly as icons in the path.
You can copy or move Queries between Query Sets by using the path and the shelf. For example, if you wanted to move a Query from one Query Set to another, first select the Query in the browser, drag it from the path onto a button on the shelf. Now select the Query Set you wish to move it into in the path, using the browser. Drag the Query from the shelf over the Query Set in the path. An alert panel will appear asking whether you wish to copy or move the Query. In this case you would select move, had you wished to create a duplicate of the original Query in the new Query Set you would select copy.
The Query Viewer also provides a means of managing Query Sets and Queries within the integrated NeXT environment. If you wish to mail a Query or Query Set to another user, you can include the selected item in the mail by dragging it from the path or shelf into the mail message. Similarly, if you receive mail containing a Query Set or Query, you can drag it into the Query Viewer. To add a Query Set into the Query Viewer from the NeXT environment, just drag it over the Query Sets icon and release. To add a Query from the NeXT environment, drag it over the desired Query Set and release. The same methods can be used between OTProvide and any application in the NeXT environment that will interact with Queries and Query Sets. For instance, if can save a Query within your home folder by dragging a Query from the Query View to the File Viewer.
Query Builder
The Query Builder allows you to modify the Query selected in the Query Viewer. When a query is selected the Query Builder will show the specifications of the selected query (unless the query has no specifications yet, as with a new query). None of the modifications to the Query's specifications will apply to the Query selected in the Query Viewer until you click the OK button on the Query Builder. By clicking on the Revert button, you can restore the specifications in the Query Builder to those of the Query selected in the Query Viewer. To clear all specifications in the Query Builder, click Clear.
The browser located at the top of the Query Builder allows you to navigate through your database. The first column (starting from the far left) displays different database servers accessible to you on your network. The second column lists the databases available on a selected server. The third column lists the tables contained within a selected database. The fourth column lists the fields for each record within a selected table. The top of each column is titled with the name of the item selected in the previous column.
A query is a combination of specifications about what to include when retrieving data from the database. The Query Builder lets you create those specifications. The row of buttons below the browser list the types of specifications you may want to include. Each of the buttons and there use is explained below.
The Select button allows you to pick which fields of information will be return from executing the query. Select one or more fields from the browser (the last column) and click Select. A small recessed dot will appear indicating that the field is selected.
The Sort button allows you to select which fields the returned data will be ordered by. Select a field and click Sort. An "S" followed by a number will appear next to the field. This indicates that the data will be sorted by that field. If you select another field and click Sort, another "S" and digit will appear. The sorts occur in order, where the sort with the lowest associated digit is first.
The Function Select button allows you to create fields which contain aggregate information for other fields. Select a field, click on the Function Select button. A panel will appear with a choice of aggregate functions. Choose which function you wish to use and click Enter. A new field appears selected into the browser's field column. It will contain the chosen function containing the field to be operated on as an argument.
The Group button provides a method of constructing abstract sets of data based on a key field. The groupings pertain mainly to queries containing aggregate functions. The aggregate functions will now apply to the abstract sets rather than the entire results of the query.
The Join and Smart Join buttons allow you to relate two tables. For two tables to be related they must each have a field that contains the same type of data. To tables that are related together by a common field are said to be joined on the field. To join two tables, select both tables by selecting the first and use Shift click to select the second. The fields column will display the fields from both tables. The fields are now identified by the table name, followed by a period, followed by the field name. Select the common field from each table so that both fields are selected. Click the Join button and a "J" followed by a digit will appear next to both of the fields. To un-join the tables, click on either of the fields, the Join button will change to UnJoin. Click the UnJoin button and the relation will disappear.
The Smart Join button is a automated method of joining tables. The Smart Join will look across all fields in the fields column of the browser and create joins on all fields having a common name.
Below the specification buttons is a pop up menu that allows you to restrict the query to unique rows in your results.
Below the uniqueness menu is a Where clause editor. The Where clause editor helps you restrict the type of data that is returned. Typically, Where clauses limit the rows returned to those matching its set of criterion. To start creating a Where clause, start editing in the Where clause editor by clicking on the editor below the word "Where:". A set of buttons will appear above the editor. The editor works similarly to Improv's formula editor. You can type or use the buttons edit using the mouse. Clicking on field names in the browser while editing in the Where clause editor will cause the field name to be inserted at the insertion point in the editor. When you a finished editing a Where clause, press Return and the editing will cease and the buttons will disappear.
Query Inspector
The Query Inspector allows you to examine and modify more specific attributes of a selected Query. The inspector is organized into sets of information. You can switch between sets using the pop up menu at the top of the inspector.
The first set of information is the Contents Inspector. The Contents Inspector puts the SQL representation of the database query in an editor. SQL is a database query specification language. If you are familiar with SQL you can create and modify the SQL for the selected query in this editor. It is important to note that once you have edited the SQL representation in the Contents Inspector, the Query Builder will be blank and no longer contain any of the query specification. Likewise, using the Query Builder, will cause the SQL representation to change to the specifications described in the Query Builder, overwriting any work done in the Contents Inspector. When you have finished editing SQL, click on OK to save the changes to the Query. Until clicking OK, you may restore the original SQL to the Contents Inspector by clicking Revert.
The second set of information is the Execution Inspector. The Execution inspector contains information about how and when the Query is executed. The top of the inspector tells you the Query's name, model name, status and time of the last execution, and how many rows were returned in the last execution.
The Query Preferences section allows you to control specific features. When the "Expand Column Widths" switch is checked, the width of each column of data will be re-sized to the largest element in the column. The "Automatically execute when loaded" switch is used to designate the query to execute when Improv is started and the OTProvide preference to auto execute has been chosen. The "Hide sheet after execution" switch tells the worksheet receiving the results of the query to move itself in back of all the other worksheets after having finished loading data.
In the Database Information section you can define the name of the default database in which the tables used in the query can be found. If the query spans more than one database, this field should be left blank. The "Key Column" can be filled with the name of one of the table fields being returned by the Query. The data associated with this field will be inserted in to the "Item" for each row of the worksheet instead of into "Cells".
The "Improv Location Information" section allows you to designate the names to use for the Column and Row Categories. The "Row Group" field allows you to designate an Item Group that the rows retrieved from the database should be included within. Using the "Row Group" feature, you can restrict the placement of data on your worksheet to a specific Range. It also facilitate calculating Improv aggregation functions, like groupsum(), on the retrieved data.
The last set of information is a simple notes editor that allows you to associate an arbitrarily long note with each query.
Start Up
Start Up
The Start Up menu item, within the Start Up menu, starts OTProvide. Until you click this item, OTProvide is not functional. If you want OTProvide to start automatically every time you launch Improv, you can enable that through the Preferences panel (see below).
Login
After starting OTProvide, you need to have OTProvide log into the database server. When you click Login, OTProvide will attempt to log into the database server using login information found in the Preferences panel. If you want OTProvide to login automatically every time you launch Improv, you can enable that through the Preferences panel (see below).
Autoexecute
The Autoexecute menu item will execute any Queries, in any of the Query Sets, having open associated models and have been set up to auto execute (see Execution Inspector).
Preferences
The Preferences panel is displayed by following the steps outlined below:
- Select Tools from the Improv main menu
- Select OTProvide from the Tools menu
- Select Info from the OTProvide menu
- Select Preferences from the OTProvide Info menu
- The OTProvide Preferences panel will display
Start Up
There are three options on the Start Up panel. The first allows you to select whether or not you want OTProvide to start when Improv is launched. The second allows you to determine if, when OTProvide has started, it should automatically attempt to login to the database using information from the Preferences Database panel. The last option, lets you determine if, after logging in to the database, OTProvide will execute any Queries, in any of the Query Sets, having open associated models and have been set up to auto execute (see Execution Inspector).
Database
This information will be used to connect you to Sybase. If the server field is left blank SYBASE will be used. Both the login name and password must be valid. The password will not show when typed. If the Database is not specified, you will be in which ever database Sybase defaults to (for the specified login id).
The connection can be made automatically each time you load OTProvide by selecting the Login To Database option in startup preferences, or you may manually connect to the server by selecting Login from the OTProvide menu.
If you want to test the login right now, go to the OTProvide menu, select the Login menu item under the OTProvide Start Up menu item. If the menu item becomes disabled then you have connected successfully. If not, check that your server is running. Also check that the login information is correct. After checking these things, try to log in once more.
NOTE: Remember to click OK at the bottom of the Preferences panel, if you have entered information in any of the fields, or clicked any of the switches.
Query Viewer
This panel defines default information for the Query Viewer. All of the Query Sets stored in the File Viewer are actually stored at a specific location on the file system. You should not manage any of the queries by manipulating any of the files stored at this location through UNIX or the Workspace FileViewer. All manipulation can and should be accomplished through the OTProvide Query Viewer. In the event that the default location is inappropriate for your uses, you can change it in the Query Viewer Directory section of this panel. You may also notice that the default Query Set that comes up in the Viewer is named "Untitled" and the default name of a Query when a new one is created is "Worksheet" followed by a digit not used by other Queries in the Query Set. You may alter those defaults in this panel.
Execution
The Preference Execution panel allows you to change some of the default values that are used to fill in the Query Inspector Execution information when a new Query is created. It also will allow you control query execution. You may limit the number of rows that come back. You may also ask OTProvide to notify you that it is about to reuse an existing sheet and give you a chance to stop the execution before the data in the sheet is overwritten.
Using OTProvide VB5.0 Copyright 1992 by Objective Technologies, Inc. All rights reserved.
OTProvide is a trademark of Objective Technologies, Inc. Improv is a trademark of Lotus Development Corporation. Sybase is a registered trademark of Sybase Inc.
OBJECTIVE TECHNOLOGIES, INC.