f90SQL - Example 3

Example 3. Reading data from an Excel spreadsheet.

(NOTE: This example works only with f90SQL-pro)

This example shows how we can write a Fortran program to read data that is stored in an Excel spreadsheet. In this case, we will read the same spreadsheet we created in Examples 1 and 2. The spreadsheet should look like this:

exampl15.gif (22158 bytes)

The ODBC driver for Excel, considers each spreadsheet in a workbook to be a table, the columns and rows of the spreadsheet are the columns and rows of the table. Excel always adds a $ symbol to the spreadsheet names when accessed through the ODBC-API. So the name of the first table (spreadsheet) is Sheet1$. The names o the columns are X and Y. With this information in hand, we can now write the Fortran program to read these columns.

program ExcelRead

!This program reads x,y pairs from Sheet1$ of the workbook specified by the user.
!The program assumes that the workbook has been prepared with two of the columns
!labeled X and Y and is registered as a DSN (ExcelTest).
!Copyright 1998, Canaima Software

!load f90SQL modules
use f90SQLConstants
use f90SQLStructures
use f90SQL

implicit none

integer(SQLINTEGER_KIND),parameter:: MaxStringLen=255

integer(SQLHENV_KIND):: EnvHndl
integer(SQLHDBC_KIND):: ConnHndl
integer(SQLHSTMT_KIND):: StmtHndl
integer(SQLRETURN_KIND)::iRet
integer(SQLSMALLINT_KIND)::ColNumber,i,ConnStrLength
double precision X,Y
character(len=MaxStringLen) ConnStr,SQLStmtStr,fname,ConnStrOut

print *,'Program ExcelRead'
print *,'Reads the 10 first rows of data from the Excel'
print *,'spreadsheet created with ExcelWrite'
print *,'Copyright 1998, Canaima Software'
print *,''

!Request from user the name and location of the excel file
print *, 'Enter name and path of the excel file (including file extension):'
read (*,*) fname

!allocate an environment handle
call f90SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, EnvHndl, iRet)

!Set ODBC version to use (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)

!Create a connection string
ConnStr='DBQ='//trim(fname)//';DRIVER={Microsoft Excel Driver (*.xls)}'

!open a connection to the excel workbook
call f90SQLDriverConnect(ConnHndl, f90SQL_NULL_PTR, ConnStr, ConnStrOut, ConnStrLength, SQL_DRIVER_COMPLETE,iRet)

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

print *,'Connection open:',trim(ConnStrOut)

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

print *,'Statement allocated:', StmtHndl, iRet

!Create a select query
!to retrieve the top 10 records
SQLStmtStr='SELECT TOP 10 X,Y FROM [Sheet1$]'

call f90SQLPrepare(StmtHndl,trim(SQLStmtStr),iRet)

print *,'Query prepared',iRet

!Prepare the SQL query and execute the query (
call f90SQLExecute(StmtHndl,iRet)

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

!Retrieve data

!bind SQL statement parameters to fortran variables
ColNumber=1
call f90SQLBindCol (StmtHndl, ColNumber, SQL_F_DOUBLE, X, f90SQL_NULL_PTR, iRet)
ColNumber=ColNumber+1
call f90SQLBindCol (StmtHndl, ColNumber, SQL_F_DOUBLE, Y, f90SQL_NULL_PTR, iRet)

do while (.true.)

call f90SQLFetch(StmtHndl,iRet)
if (iRet.ne.SQL_SUCCESS .and. iRet.ne.SQL_SUCCESS_WITH_INFO) then

if (iRet.eq.SQL_NO_DATA) then

print *,'End of data set reached'

else

print *,'Error fetching data'
call ShowDiags(SQL_HANDLE_STMT,StmtHndl)

endif
exit

endif
print *,X,Y

enddo

else

print *,'Error executing SQL query'
call ShowDiags(SQL_HANDLE_STMT,StmtHndl)

endif

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

!Free connection
call f90SQLDisconnect(ConnHndl,iRet)

else

print *,'Error opening connection to workbook'
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

Now, we are ready to compile the program. For this example, we will use the Lahey/Fujitsu FORTRAN 95 compiler:

Note that we used f90SQLLF95ModDir and f90SQLLF95LibDir, two environment variables created by the f90SQL installation, that point to the directories where the f90SQL modules and libraries are stored.

The next screen shot shows the results of running the program:

Download a PostScript version of this example

Example3.zip