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:
|
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
|
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) |