The Environment Handle

An environment is a global context in which to access data. Associated with an environment is any information that is global in nature, such as:

The environment’s state

The current environment-level diagnostics

The handles of connections currently allocated on the environment

The current settings of each environment attribute

Within a piece of code that implements ODBC (the Driver Manager or a driver), an environment handle identifies a structure to contain this information.

Environment handles are not frequently used in ODBC applications, but every application program that intends to use ODBC must begin by allocating an environment handle. Only one environment handle can be allocated per application and it must be allocated before any other handles are allocated. All other handles (i.e. all connection, statement, and descriptor handles for an application) are managed within the context of the environment handle.

An environment handle is also used by certain ODBC functions to:

Pass any errors that occur at the environment level (such as an attempt to free the environment handle while connections are still active, or an attempt to use an invalid environment handle during connection handle allocation) to the appropriate ODBC error handling function.

To serve as the context handle for the f90SQL subroutine that enumerates the data sources currently installed (f90SQLDataSources ).

To serve as the context handle for the f90SQL function that enumerates the drivers currently installed (f90SQLDrivers ).

To manage transactions when f90SQLEndTran is used to commit or roll back all open transactions on all connections (rather than have an application commit or roll back outstanding transactions on each connection, the environment handle can be used to force the Driver Manager to commit or rollback outstanding transactions on all connections).

An application allocates handles by making calls to the subroutine f90SQLAllocHandle. Handles are deallocated by calling the subroutine f90SQLFreeHandle. Deallocating a handle releases the resources used by the handle.

Subroutine f90SQLAllocHandle is called as:

call f90SQLAllocHandle(HandleType, ParentHandle, NewHandle, iRet)

Parameter HandleType is an integer that indicates the type of handle that will be allocated. f90SQL offers predefined constants that can be used to identify the type of handle (for example, the constant SQL_HANDLE_ENV identifies an environment handle). Parameter ParentHandle is the handle in which context the new one will be defined. When allocating a environment handle, ParentHandle is always set to null (using 0 or the constant SQL_NULL_HANDLE). NewHandle is the integer variable that will store the new handle. Subroutine f90SQLAllocHandle will return a status code in iRet indicating whether the operation was completed successfully.

Subroutine f90SQLFreeHandle is called as follows:

call f90SQLFreeHandle

(HandleType, HandleToRelease, iRet)

where HandleToRelease is the variable that stores the handle you want to release.

The following trivial piece of Fortran code allocates an environment handle, prints its address in the screen, and releases the handle.

Example 4.1

program Example41

!This trivial program allocates and releases an environment handle

!to link, follow instruction in the chapter 12 of the manual

!load f90SQL modules

use f90SQLConstants

use f90SQL

implicit none

integer(SQLHANDLE_KIND)::EnvHndl

integer(SQLRETURN_KIND)::iRet

call f90SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, EnvHndl, iRet)

if (iRet.eq.SQL_SUCCESS) then

print *, 'An Environment Handle has been allocated at address:', EnvHndl

!release handle

call f90SQLFreeHandle(SQL_HANDLE_ENV, EnvHndl, iRet)

if (iRet.eq.SQL_SUCCESS) then

print *, 'The handle has been released'

else

print *,'There was a problem releasing the handle'

endif

else

print *, 'There was a problem allocating the handle'

endif

stop

end

Here is a more useful example. The short program in Example 4.2 allocates an environment handle and then calls the subroutine f90SQLDataSources to retrieve a list of the first ten drivers in your system. A more detailed explanation of f90SQLDataSources is given later in the manual. In the mean time, this subroutine is called as:

call f90SQLDataSources

(EnvHandle, FetchDirection, ServerNameStr, &
ServerNameLength, DescriptionStr, &
DescriptionLength, iRet)

where EnvHandle is an environment handle. FetchDirection indicates the direction in which to fetch the list of data sources. f90SQL has defined constants that allow you to indicate this direction. For example, the constant SQL_FETCH_NEXT indicates the subroutine to fetch the list in the forward direction. The name of a data source is returned in the character string ServerNameLength, and the length of the data in the string is returned in the integer variable DescriptionStr. Similarly, the description of the data source is returned in the variable DescriptionStr and the length of the description in DescriptionLength. The subroutine also returns a status code in iRet indicating whether it was completed successfully, and possibly, other useful information. f90SQL declares several constants that allow your application to test the value returned in iRet (for example, SQL_SUCCESS, SQL_NO_DATA, and others).

Example 4.2

Program Example42

!program requests a lists of available datasources from the Driver Manager and

!presents the list to the user, allowing him to choose a driver to connect with

!load f90SQL modules

use f90SQLConstants

use f90SQL

implicit none

integer(SQLHENV_KIND):: EnvHndl

integer(SQLRETURN_KIND)::iRet

character*100 ServerName, Description

integer(SQLSMALLINT_KIND) ServerNameLength, DescriptionLength

integer i

!allocate an environment handle

call f90SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, EnvHndl, iRet)

if (iRet.eq.SQL_SUCCESS) then

!Set ODBC version we will be using (3.x in this case); call f90SQLSetEnvAttr(EnvHndl, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, iRet)
: print *, 'First 10 or less drivers registered in your system:'
: i=1; do while (i.le.10)

call f90SQLDataSources(EnvHndl, SQL_FETCH_NEXT, ServerName, ServerNameLength, Description, DescriptionLength, iRet)

!exit loop if an error condition is returned
if (iRet.ne.SQL_SUCCESS .and. iRet.ne.SQL_SUCCESS_WITH_INFO) exit

print *,i,' ', trim(ServerName)

i=i+1; enddo
: !release handle; call f90SQLFreeHandle(SQL_HANDLE_ENV, EnvHndl, iRet)

endif

stop

end

This is an example of the output I get in my system. The data sources in your system are likely to be different:

First 10 or less drivers registered in your system:
1 MS Access 97 Database
2 dBASE Files
3 Excel Files
4 FoxPro Files
5 Text Files
6 PBServer
7 AdvWorks
8 f90SQLRegisteredUsers
9 BookSale
10 BycatchDatabase

Do not worry if you do not understand all the f90SQL subroutines used in these examples. They will be explained later in more detail. The important thing is to understand that your program only needs to secure a place to store the address of the environment handle ODBC has allocated for you. Also, note that f90SQL defines constants and kind values which facilitate the access to the ODBC functionality. Most of these constants are defined in the ODBC SDK for C++ and have exactly the same meaning in f90SQL. So, if you already have some experience with ODBC programming in C++ or Visual Basic, you will find f90SQL very familiar.