IMF Developer's Guide

<sql-report>

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

The <sql-report> element is used to display additional related information contained in SQL tables in IMF's default identify, select, and drill-down identify reports. An organization often has data in SQL databases that relates to features in it's GIS. In many cases, there are multiple (many to one) records in the database that relate to a feature. This element is used to display this kind of data in the default reports.

The related data is retrieved by creating a SQL query for each record retrieved from ArcIMS or a parent sql-report (they can be nested) database query. The SQL query is created by replacing parameter placeholders in the SQL statement with parameter values returned From ArcIMS or a parent sql-report.

For example, consider the following definition:

<layer name="Parcels" .....>
  <sql-report description="Inspection {0}" connection-name="oracle_prod"
              statement="select distinct * from inspections where pid = ? order by inspection_date">
    <sql-parameter value="CMDS_PID" parameter-type="fat-field-value"/>
  </sql-report>
</layer>

For each record in the result, a SQL statement is created by replacing the question mark with the value of the layer's field named CMDS_PID, then the query is executed. If the record contains a value for the CMDS_PID field of "66954782", the query would be:

select distinct * from inspections where pid = 66954782 order by inspection_date

Each result (if any) is shown within the parent record of the default IMF report in a collapsed form with the title derived from replacing the {n} placeholders with the value of the specified column. For example, if the above query resulted in two results, and the value of the first column in the results was "12/01/2001" and "12/12/2001", the results would be labelled:

+ Inspection 12/01/2001
+ Inspection 12/12/2001

Clicking the icon beside the label will display the rest of the data for that result.

In order to use <sql-report> 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-report> element is valid within):
PO+	<dynamic-feature-layer>
PO+	<layer>
PO+	<sql-report>

Frequency	Child Elements (the <sql-report> element may contain):
RS+	<sql-parameter>
OS+	<sql-report>

Attributes	Description
connection-name	The name of the sql-connection used to connect to the database to generater the report. 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 report record for display to the user when displaying the default identify or select report. Text string expected. Can contain {n} placeholders which are replaced with the values of the result column of the specified index, example "Parcel {0}" would be expanded using the value of the first field, so may appear as Parcel 123456A on the report. Required.
maximum-results	Sets the maximum number of results to be returned by the query. Optional, defaults to 100.
statement	Sets the parameterized SQL statement to used for the query. The statement should contain one question mark for each sql-parameter sub-element. The values of the parameters are replaced into the SQL query. Required.

Frequency	Description (in this context)
OS+	The <sql-report> element may appear one or more times in the <sql-report> element.
PO+	The <sql-report> element may appear zero or more times in the <layer> element.
RS+	The <sql-parameter> element MUST appear one or more times in the <sql-report> element.

Examples:

<sql-report description="Related to {0} data"
            connection-name="oracle_prod"
            statement="select distinct * from aims_relate where nid = ?">
  <sql-parameter value="CMDS_ID" parameter-type="fat-field-value"/>
  <sql-report description="Related data2"
              connection-name="postgres_test"
              statement="select distinct cid from aims_relate where cid = '?'">
     <sql-parameter value="1" parameter-type="database-column-index"/>
  </sql-report>    
</sql-report>