Generic table - Database query
In a large enterprise environment, not all user resource data are stored in Active Directory. Other information systems, an HR system or a phone system for example, may also hold user related information. In an UMRA project form, a generic table can be inserted to display information from these systems. As part of the table setup, the administrator needs to specify the database to connect to, as well as a database query to define which data should be retrieved and shown in the table.
The generic table for databases can be used to connect to any database or information system. MS Access databases can be accessed directly through the Jet engine. Other databases (SQL, ERP, HR systems, PeopleSoft, etc.) can be accessed through OLE DB. The number of available OLE DB providers will depend on your local configuration (see ).
Figure 12 - List of available OLE DB database connections
The option to connect to other databases is an extremely powerful feature in UMRA, since it allows you to combine Active Directory with user data from a wide range of other information systems. Figures 9 and 10 show an example where a generic form table is configured to connect to an HR system (SQL server). When the connection settings have been specified correctly, the message "Test connection succeeded" will be displayed when you click the Test Connection button.
Figure 13 - Setting up a SQL database connection
When the connection data have been specified, a binding string is created to connect to the database.
Figure 14 - Binding string to connect to the database
Once the connection has been properly set up, a SQL query can be created. In the example in , a SQL query has been specified to retrieve names of employees, locations and phone numbers for the IT department from the EmployeePhone table:
SELECT Employee, Department, Location, Phone FROM EmployeePhone WHERE Department='IT'.
Figure 15 - Specifying the database query
The result of this database query is shown in . The query has returned the data in the columns Employee, Department, Location and Phone for all rows where Department is equal to "IT".
Figure 16 - Result of a database query is shown in an UMRA form
| 
