...
| Table of Contents | ||||
|---|---|---|---|---|
|
...
Use of Queries
Views on Database Content
By means of queries you can configure views on database content. For example:
Overview Versus Detail View
...
In this topic, the example database "Who is Who" is used to illustrate concepts. In this database, the whPerson table is the central table containing most of the personal data. The other tables are extensions used to store additional information about the person.
...
Connecting an External Database
...
- Make sure a filled external database is available and that you know the connection related attributes.
- Navigate to Configuration -> Database Maintenance.
- Select the [ Database connections ] tab.
- Select [ New database connection ] from the pulldrop-down menu (top left). A blank dialogue pane with connection related attributes is shown.
- Enter the connection related attributes.
- Press [Click Apply]. If XperienCentral can make the connection, the product and driver information is shown as well as a table content list.
See Database Maintenance for complete information on connecting external databases.
...
...
Using Queries
In this section, we setup set up a basic view on the whPerson table by showing the following fields: lastname, prefix, firstname and email. We start with the following query:
...
- To create a query, select ‘New query’ "New query" from the drop-down menu list (see A).
- To copy a query, select that query it and click [ Copy ] (see B).
- To delete a query, select that query it and click [ Delete ] (see C).
- To add a column to the overview for any field in the SELECT clause, click [ All fields ] (see D). Then enter the column names (headings).
- To enable sorting by column, check the ‘Sort’ box] (see E).
...
- Navigate to the desired page.
- Add the Query content element to the page.
- Select the desired overview query from the drop-down list.
...
Showing the Basic Overview
The result may look looks like this. Sorting by column has been enabled for ‘Last name’ and ‘First name’"Last name" and "First name". By clicking on it, you can toggle sorting between ascending and descending order. For this type of content element with this presentation format, the title in the content element is not shown.
...
...
Overview with Parameter
Introduction
...
- Select the desired overview query (Configuration > Queries).
- Enter the query with a parameter.
- Press [Click Apply]. A "Parameters" section is automatically added at the bottom of the screen.
- Define the parameter options:
- Set the type to "Integer".
- Select "Edit in element" to be able to assign values to the parameter on the query content element.
- Select "List" to be able to assign more than one value to the parameter on the query content element.
...
Connecting an Options Table to a Parameter
The goal is to let the editor choose from a list of options for the "place" parameter at in the Query content element. The options in the list can be obtained from another table from the database.
- Click "Connect to table" (or "Edit table" if a table already exists).
- Enter the name of the table and the names of the ID and name fields. Click [ Apply].
- Press [Click Close]. You can now select a default value for the parameter. This list is also available with the query content element now.
...
Defining a Query Content Element with Parameter
...
- Navigate to the desired page.
- Add a Query content element.
- Select an overview query that includes a parameter. A parameter pulldrop-down box is added to the content element.
- Select one or more parameter values on which you want to filter your view.
...
If you selected ‘Use "Use search pop-up’ (see 3.2.3Connecting an options table to a parameter)up", the following screen panel pops up when setting the locations on the query element:
Showing the Overview with Parameters
The result only shows records in which the location is Amsterdam or Nijmegen:
...
Overview with Link to Detail View
...
In our basic overview example, we add a link from the ‘Last name’ "Last name" field to a person’s detail view. However, to be able to link to a detail view, an integer field with the unique record ID must be present in the SELECT clause. Any other type of ID than an integer type of field will not work.
...
This record ID field must be selected in the ‘Columns settings’ "Columns settings" as well. More than likely, it is not the intention to show this integer field at the website. So, we set it to ‘Do "Do not show’show".
- Select the desired overview query (Configuration > Queries Queries tab).
- Under "Column settings" add a column for the record ID field and check "NT" for "Do not show".
- In order for the field to be linked, select "New link" under "Column settings" or "Modify link" if a link already exists.
- From the "Field" drop-down list, select the ID field(in this example "personid").
- From the "To" drop-down list, select the desired detail view. Click [Save].
- Click [Close]. The overview query looks like this:
...
Defining a Query Content Element with Detail Link
...
In the example below, the upper part shows the overview. The lower part is the detail view, which appears after clicking the last name "Groot":
Using a Special Page for the Detail View
...
The detail view will hereafter be shown on this special page.
...
More Complex Example
- A parameter that filters on the locations Amsterdam and Nijmegen.
- A link to a detailed view which is shown underneath the overview.
- Three columns (Department, Job and Location) whose fields are not retrieved directly from the
whPersontable but indirectly through six other tables. This requires a more complex WHERE clause.
...
Detail Query – Get Information
SELECT firstname + ' ' + prefix + ' ' + lastname AS fullname, title, firstname, prefix, lastname, whDepartment.name AS depname, whJob.name AS jobname, whLocation.name AS locname, email, telephone, devicenumber, fax, mobile, convert(char(10), birthdate, 105) AS birthdate FROM whPerson, whPersonDepartment, whDepartment, whPersonJob, whJob, whPersonLocation, whLocation WHERE whPerson.id = @dbid@ AND whPerson.id=whPersonDepartment.person AND whPersonDepartment.department=whDepartment.id AND whPerson.id=whPersonJob.person AND whPersonJob.job=whJob.id AND whPerson.id=whPersonLocation.person AND whPersonLocation.location=whLocation.id
...
Reference Information
Database Connection Panel
Configuration > Database Maintenance > Database connections
Field | Description |
Drop-down selection box | List of database connections. |
Delete button | Deletes the current database connection. |
Name | The name of the database connection. The name is case sensitive. |
Connection URL | The URL where an application can access the database. |
Driver class | The classpath of the JDBC driver software. |
Username | The user name to use to log in to the database. |
Password | The user's password. |
Maximum number of connections | The maximum number of connections that may be established between XperienCentral and the database for that database pool. |
Database type | Select the database type: MySQL, Oracle or MSSQL. |
Product name | The full product name of the database server. |
Product version | The product version of the database server. |
Driver name | The name of the database driver. |
Driver version | The version of the database driver. |
Tables | A list of the database tables. When you click on a table name, the field definitions of the table are shown at the right. |
...
Configuration > Queries > Query
Field | Description |
Copy button | Copies the current query. The name of the copy is "Copy of <name>". |
Delete button | Deletes the current query. |
Name | The name of the query that should be displayed in the selection lists. |
Title | Title that should appear above the result when the query is submitted on a page. |
Presentation | Select "WM query" for the presentation to use for the results of the query. |
Database | The database the query is submitted to. |
Maximum number of pages | ‘Page size’ x ‘Maximum number of pages’ = maximum number of records retrieved from the database. |
Page size | The number of records allowed to be shown on a page. |
Show column heading | Indicates whether column headings should be shown above the query result. |
Show row number | Indicates whether row numbers should be shown in front of records. |
Type | Type of query: ‘Normal’, ‘Who-is-Who’, ‘Target audience’, ‘Events’, ‘Subjects’, ‘Product overview’, ‘Themes’, ‘Questions’, or ‘Documents’. Depending on the functionality present, this list can include other types as well |
Modify query manually | The query can be manually entered here. Do not use SELECT *, but name the individual column headings in the SELECT clause. Enter a variable by entering a word that begins and ends with @, such as |
[Compose advanced query] link | If the Microsoft SQL Server is used, a table with columns can be assigned using a wizard. The SELECT statement will then be generated. |
[Preview] link | If the Microsoft SQL Server is used, the result of the query can be viewed. If parameters are used in the query, a pop-up window appears where these parameters can be entered. |
Column settings - Order | A number that determines the sequence of the columns. |
Column settings - Width | Width of the column in pixels. If no width value is entered, the browser will determine the width. |
Column settings – Query field | Database field where the column gets its information. |
Column settings – Column name | Text that should be shown above the column on the page (if the ‘Show column heading’ is set to ‘Yes’). If this field is left blank, the content of the ‘Query field’ will be used. |
Column settings - NT | Indicates whether this column should be shown. This can be handy to still have the primary key columns available when several tables are joined. |
Column settings - Null | Indicates whether a 0 should be shown if a cell in this column is empty or if the value is 0. |
Column settings – 2DB | If ‘Sort’ is checked, indicate here whether the data should be sorted in the database. Otherwise, the data will be sorted in XperienCentral. |
Column settings - Delete | The column can be deleted here. |
New link/ Modify link | To create a hyperlink to a database page. Example of a pop-up:
Field: Select the property from the pulldown which contains the ID to the detailed view of the object. This property always has to be of the integer type. To: A selection can be made from the database pages that are defined in Database Maintenance. |
New column button + number | Add a number of columns. |
All fields button | Add as many columns as there are fields in the SELECT clause of the query. |
The following fields are only visible if the query has @ parameters:
Field | Meaning |
Parameters | A number that determines the sequence of the parameters and the headings. |
Parameters - Parameter | The name of the parameter, as it occurs in the query, but then without the enclosing ‘at’ symbols (@). |
Parameters - Description | In the case of a parameter, the name of the parameter appears here. In the case of a headline, the text of the headline appears here. |
Parameters - Type | The data type of the parameter (‘Date’, ‘Integer’, or ‘Text’.) |
Parameters - List | Indicates whether it is allowed to enter more values for this parameter. If this option is checked, several values can be selected on the query content element:
|
Parameters - Optional | Indicates whether this parameter is optional or required. If a parameter has no value, XperienCentral removes the row where this parameter is used from the query. Therefore, set this type of WHERE parts in separate rows. |
Parameters – Edit in element | Indicates whether the value of the parameter may be entered on the content element or whether the parameter is filled, based on the value in the query string. |
Parameters – Default value | The default value of the parameter if a value is not entered. |
Parameters –Add headline | Enter a new headline here. Headlines are texts that can be set on the query element between groups of parameters. This is done to enhance usability. |
Connect to table/ Edit connected table | This allows connecting a parameter to a column from a table to show a list of selections on the query element. Example:
|
Parameters - [Delete] button | Only present if a headline exists. Deletes the headline. |
...
Configuration > External Databases
Field | Description | |
Select | List of database page models (detail views). | |
[Delete] button | Delete this database connection. | |
Description | Name of the database page that should be displayed in the selection lists. | |
Database | List of database connections. | |
Presentation | Select ‘WM Details’. This is the default value in XperienCentral. Other values (such as ‘WM Agenda’, ‘WM Person’, and ‘WM Product’) are added to the list if certain functionalities are imported in XperienCentral. | |
Value | A unique value used by the search engine for indexing. | |
Get information query | This is the query to be run if the detail view has to be displayed. This query is connected with @dbid@ to the record that was clicked through. The variable For example:
| |
Show settings - | A number that determines the sequence of the columns to be shown. | |
Show settings – Query field | Name of the column in the SELECT clause. This can be a column name or alias (behind the keyword ‘AS’). | |
Show settings – Column name | Text that should be shown above the column on the page (only if the ‘Show column heading’ is set to ‘Yes’). If this field is left blank, the content of the ‘Query field’ will be used. | |
Show settings – Don’t show | Indicates whether this column should be shown or not. | |
Show settings – Always index | A column that is not shown is not indexed by default. To index this column, a check mark can be placed here. This setting is, however, ignored if ‘Index for search’ is set to ‘No’. | |
Show settings - Delete | The column to be shown can be deleted here. | |
Show settings - [New column] button + number | Add a number of columns. | |
Get query | Query to retrieve the title of the database object. This title is shown on the page in a larger font. The query has to contain an ID and a name in the SELECT clause. | |
Search query | The search query is used in two places:
The query has to return an ID and a name and can use the search parameter to find the proper records in the database. For example:
| |
Link query | Using this query, the links related to this table can be retrieved from a specially created link table. The Link query is only used for the product catalog and for meetings that have been created in the Meetings and Papers component. | |
Index for search | Indicates whether this type of page should be indexed and, therefore, be shown in the search element. | |