dbaltbind
Binds a compute column (column of results from a COMPUTE clause) to a program variable.
Syntax
RETCODE dbaltbind (
PDBPROCESS dbproc,
INT computeid,
INT column,
INT vartype,
DBINT varlen,
LPCBYTE varaddr );
Arguments
dbproc
Is the DBPROCESS structure that is the handle for a particular workstation or Microsoft® SQL Server™ 2000 process. It contains all the information that DB-Library uses to manage communications and data between the workstation and SQL Server.
computeid
Is the ID of the COMPUTE clause to which the dbaltbind function refers. Because a SELECT statement can have more than one COMPUTE clause, the computeid is necessary to distinguish between them. The computeid is returned by dbnextrow or dbgetrow.
column
Is the column number of the compute data that is to be copied to a program variable. The first column is number 1.
vartype
Is a description of the binding's data type. It corresponds to the data type of the program variable that receives the copy of the data from the DBPROCESS.
The dbaltbind function supports a wide range of type conversions, so the binding's data type can be different from the type returned by the SQL query. For instance, a SQLMONEY result can be bound to a DBFLT8 program variable, using FLT8BIND, and the appropriate data conversion happens automatically.
For more information about a list of the data conversions provided by DB-Library, see dbwillconvert. For more information about a list of the type definitions used by DB-Library, see DB-Library for C Data types. The following table lists the legal vartypes recognized by dbaltbind and the program variable and SQL Server data types to which each refers.
| vartype | Program variable data type | SQL Server data type |
|---|---|---|
| CHARBIND | DBCHAR | SQLCHAR |
| STRINGBIND | DBCHAR | SQLCHAR |
| NTBSTRINGBIND | DBCHAR | SQLCHAR |
| VARYCHARBIND | DBVARYCHAR | SQLCHAR |
| BINARYBIND | DBBINARY | SQLBINARY |
| VARYBINBIND | DBVARYBIN | SQLBINARY |
| TINYBIND | DBTINYINT | SQLINT1 |
| SMALLBIND | DBSMALLINT | SQLINT2 |
| INTBIND | DBINT | SQLINT4 |
| FLT8BIND | DBFLT8 | SQLFLT8 |
| BITBIND | DBBIT | SQLBIT |
| DATETIMEBIND | DBDATETIME | SQLDATETIME |
| MONEYBIND | DBMONEY | SQLMONEY |
| SMALLMONEYBIND | DBMONEY4 | SQLMONEY4 |
| SMALLDATETIBIND | DBDATETIM4 | SQLDATETIM4 |
| FLT4BIND | DBFLT4 | SQLFLT4 |
| DECIMALBIND | DBDECIMAL | SQLDECIMAL |
| NUMERICBIND | DBNUMERIC | SQLNUMERIC |
| SRCDECIMALBIND | DBDECIMAL | SQLDECIMAL |
| SRCNUMERICBIND | DBNUMERIC | SQLNUMERIC |
Because SQLTEXT and SQLIMAGE data are never returned through a compute row (a row of results generated by a COMPUTE clause), these data types are not included in the preceding table. The SQL Server data type is listed for your reference. The vartype you specify does not necessarily have to correspond to a particular SQL Server data type because, as mentioned earlier, dbaltbind converts SQL Server data into the specified vartype.
The following table lists the four representations for character data. They differ according to whether the data is padded with blanks or is null-terminated:
| vartype | Program data type | Padding | Terminator |
|---|---|---|---|
| CHARBIND | DBCHAR | blanks | none |
| STRINGBIND | DBCHAR | blanks | \0 |
| NTBSTRINGBIND | DBCHAR | none | \0 |
| VARYCHARBIND | DBVARYCHAR | none | none |
Note that "\0" is the null terminator character. Similarly, binary data can be stored in two different ways:
| vartype | Program data type | Padding |
|---|---|---|
| BINARYBIND | DBBINARY | nulls |
| VARYBINBIND | DBVARBINARY | none |
When a column of integer data is summed or averaged, SQL Server always returns a four-byte integer, regardless of the size of the column. Therefore, be sure that the variable that is to contain the result from such a compute is declared as DBINT and that the vartype of the binding is INTBIND.
When the source column specified by the column parameter has a type of SQLDECIMAL or SQLNUMERIC, you can keep the same precision and scale in your bound C variable by using SRCDECIMALBIND or SRCNUMERICBIND.
varlen
Is the length of the program variable in bytes. For fixed-length vartypes, 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 required for special terminating bytes, such as a null terminator. If varlen is 0, the total number of bytes available is copied into the program variable. (For char and binary SQL 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, set varlen to 0. The varlen is ignored for VARYCHARBIND and VARYBINBIND data.
varaddr
Is the address of the program variable to which the data is copied. Calling dbaltbind with a NULL var address parameter breaks previously set bindings.
When binding using DECIMALBIND or NUMERICBIND, the varaddr parameter must be a pointer to a DBNUMERIC or DBDECIMAL C variable, respectively, with the precision and scale fields of the structure already set to the desired values. You can use DEFAULTPRECISION to specify a default precision and DEFAULTSCALE to specify a default scale.
Returns
SUCCEED or FAIL. The dbaltbind function returns FAIL if vartype isn't compatible with the SQL Server data type being returned, or if varaddr is NULL.
Remarks
This function directs DB-Library to copy compute column data returned by SQL Server into a program variable. When each new row containing computed data is read by 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 to be copied. It is not necessary to bind every compute column to a program variable.
SQL 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. The dbaltbind function binds data from compute rows. Use dbbind for binding data from regular rows.
The calls to dbaltbind must be made after a call to dbresults and before the first call to dbnextrow.
Using dbaltbind causes some overhead because it always copies the row data into the designated program variable. To avoid this copying, the returned data can be accessed more directly with dbadlen and dbadata.
Because null values can be returned from SQL Server, there is a set of default values, one for each data type that is substituted when binding null values. You can explicitly set your own values to be substituted for the default null values with the dbsetnull function. (For more information about a list of the default substitution values, see dbsetnull.)
Examples
This example shows the typical sequence of calls:
DBCHAR name[20];
DBINT namecount;
// Read the query into the command buffer.
dbcmd(dbproc, "SELECT name FROM employee COMPUTE COUNT(name)");
// Send the query to SQL Server.
dbsqlexec(dbproc);
// Get ready to process the results of the query.
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
}