In order to use the wizard to create a JDBC query, you have to

•open a project configuration

•the project configuration has to contain a valid Database Connection

Go through the chapter to find detailed information for every step of the wizard.

 

Start the Wizard:

 

 

Step 1: Table

Use the drop-down menus from the top to the bottom to select the desired table. First choose the database connection and click the button Get schemes in order to see all available schemes. Now select the scheme you want to use or keep the wild card *.* to search through all available schemes. Click the button Get matching table to see a list of available tables. Choose the desired table and click Get fields for continuing to the next step by clicking Next.

 

Note: After a table has been selected, a tool tip displays the full table name <database>.<schema>.<table>.

 

 

 

Step 2: Stored Query

The name of the table will also be used as the name of the predefined query by default. Change the name if desired.

 

 

In the query condition dialog choose a field of the table that shall be used for the query and define a condition. The availability of condition operators is based on the field type, e.g. the between operator can only be selected with numeric fields.

Define the search field type:

• Text input box: In the WebOffice 10.9 R4 a text input field will be available.

•Value list: In the WebOffice 10.9 R4 client a value list will be available.

Note: Use value lists for field that contain less than 1000 values.

•Value list with auto complete: In the WebOffice 10.9 R4 client there will be a text input field available which also provides suggestions. As soon as the user enters the first two letters of the search term, all possible search values will be displayed.

Note: Use value lists with auto complete if the field contains more than 1000 values or if the number of values is unknown.

 

Choose the logical operator if you want to use OR. By default the logical operator AND will be used.

Hierarchical queries defines dependencies between search fields. Configure a hierarchical level only if you want to create a hierarchical query.

Note: Use a hierarchical query with layer that contain various categories (top level, sub levels). For example:

• Address search with

1. level postal code

2. level street name

3. level street number

• Parcel Search with

1. level municipality cadaster

2. level parcel number

etc.

 

In case you want to configure a search field to be mandatory, check the box in the according field Mandatory search field?. Add the JDBC query to an existing search category or create a new one by clicking the button New search category. Click Next condition if you want to add another field to your query, otherwise click Next in order to proceed to next step.

 

Step 3: Result Fields

After creating the search fields and conditions, now the result fields and their representation have to be defined.

Change the result name if you do not like the default name (table name).

 

 

In case you want to display all fields besides one or two, uncheck the unwanted fields in the column Show field?.

Otherwise if you want to display one or two fields only you can first click No Fields and then select the desired fields again.

 

Optionally, define aliases for your result fields in the column Alias name in result (by default the field name of the table).

In the column Sort order/object name it is possible to define the way how attributes will be sorted in the search result. Select up to 5 fields to be sorted (Start with 1 (top priority), then 2 and so on). The value  defines that the field will not be used for sorting at all.

If the sorting mode is set to fieldtype then the sorting will be done according to the field values. Select alphanumeric to achieve an alphanumeric sorting with fields of data type string that also contain some numeric information, e.g. parcel numbers or street names with numbers.

 

Click Next.

 

Step 4: Finish

WebOffice author shows a summary of the query that will be created.

 

 

Click Finish to save the JDBC query.

 

Note: See chapter Tables for details about the configuration in WebOffice author.

Note: See chapters RELATE 1:n and JOIN 1:1 for details about linking configured tables to layers.

Note: See chapter Lookup for details about lookup tables.