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 re-described 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 re-described. Re-describing 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 re-described. The presence of a RESULT clause in your procedure prevents ODBC applications from re-describing the result set after a cursor is opened. This is the default setting.
Always If you specify 2, A, or ALWAYS, the cursor is re-described each time it is opened. If you use Transact-SQL procedures or procedures that return multiple result sets, you must re-describe 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 16. 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 SQLANY16 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 16 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.

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 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.

SuppressInfoForDataTypes

Set this parameter to a comma-separated list of data type names to prevent the ODBC driver from returning information about the specified data types. The specified data types are not returned by the SQLGetTypeInfo or the DatabaseMetaData.getTypeInfo functions. But, the application can use the data type in column declarations and in result set queries. For example, the following connection-string fragment prevents the ODBC driver from returning information about the NVARCHAR, LONG VARCHAR, and VARBIT data types.

SuppressInfoForDataTypes=nvarchar,long nvarchar,varbit

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.

ODBC connection parameters

See also