Bind a compute column to a program variable, with precision and scale support for numeric and decimal datatypes.
RETCODE dbaltbind_ps(dbproc, computeid, column,
vartype, varlen, varaddr,
typeinfo)
DBPROCESS *dbproc;
int computeid;
int column;
int vartype;
DBINT varlen;
BYTE *varaddr;
DBTYPEINFO *typeinfo;
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_ps 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_ps’s
parameters are identical to dbaltbind’s,
except that dbaltbind_ps has the additional parameter typeinfo,
which contains information about precision and scale for DBNUMERIC
or DBDECIMAL variables.
For a list of the type definitions used by DB-Library, see Types.
Table 2-2 lists the legal vartype values recognized by dbaltbind_ps, 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_ps 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 values of vartype that represent a fixed-length type, 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.
A pointer to a DBTYPEINFO structure containing information about the precision and scale of decimal or numeric data. An application sets a DBTYPEINFO structure with values for precision and scale before calling dbaltbind_ps to bind columns to DBDECIMAL or DBNUMERIC variables.
If typeinfo is NULL:
If the result column is of type numeric or decimal, dbaltbind_ps picks up precision and scale values from the result column.
If the result column is not numeric or decimal, dbaltbind_ps uses a default precision of 18 and a default scale of 0.
If vartype is not DECIMALBIND or NUMERICBIND, typeinfo is ignored.
A DBTYPEINFO structure is defined as follows:
typedef struct typeinfo {
DBINT precision;
DBINT scale;
} DBTYPEINFO;
Legal values for precision are from 1 to 77. Legal values for scale are from 0 to 77. scale must be less than or equal to precision.
SUCCEED or FAIL.
dbaltbind_ps returns FAIL if the column number is not valid, if the data conversion specified by vartype is not legal, or if varaddr is NULL.
dbaltbind_ps is the equivalent of dbaltbind, except that dbaltbind_ps provides precision and scale support for numeric and decimal datatypes, which dbaltbind does not. Calling dbaltbind is equivalent to calling dbaltbind_ps with typeinfo as NULL.
dbaltbind_ps 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_ps 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_ps binds data from compute rows. Use dbbind_ps for binding data from regular rows.
You must make the calls to dbaltbind_ps after a call to dbresults and before the first call to dbnextrow.
dbaltbind_ps incurs some 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.
Since the server can return null values, DB-Library provides a set of default values, one for each datatype, that it will automatically substitute when binding null values. 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.)
dbaltbind, dbanullbind, dbbind, dbbind_ps, dbconvert, dbconvert_ps, dbdata, dbnullbind, dbsetnull, dbsetversion, dbwillconvert, Types
