Lookup Table builder inputs for a Database Query data type

Table 1. General inputs

Input name	Description
Name	Name for this builder call. The WebSphere Portlet Factory Designer displays this name in the builder call list. Note: The name must begin with a letter or an underscore and contain only letters, digits, and underscores. This restriction allows the builder to use the Name input to build additional WebApp artifacts that implement the core of this builder runtime functionality.
Data Type	Select the type of data element you want to query. You can select: Database Query Choose this to perform an SQL call on a database table that contains name/value pairs.
Fetch DataSource Names	Click this button to fetch a list of JDBC DataSources defined on the application server supporting IBM® WebSphere Portlet Factory Designer. The list is inserted as choices for the SQL DataSource input. If no DataSource names are listed in the SQL DataSource input, then WebSphere Portlet Factory Designer may not be properly configured to query an application server, the server might not be running, or there are no DataSources defined by the application server. Note: Enter any valid datasource name in the SQL Datasource input. The Fetch Datasource Names button, however, lists only those datasources whose full names begin with jdbc/, jdbc/v4/, or jdbc/v5/. If you know that there is a valid datasource with a name that does not match any of these patterns, enter the name. If the name is correct, it should work. Note: Depending on the performance of the application server, it might take several seconds to load the list of datasource names. Note: This button is not displayed if the builder is configured to use an override SQL DataSource.
SQL DataSource	Open this list and select the JDBC DataSource to be used by the SQL Call when it executes the SQL statement you define later in this builder. Note: This input is displayed as a read-only prompt indicating the name of the override builder if the SQL Call builder has been configured to use a custom SQL DataSource builder.

Table 2. Database Explorer inputs

Input name	Description
Catalog Pattern	Enter a name of the catalog to search for tables. Leave blank to match all catalogs. The following wildcard values are supported: _ (underscore) Matches a single character. % (percent) Matches zero or more characters. Note: Depending on the database and JDBC driver associated with the DataSource, this entry might be case sensitive.
Schema Pattern	Enter an SQL schema pattern to use while searching for tables. Typically this is the ID of the database user that owns the table you are trying to find. Leave this input blank to match all schemas. The following wildcard values are supported: _ (underscore) Matches a single character. % (percent) Matches zero or more characters. Note: Depending on the database and JDBC driver associated with the DataSource, this entry might be case sensitive.
Table Pattern	Enter a table name to use while searching for tables. Leave blank to match all tables. The following wildcard values are supported: _ (underscore) Matches a single character. % (percent) Matches zero or more characters. Note: Depending on the database and JDBC driver associated with the DataSource, this entry might be case sensitive.
Fetch Table Names	Click to fetch all the table names that match the catalog, schema, and table patterns that you specified. The names are inserted as choices for the Table Name input. If no table names are listed in the Table Name input, WebSphere Portlet Factory Designer may not be properly configured to query an application server, the server may not be running, or there are no table names that match the patterns you defined. Tip: Review the Explorer Status panel for any warning or error messages.
Table Name	Select the name of the table from the drop down list. The selected table is used by the builder when you click the Generate Sample SQL button.
Value Column	Select the database table column that provides the values of the Lookup Table.
Label Column	Select the column name in the database table that provides the labels that match the values specified in the Value Column input.
Generate Sample SQL	Click to have the builder generate a sample SQL statement in the Sample SQL input.
Sample SQL	The sample SQL statement generated by the builder for the table selected by the Table Name input. Note: The SQL displayed in this panel is editable. For example, you can edit a SELECT statement to return only the columns you need instead of all the columns defined for a table, view, or alias.
Apply Sample SQL	Click to copy the sample SQL statement and parameters to the builder actual inputs for these values.
SQL Statement	Enter an SQL statement. Note: You cannot use positional parameters in this statement. In most cases, a select distinct is the appropriate query, because it returns name and value pairs that conform to a 1 to 1 mapping. For example: select distinct lookup_table.ID, lookup_table.NAME from PRODUCT lookup_table Alternately, you can use this builder Database Explorer to locate and generate some sample SQL. You can then edit that SQL as needed, and apply the edited SQL to this input.
Variable Type	This input allows you to use data in a resource bundle. You can choose: Value Tag and Label Tag To use standard model data in this form. Resource Bundle Style To use data in the form that the Localized Resource builder produces.
Value Tag	Enter the name (or, in the case of an XML variable, the node name) of the column to use to represent the value element. Note: If your query returns more than two columns, use this input to explicitly name the value column.
Label Tag	Enter the name (or, in the case of an XML variable, the node name) of the column to use to represent the label element. Note: If your query returns more than two columns, use this input to explicitly name the label column.
Concurrency	This input is usually not required and should be left blank. In some cases, a database can be configured to require concurrency. When that is the case, select a concurrency mode for the Result Set generated by this statement. Modes are: Read Only Underlying database result set is read-only. Updatable Underlying database result set supports data changes. Note: Some databases do not support updatable result sets. Check the documentation for your target database for detailed information about the types of result sets supported.
Scroll Type	This input is usually not required and should be left blank. In some cases, a database can be configured to require a scroll type. When that is the case, select the type of scrolling used by the result set. Types include: Forward Traditional result set type. Forward cursor movement only. Insensitive Result set not sensitive to changes in underlying database. Sensitive Result set is sensitive to changes in underlying database. Note: Some databases may not support all of these scroll types. Check the documentation for your target database for detailed information about the types of result sets supported.
Refresh Interval (secs)	Enter the time in seconds that Lookup Table builder data is to be retained in the cache. Entering a value here enables caching and improves performance. This is generally a good idea if a large enough value is used to prevent unnecessary cache refreshes. A value of 300 seconds is usually appropriate in most applications.

Table 3. Additional Data Definition inputs

Input name	Description
Get Data From	Identifies the source of data. You can choose: Builder Input To get data from Additional Pairs table. XML Data To get data from an XML type variable. This choice is useful if there is a default set of labels and values that should appear in a number of select lists.
Add a blank selection	Select this option to provide a blank selection for optional fields in a form where the user does not need to provide a specific value provided by the data set.
Additional XML Data	This input is available when the Get Data From input is XML Data. Specify an indirect reference to the location where the XML data is located.
Additional Pairs	Specify any additional label and value pairs to be included in the lookup table results. (You typically do this to allow development to proceed while the database is under construction.) Label A label to be included in the ResultSet. Value A value to be included in the ResultSet Collision Resolution This choice allows you to handle the situation that can occur if a value and label pair that you specify is identical to one already contained in the data. When this occurs, select Use Original Value to use the pair contained in the original data. Select Use this Value to use the pair that you specified in the table.
Table Position	Specifies the location of additional pairs that are in the XML output of this builder. The choices are: In Front The default. Additional data pairs placed in front of existing pairs in the XML stream. In Back Additional data pairs placed in back of existing pairs in the XML stream.
Parent Tag	Name of an XML element used to contain additional pairs in the table. Default element name is <UserAddition>.

Table 4. Filtering input

Input name	Description
Label/Value Filter Mode	Specify how XML nodes are to be filtered. You typically do this if the rows in your XML are not identical in terms of label and value content. You can choose: Use elements with both label and value elements To use elements with sub-elements that match the chosen Label and Value inputs. None: use all elements Use all the data.