Options

The functions dbsetopt and dbclropt use the following constants, defined in SQLDB.H, for setting and clearing options. All options are off by default.

DBANSItoOEM | DBOEMtoANSI (Windows and Windows NT only)

Translates, through the Windows functions AnsiToOem and OemToAnsi, all characters or text coming from or going to the server. Since MS-DOS uses the OEM character set and the Windows operating system uses the ANSI character set, this translation becomes necessary when international characters (character code larger than 127) are in the database.

There are two ways to convert data with transfers to and from the server:

• Calling dbsetopt with DBANSItoOEM or DBOEMtoANSI.

Or

• For Windows, in the WIN.INI file's [SQLSERVER] section, setting AutoANSItoOEM=ON.

  For Windows NT, use the SQL Client Configuration Utility's Automatic ANSI TO OEM option. This modifies the Registry under the following subtree:

  HKEY_LOCAL_MACHINE\
  SOFTWARE\
  Microsoft\
  MSSQLServer\
  Client\
  DB-Lib

  and sets the AutoAnsiToOem entry to ON (AutoAnsiToOem:REG_SZ:ON). This is the default entry.

The AutoANSItoOEM entry controls the default conversion behavior when you connect to a server. If AutoANSItoOEM is set to ON, conversion is turned on in the following cases:

• ANSI clients to OEM servers (Windows and Windows NT)
• OEM clients to ANSI servers (Windows NT)

If AutoANSItoOEM is set to OFF, conversion is turned off for all connections.

You can override the default conversion by calling dbsetopt after calling dbopen.

Use the DBANSItoOEM option to enable conversion when connecting an ANSI client to an OEM server. Use the DBOEMtoANSI option to enable conversion when connecting to an OEM client (Windows NT Console application) to an ISO server. You cannot set both options for the same DBPROCESS.

DBARITHABORT

Cancels a query during execution when an arithmetic exception occurs. If neither DBARITHABORT nor DBARITHIGNORE is set, SQL Server substitutes null values and prints a warning after the query has been executed.

DBARITHIGNORE

Substitutes (without warning) null values for selected or updated values when an arithmetic exception occurs during query execution. If neither DBARITHABORT nor DBARITHIGNORE is set, SQL Server substitutes null values and prints a warning after the query has been executed.

DBBUFFER

Buffers the result rows to access them nonsequentially with dbgetrow. DB-Library handles this option locally. With the option set, supply a parameter that equals the number of rows you want buffered. If you choose 0, the buffer is set to a default size (currently 100).

Row buffering keeps a specified number of SQL Server result rows in the program's memory. Without row buffering, the result row generated by each new dbnextrow call overwrites the contents of the previous result row. So use row buffering for programs that need to look at result rows nonsequentially.

When turned on, DBBUFFER reduces memory and performance because each row in the buffer must be allocated and freed individually. Therefore, write the application to turn on the DBBUFFER option only if it calls dbgetrow. Note that row buffering, an independent issue, has nothing to do with network buffering. (For more information about row buffering, see dbgetrow, dbnextrow, and dbclrbuf in DB-Library Functions.)

DBCLIENTCURSORS

Forces the use of client cursors. When this option is set, every cursor opened with dbcursoropen will be a client cursor. Even when connected to SQL Server 6.0, server cursors will not be used.

This option can be enabled for a client running Windows by placing the line UseClientCursors=ON in the [SQLSERVER] section of WIN.INI. This option can be enabled for a client running Windows NT by setting the value UseClientCursors to ON (UseClientCursors : REG_SZ : ON) in the following Windows NT Registry key:

HKEY_LOCAL_MACHINE\
SOFTWARE\
Microsoft\
MSSQLServer\
Client\
DB-Lib

DBNOAUTOFREE

Clears the command buffer only by a call to dbfreebuf. When DBNOAUTOFREE is not set, after a call to dbsqlexec or dbsqlsend, the first call to either dbcmd or dbfcmd automatically clears the command buffer before the new text is entered.

DBNOCOUNT

Stops returning information about the number of rows affected by each Transact-SQL statement. The application can otherwise get this information by calling DBCOUNT.

DBNOEXEC

Processes the query through the compile step, but does not execute it. You can use this option with DBSHOWPLAN.

DBOFFSET

Indicates where SQL Server should return offsets to certain constructs in the query. This option takes a parameter that specifies the particular construct. Valid values of this parameter include:

• select
• from
• table
• order
• compute
• statement
• procedure
• execute
• param

Note The value param refers to parameters of stored procedures.

Calls to functions such as dbsetopt can specify these parameters in either uppercase or lowercase. For the internal types that correspond to the offsets, see dbgetoff in DB-Library Functions. Offsets are returned only if the batch contains no syntax errors.

DBPARSEONLY

Checks the syntax of the query and returns error messages to the host. Offsets are returned if the DBOFFSET option is set and there are no errors.

DBROWCOUNT

Specifies a maximum number of regular rows to be returned on SELECT statements. This option does not limit the number of compute rows returned. Different from most options, DBROWCOUNT is always on, never off. Setting DBROWCOUNT to 0 sets it back to the default, returning all the rows generated by a SELECT statement. Therefore, to turn DBROWCOUNT off, turn it on with a count of 0.

DBSETTIME

Overrides the global DB-Library timeout (set using dbsettime) and sets a new DB-Library timeout for a specific DBPROCESS connection. When setting this option, supply a parameter that specifies the connection specific DB-Library timeout in seconds.

DBSHOWPLAN

Generates a description of the processing plan after compilation and continues executing the query.

DBSTAT

Determines, after each query, when performance statistics (CPU time, elapsed time, I/O, and so on) will be returned to the host. DBSTAT takes one of two parameters: io, for statistics about SQL Server internal I/O; and time, for information about SQL Server's parsing, compilation, and execution times. DB-Library receives these statistics as informational messages, and applications can access them through the user-supplied message handler.

DBSTORPROCID

Sends the stored procedure ID to the host before sending rows generated by the stored procedure.

DBTEXTLIMIT

Causes DB-Library to limit the size of returned text or image values. When setting this option, supply a parameter with the same length, in bytes, as the longest text or image value that your program can handle. DB-Library will read but ignore any part of a text or image value that goes over this limit.

In the case of huge text values, it may take some time for the entire text value to be returned over the network. To keep SQL Server from sending this extra text, use the DBTEXTSIZE option instead of DBTEXTLIMIT.

DBTEXTSIZE

Causes SQL Server to limit the size of returned text or image values. When setting this option, supply a parameter with the same length, in bytes, as the longest text or image value that SQL Server should return.

Note that, in programs that allow ad hoc queries, the application user can override this option with the Transact-SQL SET TEXTSIZE command. To set a text limit that the user cannot override, use the DBTEXTLIMIT option instead.

DBBUFFER, DBNOAUTOFREE, and DBTEXTLIMIT are DB-Library options. That is, they affect DB-Library but are not sent to SQL Server. The others are SQL Server options (options that get sent to the SQL Server). You can set them with Transact-SQL.

As mentioned in the preceding descriptions, certain options take parameters. The following table lists these options and the possible values of their parameters:

The function dbsetopt requires you to specify parameters when setting any of the options in the preceding table. On the other hand, the functions dbclropt and dbisopt require you to specify a parameter only for DBOFFSET and DBSTAT, because they can have simultaneous multiple settings, which require further definition before being cleared or checked.

Note that parameters specified in calls to dbsetopt, dbclropt, and dbisopt are always passed as character strings and enclosed in quotation marks, even if they are numeric values. For more information, see dbsetopt, dbclropt, and dbisopt in DB-Library Functions.