Follow-On Topics

Related Topics

# About the Properties Dialog

The Properties dialog box consists of four tabbed panes listed below through which all properties for the selected connection are displayed for editing.

About the General Tab

The General tab contains the following:
|  | Name — Displays the Connection name.
|  | Default — Applies the default connection settings for the specific ODBC Driver to the Connection. This will overwrite the existing settings.
| Click the image to view full size.

About the Connection Tab

The Connection tab contains the following:
|  | Connection String — Contains the full ODBC connection string.
|  | Use Pass-Through — If checked, then the connection is made directly to the ODBC driver. The default status depends on the ODBC Driver. De-selecting this option then uses Microsoft DAO to connect to the ODBC Driver. Using DAO can give you a more feature-rich implementation of SQL with a driver that normally would only support standard queries.
|  | Reconfigure — Displays the ODBC Configuration dialog box for the ODBC Driver in use. For example, if a SQL Server database is selected then the SQL Server ODBC Driver configuration dialog box is displayed. Click the image to view full size.
| Click the image to view full size.

|  | Test — Tests the connection by attempting to connect to the database.

|  | Connection Settings — This area contains items to set a time period in seconds for attempts to be made to connect to the ODBC data source. If a connection is connection cannot be made in the set time period then the connection will fail and an error will be logged.

|  | Set Lock Timeout — If checked then the option is selected and the Seconds control is activated.

|  | Seconds — The up and down arrows may be used to set the number of seconds.

About the Cursors Tab

The Cursor tab contains the following options to specify the type of database cursor to be used when executing queries.
|  | Basic forward only — Allows query rows to be read in a forward direction only. Queries cannot be re-used because the cursor cannot be re-positioned to the top of the query rows. All ODBC Drivers should support this option whereas some may not support the following more advanced cursor types.
|  | Advanced cursor attributes — This is the default selection and provides the following options:
|  | Re-usable (scrollable) — This option is the default selection and allows the cursor to move both forwards and backwards through the query rows, enabling the Database Query (ODBC) Tool to be re-used within a Task. This may be required where the Task contains more than one Format and Output Step that need to use the same Database Query (ODBC) Tool.
|  | Do not reflect changes in underlying data source — The rows returned by a query are cached independently at the time the query is executed. Therefore, when the information is actually read, any subsequent changes in the source data are not reflected.
| Click the image to view full size.

|  | Reflect row deletions and changes only — Any row deletions and row changes made between when the query is executed and when the results are read are reflected.

|  | Reflect row deletion, changes and additions — Any changes made between when the query is executed and when the results are read are reflected.

About the SQL Tab

The SQL tab is used to set the rules used in SQL statements that the chosen ODBC driver supports. This information is available from the database vendor. The tab contains the following:
|  | Current Date Function — The current date function used by the ODBC driver selected. The default depends on the driver and may be left blank if not attainable in which case it should be specified manually.
|  | Special Characters — These characters cannot be used within names to alias tables or columns within a query. If returned by the driver they will be quoted in the SQL syntax.
|  | Reserved Words — If returned by the driver or used to alias table or columns they will be quoted in the SQL syntax.
|  | Use Schemas in SQL — Whether Schemas are to be included in SQL statements or not. Yes or No (default) options are provided.
|  | Catalog Separator — The separator symbol used to separate the catalog from the table and column identifiers. The default is `.`.
| Click the image to view full size.

|  | Use Catalogs — Whether to include Catalogs in SQL statements or not. Yes or No (default) options are provided.

|  | Catalog Location — Specifies whether the Catalog identifier is at the start in the SQL Syntax, such as `catalog.table.column`, or at the end, such as `table.column.catalog`. Start (default), End and Unknown options are provided.

|  | Quoted Identifier Prefix — The character used to denote the start of a quoted identifier in SQL Syntax.

|  | Quoted Identifier Suffix — The character used to denote the end of a quoted identifier in SQL Syntax.

|  | SQL Join Syntax —

|  | SQL-89 Operator/Symbol — Join expressions are implemented in the `WHERE` clause and the Outer Join Symbol is implemented adjacent to the join Operator.

|  | SQL-89 Identifier/Symbol — Join expressions are implemented in the `WHERE` clause and the Outer Join Symbol is implemented adjacent to the join Identifiers.

|  | SQL-92 Standard — Join expressions are implemented in the `FROM` clause as per the standard SQL 92 specification.

|  | Outer Join Symbol — The symbol used to denote the direction of an outer join in conjunction with the SQL Join Syntax setting.

|  | Default Query Sample Size — The default number of records to be displayed in the Results Tab of the Database Query (ODBC) dialog box when testing the query. The default is `20`.

|  | Date Format — The date format to be used in SQL statements for literals. The default is dd-MMM-yyyy.

|  | Alias Keyword — The keyword to be used when aliasing tables and columns. The default keyword is AS.

|  | Boolean True — The number or symbol used to indicate a True condition. This is used in the Criteria area of the Query Tab of the Database Query (ODBC) dialog box to filter query results. The default is 1.

|  | Boolean False — The number or symbol used to indicate a False condition. This is used in the Criteria area of the Query Tab of the Database Query (ODBC) dialog box to filter query results. The default is 0.

|  | Quote Numeric Identifiers — A numeric identifier will be quoted using the quoted identifier prefix and suffix, for example, `table.123 -> table.'123'`. Yes or No (default) options are provided.

|  | Show Table Types — Allows the setting of the table types which you wish to be displayed so that columns within them may be selected for the query. By default the TABLE and VIEW table types are displayed. Other types may be added by manually entering them into the field, separating each type with a semi-colon. For example, a linked table from a different database (e.g Microsoft Access Database with a linked table to SQL Server) is often given a type of SYNONYM.

|  | Transaction Isolation — Used to customise the method of reading database rows that are currently being updated by application updates.

|  | Serialised — Data read by a current transaction cannot be changed by another transaction until the current transaction finishes. No new data can be inserted that would affect the current transaction. This is the safest isolation level.

|  | Repeatable Read — Data read by a current transaction cannot be changed by another transaction until the current transaction finishes. Any type of new data can be inserted during a transaction.

|  | Read Committed — A transaction cannot read data that is being modified by another transaction that has not committed.

|  | Read Uncommitted — A transaction can read any data, even if it is being modified by another transaction. This is the least safe isolation level but allows the highest concurrency.

|  | Default — Takes the default used on that particular server connection. E.g. MSSQL server default is read committed.

|  | Presentation Order — Allows the user to change the order of presentation in the ODBC schema from the default Natural order. i.e. the order the fields & tables were added to the database during design, to an alphabetic sort order. Sort order is available descending only.

|  | Currency Locale — This controls the way in which currency columns output by the ODBC driver are interpreted.

|  | Lock Timeout Command — The appropriate command for the database type may be entered. E.g. for SQL - `set lock_timeout %1`.

|  | Timeout Scale — The timeout scale in milliseconds may be entered using the up and down arrows.

Copyright © 2018 Quadrant Software B.V. All rights reserved.