Database Query Table

Database query table is a named table that contains database specific query rather than a reference to an existing database table or database view. The query is written in the SQL syntax of the database and it should be used where a query cannot be specified as MISQL query (for example you need to use sub queries, outer joins etc) or where it can perform better than a named view table. Consider the following while using a database query table:

  • If the structure of tables or views referred in the query is updated, set the attribute volatile as true. It impacts the performance because table metadata is recreated for each request.

  • A named tabled based on a database query will not support insert, update, or delete operations.
  • Supported databases include Oracle, SQL Server, and PostGIS.
  • If a query returns a geometry, then geometry column should be defined in the DBDataSourceMetadata element.
  • If a query returns a primary key, then it should be defined in the KeyAttributes element.

The database query table is similar to a view table. However, there are conditions of preferring one over the other. See view table for more details. The following tables lists major differences between the two:

Table 1. Comparing Database Query Table with View Table
View Table Database Query Table

Uses MISQL query

Uses database specific query, which is analogous to a view created in the database
Join query supports named tables from different data sources Join query must reference tables and views within the same database
Delegates the operators depending on the data provider Delegates only Attributes Comparison and EnvelopeIntersects operators
Does not support nested SQL queries Supports nested and database specific SQL queries

Delegation Rules

The Attribute Comparison and EnvelopesIntersect Operator will be delegated to the database.

Example

This is an example of a database query table.

<?xml version="1.0" encoding="UTF-8"?>
<NamedDataSourceDefinition xmlns="http://www.mapinfo.com/mxp" xmlns:gml="http://www.opengis.net/gml" version="MXP_NamedResource_1_5">
   <ConnectionSet>
      <NamedConnectionRef resourceID="/oracle_db_connection">
         <ConnectionName>connection1</ConnectionName>
      </NamedConnectionRef>
   </ConnectionSet>
   <DataSourceDefinitionSet>
      <DBDataSourceDefinition id="id1" readOnly="false" volatile="false">
         <DataSourceName>WORLD</DataSourceName>
         <ConnectionMember>
            <ConnectionName>connection1</ConnectionName>
         </ConnectionMember>
         <DBQuery>
            <Query>Select MI_Prinx, GEOM, Country, Capital from World where Country in('Canada','India')</Query>
         </DBQuery>
         <DBDataSourceMetadata>
            <FeatureGeometryAttribute srsName="epsg:4326">GEOM</FeatureGeometryAttribute>
            <KeyAttributes>
               <AttributeName>MI_Prinx</AttributeName>      
            </KeyAttributes>
         </DBDataSourceMetadata>
      </DBDataSourceDefinition>
   </DataSourceDefinitionSet>
   <DataSourceRef ref="id1" />
</NamedDataSourceDefinition>