1 Version 4.0 -- 5/1/89 dbaltbind
______________________________________________________________________
NAME: dbaltbind
FUNCTION:
Bind a compute column to a program variable.
SYNTAX:
RETCODE dbaltbind(dbproc, computeid, column, vartype,
varlen, varaddr)
DBPROCESS *dbproc;
int computeid;
int column;
dbaltbind Version 4.0 -- 5/1/89 2
______________________________________________________________________
int vartype;
DBINT varlen;
BYTE *varaddr;
COMMENTS:
o This routine directs DB-Library to copy compute column data
returned by SQL 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
via 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.
o The SQL Server can return two types of rows: regular rows con-
taining data from columns designated by a SELECT statement's
3 Version 4.0 -- 5/1/89 dbaltbind
______________________________________________________________________
select-list, and compute rows resulting from the COMPUTE
clause. dbaltbind() binds data from compute rows. Use
dbbind() for binding data from regular rows.
o You must make the calls to dbaltbind() after a call to
dbresults() and before the first call to dbnextrow().
o 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 SQL Server */
dbsqlexec(dbproc);
dbaltbind Version 4.0 -- 5/1/89 4
______________________________________________________________________
/* 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
}
o dbaltbind() incurs a little overhead, because it causes the
data to be copied into a program variable. To avoid this
5 Version 4.0 -- 5/1/89 dbaltbind
______________________________________________________________________
copying, you can use the dbadata() routine to directly access
the returned data.
o 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.
o Since SQL 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 dbset-
null() function allows you to explicitly set your own null sub-
stitution values. (See the manual page for the dbsetnull()
function for a list of the default substitution values.)
PARAMETERS:
dbproc - A pointer to the DBPROCESS structure that provides the
connection for a particular front end/SQL Server process. It
contains all the information that DB-Library uses to manage
dbaltbind Version 4.0 -- 5/1/89 6
______________________________________________________________________
communications and data between the front end and SQL Server.
computeid - 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.
column - 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)
7 Version 4.0 -- 5/1/89 dbaltbind
______________________________________________________________________
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
previous 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.
vartype - 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 vartypes and program
dbaltbind Version 4.0 -- 5/1/89 8
______________________________________________________________________
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 via FLT8BIND, and the appropriate
data conversion will happen automatically. For a list of the
data conversions provided by DB-Library, see the manual page
for dbwillconvert().
For a list of the typedefs used by DB-Library, see the manual
page for types.
Here is a list of the legal vartypes recognized by dbalt-
bind(), along with the SQL Server and program variable types
that each one refers to:
9 Version 4.0 -- 5/1/89 dbaltbind
______________________________________________________________________
Vartype Program variable type SQL Server type
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
BITBIND DBBIT SYBBIT
DATETIMEBIND DBDATETIME SYBDATETIME
MONEYBIND DBMONEY SYBMONEY
Since SYBTEXT and SYBIMAGE data are never returned through a
compute row, those datatypes are not listed above.
dbaltbind Version 4.0 -- 5/1/89 10
______________________________________________________________________
Note that the SQL 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 SQL Server
type, because, as mentioned earlier, dbaltbind() will convert
SQL Server data into the specified vartype.
The above table shows that four representations for character
data are available. 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
Note that the "\0" in the table above is the null terminator
11 Version 4.0 -- 5/1/89 dbaltbind
______________________________________________________________________
character.
If overflow occurs when converting integer or float data to a
character/text binding type, the first character of the
resulting value will contain an asterisk ("*") to indicate
the error.
Binary and image 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,
SQL 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
dbaltbind Version 4.0 -- 5/1/89 12
______________________________________________________________________
declared as DBINT and that the vartype of the binding is INT-
BIND.
varlen - 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 that may be required for special ter-
minating 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 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
13 Version 4.0 -- 5/1/89 dbaltbind
______________________________________________________________________
column.) Therefore, if you are sure that your program vari-
able is large enough to handle the results, you can just set
varlen to 0.
varaddr - The address of the program variable to which the data
will be copied.
RETURNS:
SUCCEED or FAIL. dbaltbind() returns FAIL if the column number
isn't valid, if the data conversion specified by vartype isn't
legal, or if varaddr is NULL.
SEE ALSO:
dbadata, dbbind, dbconvert, dbsetnull, dbwillconvert, types