DB-Library 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 (Microsoft® Windows® and Microsoft Windows NT® only)

Translates, through the Windows functions AnsiToOem and OemToAnsi, all characters or text coming from or going to the server. Because Microsoft MS-DOS® uses the OEM character set and Windows 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:

Call dbsetopt with DBANSItoOEM or DBOEMtoANSI
For Windows, in the Win.ini file’s [SQLSERVER] section, set AutoANSItoOEM=ON
For Windows NT, use the SQL Server Network Utility automatic ANSI to OEM option. This modifies the Registry under the following subtree:
HKEY_LOCAL_MACHINE\
SOFTWARE\
Microsoft\
MSSQLServer\
Client\
DB-Lib
This also 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 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, Microsoft 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 a negative value, the buffer is set to a default size (currently 100). A value of 1 is invalid.

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.

DBCLIENTCURSORS

Forces the use of client cursors. When this option is set, every cursor opened with dbcursoropen is a client cursor.

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

DBQUOTEDIDENT

Specifies that DB-Library will automatically place double quotation marks (“) around certain object names that are called quoted identifiers. The DBQUOTEDIDENT option is disabled by default.

Call dbsetopt with the DBQUOTEDIDENT option to enable the use of quoted identifiers.
Call dbclropt with the DBQUOTEDIDENT option to disable the use of quoted identifiers.
Call dbisopt to check the status of the DBQUOTEDIDENT option.

Note When you use DBQUOTEDIDENT, you must set param to NULL.

The following table lists the functions and the parameters that are affected when DBQUOTEDIDENT is enabled.

Function	Parameter(s)
bcp_init	tblname
dbrpcinit	rpcname
dbupdatetext	dest_object src_object
dbuse	dbname
dbwritetext	objname

When you call dbrpcinit, you must explicitly place quotation marks around remote procedure names that require quotation marks in the rpcname parameter. This example demonstrates the use of quotation marks in rpcname.

"My server".."My RPC"

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 time-out (set using dbsettime) and sets a new DB-Library time-out for a specific DBPROCESS connection. When setting this option, supply a parameter that specifies the connection specific DB-Library time-out 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.

Option	Possible parameter values
DBBUFFER	0 to 32767
DBOFFSET	select, from, table, order, compute, statement, procedure, execute, or param
DBROWCOUNT	0 to 2,147,483,647
DBSTAT	io or time
DBTEXTLIMIT	0 to 65,534 for 16-bit DB-Library 0 to 2,147,483,647 for 32-bit DB-Library
DBTEXTSIZE	0 to 2,147,483,647

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.