[原创]实现在fortran 中调用数据库和电子表格读写的理想方法

主题：[原创]实现在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

本帖地址： http://bbs.pfan.cn/post/228525.html

回复列表（共25个回复）

11 楼

htczhy [专家分：470] 发布于 2007-04-24 10:40:00

在vf6.5所带的例子中有一个是关于向EXCEL中写入数据的，默认如下目录。
C:\Program Files\Microsoft Visual Studio\DF98\SAMPLES\ADVANCED\COM\AUTODICE
关键是要写一个与excel关联的模块文件，例子中的模块说明如下：
! This module contains the Automation interfaces of the objects defined in
! D:\Program Files\Microsoft Office\Office\EXCEL8.OLB
! Generated by the Fortran Module Wizard on xx/xx/xx
如果需要其他功能也可以通过vf6.5自带的Fortran Module Wizard自动生成。

12 楼

臭石头雪球 [专家分：23030] 发布于 2007-04-24 11:37:00

楼上的办法也是可行的。

我的 IVF 一直没有找到相关的模块向导。。。

13 楼

mltx [专家分：20880] 发布于 2007-04-24 12:55:00

赞搂主的钻研精神和大气态度！！！

14 楼

sjohn [专家分：5600] 发布于 2007-04-24 17:03:00

如果是在Linux下安装二进制的MySQL，那么需要加上devel包。如果是windows下安装，要怎么处理我不能确定，但肯定能行。非商业版免费，可开源。

帮助文件里面提供了C语言的API接口，网上也有很多例子。在C语言中不需要显式地和ODBC连接。

初始化函数的原型似乎有两个版本，我下载的版本刚好跟官方帮助的不一致。官方例子是这样的。（呵呵，应楼主要求，虽然在这里发大段的C代码不太合适）

/* Copyright (C) 2000 MySQL AB

   This program is free software; you can redistribute it and/or modify
   it under the terms of the GNU General Public License as published by
   the Free Software Foundation; either version 2 of the License, or
   (at your option) any later version.

   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   GNU General Public License for more details.

   You should have received a copy of the GNU General Public License
   along with this program; if not, write to the Free Software
   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */

#include <stdio.h>
#include <stdlib.h>
#include "mysql.h"

static void change_user(MYSQL *sock,const char *user, const char *password,
            const char *db,my_bool warning)
{
  if (mysql_change_user(sock,user,password,db) != warning)
  {
    fprintf(stderr,"Couldn't change user to: user: '%s', password: '%s', db: '%s':  Error: %s\n",
        user, password ? password : "", db ? db : "",
        mysql_error(sock));
  }
}

int main(int argc, char **argv)
{
  MYSQL *sock;

  if (!(sock=mysql_init(0)))
  {
    fprintf(stderr,"Couldn't initialize mysql struct\n");
    exit(1);
  }
  mysql_options(sock,MYSQL_READ_DEFAULT_GROUP,"connect");
  if (!mysql_real_connect(sock,NULL,NULL,NULL,NULL,0,NULL,0))
  {
    fprintf(stderr,"Couldn't connect to engine!\n%s\n",mysql_error(sock));
    perror("");
    exit(1);
  }
  sock->reconnect= 1;

  if (mysql_select_db(sock,"test"))
  {
    fprintf(stderr,"Couldn't select database test: Error: %s\n",
        mysql_error(sock));
  }

  change_user(sock,"test_user","test_user","test",0);
  change_user(sock,"test",NULL,"test",0);
  change_user(sock,"test_user",NULL,"test",1);
  change_user(sock,"test_user",NULL,NULL,1);
  change_user(sock,"test_user","test_user","mysql",1);

  mysql_close(sock);
  exit(0);
  return 0;
}

另外Fortran语言可以和C语言混编——虽然不成ISO标准，但是windows，Linux/Unix下都已经在事实上支持了，可移植性还挺好——那么尽管MySQL没有提供Fortran接口，楼主也可以用这个方案了。

呵呵，人帮我，我帮人，分享中进步，进步中分享。

15 楼

sjohn [专家分：5600] 发布于 2007-04-24 17:14:00

上面那个例子光连接了数据库，还什么都没有做
来一个插入和查询的例子。
数据库的配置很简单，尤其windows底下，有可视化的wizard.

#if defined(_WIN32) || defined(_WIN64)
#include <windows.h>
#endif
#include <stdio.h>
#include <stdlib.h>
#include "mysql.h"

#define SELECT_QUERY "select name from test where num = %d"
#define INSERT_QUERY "insert into test (name,num) values ('item %d', %d)"

int main(int argc, char **argv)
{
  int    count, num;
  MYSQL mysql,*sock;
  MYSQL_RES *res;
  char    qbuf[160];

  if (argc != 3)
  {
    fprintf(stderr,"usage : select_test <dbname> <num>\n\n");
    exit(1);
  }

  mysql_init(&mysql);
  if (!(sock = mysql_real_connect(&mysql,NULL,0,0,argv[1],0,NULL,0)))
  {
    fprintf(stderr,"Couldn't connect to engine!\n%s\n\n",mysql_error(&mysql));
    perror("");
    exit(1);
  }
  mysql.reconnect= 1;

  num = atoi(argv[2]);
  count = 0;
  while (count < num)
  {
    sprintf(qbuf,INSERT_QUERY,count,count);
    if(mysql_query(sock,qbuf))
    {
      fprintf(stderr,"Query failed (%s)\n",mysql_error(sock));
      exit(1);
    }
    count++;
  }
  count = 0;
  num = atoi(argv[2]);
  while (count < num)
  {
    sprintf(qbuf,SELECT_QUERY,count);
    if(mysql_query(sock,qbuf))
    {
      fprintf(stderr,"Query failed (%s)\n",mysql_error(sock));
      exit(1);
    }
    if (!(res=mysql_store_result(sock)))
    {
      fprintf(stderr,"Couldn't get result from %s\n",
          mysql_error(sock));
      exit(1);
    }
#ifdef TEST
    printf("number of fields: %d\n",mysql_num_fields(res));
#endif
    mysql_free_result(res);
    count++;
  }
  mysql_close(sock);
  exit(0);
  return 0;                    /* Keep some compilers happy */
}

16 楼

nosper [专家分：60] 发布于 2007-04-24 18:24:00

谢谢sjohn 先生了

我在好好研究下mysql：）

17 楼

nosper [专家分：60] 发布于 2007-04-24 18:58:00

[quote]在vf6.5所带的例子中有一个是关于向EXCEL中写入数据的，默认如下目录。
C:\Program Files\Microsoft Visual Studio\DF98\SAMPLES\ADVANCED\COM\AUTODICE
关键是要写一个与excel关联的模块文件，例子中的模块说明如下：
! This module contains the Automation interfaces of the objects defined in
! D:\Program Files\Microsoft Office\Office\EXCEL8.OLB
! Generated by the Fortran Module Wizard on xx/xx/xx
如果需要其他功能也可以通过vf6.5自带的Fortran Module Wizard自动生成。[/quote]

  我刚才在vf6.5里面好像并没有找到这个文件
   能把该文件上传到该网站上么？这样的话，大家可以下载：：）

  顺便问下，Fortran Module Wizard 这个怎么用呀，
  在ivf9.1  怎么没找到这个亚？

18 楼

南洋云贵 [专家分：30] 发布于 2007-04-30 17:49:00

顶

19 楼

htczhy [专家分：470] 发布于 2007-05-08 11:51:00

你装的版本可能是简化版，如果标准版或专业版都有示例程序。
不知道本站能否上传文件，放到mofile里。
匿名提取文件连接 http://pickup.mofile.com/5105075293636900
或登录Mofile，使用提取码 5105075293636900 提取文件
以下为 vf6.5里帮助里的Fortran Module Wizard帮助

To run the Visual Fortran Module Wizard, choose the Tools menu item Fortran Module Wizard. The module wizard asks a series of questions, including the name and type of the object as well as certain characteristics. If you have not already obtained the object's characteristics, see The Role of the Module Wizard.

The Visual Fortran Module Wizard presents a series of dialog boxes that allow you to select the type of information needed.

An object's type information contains programming language independent descriptions of the object's interfaces. Depending on the implementation of the object, type information can be obtained from the running object (see Automation Object below) or from a type library.

A type library is a collection of type information for any number of object classes, interfaces, and so on. A type library can also be used to describe the routines in a DLL. You can store a type library in a file of its own (usually with an extension of .TLB) or it can be part of another file. For example, the type library that describes a DLL can be stored in the DLL itself.

After you start the Module Wizard (Tools menu, Fortran Module Wizard), a dialog box requests the source of the type information that describes the object you need to use. You need to determine what type of object it is (or DLL) and how it makes its type information available. The choices are:

Automation Object
Type Library containing Automation information
Type Library containing COM interface information
Type Library containing DLL information
DLL containing type information
Object servers typically contain the type library in the same file (either .dll or .exe) as the object implementation.

Many objects implement dual interfaces, which supports both COM and Automation. For an object that supports dual interfaces, you can choose either of these options:

Type Library containing Automation information
Type Library containing COM interface information
The COM object interfaces tend to be more efficient (better run-time performance).

ActiveX controls implement an Automation interface. When using an ActiveX control, choose the Type Library containing Automation interface information option.

Most DLLs do not provide a type library that describes the programming interfaces in the DLL, and therefore cannot be used by the Module Wizard.

For these reasons, the Automation Information (second option) or COM Interface Information (third option) are the most commonly used.

The following initial screen appears after you select the Visual Fortran Module Wizard:

Initial Module Wizard Screen

After you select one of the five choices, one of two different screens will appear depending on the selection made. The "Module Name" in the initial Module Wizard screen is used as the name of the Fortran module being generated. It is also used as the default file name of the generated source file.

If You Select Automation Object
If you select Automation Object, the following screen appears:

Application Object Screen

20 楼

htczhy [专家分：470] 发布于 2007-05-08 11:52:00

Microsoft recommends that object servers provide a type library. However some applications do not, but do provide type information dynamically when running. Use this option for such an application. Enter the name of the application, name of the object, and version number. The version number is optional. If you do not specify it, you will get the latest version of the object. Note that this method only works for objects that provide a programmatic identifier (ProgID). ProgIDs are entered into the system registry and identify, among other things, the executable program that is the object's server.

After entering the information and pressing the Generate button, the Fortran Module Wizard asks you for the name of the source file to be generated. It then asks COM to create an instance of the object identified by the ProgID that the wizard constructs using the supplied information. COM starts the object's server if it needs to do so. The wizard then asks the object for its type information and generates a file containing Fortran modules.

If You Select Other Options
After selecting any of the remaining options in the initial screen and press the "Next" button, the Module Wizard displays the following screen:

Type Library Screen

Choose the type library (or file containing the type library), and optionally specific components of the type library.

At the top of the dialog box is a combo box that lists all of the type libraries that have been registered with the system. You will notice a number of different file extensions, for example, .OLB (object libraries) and .OCX (ActiveX controls). Select a type library from the list or press Browse to find the file using the standard "Open" dialog box. Once you have selected a type library press the Show button to list the components you can select from the type library. By default, the Fortran Module Wizard will use all of the components. Optionally, you can select the ones desired from the list.

After entering the information and pressing the "Generate" button, the Fortran Module Wizard asks you for the name of the source file to be generated. It then asks COM to open the type library and generates a file containing Fortran modules.

The Fortran Module Wizard also has a command-line interface. The MODWIZ command has the following form:

MODWIZ [options] typeinfo-name
For a list of MODWIZ command options and an explanation of typeinfo-name, type the following command in a Fortran Command Prompt (available from the Visual Fortran program folder):

MODWIZ /?

我来回复

您尚未登录，请登录后再回复。点此登录或注册

主题：[原创]实现在fortran 中调用数据库和电子表格读写的理想方法

回复列表（共25个回复）

我来回复

程序员工具箱 new

代码片段

本版新帖

主题：[原创]实现在fortran 中调用数据库和电子表格读写的理想方法

回复列表 （共25个回复）

我来回复

程序员工具箱 new

代码片段

本版新帖

回复列表（共25个回复）