XML settings for data source templates

The following table defines all the available XML settings for data source templates; only the first three are required. All other settings use their default values if not specified.

Setting Description Default value
type-name A unique name for the configuration.
driver The JDBC driver Java class used for creating connections.
connection-url-pattern A pattern for the connection URL. The URL syntax is driver specific.
ping-command A dummy command to test connections. SELECT 1
connection-properties JDBC connection properties.
metadata-provider Java class that provides database metadata. BasicJDBCMetadataProvider
sql-filter Java class that generates SQL. BasicSQLFilter
sql-runtime Java class that handles SQL execution. BasicSQLRuntime
fetch-size A fetch size specifies the amount of data fetched with each database round trip for a query. The specified value is shown as the default value in Information Designer. May be changed at instance level. 10000
batch-size A batch size specifies the amount of data in each batch update. The specified value is shown as the default value in Information Designer. May be changed at instance level. 100
max-column-name-length The maximum length of a database column name. This limit is used when creating temporary tables. 30
table-types Specify which table types to retrieve. TABLE, VIEW
supports-catalogs Tells if the driver supports catalogs. true
supports-schemas Tells if the driver supports schemas. true
supports-procedures Tells if the driver supports stored procedures. false
supports-distinct Tells if the driver supports distinct option in SQL queries. true
supports-order-by Tells if the driver supports order-by option in SQL queries. true
column-name-pattern Determines how a column name is written in the SQL query. "$$name$$"
table-name-pattern Determines how a table name is written in the SQL query. "$$name$$"
schema-name-pattern Determines how a schema name is written in the SQL query "$$name$$"
catalog-name-pattern Determines how a catalog name is written in the SQL query. "$$name$$"
procedure-name-pattern Determines how a procedure name is written in the SQL query. "$$name$$"
column-alias-pattern Determines how a column alias is written in the SQL query. "$$name$$"
string-literal-quote The character used as quote for string literals. SQL-92 standard
max-in-clause-size The maximum size of an SQL IN-clause. Larger lists are split into several clauses that are OR:ed together. 1000
condition-list-threshold A temporary table is used when executing an SQL query, where total size of a condition list is larger than this threshold value. A Data Base Administrator may prefer a lower value than the default. Depends on the maximum SQL query size. 10000
expand-in-clause If true, an SQL IN-clause will be expanded into OR conditions. false
table-expression-pattern Determines how a table expression is written in the SQL query; catalog and schema may be optional (surrounded by brackets). [$$catalog$$.][$$schema$$.]$$table$$
procedure-expression-pattern Determines how a procedure expression is written in the SQL query. [$$catalog$$.][$$schema$$.]$$procedure$$
procedure-table-jdbc-type Integer representing the jdbc type identifying a table returned form a procedure as defined by java.sql.Types. 0
procedure-table-type-name Display name for tables from procedure. This is currently not visible to the user in any UI. null
date-format-expression An expression that converts a date field to a string value on the format: YYYY-MM-DD, for example, 2002-11-19. Used in WHERE and HAVING clauses. The tag $$value$$ is a placeholder for the date field. $$value$$
date-literal-format-expression An expression that converts a date literal on the format YYYY-MM-DD to a date field value. Used in WHERE and HAVING clauses. The tag $$value$$ is a placeholder for the date literal. '$$value$$'
time-format-expression An expression that converts a time field to a string value on the format: HH:MM:SS, for example 14:59:00. Used in WHERE and HAVING clauses. The tag $$value$$ is a placeholder for the time field. $$value$$
time-literal-format-expression An expression that converts a time literal on the format HH:MM:SS to a time field value. Used in WHERE and HAVING clauses. The tag $$value$$ is a placeholder for the time literal. '$$value$$'
date-time-format-expression An expression that converts a datetime field to string value on the format: YYYY-MM-DD HH:MM:SS, for example 2002-11-19 14:59:00. Used in WHERE and HAVING clauses. The tag $$value$$ is a placeholder for the date-time field. $$value$$
date-time-literal- format-expression An expression that converts a date-time literal on the format YYYY- MM-DD HH:MM:SS to a date-time field value. Used in WHERE and HAVING clauses. The tag $$value$$ is a placeholder for the date-time literal. '$$value$$'
java-to-sql-type-conversions:
  • String
  • Integer
  • Long
  • Float
  • Double
  • Date
  • Time
  • DateTime
Type conversions needed when a join data source creates a temporary table for result from a subquery. For String conversion %s will be replaced by the size of the string. A match-length attribute may be specified (see MySQL). Different String types may be needed dependant of the length of the string. Note that there must be a VARCHAR conversion for when the length of the string is unknown (255 in the example here). When several VARCHAR mappings are specified, the mapping that first matches the match-length is used. VARCHAR($$value$$) VARCHAR(255) INTEGER BIGINT REAL DOUBLE PRECISION DATE TIME TIMESTAMP
temp-table-name-pattern Determines how to format a temporary table name in an SQL command. $$name$$
create-temp-table-command SQL commands for creating a temporary table. This is used to store filter values (when more than condition-list-threshold) and to store result from subqueries. The syntax may vary between databases. $$name$$ is a placeholder for the table name. $$column_list$$ is a placeholder for a column list on the format (name type, name type, ...). CREATE TEMPORARY TABLE $$name$$ $$column_list$$
drop-temp-table-command SQL commands for deleting a temporary table. The syntax may vary between databases. $$name$$ is a placeholder for the table name DROP TABLE $$name$$
data-source-authentication Default value data source authentication. (boolean). This value can be set (overridden) in the Information Interaction Designer. false
lob-threshold Threshold when LOB values used as parameters in a WHERE clause, must be written in temporary tables. The default means no limit. -1
use-ansi-join The default generated SQL creates joins with where statements.

If this setting is set to true, an attempt is made to rewrite it to standard ANSI format.

If this setting is set to false, no attempt to rewrite inner joins will be made and outer joins depend on the value set for use-ansii-style-outer-join.

false
use-ansii-style-outer-join The default generated SQL uses the Oracle way with "(+)" to indicate joins. If this setting is set to true an attempt is made to rewrite it to standard ANSI format, making it possible to run on non Oracle databases.
Note: If use-ansi-join is set to true, then this setting has no effect.
false
credentials-timeout Defines the time in seconds user credentials are cached on the server for a particular data source. Value must be between 900 (15 minutes) and 604800 (1 week). Applicable only if data-source-authentication is set to true. 86400 (24 hours)