IMF Developer's Guide

<sql-query>

IMF Developer's Guide » Layer Configuration » <sql-query>

A <sql-query> element is used to specify columns in a SQL table or view that can be used in IMF's query builder to select features from the layer. Features are selected from the layer by first selecting records from the database using a SQL statement created using the columns specified by the <sql-query-column> child elements, the table attribute, and the where clause created by the query form. Once the database records are found, an ArcIMS filter request is created using the <sql-relate-column> elements to map the database colums to the fields of the feature attribute table. An example provides the best explanation. Consider the following definition in the layer element:

<sql-query description="Database Parcel Data" connection-name="oracle_prod" table="parcel_vw">
  <sql-query-column column-alias="Owner Name" column-data-type="string" column-name="owner"/>
  <sql-query-column column-alias="Zone" column-data-type="string" column-name="zone_code"/>
  <sql-relate-column column-data-type="string" column-name="pid" field-name="SCHEMA.TABLE.PARCEL_ID"/>
</sql-query>

When the query builder opens, it will show the attributes of the feature attribute table and a drop-down selection box with two entries, "Feature Attribute Table" and "Database Parcel Data". If the second is selected by the user, the drop-down selection box marked "Having" will refresh to contain two choices, "Owner Name" and "Zone".

When generating examples, a query will be constructed something like:

SELECT DISTINCT zone_code FROM parcel_vw

... and the user might select one to add to the query resulting in a query like:

zone_code = 'RES_1'

When this query is submitted, records from the database are selected like:

SELECT DISTINCT pid FROM parcel_vw WHERE zone_code = 'RES_1'

Suppose two results were returned with values "X00001" and "X00002". Next, an ArcIMS filter is created, with the where expression of:

(SCHEMA.TABLE.PARCEL_ID='X00001') OR (SCHEMA.TABLE.PARCEL_ID='X00002')

... and the layer is queried to get select the results. Note that multiple relate columns can also be used. If there was a second relate column like:

<sql-query description="Database Parcel Data" connection-name="oracle_prod" table="parcel_vw">
  <sql-query-column column-alias="Owner Name" column-data-type="string" column-name="owner"/>
  <sql-query-column column-alias="Zone" column-data-type="string" column-name="zone_code"/>
  <sql-relate-column column-data-type="string" column-name="pid" field-name="SCHEMA.TABLE.PARCEL_ID"/>
  <sql-relate-column column-data-type="string" column-name="cid" field-name="SCHEMA.TABLE.CITY_ID"/>
</sql-query>

... the SQL query would be like:

SELECT DISTINCT pid, cid FROM parcel_vw WHERE zone_code = 'RES_1'

... and the ArcIMS filter expression may be like:

(SCHEMA.TABLE.PARCEL_ID='X00001' AND SCHEMA.TABLE.CITY_ID='21') OR (SCHEMA.TABLE.PARCEL_ID='X00002' AND SCHEMA.TABLE.CITY_ID='21')

In order to use <sql-query> elements, you must set up JDBC database connections and connection pools for your IMF installation. See the Database Configuration Reference section for more information about this.

Frequency	Parent Elements (the <sql-query> element is valid within):
PO+	<dynamic-feature-layer>
PO+	<layer>

Frequency	Child Elements (the <sql-query> element may contain):
RS+	<sql-query-column>
RS+	<sql-relate-column>

Attributes	Description
connection-name	The name of the sql-connection used to connect to the database to perform the query. Must be the name of a sql-connection defined in $IMF/WEB-INF/sql-connections.xml which is successfully running as a connection pool. Required.
description	The name of the query, or a title that will be shown in the query builder to indicate the destination of the query, as opposed to searhing in the layer's "feature attribute table". Required.
example-maximum-results	Sets the maximum number of example results to be returned by the query form. Optional, defaults to 100.
maximum-results	Sets the maximum number of database results to be returned by the query. Optional, defaults to 100. Note that more features than specified may be selected if the database relationship is many to one.
table	The name of the table or view used in the query. Required.

Frequency	Description (in this context)
PO+	The <sql-query> element may appear zero or more times in the <layer> element.
RS+	The <sql-query-column> and <sql-relate-column> elements MUST each appear one or more times in the <sql-query> element in the specified order.

Examples:

<sql-query description="Parcel Data" connection-name="oracle_prod" table="parcel_vw">
  <sql-query-column column-alias="Owner Name" column-data-type="string" column-name="owner"/>
  <sql-query-column column-alias="Zone" column-data-type="string" column-name="zone_code"/>
  <sql-relate-column column-data-type="string" column-name="pid" field-name="SCHEMA.TABLE.PARCEL_ID"/>
</sql-query>