1 Version 4.0 -- 5/1/89 options
______________________________________________________________________
NAME: options
FUNCTION:
The complete list of DB-Library options.
SYNTAX:
#include <sybfront.h>
#include <sybdb.h>
COMMENTS:
o dbsetopt() and dbclropt() use the following constants, defined
in sybdb.h, for setting and clearing options. All options are
options Version 4.0 -- 5/1/89 2
______________________________________________________________________
off by default. These options are available:
DBPARSEONLY - If this option is set, the SQL Server will only check
the syntax of the query and return error messages to the host.
Offsets will be returned if the DBOFFSET option is set and there
are no errors.
DBSHOWPLAN - If this option is set, the SQL Server will generate a
description of the processing plan after compilation and continue
executing the query.
DBNOEXEC - If this option is set, the SQL Server will process the
query through the compile step but the query will not be executed.
This can be used in conjunction with DBSHOWPLAN.
DBARITHABORT - If this option is set, the SQL Server will abort a
query when an arithmetic exception occurs during its execution.
DBARITHIGNORE - If this option is set, the SQL Server will substitute
null values for selected or updated values when an arithmetic
exception occurs during query execution. The SQL Server will not
return a warning message. If neither DBARITHABORT nor
3 Version 4.0 -- 5/1/89 options
______________________________________________________________________
DBARITHIGNORE is set, SQL Server will substitute null values and
print a warning message after the query has been executed.
DBNOCOUNT - This option causes SQL Server to stop sending back infor-
mation about the number of rows affected by each SQL statement.
The application can otherwise obtain this information by calling
DBCOUNT().
DBTEXTSIZE - This option causes SQL Server to limit the size of
returned text or image values. When setting this option, you sup-
ply a parameter which is the length, in bytes, of the longest text
or image value that SQL Server should return. Note that, in pro-
grams that allow application users to make ad hoc queries, the
user may 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.
DBOFFSET - This option indicates where SQL Server should return
offsets to certain constructs in the query. DBOFFSET takes a
parameter that specifies the particular construct. The valid
options Version 4.0 -- 5/1/89 4
______________________________________________________________________
parameters for this option are "select", "from", "table", "order",
"compute", "statement", "procedure", "execute", or "param". (Note
that "param" refers to parameters of stored procedures.) Calls to
routines such as dbsetopt() can specify these option parameters in
either lower or upper case. For the internal types that
correspond to the offsets, see dbgetoff(). Offsets are returned
only if the batch contains no syntax errors.
DBSTAT - This option determines when performance statistics (CPU
time, elapsed time, I/O, etc.) will be returned to the host after
each query. DBSTAT takes one of two parameters: "io", for statis-
tics about SQL Server internal I/O; and "time", for information
about SQL Server's parsing, compilation, and execution times.
These statistics are received by DB-Library in the form of infor-
mational messages, and application programs can access them
through the user-supplied message handler.
DBSTORPROCID - If this option is set, SQL Server will send the stored
procedure id to the host before sending rows generated by the
5 Version 4.0 -- 5/1/89 options
______________________________________________________________________
stored procedure.
DBROWCOUNT - If this option is set, the SQL Server will return only a
maximum specified number of regular rows for SELECT statements.
This option does not limit the number of compute rows returned.
DBROWCOUNT works somewhat differently from most options. It is
always set on, never off. Setting DBROWCOUNT to 0 sets it back to
the default-that is, to return all the rows generated by a SELECT
statement. Therefore, the way to turn DBROWCOUNT off is to set it
on with a count of 0.
DBBUFFER - This option allows the application to buffer result rows,
so that it can access them non-sequentially via the dbgetrow()
function. This option is handled locally by DB-Library and is not
a SQL Server option. When the option is set, you supply a parame-
ter which is the number of rows you want buffered. If you use 0
as the number of rows to buffer, the buffer will be set to a
default size (currently 1000).
options Version 4.0 -- 5/1/89 6
______________________________________________________________________
Row buffering provides a way to keep a specified number of
SQL Server result rows in program memory. Without row buffering,
the result row generated by each new dbnextrow() call overwrites
the contents of the previous result row. Row buffering is there-
fore useful for programs that need to look at result rows in a
non-sequential manner. It does, however, carry a memory and per-
formance penalty because each row in the buffer must be allocated
and freed individually. Therefore, use it only if you need to.
Specifically, the application should only turn the DBBUFFER option
on if it calls dbgetrow(). Note that row buffering has nothing to
do with network buffering and is a completely independent issue.
(See dbgetrow(), dbnextrow(), and dbclrbuf() for more information
about row buffering.)
DBNOAUTOFREE - This option causes the command buffer to be cleared
only by an explicit 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
7 Version 4.0 -- 5/1/89 options
______________________________________________________________________
before the new text is entered.
DBTEXTLIMIT - This option causes DB-Library to limit the size of
returned text or image values. When setting this option, you sup-
ply a parameter which is the length, in bytes, of 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 in the first place, use
the DBTEXTSIZE option instead.
o DBBUFFER, DBNOAUTOFREE, and DBTEXTLIMIT are DB-Library options.
That is, they affect DB-Library but are not sent to the SQL Server.
The other options are SQL Server options-they get sent to the
SQL Server. SQL Server options are also settable through
Transact-SQL.
o As mentioned in the preceding descriptions, certain options take
options Version 4.0 -- 5/1/89 8
______________________________________________________________________
parameters:
Option Possible parameter values
DBTEXTSIZE "0" to "2,147,483,647"
DBOFFSET "select", "from", "table", "order", "compute", "statement",
"procedure", "execute", or "param"
DBSTAT "io" or "time"
DBROWCOUNT "0" to "2,147,483,647"
DBBUFFER "0" to either "32,767" or "2,147,483,647", depending on whether
your int datatype is 2 or 4 bytes long
DBTEXTLIMIT "0" to "2,147,483,647"
9 Version 4.0 -- 5/1/89 options
______________________________________________________________________
dbsetopt() requires that an option parameter be specified when set-
ting any option on the preceding list. dbclropt() and dbisopt()
require that the parameter be specified only for DBOFFSET and
DBSTAT. This is because DBOFFSET and DBSTAT are the only options
that can have multiple settings at a time, and thus they 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 must be
quoted, even if they are numeric values. See the dbsetopt() manual
page for more information.
SEE ALSO:
dbclropt, dbisopt, dbsetopt