Bind a compute column to a program variable.
RETCODE dbaltbind(dbproc, computeid, column, vartype,
varlen, varaddr)
DBPROCESS *dbproc;
int computeid;
int column;
int vartype;
DBINT varlen;
BYTE * varaddr;
A pointer to the DBPROCESS structure that provides the connection for a particular front end/server process. It contains all the information that DB-Library uses to manage communications and data between the front end and server.
The ID that identifies the particular compute row of interest. A select statement may have multiple compute clauses, each of which returns a separate compute row. The computeid corresponding to the first compute clause in a select is 1.
The column number of the row data that is to be copied to a program variable. The first column is column number 1. Note that the order in which compute columns are returned is determined by the order of the corresponding columns in the select list, not by the order in which the compute columns were originally specified. For example, in the following query the result of “sum(price)” is referenced by giving column a value of 1, not 2:
select price, advance from titles
compute sum(advance), sum(price)
The relative order of compute columns in the select list, rather than their absolute position, determines the value of column. For instance, given the following variation of the earlier select:
select title_id, price, advance from titles
compute sum(advance), sum(price)
the column for “sum(price)” still has a value of 1 and not 2, because the “title_id” column in the select list is not a compute column and therefore is ignored when determining the compute column’s number.
This describes the datatype of the binding. It must correspond to the datatype of the program variable that will receive the copy of the data from the DBPROCESS. The table below shows the correspondence between vartype values and program variable types.
dbaltbind supports a wide range of type conversions, so the vartype can be different from the type returned by the SQL query. For instance, a SYBMONEY result may be bound to a DBFLT8 program variable through FLT8BIND, and the appropriate data conversion will happen automatically. For a list of the data conversions provided by DB-Library, see the reference page for dbwillconvert.
dbaltbind does
not offer explicit precision and scale support for numeric and decimal datatypes.
When handling numeric or decimal data, dbaltbind uses a
default precision and scale of 18 and 0, respectively, unless the
bind is to a numeric or decimal column,
in which case dbaltbind uses
the precision and scale of the source data. Use dbaltbind_ps to explicitly
specify precision and scale values—calling dbaltbind is equivalent
to calling dbaltbind_ps with
a NULL typeinfo value.
For a list of the type definitions used by DB-Library, see Types.
Table 2-1 lists the legal vartype values recognized by dbaltbind, along with the server and program variable types that each one refers to:
Vartype |
Program variable type |
Server datatype |
|---|---|---|
CHARBIND |
DBCHAR |
SYBCHAR |
STRINGBIND |
DBCHAR |
SYBCHAR |
NTBSTRINGBIND |
DBCHAR |
SYBCHAR |
VARYCHARBIND |
DBVARYCHAR |
SYBCHAR |
BINARYBIND |
DBBINARY |
SYBBINARY |
VARYBINBIND |
DBVARYBIN |
SYBBINARY |
TINYBIND |
DBTINYINT |
SYBINT1 |
SMALLBIND |
DBSMALLINT |
SYBINT2 |
INTBIND |
DBINT |
SYBINT4 |
FLT8BIND |
DBFLT8 |
SYBFLT8 |
REALBIND |
DBREAL |
SYBREAL |
NUMERICBIND |
DBNUMERIC |
SYBNUMERIC |
DECIMALBIND |
DBDECIMAL |
SYBDECIMAL |
BITBIND |
DBBIT |
SYBBIT |
DATETIMEBIND |
DBDATETIME |
SYBDATETIME |
SMALLDATETIMEBIND |
DBDATETIME4 |
SYBDATETIME4 |
MONEYBIND |
DBMONEY |
SYBMONEY |
SMALLMONEYBIND |
DBMONEY4 |
SYBMONEY4 |
BOUNDARYBIND |
DBCHAR |
SYBBOUNDARY |
SENSITIVITYBIND |
DBCHAR |
SYBSENSITIVITY |
WARNING! It is an error to use any of the following values for vartype if the library version has not been set (with dbsetversion) to DBVERSION_100 or higher: BOUNDARYBIND, DECIMALBIND, NUMERICBIND, or SENSITIVITYBIND.
Since SYBTEXT and SYBIMAGE data are never returned through a compute row, those datatypes are not listed above.
Note that the server type in the table above is listed merely for your information. The vartype you specify does not necessarily have to correspond to a particular server type, because, as mentioned earlier, dbaltbind will convert server data into the specified vartype.
The available representations for character data are shown below. They differ according to whether the data is blank-padded or null-terminated:
Vartype |
Program type |
Padding |
Terminator |
|---|---|---|---|
CHARBIND |
DBCHAR |
blanks |
none |
STRINGBIND |
DBCHAR |
blanks |
\0 |
NTBSTRINGBIND |
DBCHAR |
none |
\0 |
VARYCHARBIND |
DBVARYCHAR |
none |
none |
BOUNDARYBIND |
DBCHAR |
none |
\0 |
SENSITIVITYBIND |
DBCHAR |
none |
\0 |
Note that the “\0” in the table above is the null terminator character.
If overflow occurs when converting integer or float data to a character binding type, the first character of the resulting value will contain an asterisk (“*”) to indicate the error.
Binary data may be stored in two different ways:
Vartype |
Program Type |
Padding |
|---|---|---|
BINARYBIND |
DBBINARY |
nulls |
VARYBINBIND |
DBVARBINARY |
none |
When a column of integer data is summed or averaged, the server always returns a 4-byte integer, regardless of the size of the column. Therefore, be sure that the variable which is to contain the result from such a compute is declared as DBINT and that the vartype of the binding is INTBIND.
The length of the program variable in bytes.
For vartype values that represent fixed-length types, such as MONEYBIND or FLT8BIND, this length is ignored.
For character and binary types, varlen must describe the total length of the available destination buffer space, including any space that may be required for special terminating bytes, such as a null terminator. If varlen is 0, the total number of bytes available will be copied into the program variable. (For char and binary server data, the total number of bytes available is equal to the defined length of the database column, including any blank padding. For varchar and varbinary data, the total number of bytes available is equal to the actual data contained in the column.) Therefore, if you are sure that your program variable is large enough to handle the results, you can just set varlen to 0.
The address of the program variable to which the data will be copied.
SUCCEED or FAIL.
dbaltbind returns FAIL if the column number is not valid, if the data conversion specified by vartype is not legal, or if varaddr is NULL.
This routine directs DB-Library to copy compute column data returned by the server into a program variable. (A compute column results from the compute clause of a Transact-SQL select statement.) When each new row containing compute data is read using dbnextrow or dbgetrow, the data from the designated column in that compute row is copied into the program variable with the address varaddr. There must be a separate dbaltbind call for each compute column that is to be copied. It is not necessary to bind every compute column to a program variable.
The server can return two types of rows: regular rows containing data from columns designated by a select statement’s select list, and compute rows resulting from the compute clause. dbaltbind binds data from compute rows. Use dbbind for binding data from regular rows.
You must make the calls to dbaltbind after a call to dbresults and before the first call to dbnextrow.
The typical sequence of calls is:
DBCHAR name[20];
DBINT namecount;
/* read the query into the command buffer */
dbcmd(dbproc, "select name from emp compute
count(name)");
/* send the query to Adaptive Server Enterprise */
dbsqlexec(dbproc);
/* get ready to process the query results */
dbresults(dbproc);
/* bind the regular row data (name) */
dbbind(dbproc, 1, STRINGBIND, (DBINT) 0, name);
/* bind the compute column data (count of name) */
dbaltbind(dbproc, 1, 1, INTBIND, (DBINT) 0,
(BYTE *) &namecount);
/* now process each row */
while (dbnextrow(dbproc) != NO_MORE_ROWS)
{
C-code to print or process row data
}
dbaltbind incurs a little overhead because it causes the data to be copied into a program variable. To avoid this copying, you can use the dbadata routine to directly access the returned data.
You can only bind a result column to a single program variable. If you bind a result column to multiple variables, only the last binding takes effect.
The server can return null column values, and DB-Library provides the following aids for handling null values:
A pre-defined set of default values, one for each datatype, that DB-Library automatically substitutes when a bound column contains a null value. The dbsetnull function allows you to explicitly set your own null substitution values. See the reference page for the dbsetnull function for a list of the default substitution values.
The ability to bind an indicator variable to a column with dbnullbind (or dbanullbind for compute rows). As rows are fetched, the value of the indicator variable will be set to indicate whether or not the column value was null. See the reference page for the dbnullbind function for indicator values and meanings.
dbadata, dbaltbind_ps, dbanullbind, dbbind, dbbind_ps, dbconvert, dbconvert_ps, dbnullbind, dbsetnull, dbsetversion, dbwillconvert, Types
