Connection

Definition

Overview

The odbc_connection module provides a modern Fortran interface for interacting with ODBC (Open Database Connectivity) databases in an object-oriented manner. It encapsulates the complexity of the ODBC API, offering a simplified and intuitive way to establish database connections, execute SQL statements, and manage transactions. The module is designed to work with the odbc_resultset module for handling query results.

This documentation describes the connection type, its methods, and how to use it to connect to a database, execute queries, and manage transactions.

Prerequisites

• A working ODBC driver for your database.
• Fortran compiler supporting modern Fortran (e.g., gfortran, Intel Fortran).
• The odbc_resultset, sql, sqlext, and odbc_constants modules, as well as the c_interop.inc include file.
• The iso_c_binding and iso_fortran_env intrinsic modules.

Module Components

Type: connection

The connection type represents a database connection and provides methods for managing the connection, executing SQL statements, and handling transactions. The type contains private data members for ODBC handles (env, dbc, stmt), connection state, and error handling.

Public Methods

The connection type provides the following public methods:

• open(connstring): Opens a database connection using the provided connection string.
• set_timeout(n): Sets the connection timeout in seconds.
• get_timeout(): Returns the current connection timeout.
• is_open(): Returns .true. if the connection is open, .false. otherwise.
• execute(sql): Executes a non-query SQL statement (e.g., INSERT, UPDATE, DELETE) and returns the number of affected rows.
• execute_query(sql, rslt): Executes a query and stores the results in a resultset object.
• execute_query(sql, cursor_type, scrollable, rslt): Executes a query with a specified cursor type and scrollable option, storing results in a resultset object.
• commit(): Commits the current transaction.
• rollback(): Rolls back the current transaction.
• close(): Closes the database connection and frees resources.

Constructor

• new(this, connstring): Creates a new connection object with the specified ODBC connection string.

Error Handling

Errors are handled by the handle_error subroutine, which retrieves diagnostic information from the ODBC API and throws an error with a message and error code using the throw interface. Errors are written to the standard error unit (stderr) and terminate the program with the error code.

Usage

Creating a Connection

To create a new connection, use the connection constructor with an ODBC connection string. Then, call the open method to establish the connection.

use odbc_connection
 
type(connection) :: conn
character(len=1024) :: connstr
 
connstr = 
conn = connection(connstr)
call conn%open()

Setting Timeout

Set the connection timeout (in seconds) using set_timeout. Retrieve the current timeout with get_timeout.

call conn%set_timeout(30)  ! Set timeout to 30 seconds
print *, conn%get_timeout()  ! prints 30

Executing Non-Query SQL Statements

Use the execute method to run SQL statements that do not return results (e.g., INSERT, UPDATE, DELETE). The method returns the number of affected rows.

integer :: nrows
 
nrows = conn%execute('INSERT INTO users (name, age) VALUES (''Alice'', 30)')
print *, , nrows

Executing Queries

Use execute_query to run SELECT queries and retrieve results in a resultset object. Two variants are available:

1. Basic Query:

   use odbc_resultset
   type(resultset) :: rslt
    
   call conn%execute_query('SELECT name, age FROM users', rslt)
   ! process rslt(see odbc_resultset documentation)

2. Query with Cursor: Specify a cursor type and scrollable option for advanced query handling.

   use odbc_resultset
   use odbc_constants
   type(resultset) :: rslt
    
   call conn%execute_query('SELECT name, age FROM users', SQL_CURSOR_STATIC, .true., rslt)
   ! process rslt with scrollable cursor

   Supported cursor types:

   • SQL_CURSOR_DYNAMIC
   • SQL_CURSOR_FORWARD_ONLY
   • SQL_CURSOR_KEYSET_DRIVEN
   • SQL_CURSOR_STATIC

Managing Transactions

Use commit and rollback to manage transactions.

logical :: success
 
success = conn%commit()    ! Commit transaction
if (.not. success) print *, 
 
success = conn%rollback()  ! Roll back transaction
if (.not. success) print *, 

Closing the Connection

Close the connection to free resources when done.

call conn%close()

The connection is automatically closed when the connection object is finalized (e.g., goes out of scope).

Error Handling

The module includes robust error handling. If an ODBC operation fails, the handle_error subroutine retrieves diagnostic information and calls throw to report the error. For example:

‍! If SQLDriverConnect fails, an error like this may be printed:

! connection error: [Microsoft][ODBC SQL Server Driver]Login failed for user. Error code: -1

Ensure your connection string and database configuration are correct to avoid errors.

Example Program

Here is a complete example demonstrating the usage of the odbc_connection module:

program test_odbc
    use odbc_connection
 
    implicit none
 
    type(connection) :: conn
    type(resultset) :: rslt
    character(len=1024) :: connstr
    integer :: rows
 
    ! Initialize connection
    connstr = 
    conn = connection_new(connstr)
    call conn%open()
 
    ! Set timeout
    call conn%set_timeout(20)
 
    ! Execute non-query
    rows = conn%execute('INSERT INTO users (name, age) VALUES (''Bob'', 25)')
    print *, , rows, 
 
    ! Execute query
    call conn%execute_query('SELECT name, age FROM users', rslt)
 
    ! Commit transaction
    if (conn%commit()) print *, 
 
    ! Close connection
    call conn%close()
end program

Note

• The connstring must be a valid ODBC connection string compatible with your database driver (e.g., SQL Server, PostgreSQL, MySQL).
• Ensure the ODBC driver and database server are properly configured on your system.

Data Types
type	connection
	Represents a database connection with ODBC, managing environment, connection, and statement handles, and providing methods to open connections, execute SQL statements, and manage transactions, with support for odbc_resultset::resultset objects. More...

Methods

close()

procedure, pass, public close

(

class(connection), intent(inout)

this

)

Closes the database connection and frees resources in the connection object.

Parameters

[in,out]

this

The connection object.

Definition at line 50 of file connection.f90.

commit()

procedure, pass, public commit

(

class(connection), intent(inout)

this

)

Commits the current transaction using the connection object.

Parameters

[in,out]

this

The connection object.

Returns

.true. if the commit succeeds, .false. otherwise.

Definition at line 48 of file connection.f90.

connection_finalize()

final connection_finalize ( type(connection), intent(inout) this )

final

Definition at line 51 of file connection.f90.

connection_new()

type(connection) function connection_new

(

character(*), intent(in)

connstring

)

Creates a new connection object with the specified ODBC connection string.

Parameters

[in]

connstring

The ODBC connection string (e.g., "DRIVER={SQL Server};SERVER=localhost;DATABASE=mydb;").

Returns

A new connection object initialized with the connection string.

Definition at line 75 of file connection.f90.

execute()

procedure, pass, public execute	(	class(connection), intent(inout)	this,
		character(*), intent(in)	sql )

Executes a non-query SQL statement (e.g., INSERT, UPDATE, DELETE) and returns the number of affected rows using the connection object.

Parameters

[in,out]	this	The connection object.
[in]	sql	The SQL statement to execute.

Returns

The number of affected rows, or an error code if the operation fails.

Definition at line 43 of file connection.f90.

execute_query() [1/2]

generic, public execute_query	(	class(connection), intent(inout)	this,
		character(*), intent(in)	sql,
		integer(c_short), intent(in), target	cursor_type,
		logical, intent(in)	scrollable,
		type(resultset), intent(inout)	rslt )

Executes a query with a specified cursor type and scrollable option, storing results in a odbc_resultset::resultset object.

Parameters

[in,out]	this	The connection object.
[in]	sql	The SQL query to execute.
[in]	cursor_type	The cursor type (e.g., SQL_CURSOR_STATIC, SQL_CURSOR_FORWARD_ONLY).
[in]	scrollable	Whether the cursor is scrollable (.true. or .false.).
[in,out]	rslt	The odbc_resultset::resultset object to store the query results.

Definition at line 46 of file connection.f90.

execute_query() [2/2]

generic, public execute_query	(	class(connection), intent(inout)	this,
		character(*), intent(in)	sql,
		type(resultset), intent(inout)	rslt )

Executes a query and stores results in a odbc_resultset::resultset object.

Parameters

[in,out]	this	The connection object.
[in]	sql	The SQL query to execute.
[in,out]	rslt	The odbc_resultset::resultset object to store the query results.

Definition at line 46 of file connection.f90.

get_timeout()

procedure, pass, public get_timeout

(

class(connection), intent(in)

this

)

Opens a database connection using the stored connection string in the connection object.

Parameters

[in,out]

this

The connection object.

Definition at line 39 of file connection.f90.

is_open()

procedure, pass, public is_open

(

class(connection), intent(in)

this

)

Checks if the connection is open.

Parameters

[in]

this

The connection object.

Returns

.true. if the connection is open, .false. otherwise.

Definition at line 40 of file connection.f90.

open()

generic, public open

(

class(connection), intent(inout)

this

)

Opens a database connection using the stored connection string in the connection object.

Parameters

[in,out]

this

The connection object.

Definition at line 42 of file connection.f90.

rollback()

procedure, pass, public rollback

(

class(connection), intent(inout)

this

)

Rolls back the current transaction using the connection object.

Parameters

[in,out]

this

The connection object.

Returns

.true. if the rollback succeeds, .false. otherwise.

Definition at line 49 of file connection.f90.

set_timeout()

procedure, pass, public set_timeout	(	class(connection), intent(inout)	this,
		integer, intent(in)	n )

Sets the connection timeout in seconds for the connection object.

Parameters

[in,out]	this	The connection object.
[in]	n	The timeout value in seconds.

Definition at line 38 of file connection.f90.

throw_i2()

subroutine throw_i2	(	character(*), intent(in)	msg,
		integer(sqlsmallint), intent(in)	ierr )

Definition at line 341 of file connection.f90.

throw_i4()

subroutine throw_i4	(	character(*), intent(in)	msg,
		integer(sqlinteger), intent(in)	ierr )

Definition at line 349 of file connection.f90.