主题:[原创]实现在fortran 中调用数据库和电子表格读写的理想方法
nosper
[专家分:60] 发布于 2007-04-23 23:16:00
实现在fortran 中调用数据库和电子表格读写的理想方法
感谢这段时间里 编成爱好者fortran版的各位热心的老师给予我无私的帮助!!
我以前只是简单的学习了一些c语言的内容,对程序一直不是太懂。只是因为毕业设计的原应才与两个月前开始学习fortran。我毕设的内容是要模拟理想的电力市场,并且预测发电厂的发电量和电力市场里面其他的量,里面关于计算的部分已经存在了,我要做的就是让fortran 从数据库里进行读写数据,这样是便于和java 结合,使得可以让用户从网上实时输入数据。
我开始一直以为fortran 不能从数据库里读写数据的,而且网上也有相似的问题,但并没有答案,或者说是实现不了。所以我就准备将已由的程序编译成dll文件,让C++调用。但也有的程序太多了,而且在c++里调用数据库网上提到比较多的就是mfc方法,这样的话或产生界面,而我是不需要界面(界面会有java处理),在win32用c++直接调用数据库我有没有找到具体的例子。(大家有的话可以提供给我,谢谢了)
这时候我都准备放弃了,还好,在编成网上有人提出了用f90SQL可以。
然后我就在实验室的机子上开始尝试,刚开始时用cvf6.5 的,好像感觉并不是很行。实验室刚好这个时候新买了ivf9.1 ,幸运的是f90SQl在那个上面可以,直到这个时候我的毕设才真正的开始,在大家的帮助下,我进展的很快,现在已经差不多完成了。
我想把我在编成的一些经验和问题提出来,和大家一起分享一下:)
首先fortran是可以在数据库和电子表之间进行读写的。而且数据库与电子表的操作完全是一样的
1 调用F90SQL,
http://softwarecommunity.intel.com/isn/Community/en-US/forums/thread/117268.aspx
这里可以免费下载f90SQL-lite版
安装:
复制f90SQL library (f90SQL.lib) 到fortran compiler 的LIB路径下, 我是直接在编译路径里面添加的。
复制 f90SQL dynamic link library (f90SQLxxx.DLL) (共2个)到系统目录下,如:WinNT\SYSTEM32 in Windows NT
复制f90SQL modules (f90SQL.f90, f90SQL.mod,共6个) 到fortran compiler 的include/module路径下,我是直接在编译路径里面添加的。
之后就可以调用f90SQL语言进行数据库操作了。 因为使用的是lite版,所以数据库在使用前必须先到Data Sources (ODBC) 里面注册,另外数据库必须是2003或以前的,不支持2007(已测试过)
选用的是access database 和excel表格
从数据库中读取数据:
program main
!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
!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 input
call f90SQLConnect(ConnHndl,'input','Admin','',iRet) ! 数据库DSN名字为input
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) !sql的调用语句,Authors为其中的一个表,Author为该表中的栏
print *,'Name of first 10 authors in input 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
!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
如果input是电子表格,只需要将call f90SQLExecDirect(StmtHndl,'SELECT TOP 10 Author from Authors',iRet)换成SELECT TOP 10 Y FROM [Sheet1$] 就可以 了(后面会贴出一个例子)
最后更新于:2007-04-24 23:23:00
回复列表 (共25个回复)
沙发
nosper [专家分:60] 发布于 2007-04-23 23:24:00
从电子表格里读取(必须先到Data Sources (ODBC) 里面注册)
program main
!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) SQLStmtStr
!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)
!open a connection to the excel workbook
call f90SQLConnect(ConnHndl, input, ’ ’, ’ ’, 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
subroutine ShowDiags(HndlType,Hndl)
!This subroutine prints error diagnostics
!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
在input电子表格中[Sheet1$]里有X,Y 两栏
板凳
sjohn [专家分:5600] 发布于 2007-04-23 23:32:00
非常感谢楼主分享,呵,问一下入门级的问题,经过了ODBC是不是会慢一些?
我最近用MySQL自带的C语言接口,还挺好用的。
3 楼
nosper [专家分:60] 发布于 2007-04-23 23:39:00
向数据库中写入数据:
一般有两种方法:
1 INSERT INTO 表名 (X,Y) VALUES ({fn CONVERT(',X ,',SQL_DOUBLE)},{fn CONVERT(',Y ,',SQL_DOUBLE)}) 这是向里面插入数据,即每运行一次,向里面增加数据,这样不符合要求,我用的下一种方法
2 Updata,下面有实例 ,这要求数据库开始要存在数据,该操作只是更新
program ExcelUpdateLight
!load f90SQL modules
use f90SQLConstants
use f90SQL
implicit none
double precision, parameter::pi=3.14159d0
!integer(SQLUINTEGER_KIND),parameter::RowsetSize=10
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) SQLStmtStr
!Request from user the name and location of the excel file
Print *,'Enter a starting value for X (e.g. 0.5):'
read(*,*) X
!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)
!open a connection to the excel workbook
call f90SQLConnect(ConnHndl, input,‘’,’’,iRet) !input为数据库
if (iRet.eq.SQL_SUCCESS .or. iRet.eq. SQL_SUCCESS_WITH_INFO) then
!Set connection attributes to allow read/Update
!(usually this is set before the connection is established, but there seems to be a bug
!in the excel ODBC driver that does not recognize this setting if done before connecting)
call f90SQLSetConnectAttr (ConnHndl, SQL_ATTR_ACCESS_MODE, SQL_MODE_READ_WRITE, iRet)
!Allocate a statement handle
call f90SQLAllocHandle(SQL_HANDLE_STMT,ConnHndl, StmtHndl, iRet)
!loop to generate values of X and Y and insert them into workbook
do i=1,23
Y=sin(2.0*pi*X)
!book 为表名, X,Y为其中两个栏
Write(SQLStmtStr,*) 'UPDATE book SET X=',X,', Y=',Y,' WHERE ID=',i
print *,trim(SQLStmtStr)
call f90SQLExecDirect(StmtHndl,trim(SQLStmtStr),iRet)
!Check for error when adding rows
if (iRet.ne.SQL_SUCCESS .and. iRet.ne.SQL_SUCCESS_WITH_INFO) then
print *,'Error adding new records'
call ShowDiags(SQL_HANDLE_STMT,StmtHndl)
exit
endif
X=X+0.1D0
enddo
!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
subroutine ShowDiags(HndlType,Hndl)
!This subroutine prints error diagnostics
!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
4 楼
nosper [专家分:60] 发布于 2007-04-23 23:40:00
向电子表格里写入同上
5 楼
nosper [专家分:60] 发布于 2007-04-23 23:47:00
目前好像网站上只能下载到lite版,而做这个的canaimasoft好像2002年已经不做了,所以prof版很难找的到。
lite版会有限制,但基本的读写还是可以的
还有一点要注意,就是lite版里面用的1 个字节的指针,一次读写不能超过128个数据
但是如果想要读多于128个数据,可以:
query(1)='SELECT * FROM MyTable WHERE RID<=100'
query(2)='SELECT * FROM MyTable WHERE RID>100 and RID<=200'
query(3)='SELECT * FROM MyTable WHERE RID>200 and RID<=300'
Open an environment handle
do i=1,3
Open a database connection (Using f90SQLConnect)
Open a statement handle (Using f90SQLAllocHndle)
Execute query(i) (Using f90SQLExecDirect)
Fetch the result set of query(i)
Close the statement handle
Close the Connection
enddo
Close the environment handle
6 楼
nosper [专家分:60] 发布于 2007-04-23 23:49:00
以上就是在fortran中直接调用数据库和电子表格的方法。
期待大家给出其他的方法!
7 楼
f2003 [专家分:7960] 发布于 2007-04-23 23:51:00
本帖收藏中
[em9][em9][em9][em9][em9]
8 楼
nosper [专家分:60] 发布于 2007-04-24 00:03:00
在补充一点:
在project link的时候,必须要把f90SQL.lib加入到项目中,否则会出错
9 楼
nosper [专家分:60] 发布于 2007-04-24 00:10:00
[quote]非常感谢楼主分享,呵,问一下入门级的问题,经过了ODBC是不是会慢一些?
我最近用MySQL自带的C语言接口,还挺好用的。
[/quote]
这个好像差别不是很大,但具体怎么样,我也不清楚,f90SQL -prof有这个可以这样,在程序中调用drive,在读数据库,不需要注册,好像没得下,
如果谁有的话,可以传我一个:)
MySQL自带的C语言接口,是在c语言中直接调用数据库么?这比较有意思,可以具体讲一下么?
10 楼
臭石头雪球 [专家分:23030] 发布于 2007-04-24 08:10:00
[quote]非常感谢楼主分享,呵,问一下入门级的问题,经过了ODBC是不是会慢一些?
我最近用MySQL自带的C语言接口,还挺好用的。
[/quote]
我想可能会慢一些。
但是经过了 ODBC ,可以方便以后更换数据库类型。我个人是这么认为的。。
我来回复