SQLite

SQLite is a public domain relational database management system (RDBMS) that can be easily embedded into applications. The library does not require a separate server process, as the database is entirely file-based. Most of the query features of the SQL-92 standard are supported. Multiple threads or processes may access a single database concurrently.

The FLIBS library includes non-standard wrapper routines to access the SQLite 3 API from Fortran 90. Modern ISO_C_BINDING interfaces for Fortran 2018 are provided by fortran-sqlite3.

fortran-sqlite3

At first, install SQLite 3 with development headers. On FreeBSD, simply add the package databases/sqlite3:

# pkg install databases/sqlite3

Then, clone the fortran-sqlite3 repository, and build the static library libfortran-sqlite3.a; either with make:

$ git clone https://github.com/interkosmos/fortran-sqlite3
$ make

Or, by running fpm:

$ fpm build

Finally, link your application against libfortran-sqlite3.a and -lsqlite3 to access SQLite from Fortran.

Example

The example program opens SQLite database example.db, and create a new table example_table of the following SQL schema:

CREATE TABLE IF NOT EXISTS example_table (
    id     INTEGER PRIMARY KEY,
    string VARCHAR(32),
    value  INTEGER
);

Then, a row is inserted and printed to console.

! example.f90
program main
    use, intrinsic :: iso_c_binding, only: c_ptr
    use :: sqlite
    implicit none
    character(len=*), parameter :: DB_FILE  = 'example.db'
    character(len=*), parameter :: DB_TABLE = 'example_table'

    character(len=:), allocatable :: errmsg ! Error message.
    integer                       :: rc     ! Return code.
    type(c_ptr)                   :: db     ! SQLite database.
    type(c_ptr)                   :: stmt   ! SQLite statement.
    type(c_ptr)                   :: udp    ! User-data pointer.

    ! Open SQLite database.
    rc = error(sqlite3_open(DB_FILE, db), 'sqlite3_open()')
    if (rc /= SQLITE_OK) stop

    ! Create table.
    rc = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS " // DB_TABLE // " (" // &
                          "id     INTEGER PRIMARY KEY," // &
                          "string VARCHAR(32)," // &
                          "value  INTEGER)", c_null_ptr, c_null_ptr, errmsg)
    rc = error(rc, 'sqlite3_exec()')

    ! Insert values through prepared statement.
    rc = sqlite3_prepare(db, "INSERT INTO " // DB_TABLE // "(string, value) VALUES(?, ?)", stmt)
    rc = error(rc, 'sqlite3_prepare()')

    ! Bind values to prepared statement.
    rc = error(sqlite3_bind_text(stmt, 1, 'one'), 'sqlite3_bind_text()')
    rc = error(sqlite3_bind_int(stmt, 2, 12345), 'sqlite3_bind_int()')

    ! Insert bound values into database.
    rc = error(sqlite3_step(stmt), 'sqlite3_step()')

    ! Clean-up prepared statement.
    rc = error(sqlite3_finalize(stmt), 'sqlite3_finalize()')

    ! Read values from database and print them to stdout.
    rc = error(sqlite3_prepare(db, "SELECT * FROM " // DB_TABLE, stmt), 'sqlite3_prepare()')

    do while (sqlite3_step(stmt) /= SQLITE_DONE)
        call print_values(stmt, 3)
    end do

    ! Clean-up prepared statement.
    rc = error(sqlite3_finalize(stmt), 'sqlite3_finalize()')

    ! Close SQLite handle.
    rc = error(sqlite3_close(db), 'sqlite3_close()')
contains
    function error(code, str)
        !! Prints error message.
        integer,          intent(in)           :: code
        character(len=*), intent(in), optional :: str
        integer                                :: error

        error = code

        if (code == SQLITE_OK .or. code == SQLITE_DONE) return

        if (present(str)) then
            print '(a, ": failed")', str
            return
        end if

        print '("unknown error")'
    end function error

    subroutine print_values(stmt, ncols)
        !! Prints then `ncols` columns of the given SQLite statement.
        type(c_ptr), intent(inout) :: stmt
        integer,     intent(in)    :: ncols
        integer                    :: col_type
        integer                    :: i

        do i = 0, ncols - 1
            col_type = sqlite3_column_type(stmt, i)

            select case (col_type)
                case (SQLITE_INTEGER)
                    write (*, '(i12)', advance='no') sqlite3_column_int(stmt, i)

                case (SQLITE_FLOAT)
                    write (*, '(f0.8)', advance='no') sqlite3_column_double(stmt, i)

                case (SQLITE_TEXT)
                    write (*, '(a12)', advance='no') sqlite3_column_text(stmt, i)

                case default
                    write (*, '(" unsupported")', advance='no')
            end select
        end do

        print *
    end subroutine print_values
end program main

Compile, link, and run the demo program with:

$ gfortran10 -o example example.f90 libfortran-sqlite3.a -lsqlite3
$ ./example
           1         one       12345

Executing the program multiple times will add more rows to the table.

Libraries

References