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.
For more information, see JDBC connection properties. |
|
| metadata-provider | Java class that provides database metadata.
Must implement the
com.spotfire.ws.im.ds.sql.JDBCMetadataProvider
interface.
|
BasicJDBCMetadataProvider |
| sql-filter | Java class that generates SQL. Must implement
the
com.spotfire.ws.im.ds.sql.SQLFilter interface.
|
BasicSQLFilter |
| sql-runtime | Java class that handles SQL execution. Must
implement the
com.spotfire.ws.im.ds.sql.SQLRuntime interface.
|
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 |
| use-get-schemas | Use the JDBC getSchemas function to fetch schemas instead of the getTables function. | false |
| 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$$ |
| 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).
Only applicable if the data source uses username and password for authentication, and user authentication is enabled. |
86400 (24 hours) |
| credentials-provider-class |
Java class that provides a credentials provider SPI
implementation. Must implement the
|
|
| credentials-provider-init-params | The credentials provider initialization parameters. See Identity provider (OAuth2/OpenID Connect) authentication for Information Services for a description of all attributes. |