f90SQL - Example 5

Example 5. Writing data to an Excel spreadsheet.

(NOTE: This example was adapted to work with f90SQL-lite and f90SQL-pro)

This example shows how you can issue a query against a Microsoft Access Database and incorporate the resulting data into a Fortran program. For simplicity, the example uses a DSN (Data Source Name) that needs to be created using the ODBC administrator. However, with f90SQL-pro, a Fortran program can define a connection on-the-flight, using f90SQLDriverConnect.

Because we decided to use a predefined Data Source Name, The next step is to create a DSN for the BookSales database. To do this you must run the ODBC Administrator. Open the Control Panel windows and double click on the icon named ODBC (alternatively, run the program ODBCAD32.EXE located in the system or system32 directory of your Windows installation). This program will show a window with several tabs.

ODBCAdmin.jpg (41040 bytes)

Select the tab labeled System DSN and click the Add button. Select the Microsoft Access Driver and click the finish button.

This will open a new window that prompts you for information regarding the database associated to the new DSN. In the field Data Source Name, enter the following name BookSales and fill the description field as illustrated below.

Now, click the Select Button and navigate the directory tree to where you copied the file BOOKSALES.MDB.

Select the file BOOKSALES.MDB and click OK. After this, the ODBC administrator window will look like this (note that directory where BOOKSALES.MDB resides may be different in your system):

Click OK again. You now have created a new DSN named BookSales that is linked to the file BOOKSALES.MDB. Note that the new DSN is listed in the ODBC Administrator window.

Now that you have created the DSN for the database, you are ready to compile and run your program. We want a program that will let us retrieve a list author names, stored in a table called Authors. The following is a printout of such program:

program Example44

!read and print a few records from the BookSales database
!Note that BookSales database must be registered as a 
!DSN for this program to work. This was done for simplicity,
!but f90SQL allows direct access through f90SQLDriverConnect
!Copyright 1998, Canaima Software

!load f90SQL modules
use f90SQLConstants
use f90SQL

implicit none

integer(SQLHENV_KIND):: EnvHndl
integer(SQLHDBC_KIND):: ConnHndl
integer(SQLHSTMT_KIND)::StmtHndl
integer(SQLRETURN_KIND):: iRet
character(len=20):: AuthorsStr
integer:: i

print *,'Example 44'
print *,'Reads and prints a few records from the'
print *,'Booksales database.'
print *,'Note that the database must be registered'
print *,'as a system or user DSN with the name'
print *,'BookSales. Otherwise the program will not'
print *,'work correctly.'
print *,'Copyright 1998, Canaima Software'
print *,''

!allocate an environment handle
call f90SQLAllocHandle(SQL_HANDLE_ENV, 0, 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)

!Open DSN BookSales
call f90SQLConnect(ConnHndl,'BookSales','Admin','',iRet)

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

    !Allocate statement handdle
    call f90SQLAllocHandle(SQL_HANDLE_STMT,ConnHndl, StmtHndl, iRet)

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

        !Bind AuthorStr variable to the only column returned by SQL stmt
        call f90SQLBindCol(StmtHndl,int(1,SQLSMALLINT_KIND),SQL_F_CHAR, &
                           AuthorsStr,0,iRet)

        !Instruct driver to execute statement
        call f90SQLExecDirect(StmtHndl,'SELECT TOP 10 Author from Authors',iRet)

        print *,'Name of first 10 authors in BookSales database'

        !loop through result set and print results to screen
        do i=1, 10
            call f90SQLFetch(StmtHndl, iRet)
            if (iRet.ne.SQL_SUCCESS .and. iRet.ne.SQL_SUCCESS_WITH_INFO) &
                exit
            !reformat string in AuthorsStr to make it fortran-compatible
            call f90SQLStrFormat(AuthorsStr,AuthorsStr) 
            !print authors
            print *, i, ' ', trim(AuthorsStr)
        enddo

    else

        print *,'Error preparing SQL statement'
        call ShowDiags(SQL_HANDLE_STMT,StmtHndl)

    endif

    !release statement handle
    call f90SQLFreeHandle(SQL_HANDLE_STMT, StmtHndl, iRet)

    !disconnect
    call f90SQLDisconnect(ConnHndl,iRet)

else
 
   print *,'Error connecting to data source'
   call ShowDiags(SQL_HANDLE_DBC,ConnHndl)

endif

!release connection handle
call f90SQLFreeHandle(SQL_HANDLE_DBC, ConnHndl, iRet)

!release environment handle
call f90SQLFreeHandle(SQL_HANDLE_ENV, EnvHndl, iRet)

stop
end



subroutine ShowDiags(HndlType,Hndl)

!This subroutine prints error diagnostics as
!reported by the ODBC manager. 

!load f90SQL modules
use f90SQLConstants
use f90SQL

implicit none

integer(SQLHANDLE_KIND)::Hndl
integer(SQLSMALLINT_KIND)::HndlType


character(len=6):: SqlState 
character(len= SQL_MAX_MESSAGE_LENGTH)::Msg 
integer(SQLINTEGER_KIND)::NativeError 
integer(SQLSMALLINT_KIND):: iDiag, MsgLen 
integer(SQLRETURN_KIND):: DiagRet

iDiag = 1 
do while (.true.)
   call f90SQLGetDiagRec(HndlType, Hndl, iDiag, SqlState, NativeError, Msg, MsgLen, DiagRet) 
   if (DiagRet.ne.SQL_SUCCESS.and.DiagRet.ne.SQL_SUCCESS_WITH_INFO) exit 
   print *,trim(SqlState),',', NativeError,',', Msg(1:MsgLen)
   iDiag=iDiag+1 
enddo 

end subroutine ShowDiags

Consult Chapters 4 and 5 of the f90SQL online manual if you do not completely understand any part of the previous program.

Now, we are ready to compile and link the program.

If you installed the f90SQL library and modules in their default directories (i.e. f90SQL.DLL in the windows system directory, f90SQL.LIB in the compiler's library directory and f90SQL.MOD, f90SQLConstants.mod and f90SQLStructures.mod in the compiler include directory) then open a command window in the directory where you saved the program and enter the following command (we are using Lahey's LF95 in this example, check other examples for instructions with other compilers):

wpe2B.jpg (36780 bytes)

and now we can run the program:

wpe2C.jpg (27985 bytes)

Download a PostScript version of this example

Example5.zip