Embedded SQL in FORTRAN

Introduction

Embedded SQL means that you can use SQL statements through a host programming language, C/C++, COBOL, and FORTRAN. FORTRAN is used as an example in this article.

You include SQL statements as part of the source code for an application. The source code is compiled and linked with the appropriate language-specific facilities. The SQL statements are executed in the context of the application.

Mimer SQL supports embedded SQL for FORTRAN according to the ISO standard.

Function

The ESQL preprocessor processes SQL statements embedded in the host language. SQL statements are identified by the leading delimiter “EXEC SQL”, e.g. “EXEC SQL DELETE FROM HOTEL”.

You must declare host variables used in SQL statements within the SQL DECLARE SECTION, delimited by the statements BEGIN DECLARE SECTION and END DECLARE SECTION.

You can find detailed information on Embedded SQL in the Programmer’s Manual in the Mimer SQL Documentation set.

Example

The following is an example that connects to the database defined as the default. All rows are then fetched from the table INFORMATION_SCHEMA.TABLES.

This example is stored in a file called simple.efo (used in the call to esql below):

      PROGRAM SQLEX

      EXEC SQL BEGIN DECLARE SECTION
      CHARACTER*5 SQLSTATE
      CHARACTER*128 SCHEMA
      CHARACTER*128 TABLE
      CHARACTER*20 TYPE
      EXEC SQL END DECLARE SECTION

      EXEC SQL DECLARE MYCURSOR CURSOR FOR
     +         SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
     +         FROM INFORMATION_SCHEMA.TABLES

C     Connect to default database
      EXEC SQL CONNECT TO '' USER 'SYSADM' USING 'SYSADM'

      EXEC SQL OPEN MYCURSOR

      EXEC SQL FETCH MYCURSOR INTO :SCHEMA, :TABLE, :TYPE
      DO WHILE (SQLSTATE .NE. ‘02000’)
          WRITE(6,100) SCHEMA, TABLE, TYPE
  100     FORMAT(X,A,X,A,X,A)
          EXEC SQL FETCH MYCURSOR INTO :SCHEMA, :TABLE, :TYPE
      END DO

      EXEC SQL CLOSE MYCURSOR
      EXEC SQL COMMIT
      EXEC SQL DISCONNECT DEFAULT
      END

The program is preprocessed as follows:

esql –for simple.efo

The resulting file, ‘simple.for’, is compiled using a FORTRAN-compiler and then linked. When executing the resulting ‘simple’ program, a list of the items selected will be displayed.

You can find a more complete example in example.efo.

Benefits

Embedded SQL is a straightforward way of writing applications that access databases.

Links

Program example: simple.efo, example.efo

See the Programmer’s Manual in the Mimer SQL Documentation set.