Step 2

Step 2. Initializing statements

ODBC applications perform almost all database access by executing SQL statements. The general sequence of events in this step is to allocate a statement handle and set any statement attributes.

In some cases, an application may want to probe the driver to discover its capabilities. This can be done by calling subroutines f90SQLGetInfo and f90SQLGetFunctions.

To allocate a statement handle, an application calls f90SQLAllocHandle with the option SQL_HANDLE_STMT. The following is an example of a call to f90SQLAllocHandle, in which a statement is allocated under the context of the connection ConnHndl. The handle (address) for the statement space is stored in variable StmtHndl:

integer(SQLHANDLE_KIND):: EnvHndl, ConnHndl, StmtHndl,

!Allocate environment handle

call f90SQLAllocHandle(SQL_HANDLE_ENV, SQL_HANDLE_NULL, EnvHndl, iRet)

!Allocate connection handle

call f90SQLAllocHandle(SQL_HANDLE_DBC, EnvHndl, ConnHndl, iRet)

!Allocate statement handle

call f90SQLAllocHandle(SQL_HANDLE_STMT, ConnHndl, StmtHndl, iRet)

Once a statement has been allocated, some of its attributes can also be set. Attributes are characteristics of the statement. For example, whether to use bookmarks and what kind of cursor to use with the statement’s result set are statement attributes.

Statement attributes are set with f90SQLSetStmtAttr and their current settings retrieved with f90SQLGetStmtAttr. There is no requirement that an application must set any statement attributes. All statement attributes have defaults, some of which are driver-specific.

When a statement attribute can be set depends on the attribute itself. The SQL_ATTR_CONCURRENCY, SQL_ATTR_CURSOR_TYPE, SQL_ATTR_SIMULATE_CURSOR, and SQL_ATTR_USE_BOOKMARKS statement attributes must be set before the statement is executed. The SQL_ATTR_ASYNC_ENABLE and SQL_ATTR_NOSCAN statement attributes can be set at any time, but are not applied until the statement is used again. SQL_ATTR_MAX_LENGTH, SQL_ATTR_MAX_ROWS, and SQL_ATTR_QUERY_TIMEOUT statement attributes can be set at any time, but it is driver-specific whether they are applied before the statement is used again. The remaining statement attributes can be set at any time.

Most statement attributes introduced in ODBC 3.5 (except for SQL_ATTR_METADATA_ID) can also be set at the connection level.

The next example, shows how to use f90SQLGetInfo to probe the driver for a feature support. We want to obtain a list of publishers and their cities, ordered by state. But we do not want the state to be part of the output. Some DBMSs support the use of the SQL ORDER BY clause (to order a record set) using a column that is not a part of the SELECT statement. Others do not support this feature.

If the driver supports the feature, then we prepare the statement

SELECT Name, City
FROM Publishers
WHERE State='NY' OR State='CA' OR STATE='MA'
ORDER BY State

If the driver does not support the feature, then we use the following SQL statement instead:

SELECT Name, City, State
FROM Publishers
WHERE State='NY' OR State='CA' OR STATE='MA'
ORDER BY State

Note that the second statement will also return the state as a column in the recordset. We do not want this column, so we only bind columns Name and City to variables in the application. Binding a column simply means that we assign an application variable, which act as a buffer, to receive the value of that column in the resulting recordset. To bind a column, an application uses subroutine f90SQLBindCol. A more detailed explanation of this subroutine is given later in this chapter (see section Binding Resultset Columns). In the mean time, the next explanation should allow you to follow the program in example 5.2.

A typical call to f90SQLBindCol takes this form:

call f90SQLBindCol

(StmtHandle, ColumnNumber, TargetType, TargetVar, StrLenIndPtr, iRet)

where StmtHandle is a statement handle, ColumnNumber is an integer*2 argument indicating the column number of the result set that will be bound to the variable passed in TargetVar. TargetType is the type of variable TargetVar. f90SQL defines constants that identify the different types. For example, constant SQL_F_CHAR tells f90SQLBindCol that variable TargetVar is a character variable. Argument StrLenIndPtr is a pointer to an integer variable. This variable will contain the length of the data stored in variable TargetVar when the rows of the recordset are fetched. In Fortran, to pass a pointer you use the function loc(var). If your are not interested in this value, you can pass a null pointer (a 0 or the constant f90SQL_NULL_PTR).

Example 5.2 also illustrates how to allocate a statement handle and some of the steps presented in the flowchart of Figure 5.1.

Example 5.2

program Example52

!Demonstrates:

!Use of f90SQLGetInfo to request information about a driver

!Allocation and use of a statement handle

!Main steps in flow chart in Figure 5.1

!load f90SQL modules

use f90SQLConstants

use f90SQL

implicit none

integer, parameter::MaxNDrivers=30

type PublishersRec

character(len=30) Name

character(len=30) City

integer(SQLINTEGER_KIND)::StrLenIndctr(2)

end type PublishersRec

integer(SQLHENV_KIND):: EnvHndl

integer(SQLHDBC_KIND):: ConnHndl

integer(SQLHSTMT_KIND):: StmtHndl

integer(SQLRETURN_KIND)::iRet

character(len=2):: OrderByCond

character(len=100)::StmtStr

type(PublishersRec)::Publishers

integer(SQLSMALLINT_KIND)::ColNumber

!STEP 1: Initializing ODBC Environment and Connecting to data source

!allocate an environment handle

call f90SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, EnvHndl, iRet)

!Set ODBC version we will be using (3.x in this case)

call f90SQLSetEnvAttr(EnvHndl, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, iRet)

!Allocate a connection handle

call f90SQLAllocHandle(SQL_HANDLE_DBC,EnvHndl, ConnHndl, iRet)

!connect to BookSales database

call f90SQLConnect(ConnHndl,'BookSales','Admin','',iRet)

if (iRet.eq.SQL_SUCCESS .or. iRet.eq. SQL_SUCCESS_WITH_INFO) then

!STEP 2: Probing Connection features and allocating statement handles

!Check if driver allows ordering of a record set by columns

!that are not in a SELECT statement, and prepare a SQL query

!accordingly

call f90SQLGetInfo(ConnHndl, SQL_ORDER_BY_COLUMNS_IN_SELECT, OrderByCond, int(0,SQLSMALLINT_KIND), iRet)

if (trim(OrderByCond).eq."Y") then

!column in "order by" must be in select

StmtStr= 'SELECT Title, City, State FROM Publishers '// 'WHERE State=''NY'' OR State=''CA'' OR STATE=''MA'' '// 'ORDER BY State'

else

!column in "order by" does not need to be in select

StmtStr= 'SELECT Name, City FROM Publishers ' // 'WHERE State=''NY'' OR State=''CA'' OR STATE=''MA'' '// 'ORDER BY State'

endif

!Allocate statement handle

call f90SQLAllocHandle(SQL_HANDLE_STMT,ConnHndl, StmtHndl, iRet)

!STEP 3: Preparing and Executing

!Prepare statement. Note that for this case, we could have

!used f90SQLExecDirect instead, omit this step

call f90SQLPrepare(StmtHndl, StmtStr, iRet)

!Execute SQL statement

call f90SQLExecute(StmtHndl, iRet)

!STEP 4: Fetching results

!Bind variables to columns in result set

ColNumber=1

call f90SQLBindCol(StmtHndl,ColNumber,SQL_F_CHAR, Publishers%Name, f90SQL_NULL_PTR, iRet)

ColNumber=ColNumber+1

call f90SQLBindCol(StmtHndl,ColNumber,SQL_F_CHAR, Publishers%City, f90SQL_NULL_PTR, iRet)

!Loop to fetch records (one at a time, in this case)

do while (.true.)

!Fetch a record from the result set

call f90SQLFetch(StmtHndl,iRet)

if (iRet.eq.SQL_NO_DATA) exit

!reformat strings to fortran convection

call f90SQLStrFormat(Publishers%Name,Publishers%Name)

call f90SQLStrFormat(Publishers%City,Publishers%City)

!print record to screen

print *,trim(Publishers%Name),',',trim(Publishers%City)

enddo

!STEP 6(a): Disconnecting

!Release statement handle

call f90SQLFreeHandle(SQL_HANDLE_STMT,StmtHndl, iRet)

!release connection

call f90SQLDisconnect(ConnHndl, iRet)

endif

!STEP 6(b): Disconnecting

!release connection handle

call f90SQLFreeHandle(SQL_HANDLE_DBC,ConnHndl, iRet)

!release environment handle

call f90SQLFreeHandle(SQL_HANDLE_ENV,EnvHndl, iRet)

stop

end