The complete list of DB-Library options.
#include <sybfront.h> #include <sybdb.h>
dbsetopt and dbclropt use the following constants, defined in sybdb.h, for setting and clearing options. All options are off by default. These options are available:
DBARITHABORT – If this option is set, the server will abort a query when an arithmetic exception occurs during its execution.
DBARITHIGNORE – If this option is set, the server will substitute null values for selected or updated values when an arithmetic exception occurs during query execution. The Adaptive Server Enterprise will not return a warning message. If neither DBARITHABORT nor DBARITHIGNORE is set, Adaptive Server Enterprise will substitute null values and print a warning message after the query has been executed.
DBAUTH – This option sets system administration authorization levels. Possible levels are: “sa”, “sso”, “oper”, and “dbcc_edit”. For information on these levels, see the Adaptive Server Enterprise Reference Manual.
DBBUFFER – This option allows the application to buffer result rows, so that it can access them non-sequentially using the dbgetrow function. This option is handled locally by DB-Library and is not a server option. When the option is set, you supply a parameter that 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 rows).
When an application calls dbclropt to clear the DBBUFFER option, DB-Library frees the memory associated with the row buffer.
DBCHAINXACTS – This option is used to select chained or unchained transaction behavior.
Chained behavior means that each SQL statement that modifies or retrieves data implicitly begins a multi-statement transaction. Any delete, insert, open, fetch, select, or update statement implicitly begins a transaction. An explicit commit or rollback statement is required to end the transaction. Chained mode provides compatibility with ANSI SQL.
Unchained behavior means that each SQL statement that modifies or retrieves data is implicitly a distinct transaction. Explicit begin transaction and commit or rollback statements are required to define a multi-statement transaction.
This option is off (indicating unchained behavior) by default. Applications that operate in chained mode should turn on the option right after a connection has been opened, since this option affects the behavior of all queries.
DBDATEFIRST – Sets the first weekday to a number from 1 to 7. The us_english default is 1 (Sunday).
DBDATEFORMAT – Sets the order of the date parts month/day/year for entering datetime or smalldatetime data. Valid arguments are “mdy,” “dmy,” “ymd,” “ydm,” “myd,” or “dym”. The us_english default is “mdy.”
Row buffering provides a way to keep a specified number of 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. Therefore, row buffering is useful for programs that need to look at result rows in a non-sequential manner. However, it does carry a memory and performance 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 or dbsetrow. Note that row buffering has nothing to do with network buffering and is a completely independent issue. (See the dbgetrow, dbnextrow, and dbclrbuf reference pages.)
DBFIPSFLAG – Setting this option causes the server to flag non-standard SQL commands. This option is off by default.
DBISOLATION – This option is used to specify the transaction isolation level. Possible levels are 1 and 3. The default level is 1. Setting the level to 3 causes all pages of tables specified in a select query inside a transaction to be locked for the duration of the transaction.
DBNATLANG – This is a DB-Library Internationalization option. Associate the specified DBPROCESS (or all open DBPROCESSes, if a DBPROCESS is not specified) with a national language. If the national language is not set for a particular DBPROCESS, U.S. English is used by default.
In programs that allow application users to make ad hoc queries, the user may override DBNATLANG with the Transact-SQL set language command.
All DBPROCESSes opened
using a particular LOGINREC will
also use that LOGINREC’s associated
national language. Use the DBSETLNATLANG macro
to associate a national language with a LOGINREC.
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 before the new text is entered.
DBNOCOUNT – This option causes the server to stop sending back information about the number of rows affected by each SQL statement. The application can otherwise obtain this information by calling DBCOUNT.
DBNOEXEC – If this option is set, the server will process the query through the compile step but the query will not be executed. This can be used in conjunction with DBSHOWPLAN.
DBOFFSET – This option indicates that the server should return offsets for certain constructs in the query. DBOFFSET takes a parameter that specifies the particular construct. The valid 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 lowercase or uppercase. Offsets are returned only if the batch contains no syntax errors.
DBPARSEONLY – If this option is set, the server only 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.
DBPRCOLSEP – Specify the column separator character(s). Query results rows formatted using dbprhead, dbprrow, dbsprhead, dbsprline, and dbspr1row will have columns separated by the specified string. The default separator is an ASCII 0x20 (space). The third parameter, a string, is not necessarily null-terminated. The length of the string used is given as the fourth parameter in the call to dbsetopt. To revert to using the default separator, specify a length of -1. In this case, the third parameter is ignored.
DBPRLINELEN – Specify the maximum number of characters to be placed on one line. This value is used by dbprhead, dbprrow, dbsprhead, dbsprline, and dbspr1row. The default line length is 80 characters.
DBPRLINESEP – Specify the row separator character to be used by dbprhead, dbprrow, dbsprhead, dbsprline, and dbspr1row. The default separator is a newline (ASCII 0x0D or 0x0A, depending on the host system). The third parameter, a string, is not necessarily null-terminated. The length of the string is given as the fourth parameter in the call to dbsetopt. To revert to the default terminator, specify a length of -1; in this case, the third parameter is ignored.
DBPRPAD – Specify the pad character used when printing results using dbprhead, dbprrow, dbsprhead, dbsprline, and dbspr1row. To activate padding, specify DBPADON as the fourth parameter in the dbsetopt call. The pad character may be specified as the third parameter in the dbsetopt call. If the character is not specified, the ASCII character 0x20 (space) is used. To turn off padding, call dbsetopt with DBPADOFF as the fourth parameter; the third parameter is ignored when turning padding off.
DBROWCOUNT – If this option is set to a value greater than 0, the server limits the number of regular rows returned for select statements and the number of table rows affected by update or delete statements. This option does not limit the number of compute rows returned by a select statement.
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.
DBSHOWPLAN – If this option is set, the server generates a description of the processing plan after compilation and continue executing the query.
DBSTAT – This option determines when performance statistics (CPU time, elapsed time, I/O, and so on) will be returned to the host after each query. DBSTAT takes one of two parameters: “io”, for statistics about Adaptive Server Enterprise internal I/O; and “time”, for information about Adaptive Server Enterprise’s parsing, compilation, and execution times. These statistics are received by DB-Library in the form of informational messages, and application programs can access them through the user-supplied message handler.
DBSTORPROCID – If this option is set, the server will send the stored procedure ID to the host before sending rows generated by the stored procedure.
DBTEXTLIMIT – This option causes DB-Library to limit the size of returned text or image values. When setting this option, you supply a parameter that 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. DB-Library’s default behavior is to read and return all the data sent by the server. To restore this default behavior, set DBTEXTLIMIT to a value less than 1. 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 the server from sending this extra text in the first place, use the DBTEXTSIZE option instead.
DBTEXTSIZE – This option changes the value of the server global variable @@textsize, which limits the size of text or image values that the server returns. When setting this option, you supply a parameter that is the length, in bytes, of the longest text or image value that the server should return. @@textsize has a default value of 32,768 bytes. Note that, in programs 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.
DBBUFFER, DBNOAUTOFREE, and DBTEXTLIMIT are DB-Library options. That is, they affect DB-Library but are not sent to the server. The other options are Adaptive Server Enterprise options – they are sent to the server. Adaptive Server Enterprise options can also be set through Transact-SQL commands.
As mentioned in the preceding descriptions, certain options take parameters as shown in Table 2-34.
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” |
dbsetopt requires that an option parameter be specified when setting 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 reference page.
