Setting options externally

The Client-Library routine ct_connect optionally reads a section from the Open Client/Server runtime configuration file to set server options for a newly-opened connection.

For a description of this feature, see “Using the runtime configuration file”.

Table 2-27 lists the symbolic constants used with ct_options:

Table 2-27: Symbolic constants for server options

Symbolic constant

Meaning

Default value

CS_OPT_ANSINULL

Determines whether evaluation of NULL-valued operands in SQL equality (=) or inequality (!=) comparisons is ANSI-compliant.

If CS_TRUE, Adaptive Server enforces the ANSI behavior that “= NULL” and “is NULL” are not equivalent. In standard Transact SQL, “= NULL” and “is NULL” are considered to be equivalent.

This option affects “<> NULL” and “is not NULL” behavior in a similar fashion.

CS_FALSE.

CS_OPT_ANSIPERM

Determines whether Adaptive Server is ANSI-compliant with respect to permissions checks on update and delete statements.

If CS_TRUE, Adaptive Server is ANSI-compliant.

CS_FALSE.

CS_OPT_ARITHABORT

Determines how Adaptive Server behaves when an arithmetic error occurs.

If CS_TRUE, both the arith_overflow and numeric_truncation options are set to on. An entire transaction or batch in which an error occurred is rolled back when a divide-by-zero error or a loss of precision occurs during either an explicit or implicit datatype conversion. If a loss of scale by an exact numeric type occurs during an implicit datatype conversion, the statement that caused the error is aborted, but the other statements in the transaction or batch continue to be processed.

If CS_FALSE, both the arith_overflow and numeric_truncation options are set to off. The statement that caused a divide-by-zero error or a loss of precision during either an explicit or implicit datatype conversion is aborted, but the other statements in the transaction or batch continue to be processed. If a loss of scale by an exact numeric type during an implicit datatype conversion occurs, the query results are truncated and other statements in the transaction or batch continue to be processed.

CS_FALSE.

CS_OPT_ARITHIGNORE

Determines whether Adaptive Server returns a message after a divide-by-zero error or a loss of precision.

If CS_TRUE, warning messages are suppressed after these errors. If CS_FALSE, warning messages are displayed after these errors.

CS_FALSE.

CS_OPT_AUTHOFF

Turns the specified authorization level off for the current server session. When a user logs in, all authorizations granted to that user are automatically turned off.

Not applicable.

CS_OPT_AUTHON

Turns the specified authorization level on for the current server session. When a user logs in, all authorizations granted to that user are automatically turned on.

Not applicable.

CS_OPT_CHAINXACTS

If CS_TRUE, Adaptive Server uses chained transaction behavior. Chained transaction behavior means that each server command is considered to be a distinct transaction. Adaptive Server implicitly executes a begin transaction before any of the following statements: delete, fetch, insert, open, select, and update.

If CS_FALSE, an application must specify an explicit commit transaction statement to end a transaction and begin a new one.

CS_FALSE.

CS_OPT_ CURCLOSEONXACT

If CS_TRUE, all cursors opened within a transaction are closed when the transaction completes.

CS_FALSE.

CS_OPT_DATEFIRST

Sets the first day of the week.

For us_english, the default is CS_OPT_SUNDAY.

CS_OPT_ DATEFORMAT

Sets the order of the date parts month/day/year for entering date, datetime or smalldatetime data.

For us_english, the default is CS_OPT_FMTMDY.

CS_OPT_FIPSFLAG

Determines whether Adaptive Server displays a warning message when SQL extensions are used.

If CS_TRUE, Adaptive Server flags any non-standard SQL commands that are sent.

If CS_FALSE, Adaptive Server does not flag non-ANSI SQL.

CS_FALSE.

CS_OPT_FORCEPLAN

If CS_TRUE, Adaptive Server joins tables in the order in which the tables are listed in the from clause of the query.

CS_FALSE.

CS_OPT_ FORMATONLY

If CS_TRUE, Adaptive Server sends back a description of the data, rather than the data itself, in response to a select query.

If CS_FALSE, Adaptive Server sends back data in response to a select query.

CS_FALSE.

CS_OPT_IDENTITYOFF

Disables inserts into a table’s IDENTITY column.

See the set command (identity_insert option) in the Adaptive Server documentation.

Not applicable.

CS_OPT_IDENTITYON

Enables inserts into a table’s IDENTITY column.

See the set command (identity_insert option) in the Adaptive Server documentation.

Not applicable.

CS_OPT_IDENTITYUPD_OFF

Disables the identity update option.

Null

CS_OPT_IDENTITYUPD_ON

Enables the identity update option. This option allows you to update the “high”, out-of-range identity column values by writing a single SQL update statement that specifies the required range of rows and replaces them with the correct values.

Null

CS_OPT_ISOLATION

Specifies a transaction isolation level. Possible levels are CS_OPT_LEVEL0, CS_OPT_LEVEL1, and CS_OPT_LEVEL3.

These correspond to the three levels for Adaptive Server’s set transaction isolation level command. CS_OPT_LEVEL0 requires SQL Server version 11.0, 11.1, 11.1.x, or Adaptive Server.

CS_OPT_LEVEL1.

CS_OPT_NOCOUNT

Turns off the return of the number of rows affected by each SQL statement. An application obtains this information by calling ct_res_info.

CS_FALSE.

CS_OPT_NOEXEC

If CS_TRUE, Adaptive Server compiles each query but does not execute it.

Use this option in conjunction with CS_OPT_SHOWPLAN.

CS_FALSE.

CS_OPT_PARSEONLY

If CS_TRUE, Adaptive Server checks the syntax of each query and returns any error messages as necessary, but does not execute the query.

CS_FALSE.

CS_OPT_QUOTED_ IDENT

If CS_TRUE, Adaptive Server treats all strings enclosed in double quotes (“) as identifiers.

CS_FALSE.

CS_OPT_RESTREES

If CS_TRUE, Adaptive Server checks the syntax of each query and returns parse resolution trees (in the form of image columns in a regular row result set) and error messages as necessary, but does not execute the query.

CS_FALSE.

CS_OPT_ROWCOUNT

Sets a limit for the number of rows that are affected by a query: limits the number of regular rows returned by a select statement and the number of rows affected by an update or delete statement.

If set to 0, the number of rows returned or affected by a command is not limited.

If set to a value greater than 0, Adaptive Server stops processing a command when the specified number of rows has been affected.

This option does not limit the number of compute rows returned.

0

CS_OPT_SHOWPLAN

Determines whether a description of each query’s processing plan is returned between its compilation and execution.

If CS_TRUE, Adaptive Server compiles a query, generates a description of its processing plan, and then executes the query.

Client-Library receives the description as a sequence of informational server messages. Application programs access them through the user-supplied server message handler.

CS_FALSE.

CS_OPT_SORTMERGE

Determines whether the use of sort-merge joins during a session are enabled or disabled.

See the Adaptive Server Enterprise Performance and Tuning Guide.

CS_FALSE.

CS_OPT_STATS_IO

Determines whether Adaptive Server internal I/O statistics (the number of scans, logical reads, physical reads, and pages written) are returned for each query.

If CS_TRUE, statistics are returned.

Client-Library receives these statistics as informational server messages. Application programs access them through the user-supplied server message handler.

CS_FALSE.

CS_OPT_STATS_TIME

Determines whether Adaptive Server parsing, compilation, and execution time statistics are returned for each query.

If CS_TRUE, statistics are returned.

Client-Library receives these statistics as informational server messages. Application programs access them through the user-supplied server message handler.

CS_FALSE.

CS_OPT_STR_ RTRUNC

Determines whether Adaptive Server is ANSI-compliant with respect to right truncation of character data.

When this option is set to CS_TRUE, Adaptive Server raises an error when an insert or an update operation truncates a char or varchar column value and the truncated characters are not all blank. This behavior is ANSI- compliant.

When this option is set to CS_FALSE, the Adaptive Server silently truncates char or varchar values that are too long for the column definition.

CS_FALSE.

CS_OPT_TEXTSIZE

Specifies the value of the Adaptive Server global variable @@textsize, which limits the size of text or image values that Adaptive Server returns.

When setting this option, supply a parameter that specifies length, in bytes, of the longest text or image value that Adaptive Server should return.

The Client-Library property CS_TEXTLIMIT has a similar effect. The CS_TEXTLIMIT property sets the size of the largest text/image value that Client-Library returns to the application. CS_TEXTLIMIT does not affect server processing: Client-Library truncates text/image values as they are read from the network. On the other hand, CS_OPT_TEXTSIZE causes the server to truncate values before sending them.

In programs that allow application users to run ad hoc queries, the user may override the CS_OPT_TEXTSIZE option with the Transact-SQL set textsize command. To set a text limit that the user cannot override, use the CS_TEXTLIMIT property instead.

32,768 bytes.

CS_OPT_ TRUNCIGNORE

If CS_TRUE, Adaptive Server ignores truncation errors. This is standard ANSI behavior.

If CS_FALSE, Adaptive Server raises an error when conversion results in truncation.

CS_FALSE.