Fortran and MariaDB

Introduction

Fortran (FORmula TRANslating System) is a general-purpose, imperative programming language that is especially suited to numeric computation and scientific computing. History of FORTRAN can be tracked late 1953 when John W. Backus submitted a proposal to his superiors at IBM. The First FORTRAN compiler appeared in April 1957.

Some notable historical steps where:

  • FORTRAN II in 1958
  • FORTRAN III in 1958,
  • FORTRAN IV in 1962.
  • FORTRAN 66 or X3.9-1966 become the first industry-standard
  • FORTRAN 77 or X3.9-1978. This is the version of the Fortran I learned 1996.
  • Fortran 90 was released as ISO/IEC standard 1539:1991 and ANSI Standard in 1992
  • Fortran 95 was released as ISO/IEC standard 1539-1:1997
  • Fortan 2003 was released as ISO/IEC 1539-1:2004
  • Fortran 2008 was released as ISO/IEC 1539-1:2010 is most recent standard
  • Fortran 2015 is planned in late 2016.

More comprehensive history and introduction can be found e.g. from https://en.wikipedia.org/wiki/Fortran.

Thus Fortran programming language is not dead ! I did use Fortran in same day as I started writing this blog (05/07/2015). There is some historical reason why I decided to learn Fortran. In department of computer science, university of Helsinki there is course named Software Project where students design, implement and test larger application. I participated on this course 1996 and my application was Ringing Software for Ringing Centre, Natural History Museum, University of Helsinki. Their original software used magnetic tapes and Fortran66/77 programs. Our assignment was to transform this to use Oracle database and UNIX. At that time we decided to use Fortran77 (with some Fortran90 extensions, mainly structures) and ProFortran precompiler from Oracle.

Compilers

There is version of GNU Fortran named GFortran. The GFortran compiler is fully compliant with the Fortran 95 Standard and includes legacy F77 support. In addition, a significant number of Fortran 2003 and Fortran 2008 features are implemented.

To my experience GFortran is very good compiler and really includes most of the legacy support you need (and lot of new stuff I really do not need). However, I found one example that is useful but not supported, variable length format. Consider following:

       cnt = max_year - min_year + 1
       WRITE (*, 20) (i, i = min_year, max_year)
   20  FORMAT ('Reng', (2X, I4), 2X, '  Total')

Here format (2x, I4) is repeated times and depends runtime values. This can be transformed to:

       cnt = max_year - min_year + 1
       WRITE(fmt,'(A,I2,A,A)') '(A,',cnt,'(2X,I4)',',A)'
       WRITE (*, fmt) 'Reng', (i, i = min_year, max_year), ' Total'

This is because format can be a string variable and above produces format (A,44(2X,I4),A) (assuming years 1971 and 2014). But, in my opinion the first one is more clearer and simpler. Additionally, I learned to use pre-Fortran90 STRUCTURE and RECORD extensions, like

  STRUCTURE /TVERSION/
    CHARACTER *80  VERSION
  END STRUCTURE

  RECORD /TVERSION/ MARIADB

  MARIADB.VERSION = ''

This can naturally expressed using TYPE:

  TYPE t_version
    CHARACTER *80  :: version
  END TYPE

  TYPE(t_version) mariadb
  mariadb%version = ' '

I mostly use Fortran90 and free-form (longer line lengths than  allowed by standard Fortran77) but only limited amount of new features. Thus code might look like Fortran77 mostly:

   50  CONTINUE
   55  FORMAT(I10,1X, A1, 1X, A)
       READ (10, 55, END = 70, ERR=800, IOSTAT = readstat, IOMSG=emsg) pesaid, rlaani, rkunta
       plaani(pesaid) = rlaani
       pkunta(pesaid) = rkunta
       GOTO 50

Naturally, there is number of commercial Fortran compilers like Intel Fortran https://software.intel.com/en-us/fortran-compilers and NAG http://www.nag.com/nagware/np.asp .

Clearly one of the bad features of Fortran are implicit types. If a variable is undeclared, Fortran 77 uses a set of implicit rules to establish the type. This means all variables starting with the letters i-n are integers and all others are real. Many old Fortran 77 programs uses these implicit rules, but you should not! The probability of errors in your program grows dramatically if you do not consistently declare your variables. Therefore, always put following in start of your Fortran program:

     PROGRAM myprogram

        IMPLICIT NONE  ! No implicit rules used, compiler error instead

SQL and Fortran

Fortran does not natively understand SQL-clauses, but you can use e.g. embedded SQL. Embedded SQL is SQL-clauses inside a host language like Fortran. Lets take a example:

      EXEC SQL BEGIN DECLARE SECTION
       CHARACTER *24 HTODAY
      EXEC SQL END DECLARE SECTION
      EXEC SQL INCLUDE SQLCA
      EXEC ORACLE OPTION (ORACA = YES)
      EXEC SQL INCLUDE ORACA
      EXEC SQL CONNECT :UID1 IDENTIFIED BY :UID2
      EXEC SQL SELECT TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS')
     -      INTO :HTODAY
     -      FROM DUAL

Naturally, normal Fortran compiler will not understand clauses starting with EXEC SQL. Thus, you need to first use precompiler. Precompiler changes embedded SQL-clauses (above include clauses are copied to resulting file) and other SQL-clauses are transformed to CALL-clauses to provided database server API-calls. Naturally, this means that you software will work only for precompiled (and then compiled) database provider.

Currently, there are precompilers at least for Oracle and DB2 databases (see https://en.wikipedia.org/wiki/Embedded_SQL). However, OS support is diminishing. E.g. Oracle Fortran Precompiler does not anymore work on Linux 64bit when using Oracle >10g. This in my opinion is bad because porting your Fortran software from Oracle to e.g. DB2 is not trivial especially if you have application with 100000 lines of Fortran code.

This fact has lead on my experience to situation where some of the system is re-implemented using Java and some of the code modified to pure Fortran so that it read input from files (generated using pure SQL) and by removing all embedded SQL-clauses.

Fortran and MariaDB

There is no connectors for Fortran to MariaDB /MySQL. However, you could use ODBC, however the free ODBC modules FLIBS and fodbc fail to compile in my 64-bit Linux and after some hacking with fodbc, it did not really work. Naturally, you could write your own fodbc for MariaDB/MySQL but currently I do not have a real need or enough free time to do so. Alternative way of doing this is create C-language interface between Fortran code and ODBC driver.

Lets take very simple example where Fortran program connects to MariaDB database, selects a version and disconnects.

  PROGRAM myodbc_test

  INTEGER :: RC

  TYPE t_version
    CHARACTER *80  :: version
  END TYPE

  TYPE(t_version) mariadb

  RC = 0

  RC = connect()
  mariadb%version='select version()'//char(0)
  RC = version(mariadb)
  CALL mstrc2f(mariadb%version)
  WRITE (*,'(A)') mariadb%version
  RC = disconnect()

  STOP

  END PROGRAM

  SUBROUTINE mstrc2f(STR)

      IMPLICIT NONE

      CHARACTER *(*) STR
      INTEGER   *4 MAX
      INTEGER   *4 IND
      CHARACTER *1  EOS
      EOS  = CHAR(0)
      MAX  = LEN(STR)
      IND = MAX
  100 CONTINUE
      IF ( IND .GE. 1 ) THEN
          IF ( STR(IND:IND) .EQ. EOS) THEN
              GO TO 200
          ENDIF

          STR(IND:IND) = ' '
          IND = IND - 1

          GO TO 100
      ENDIF


  200 CONTINUE

      IF (IND .GE. 1) THEN
          STR(IND:IND) = ' '
      ENDIF

      RETURN

      END

As you note string variables need special handling as Fortran has constant strings. Therefore, we need to add C string end character before calling C-routines and then remove trailer before using string in Fortran again. And then simple C-interface (no real error handling):

#include 
#include 
#include 
#include 

SQLHENV env;
SQLHDBC dbc;

int connect_(void) {

  SQLHSTMT stmt;
  SQLRETURN ret;
  SQLCHAR outstr[1024];
  SQLSMALLINT outstrlen;

  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
  SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
  SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
  ret = SQLDriverConnect(dbc, NULL, "DSN=test;", SQL_NTS,
			 outstr, sizeof(outstr), &outstrlen,
			 SQL_DRIVER_COMPLETE);

  return ret;
}

int disconnect_(void) {
    SQLDisconnect(dbc);
    SQLFreeHandle(SQL_HANDLE_DBC, dbc);
    SQLFreeHandle(SQL_HANDLE_ENV, env);
    fprintf(stderr, "Disconnected...n");
}

typedef struct {
	char version[80];
} t_version;

int version_(t_version *version) {
	SQLHSTMT stmt;
	SQLRETURN ret;
	SQLSMALLINT columns;
	char buf[80];
	SQLLEN indicator;
	SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

	fprintf(stderr, "Selecting version...n");

	ret = SQLPrepare(stmt,
           version->version, SQL_NTS);
	ret = SQLExecute(stmt);
	ret = SQLFetch(stmt);
	ret = SQLGetData(stmt, 1, SQL_C_CHAR, buf, sizeof(buf), &indicator);
	strcpy(version->version, buf);
	return ret;
}

And, if you compile these and run the resulting program you might see something like following:

$ gcc myodbc.c -c -g -l myodbc5
$ gfortran myodbc_test.f90 myodbc.o -l myodbc5 -g
$ ./a.out
Selecting version...
10.0.18-MariaDB-debug                                                           
Disconnected...

Future of Fortran ?

There is clearly need languages like Fortran. It has some very nice features like formatted I/O and mathematical functions. However, learning Fortran might be up to you because it is not taught as first (or second) programming language on most universities or similar schools. Thus number of people who can use Fortran on programming or teach it is decreasing rapidly. However, my experience is that learning Fortran is simple if you can master at least one programming language (ok, I had learn already C/C++, COBOL, PL/I, Basic on my earlier studies). So you want to learn Fortran ? If Internet resources are not enough there is number of books. Book I have used is obsolete (Fortran 77 and Finish-language version Fortran 90/95) but e.g. http://www.amazon.com/Introduction-Programming-Fortran-With-Coverage/dp/0857292323 is a good one.