ODBC defines discrete states for each environment, connection, and SQL statement. A particular item, as identified by its handle, moves from one state to another when the application calls a certain function or functions, and passes the handle to that item. Such movement is called a state transition. For example, allocating an environment handle with f90SQLAllocHandle moves the environment from Unallocated state to Allocated state. Calling f90SQLFreeHandle frees that handle, moving it from Allocated state to Unallocated state. ODBC defines a limited number of legal state transitions. This means some subroutines must be called in a certain order.
Some subroutines, such as f90SQLGetConnectAttr, do not affect state at all. Other subroutines affect the state of a single item. For example, f90SQLDisconnect moves a connection from a Connection state to an Allocated state. Finally, some subroutines affect the state of more than one item. For example, allocating a connection handle with f90SQLAllocHandle moves a connection from an Unallocated to an Allocated state and moves the environment from an Allocated to a Connection state.
If an application calls a subroutine out of order, the function returns a state transition error. For example, if an environment is in a Connection state and the application calls f90SQLFreeHandle with that environment handle, f90SQLFreeHandle returns SQLSTATE HY010 (Function sequence error), because it can be called only when the environment is in an Allocated state. By defining this as an invalid state transition, ODBC prevents the application from freeing the environment while there are active connections.
Some state transitions are inherent in the design of ODBC. For example, it is not possible to allocate a connection handle without first allocating an environment handle. The function that allocates a connection handle requires an environment handle. Other state transitions are enforced by the Driver Manager and the drivers. For example, f90SQLExecute executes a prepared statement. If the statement handle passed to it is not in a Prepared state, f90SQLExecute returns SQLSTATE HY010 (Function sequence error).
From the application’s point of view, state transitions are generally straightforward. Legal state transitions tend to go hand-in-hand with the flow of a well-written application. This means applications that requests ODBC functions in the right order need no worry about state transitions. Throughout this manual, state transitions will not be mentioned explicitly. Instead, the order in which f90SQL subroutines must be called will be indicated. For reference, the next paragraphs describe the different states associated to the three main types of handles (the identifiers for the states are given in parenthesis).
An environment handle has three possible states:
A connection handle has seven possible states:
A statement has thirteen possible states:
An environment, a connection, or a statement move from one state to another whenever an application calls an ODBC API function. This movement is known as state transition. For example, allocating an environment handle with the ODBC API function f90SQLAllocHandle or f90SQLAllocEnv moves the environment from the Unallocated state to the Allocated state. Freeing the environment with the ODBC API functions f90SQLFreeHandle or f90SQLFreeEnv moves the environment from the Allocated state back to the Unallocated state.
Some ODBC API functions do not affect states at all. For example, the functions that retrieve environment, connection, and statement attributes. Other ODBC API functions only affect the state of a single item. For example, the function that disconnects from a data source can change a connection from the Connected state to the Allocated state. Other ODBC API functions affect the state of more than one item. For example, the functions that allocate a connection handle, change both a connection from the Unallocated state to the Allocated state, and the environment from the Allocated state to the Connection state.
Because ODBC defines a limited number of legal state transitions for an environment, a connection, and a statement, most ODBC API functions must be called in a certain order. For example, an application must call the ODBC API function that executes a SQL statement before it can call the functions that retrieve data from the result set. If an application calls an ODBC API function out of order, the function returns a state transition error -- SQLSTATE HY010 (Function sequence error). Some state transitions are inherent in the design of ODBC. For example, it is not possible to allocate a connection handle without first allocating an environment handle. The ODBC API function that allocates a connection handle requires an environment handle as one of its arguments. Other state transitions are enforced either by the Driver Manager or by drivers.