DB-Library for Visual Basic Options

The DB-Library for Visual Basic options described in this appendix give you additional control over SQL Server queries. Design your applications to set and clear these options using SqlSetOpt% and SqlClrOpt%. Although the Transact-SQL SET statement can set and clear some DB-Library for Visual Basic options, SqlSetOpt% and SqlClrOpt% provide a uniform interface for all the options. They also mean that your application can use SqlIsOpt% to check the status of an option.

Some of the DB-Library for Visual Basic options take parameters, and in these cases the parameters are included in the descriptions below. However, each time you use SqlSetOpt%, SqlClrOpt%, or SqlIsOpt%, you must include both an option and a parameter, whether the option takes a parameter or not.

For example, the sqlrowcount option requires a parameter to set the number of rows to be counted. To set row count to 100, use the following statement:

SqlSetOpt%(SqlConn%, SQLROWCOUNT, "100")

The sqlarithabort option does not require a parameter. To set this option, use an empty string as a parameter, as shown in the following statement:

SqlSetOpt%(SqlConn%, SQLARITHABORT, "")

For more information on SqlSetOpt%, SqlClrOpt%, and SqlIsOpt%, see Stored Procedure Functions.

sqlarithabort

Terminates a query when an overflow or divide-by-zero error occurs during query execution. If sqlarithabort is not set, SQL Server substitutes null values and returns a warning message after the query has been executed. The default setting is off.

sqlarithignore

Substitutes null values when an overflow or divide-by-zero error occurs during a query. No warning message is returned. If sqlarithignore is not set, SQL Server substitutes null values and returns a warning message after the query has been executed. The default setting is off.

sqlbuffer

Buffers result rows. sqlbuffer is required when you use SqlGetRow%. You can only set this option using DB-Library for Visual Basic; it cannot be set with the Transact-SQL SET statement. When you set sqlbuffer, supply a parameter for the number of rows you want buffered. The default setting is 0 (no row buffering).

Parameter	Description
Less than 0	Buffer set to 100 rows.
0	No result rows buffered.
1	Not allowed.
2 - 32,767	The number of rows to buffer.

For more information about row buffering, see SqlNextRow%, SqlGetRow%, and SqlClrBuf in Stored Procedure Functions.

SQLCLIENTCURSORS

Forces the use of client cursors. When this option is set, every cursor opened with SqlCursorOpen% 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.

sqlnoautofree

Causes the command buffer to clear only with a call to SqlFreeBuf. When sqlnoautofree is not set, the first call to SqlCmd% after a call to SqlExec% or SqlSend%, automatically clears the command buffer before new text is entered. The default setting is off.

sqlnocount

Stops returning information about the number of rows affected by each Transact-SQL statement. The default setting is off.

sqlnoexec

Processes a query through the compile step but does not execute it. You can use this option with sqlshowplan. Once sqlnoexec is set, no subsequent statements are executed until sqlnoexec is turned off. The default setting is off.

sqloffset

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	order	procedure
from	compute	execute
table	statement	param

Note The value param refers to parameters of stored procedures.

For the internal types that correspond to these offset values, see the SqlGetOff% function description. Offsets are returned only if the batch contains no syntax errors.

sqlparseonly

Checks the syntax of a query and returns error messages to the workstation. The default setting is off.

sqlrowcount

Specifies a maximum number of regular rows to be returned for SELECT statements. sqlrowcount does not limit the number of compute rows returned.

When you set sqlrowcount, supply a parameter for the number of rows you want returned. The default setting is 0, which returns all rows determined by SELECT statements.

Parameter	Description
0	Returns all rows generated by a SELECT statement.
1 - 2,147,483,647	Defines the maximum number of regular rows to be returned for SELECT statements.

This option is different from the other options in that you can turn it off by using SqlClrOpt% or by using SqlSetOpt% to set it to 0.

sqlshowplan

Generates a description of the processing plan after compilation and continues executing the query. The default setting is off.

sqlstat

Returns performance statistics (CPU time, elapsed time, I/O) to the workstation after each query. DB-Library for Visual Basic receives these statistics in the form of informational messages, and applications can access them through a user-defined message handler.

When you set sqlstat, supply a parameter for the type of performance statistics you want. The default setting is off.

Parameter	Description
IO	Returns statistics about SQL Server's internal I/O: the number of scans, the number of logical reads (pages accessed), and the number of physical reads (disk accesses) for each table referenced in the query. Also displays the number of pages written for each statement.
TIME	Returns information about SQL Server parsing, compilation, and execution times. Times are given in milliseconds.

sqlstorprocid

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

sqltextlimit

Causes DB-Library for Visual Basic to limit the size of returned text or image values. When setting this option, supply a parameter that is the length, in bytes, of the longest text or image value your application can handle. DB-Library for Visual Basic reads but ignores any part of a text or image value that goes over this limit. In the case of very large 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 in the first place, use the sqltextsize option. The default setting is 4096.

Parameter	Description
0 - 32,768	Size, in bytes, of the longest text or image value that your application can handle.

sqltextsize

Limits the size of text or image values SQL Server returns. When setting this option, supply a parameter that is the length, in bytes, of the longest text or image value that SQL Server returns. In applications that allow users to make ad hoc queries, the user can override this option with the Transact-SQL SET TEXTSIZE statement. To set a text limit that the user cannot override, use the sqltextlimit option instead. The default setting is 4096.

Parameter	Description
0 - 32,768	Size, in bytes, of the longest text or image value that SQL Server returns.