!Version of original ExcelRead adapted to work with f90SQL-lite

!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
double precision X,Y
character(len=MaxStringLen) DSNname,SQLStmtStr,UsrName,UsrPsswrd

print *,'Program ExcelRead'
print *,'Reads the 10 first rows of data from the Excel'
print *,'spreadsheet created with ExcelWrite'
print *,'Note that you must create a DSN for this Workbook'
print *,'before you can run this program. It is also possible'
print *,'to read directly from the file (without creating a DSN)'
print *,'using a similar technique to the one used in ExcelWrite'
print *,'Copyright 1998, Canaima Software'
print *,''

!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)

!Request from user the name and location of the excel file
print *, 'Enter name the Data Source Name (DSN) that points to the Excel Workbook'
read (*,*) DSNname
print *,'Enter your user name:'
read (*,*) UsrName
print *,'Enter your password (WARNING: password will show on the screen):'
read (*,*) UsrPsswrd

!open a connection to the excel workbook
call f90SQLConnect(ConnHndl, trim(DSNname),trim(UsrName),trim(UsrPsswrd),iRet)

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

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

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

!Prepare the SQL query and execute the query
call f90SQLExecDirect(StmtHndl,trim(SQLStmtStr),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 .or. iRet.eq.SQL_NO_IMPLEMENTED) 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