Application Variables

The application programming languages such as C, C++, Basic, and Java use variables to hold data. Variables are areas of storage that can hold a data value. Each application variable has a data type and size. Numeric variables also have a precision, the number of digits the variable can hold, and scale, the number of digits that are to the right of the decimal point.

In order for an application to work with the data returned from Transact-SQL statements, it must have a mechanism to move the Transact-SQL data into application variables. The database APIs support the concept of binding a result set column, parameter, return code, or parameter marker in a Transact-SQL statement to an application variable.

To retrieve the data in a result set, an application uses a process similar to this:

  1. Executes a Transact-SQL statement.

  2. Calls a database API function to find out how many columns are in the result set.

  3. For each result set column the application:

    • Calls a database API function that returns the attributes, data type size, and so on, of the column.

    • Allocates an application variable with attributes compatible with the attributes of the column.

    • Calls a database API function to bind, or map, the result set column with the application variable.

  4. Uses database API functions to fetch the result set rows one row at a time. On each fetch, the values of each result set column are placed in the application variable bound to the column.

Applications can vary this process. For example, if the application is executing a hard-coded Transact-SQL statement against a known table, the attributes of the result set columns are known in advance and the application does not have to call the database API to get these attributes.

If the application binds a result set column to an application variable whose data type differs from that of the associated database object, then the OLE DB provider or ODBC driver must convert the data. For example, if an application binds a money column to a character array, the OLE DB provider or ODBC driver has to convert the money data to a character string. The documentation for the Microsoft OLE DB Provider for SQL Server and the SQL Server ODBC driver define the data type conversions they support.

For more information about retrieving parameters and return codes in applications, see Parameter Markers (Database Engine).