Query Properties

600

The number of seconds before the connection is deleted from the cache and automatically closed. A default timeout is 10 minutes.

The SQL connection ID used to look up the metadata that defines the JDBC settings used when connecting to the relational database.

The name of the DataTable in the DataSet to contain the query results.

0

Rows are retrieved by a database server in blocks that are specified by the fetch size. The FetchSize property allows for the number of rows fetched to be controlled for a given query.

Fetch size support and the default fetch size varies by database type. The default fetch size is typically small. Although a larger fetch size can result in faster retrievals, memory usage may be impacted. So, for any given query, it is best to test using different fetch sizes to determine the most appropriate setting.

-1

Controls the maximum number of rows retrieved from the database by the server.

If the RowLimit value is less than 0, the one million rows (the default value) are retrieved from the database. If the value is 0, all rows are retrieved from the database. If the value is greater than 0, the specified number or rows is retrieved from the database.

This setting can be used to optimize memory usage and improve performance for very large data sets.

QueryRowSetCachingProvider.Default

Controls the caching provider utilized internally by the server when assembling row sets. The Default option utilizes the standard provider, while the GZipCompressed option reduces the memory used by the server to perform a SQL operation and returna data set.

QuerySqlStandardizationPolicy.FullStandardization

Controls whether Dodeca standardizes the given SQL statements with respect to whitespace. The default option, FullStandardization, helps prevent syntax errors related to spacing, which are sometimes generated by JDBC drivers.

FullStandardization preserves all literals and identifiers, removes line comments, then does the following in this order:

FullStandardizationClassic is the classic Dodeca implementation of FullStandardization; it does the following:

0

The number of seconds before the execution of the query times out. A Timeout value of 0 or -1 indicates that the query execution does not time out.

false

Controls whether the query’s insert, delete, and update statements are executed within a transaction.

The SQL statement used to delete rows from a relational database table. The following guidelines apply:

 — The statement should include a parameter for each value that refers to a column in the data table. The parameter name must be the same as the corresponding column name. The column name must be the same as the column name returned by the SelectSQL.

For example, the following statement deletes a row from MyTable:

DELETE FROM MyTable WHERE ColumnID = @ColumnID

--The SQL statement must use the native syntax supported by the database.

 — The statement can be tokenized.

The SQL statement used to insert a new row into a relational database table. The following guidelines apply:

 — The statement should include a parameter for each value that refers to a column in the data table. The parameter name must be the same as the corresponding column name. The column names must be the same as the column names returned by the SelectSQL.

For example, the following statement inserts a row into MyTable:

INSERT INTO MyTable (ColumnABC, ColumnXYZ, Column123) VALUES (@ColumnABC, @ColumnXYZ, @Column123)

--The SQL statement must use the native syntax supported by the database.

 — The statement can be tokenized.

The SQL statement is used to retrieve rows from the relational database. The following guidelines apply:

 — The SELECT statement must use the native syntax supported by the database.

 — If a selected column is used also as a parameter in the InsertSQL, UpdateSQL, or DeleteSQL, an alternate name or alias cannot be specified for the column. The column name returned by the statement must be the same as the column name in the source database table.

 — The statement can be tokenized. A tokenized SELECT statement is used, for instance, when the statement contains a WHERE clause that defines the search criteria for rows by including one or more selector tokens, such as "WHERE Sales.SalesOrderDetail.ProductID IN ([T.Product])" where [T.Product] is a comma-delimited list of product IDs.

 — When the first row of a data table sheet range contains a formula in a given column, the corresponding value returned by the SelectSQL statement should be null, and converted or cast to the appropriate data type by using the CONVERT or CAST function. For example, CAST(null as VARCHAR) as FORMULA.

The SQL statement used to change existing data in the relational database. The following guidelines apply:

 — The statement should include a parameter for each value that refers to a column in the data table. The parameter name must be the same as the corresponding column name. The column name must be the same as the column name returned by the SelectSQL.

For example, the following statement updates the EmailAddress and Phone column values for a row in the Person.Contact table:

UPDATE Person.Contact SET EmailAddress = @EmailAddress, Phone = @Phone WHERE ContactID = @ContactID

--The SQL statement must use the native syntax supported by the database.

 — The statement can be tokenized.