Seed7 provides an abstraction layer for database access. There is an application programming interface (API), which defines how a client may access a database. Seed7 accomplishes database independence by using database drivers as abstraction layers between the application and the database. There are database drivers for MySQL, MariaDB, SQLLite, PostgreSQL, Oracle, Firebird, Interbase, Db2, Informix and SQL Server databases. Databases can also be accessed via the ODBC interface. The following example uses the database abstraction API:

const proc: dbDemo is func
  local
    var database: currDb is database.value;
    var sqlStatement: statement is sqlStatement.value;
    var integer: index is 0;
  begin
    currDb := openDatabase(DB_MYSQL, "testDb", "testUser", "testPassword");
    if currDb <> database.value then
      statement := prepare(currDb, "SELECT * FROM testTable");
      execute(statement);
      while fetch(statement) do
        for index range 1 to columnCount(statement) do
          write(column(statement, index, string) <& ", ");
        end for;
        writeln;
      end while;
      close(currDb);
    end if;
  end func;

To open a database connection you need to provide several things:

• The database type used.
• The host where the database is running. An empty host name ("") specifies the local machine ("localhost").
• The port to access the database. A port of 0 specifies the default port of the database.
• The name of the database.
• A database user name.
• The password of the database user.

Depending on the database a corresponding database driver must be used:

Database Type	Driver	Default port	Comment
MySQL	DB_MYSQL	3306
MariaDB	DB_MYSQL	3306
SQLLite	DB_SQLITE		Host must be "" and port must be 0.
PostgreSQL	DB_POSTGRESQL	5432
Oracle	DB_OCI	1521
Firebird	DB_FIRE
Interbase	DB_FIRE
Db2	DB_DB2	50000
Informix	DB_INFORMIX	1526
SQL Server	DB_SQL_SERVER	1433
various DBs	DB_ODBC		Needs an ODBC driver to connect to a database.
Sybase based	DB_TDS	1433	Supports SQL Server and Sybase.

A basic function to open a database is:

const func database: openDatabase (in dbCategory: driver, in string: host,
                                   in integer: port, in string: dbName,
                                   in string: user, in string: password)

The following statements each open a database:

currDb := openDatabase(DB_MYSQL, "www.example.org", 0, "testDb", "testUser", "testPassword");

currDb := openDatabase(DB_MYSQL, "192.0.2.235", 0, "testDb", "testUser", "testPassword");

currDb := openDatabase(DB_POSTGRESQL, "1234:feed::dead:beef", 0, "testDb", "testUser", "testPassword");

currDb := openDatabase(DB_POSTGRESQL, "localhost", 0, "testDb", "testUser", "testPassword");

currDb := openDatabase(DB_OCI, "", 0, "aServiceName", "testUser", "testPassword");

currDb := openDatabase(DB_OCI, "www.example.org", 2345, "aServiceName", "testUser", "testPassword");

currDb := openDatabase(DB_OCI, "192.0.2.235", 0, "aSid", "testUser", "testPassword");

currDb := openDatabase(DB_DB2, "www.example.org", 0, "testDb", "testUser", "testPassword");

currDb := openDatabase(DB_SQL_SERVER, "192.168.1.13", 0, "testDb", "testUser", "testPassword");

currDb := openDatabase(DB_TDS, "192.168.1.13", 0, "testDb", "testUser", "testPassword");

The host can be specified by name (e.g.: "www.example.org"), by an IPv4 address (e.g.: "192.0.2.235") or by an IPv6 address in colon notation (e.g.: "1234:feed::dead:beef"). Specifying "" as host means "localhost".

When using the DB_OCI driver you can either supply a net_service_name from tnsnames.ora or a service_name or a sid as dbName. If tnsnames.ora is used the parameter host must be "" and the parameter port must be 0.

currDb := openDatabase(DB_OCI, "", 0, "tnsnamesOraEntryName", "testUser", "testPassword");

When using the DB_DB2 driver you can either supply a DSN (data source name) from db2cli.ini (or db2dsdriver.cfg) or a database name as dbName. If a DSN is used the parameter host must be "" and the parameter port must be 0.

currDb := openDatabase(DB_DB2, "", 0, "databaseAlias", "testUser", "testPassword");

When using the DB_SQL_SERVER driver you can either supply a database name from the local machine or a database that can be accessed via TCP/IP. To access a database at the local machine the parameter host must be "" and the parameter port must be 0. If TCP/IP is used it must be enabled in the database.

currDb := openDatabase(DB_SQL_SERVER, "", 0, "localDb", "testUser", "testPassword");

A SQLite database can be opened with:

currDb := openDatabase(DB_SQLITE, "", 0, "aDir/dbName", "", "");

currDb := openDatabase(DB_SQLITE, "", 0, "aDir/dbName.db", "", "");

currDb := openDatabase(DB_SQLITE, "", 0, "/c/Users/JohnSmith/dbName", "", "");

The path to the database file (in the example above "aDir/dbName") must use the Seed7 standard path representation. If the database file is specified with a relative path it is searched relative to the current working directory. The database file name can be specified with or without the extension ".db". For a SQLite database host must be "" and port must be 0. Since SQLite works without user and password the parameters user and password are always ignored.

When using the DB_INFORMIX driver you need to specify also a server:

const func database: openDatabase (DB_INFORMIX, in string: host, in integer: port,
                                   in string: server, in string: dbName,
                                   in string: user, in string: password)

A Informix database can be opened with:

currDb := openDatabase(DB_INFORMIX, "", 0, "serverName", "databaseName", "testUser", "testPassword");

currDb := openDatabase(DB_INFORMIX, "www.example.org", 0, "testServer", "testDb", "testUser", "testPassword");

The Seed7 database driver DB_ODBC is special, as the ODBC interface itself allows connecting to several database types. The ODBC interface library accomplishes this by using ODBC drivers. Unfortunately these drivers do come in varying quality. Accessing a database directly via the Seed7 driver should be the preferred method. For SQL Server ODBC is the only connection interface. Opening an ODBC database can be done with the following function:

const func database: openDatabase (DB_ODBC, in string: odbcDriver,
                                   in string: server, in string: dbName,
                                   in string: user, in string: password)

A SQL Server can be opened with:

currDb := openDatabase(DB_ODBC, "sqlserver", "", "", "testUser", "testPassword");

currDb := openDatabase(DB_ODBC, "sqlserver", "", "", "", "");

If server or dbName are empty ("") the default values of the ODBC driver are used. The ODBC driver may provide also default values for user and password.

ODBC uses also data source names (DSNs) to specify possible database connections. It is possible to open an ODBC database by specifying the data source name (DSN) in the parameter dbName. In this case the parameters odbcDriver and server must be "".

currDb := openDatabase(DB_ODBC, "", "", "dsnName", "testUser", "testPassword");

currDb := openDatabase(DB_ODBC, "", "", "dsnName", "", "");

When opening using a DSN succeeds, the parameters odbcDriver and server are ignored. A DSN might also specify user and password. In this case this parameters can be left empty, when openDatabase() is called.

When using windows the DSNs and the ODBC drivers can be managed with:

Start -> Control Panel -> Administrative Tools -> Data Sources (ODBC).

The data source names (DSNs) of unixODBC are specified in the file odbc.ini. The ODBC drivers of unixODBC are specified in the file odbcinst.ini.

There is another variant of opening a database:

const func database: openDatabase (in dbCategory: driver, in string: dbPath,
                                   in string: user, in string: password)

The dbPath can be given in one of these forms:

• host:port/dbName
• host/dbName
• dbName

The host can be specified by name (e.g.: "www.example.org"), or by IPv4 address (e.g.: "192.0.2.235") or by IPv6 address in colon notation (e.g.: "[1234:feed::dead:beef]"). Note that an IPv6 address must be enclosed in brackets. If host is not specified the default host ("localhost") will be used. If port is not specified the default port of the driver will be used. The following statements each open a database:

currDb := openDatabase(DB_MYSQL, "www.example.org:1234/testDb", "testUser", "testPassword");

currDb := openDatabase(DB_MYSQL, "[1234:feed::dead:beef]:1234/testDb", "testUser", "testPassword");

currDb := openDatabase(DB_POSTGRESQL, "192.0.2.235/testDb", "testUser", "testPassword");

currDb := openDatabase(DB_POSTGRESQL, "testDb", "testUser", "testPassword");

currDb := openDatabase(DB_OCI, "sidOrServiceName", "testUser", "testPassword");

currDb := openDatabase(DB_OCI, "tnsnamesOraEntryName", "testUser", "testPassword");

If driver is DB_ODBC the dbPath can be specified in one of these forms:

• odbcDriver:dbServer/dbName
• odbcDriver:dbServer
• dbServer/dbName
• odbcDataSourceName

Use a value like "sqlserver" for odbcDriver.

If driver is DB_SQLITE then dbPath is the path of a database file:

• sqlitePath

The sqlitePath uses the Seed7 standard path representation. If sqlitePath is a relative path the database file is searched relative to the current working directory. The database file name can be specified with or without the extension ".db". The following statements each open a SQLite database:

currDb := openDatabase(DB_SQLITE, "aDir/dbName", "", "");

currDb := openDatabase(DB_SQLITE, "aDir/dbName.db", "", "");

currDb := openDatabase(DB_SQLITE, "/c/Users/JohnSmith/dbName", "", "");

It is also possible ot open a database with a connect string:

const func database: openDatabase (in dbCategory: driver, in string: connectStri)

The connectStri must be in one of the forms

• user:password@dbPath
• user@dbPath
• dbPath

If no user is specified the user "guest" will be used. If no password is specified the password "guest" will be used. The dbPath is specified as before.

All SQL statements (e.g. SELECT, CREATE, INSERT, UPDATE) can be executed by using prepared statements. The database abstraction API does not provide a way to execute SQL without a prepared statement. After a prepared statement has been created it can be executed multiple times. Databases usually optimize prepared statements for fast execution. Prepared statements are created with the following function:

const func sqlStatement: prepare (in database: db, in string: sqlStatementStri)

Regarding the parameter sqlStatementStri some things must be considered:

• A question mark (?) will be used as placeholder for bind variables.
• Strings in a SQL statement are enclosed in apostrophes (e.g. 'abc').
  • A string consists of all characters between the enclosing apostrophes.
  • Apostrophe characters (') in a string must be doubled to get one apostrophe character in the string.
• Comments in a SQL statement start with /* and end with */.
• Line comments in a SQL statement start with -- and end with a newline character ('\n').
• Table and field names can be enclosed in double quotes (e.g. "aTable").
  • This way a table or field name may contain spaces or other characters, that are normally not allowed.
  • Double quotes in a quoted name must be doubled (be aware that several databases do not allow double quotes in names).

The following statements each create a prepared SQL statement:

statement := prepare(currDb, "CREATE TABLE customers (name CHAR(128), area CHAR(128))");

statement := prepare(currDb, "SELECT * FROM customers");

statement := prepare(currDb, "SELECT * FROM customers /* comment */ WHERE name = 'adam'");

statement := prepare(currDb, "SELECT * FROM customers -- Comment\n  WHERE name != 'adam'");

statement := prepare(currDb, "SELECT * FROM customers WHERE name = ?");

statement := prepare(currDb, "SELECT * FROM customers WHERE name LIKE '%''%'");

statement := prepare(currDb, "SELECT \"a field\" FROM \"a table\"");

Preparing a statement fails with a RANGE_ERROR, if the database is not open. This is checked by the driver. Other things are checked by the database. The database might raise the exception DATABASE_ERROR. Note that some databases do not check everything, when preparing the statement. So an invalid SQL statement might be accepted by prepare(), but executing the prepared statement later will fail.

Executing a prepared statement that neither has bind variables nor returns a result is quite simple:

statement := prepare(currDb, "CREATE TABLE customers (name CHAR(128), area CHAR(128))");
execute(statement);

Question marks (?) in a prepared statement string are used as placeholder for bind variables. Before a prepared statement is executed it is necessary to bind values to the placeholders (?). Prepared statements without placeholders do not need a binding. It is possible to bind the same placeholder as often as you like. even with values from different types. This can be used to execute the same prepared statement multiple times with different values. The binding is done with a bind() function. For several types bind() functions are defined:

const proc: bind (inout sqlStatement: statement, in integer: pos, in bigInteger: num)

const proc: bind (inout sqlStatement: statement, in integer: pos, in bigRational: num)

const proc: bind (inout sqlStatement: statement, in integer: pos, in boolean: flag)

const proc: bind (inout sqlStatement: statement, in integer: pos, in bstring: bstri)

const proc: bind (inout sqlStatement: statement, in integer: pos, in float: number)

const proc: bind (inout sqlStatement: statement, in integer: pos, in integer: number)

const proc: bind (inout sqlStatement: statement, in integer: pos, NULL)

const proc: bind (inout sqlStatement: statement, in integer: pos, in string: stri)

const proc: bind (inout sqlStatement: statement, in integer: pos, in time: timeData)

const proc: bind (inout sqlStatement: statement, in integer: pos, in duration: durationData)

Binding fails with a RANGE_ERROR, if the statement has not been prepared. Binding is done by position. Position numbers start with 1. To bind the integer 12345 to the third placeholder (?) of a prepared statement do:

bind(statement, 3, 12345);

The bind() functions check the given position (pos) and raise the exception RANGE_ERROR, if no corresponding placeholder (?) is found. This happens if pos <= 0 holds or if pos is greater than the number of placeholders. The bind() functions check also, if the type of the given value fits to the database column. If the type does not fit the exception RANGE_ERROR is raised.

Another example with binding is:

write("name? ");
readln(name);
statement := prepare(currDb, "SELECT name, area FROM customers WHERE name = ?");
bind(statement, 1, name);
execute(statement);
. . .  Write a list of names and areas.

Binding protects against SQL injection. SQL injection is a technique to attack applications. SQL injection is possible, if the SQL statement is build from user input. Suppose we do the example above without binding:

write("name? ");
readln(name);
statement := prepare(currDb, "SELECT name, area FROM customers WHERE name = '" <& name <& "'");
execute(statement);
. . .  Write a list of names and areas.

Suppose the user enters (when asked for the name):

' UNION SELECT login, password FROM user --

This would prepare and execute the SQL statement:

SELECT name, area FROM customers WHERE name = '' UNION SELECT login, password FROM user --'

The result would additionally contain secret data you certainly would not like to be displayed.

To make a prepared statement run it must be executed. Prepared statements are executed with the following function:

const proc: execute (inout sqlStatement: statement)

The following statement executes a prepared SQL statement:

execute(statement);

Executing fails with a RANGE_ERROR, if the statement has not been prepared. Executing fails with a DATABASE_ERROR, if not all parameters have been bound. If the database returns an error the exception DATABASE_ERROR gets also raised. Note that invalid SQL statements might raise a DATABASE_ERROR in prepare() or in execute(). When the exception is raised depends on the database.

There are prepared statements, that return a result set of records (e.g.: SELECT) and others that do not return data (e.g.: UPDATE). After executing a prepared statement the number of columns in a record (row) of the result set can be retrieved with the following function:

const func integer: columnCount (in sqlStatement: statement)

If a prepared statement does not return data columnCount() returns 0. The names of the columns in a record (row) of the result set can be obtained with the function:

const func string: columnName (in sqlStatement: statement, in integer: column)

These functions can be combined to write the column names of a prepared statement:

execute(statement);
for columnNum range 1 to columnCount(statement) do
  write(columnName(statement, columnNum) <& " ");
end for;
writeln;

After executing a prepared statement that returns data (e.g.: SELECT), the records (rows) can be fetched one by one with fetch(). After executing a prepared statement returning no data fetching is not necessary. Fetching is done with the following function:

const func boolean: fetch (in sqlStatement: statement)

The function returns TRUE, if a record of the result set could be fetched successfully. The function returns FALSE, if there is no data (e.g.: UPDATE) or no more row (record) to fetch. The following example shows how fetch() is usually used:

execute(statement);
while fetch(statement) do
  . . .  Process a record from the result set.
end while;

After a record (row) has been fetched from a result set individual column data can be retrieved with a column() function. For several types column() functions are defined:

const func bigInteger: column (in sqlStatement: statement, in integer: column, attr bigInteger)

const func bigRational: column (in sqlStatement: statement, in integer: column, attr bigRational)

const func boolean: column (in sqlStatement: statement, in integer: column, attr boolean)

const func bstring: column (in sqlStatement: statement, in integer: column, attr bstring)

const func duration: column (in sqlStatement: statement, in integer: column, attr duration)

const func float: column (in sqlStatement: statement, in integer: column, attr float)

const func integer: column (in sqlStatement: statement, in integer: column, attr integer)

const func string: column (in sqlStatement: statement, in integer: column, attr string)

const func time: column (in sqlStatement: statement, in integer: column, attr time)

Getting a column fails with a RANGE_ERROR, if no record has been fetched from the result set. Column numbers start with 1. To get column number four from the current result record (row) as integer do:

column(statement, 4, integer);

The column() functions check the given position (column) and raise the exception RANGE_ERROR, if no corresponding column exists in the result record. This happens if column <= 0 is TRUE or if column is greater than the number of columns (which can be obtained with columnCount()). Database columns can be NULL. This can be checked with the following function:

const func boolean: isNull (in sqlStatement: statement, in integer: column)

The function isNull() returns TRUE, if the specified column is NULL. Otherwise isNull() returns FALSE. The column() functions return a default value, if a column is NULL. The following default values are used:

Type	Default value
bigInteger	0_
bigRational	0_/1_
boolean	FALSE
bstring	bstring("")
duration	duration.value
float	0.0
integer	0
string	""
time	time.value