ODBC connection parameters

The Data Source utility (dbdsn) supports the following ODBC connection parameters. Boolean (true or false) arguments are either YES or 1 if true, or NO or 0 if false.

Name Description
Delphi Delphi cannot handle multiple bookmark values for a row. When you set this value to NO, one bookmark value is assigned to each row, instead of the two that are otherwise assigned. Setting this option to YES can improve scrollable cursor performance.
DescribeCursor

This parameter lets you specify how often you want a cursor to be redescribed when a procedure is called. The default setting is If Required.

  • Never   Specify 0, N, or NO if you know that your cursors do not have to be redescribed. Redescribing cursors is expensive and can decrease performance.

  • If Required   Specify 1, Y, or YES if you want the ODBC driver to determine whether a cursor must be redescribed. The presence of a RESULT clause in your procedure prevents ODBC applications from redescribing the result set after a cursor is opened. This is the default setting.

  • Always   If you specify 2, A, or ALWAYS, the cursor is redescribed each time it is opened. If you use Transact-SQL procedures or procedures that return multiple result sets, you must redescribe the cursor each time it is opened.

Description This parameter allows you to provide a description of the ODBC data source.
Driver

This parameter allows you to specify an ODBC driver for the connection, as follows: Driver=driver-name. By default, the driver that is used is SQL Anywhere 12. The driver-name must be SQL Anywhere X, where X is the major version number of the software. If the driver-name does not begin with SQL Anywhere, it cannot be read by the Data Source utility (dbdsn).

On Unix, this parameter specifies the fully-qualified path to the shared object. If you do not specify the Driver connection parameter on Unix, the Data Source utility automatically adds a Driver entry with the full path of the SQL Anywhere ODBC driver based on the setting of the SQLANY12 environment variable.

Escape

This parameter specifies the escape character used in the LIKE clause of SQL statements that are generated by the ODBC driver when returning a list of tables or columns.

By default the ODBC driver uses the tilde character (~), but some applications assume that the escape character is the backslash character (\).

The following connection string fragment specifies that the backslash is the escape character:

"DSN=SQL Anywhere 12 Demo;UID=DBA;PWD=sql;ESCAPE=\\;Host=myhost"
GetTypeInfoChar When this option is set to YES, CHAR columns are returned as SQL_CHAR instead of SQL_VARCHAR. By default, CHAR columns are returned as SQL_VARCHAR.
InitString InitString allows you to specify a command that is run immediately after the connection is established. For example, you may want to set a database option or call a stored procedure.
IsolationLevel

You can specify one of the following values to set the initial isolation level for this data source:

  • 0   This is also called the read uncommitted isolation level. This is the default isolation level. It provides the maximum level of concurrency, but dirty reads, non-repeatable reads, and phantom rows may be observed in result sets.

  • 1   This is also called the read committed level. This provides less concurrency than level 0, but eliminates some of the inconsistencies in result sets at level 0. Non-repeatable rows and phantom rows may occur, but dirty reads are prevented.

  • 2   This is also called the repeatable read level. Phantom rows may occur. Dirty reads and non-repeatable rows are prevented.

  • 3   This is also called the serializable level. This provides the least concurrency, and is the strictest isolation level. Dirty reads, non-repeatable reads, and phantom rows are prevented.

  • Snapshot   You must enable snapshot isolation for the database to use this isolation level. The snapshot isolation levels prevent all interference between reads and writes. Writes can still interfere with each other. For contention, a few inconsistencies are possible and performance is the same as isolation level 0.

  • Statement-snapshot   You must enable snapshot isolation for the database to use this isolation level. The snapshot isolation levels prevent all interference between reads and writes. Writes can still interfere with each other. For contention, a few inconsistencies are possible and performance is the same as isolation level 0.

  • Readonly-statement-snapshot   You must enable snapshot isolation for the database to use this isolation level. The snapshot isolation levels prevent all interference between reads and writes. Writes can still interfere with each other. For contention, a few inconsistencies are possible and performance is the same as isolation level 0.

See Guidelines for choosing isolation levels.

KeysInSQLStatistics Set this parameter to YES if you want the SQLStatistics function to return foreign keys. The ODBC specification states that SQLStatistics should not return primary and foreign keys; however, some Microsoft applications (such as Microsoft Visual Basic and Microsoft Access) assume that primary and foreign keys are returned by SQLStatistics.
LazyAutocommit Set this parameter to YES to delay the commit operation until a statement closes.
PrefetchOnOpen When PrefetchOnOpen is set to YES, a prefetch request is sent with a cursor open request. The prefetch eliminates a network request to fetch rows each time a cursor is opened. Columns must already be bound for the prefetch to occur on the open. This connection parameter can help reduce the number of client/server requests to help improve performance over a LAN or WAN.
PreventNotCapable The SQL Anywhere ODBC driver returns an error because it does not support qualifiers. Some ODBC applications do not handle this error properly. Set this parameter to YES to prevent this error code from being returned, allowing these applications to work.
SuppressWarnings Set this parameter to YES if you want to suppress warning messages that are returned from the database server on a fetch. Versions 8.0.0 and later of the database server return a wider range of fetch warnings than earlier versions of the software. For applications that are deployed with an earlier version of the software, you can select this option to ensure that fetch warnings are handled properly.
TranslationDLL This option is provided for backward compatibility. The use of translators is not recommended.
TranslationName This option is provided for backward compatibility. The use of translators is not recommended.
TranslationOption This option is provided for backward compatibility. The use of translators is not recommended.
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1