const type: dbCategory

Enumeration type to describe database drivers.

• NO_DB No database
• DB_MYSQL MySql/MariaDb
• DB_SQLITE SQLLite
• DB_POSTGRESQL PostgreSQL
• DB_OCI Oracle
• DB_ODBC Odbc
• DB_FIRE Firebird/Interbase
• DB_DB2 Db2
• DB_SQL_SERVER SQL Server
• DB_TDS Tabular Data Stream

• DB_INFORMIX Informix

const type: database

Abstract data type to store database connections.

const type: sqlStatement

Abstract data type to store a prepared sql statement.

const database: (attr database) . value

Default value of database (empty database).

const func boolean: (in database: db1) = (in database: db2)

Check if two database connections are equal.

Returns:

TRUE if the two database connections are equal, FALSE otherwise.

const func boolean: (in database: db1) <> (in database: db2)

Check if two database connections are not equal.

Returns:

FALSE if the two database connections are equal, TRUE otherwise.

const sqlStatement: (attr sqlStatement) . value

Default value of sqlStatement (empty prepared sql statement).

const func boolean: (in sqlStatement: stmt1) = (in sqlStatement: stmt2)

Check if two prepared sql statements are equal.

Returns:

TRUE if the two prepared sql statements are equal, FALSE otherwise.

const func boolean: (in sqlStatement: stmt1) <> (in sqlStatement: stmt2)

Check if two prepared sql statements are not equal.

Returns:

FALSE if the two prepared sql statements are equal, TRUE otherwise.

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

Open database with host, port, dbName user and password.

Parameters:

driver - Database driver to be used.

host - The host can be specified by name, with an IPv4 address or with an IPv6 address. If host is empty it is assumed to be "localhost".

port - The port to be used to access the database. If port is zero the default port of the database or driver is used.

dbName - The name of the database at the specified host.

user - Database user name.

password - Database password.

Returns:

the database connection.

Raises:

RANGE_ERROR - If dbPath, user or password cannot be converted to the character set of the database.

DATABASE_ERROR - If the DLL of the database could not be found, or the connection to the database failed.

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

Open ODBC database with odbcDriver, server, dbName user and password.

Parameters:

driver - Database driver to be used (DB_ODBC).

odbcDriver - The name of the ODBC driver (e.g.: "sqlserver").

server - The database server to be used.

dbName - The name of the database at the specified server.

user - Database user name.

password - Database password.

Returns:

the database connection.

Raises:

RANGE_ERROR - If dbPath, user or password cannot be converted to the character set of the database.

DATABASE_ERROR - If the DLL of the database could not be found, or the connection to the database failed.

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

Open Informix database with host, port, server, dbName user and password.

Parameters:

driver - Database driver to be used (DB_INFORMIX).

host - The host can be specified by name, with an IPv4 address or with an IPv6 address. If host is empty it is assumed to be "localhost".

port - The port to be used to access the database. If port is zero the default port of the database or driver is used.

server - The database server to be used.

dbName - The name of the database at the specified server.

user - Database user name.

password - Database password.

Returns:

the database connection.

Raises:

RANGE_ERROR - If dbPath, user or password cannot be converted to the character set of the database.

DATABASE_ERROR - If the DLL of the database could not be found, or the connection to the database failed.

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

Open the database dbPath with the specified user and password. The dbPath can be in one of the forms:

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

The host can be specified with name (e.g.: www.example.org), or with IPv4 address (e.g.: 192.0.2.235) or as IPv6 address in colon notation (e.g.: [1234:feed::dead:beef]). Note that an IPv6 address must be enclosed in brackets.

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

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

The odbcDriver can have a value like sqlserver.

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

• sqlitePath

The sqlitePath uses the Seed7 standard path representation.

Parameters:

driver - Database driver to be used.

dbPath - Database name in one of the forms listed above. If no host is specified the host "localhost" is used. If no port is specified the port of the database driver is used. If no odbcDriver is specified "sqlserver" is used.

user - Database user name.

password - Database password.

Returns:

the database connection.

Raises:

RANGE_ERROR - If dbPath, user or password cannot be converted to the character set of the database.

DATABASE_ERROR - If the DLL of the database could not be found, or the connection to the database failed.

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

Open a database with the specified driver and connectStri. The connectStri must be in one of the forms

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

The dbPath can be specified with host name ("e.g.: "www.example.org/myDb"), or with IPv4 address in standard dot notation (e.g.: "192.0.2.235/myDb"). Operating systems supporting IPv6 may also accept an IPv6 address in colon notation. Some databases allow also the specification of a port number (e.g.: "localhost:1234/myDb" or "[1234:feed::dead:beef]:1234/myDb", with an IPv6 address).

Parameters:

driver - Database driver to be used.

connectStri - Connection string in one of the forms listed above. If no user is specified the user "guest" is used. If no password is specified the password "guest" is used.

Returns:

the database connection.

Raises:

RANGE_ERROR - If dbPath, user or password cannot be converted to the character set of the database.

DATABASE_ERROR - If the DLL of the database could not be found, or the connection to the database failed.

const proc: close (in database: db)

Close the specified database db.

Parameters:

database - Database to be closed.

Raises:

RANGE_ERROR - If the database was not open.

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

Create a prepared statement for the given database db.

Parameters:

db - Database connection for which the prepared statement should be created.

sqlStatementStri - SQL statement in a string. For bind variables use a question mark (?).

Raises:

RANGE_ERROR - If the database is not open.

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

Bind a bigInteger parameter to a prepared SQL statement.

Parameters:

statement - Prepared statement.

pos - Position of the bind variable (starting with 1).

num - Value for the binding.

Raises:

RANGE_ERROR - If the statement was not prepared or if pos is negative or too big or if num cannot be converted.

DATABASE_ERROR - If a database function fails.

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

Bind a bigRational parameter to a prepared SQL statement.

Parameters:

statement - Prepared statement.

pos - Position of the bind variable (starting with 1).

bigRatData - Value for the binding.

Raises:

RANGE_ERROR - If the statement was not prepared or if pos is negative or too big or if bigRatData cannot be converted.

DATABASE_ERROR - If a database function fails.

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

Bind a boolean parameter to a prepared SQL statement.

Parameters:

statement - Prepared statement.

pos - Position of the bind variable (starting with 1).

flag - Value for the binding.

Raises:

RANGE_ERROR - If the statement was not prepared or if pos is negative or too big or if flag cannot be converted.

DATABASE_ERROR - If a database function fails.

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

Bind a bstring parameter to a prepared SQL statement.

Parameters:

statement - Prepared statement.

pos - Position of the bind variable (starting with 1).

bstri - Value for the binding.

Raises:

RANGE_ERROR - If the statement was not prepared or if pos is negative or too big or if bstri cannot be converted.

DATABASE_ERROR - If a database function fails.

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

Bind a float parameter to a prepared SQL statement.

Parameters:

statement - Prepared statement.

pos - Position of the bind variable (starting with 1).

number - Value for the binding.

Raises:

RANGE_ERROR - If the statement was not prepared or if pos is negative or too big or if number cannot be converted.

DATABASE_ERROR - If a database function fails.

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

Bind an integer parameter to a prepared SQL statement.

Parameters:

statement - Prepared statement.

pos - Position of the bind variable (starting with 1).

number - Value for the binding.

Raises:

RANGE_ERROR - If the statement was not prepared or if pos is negative or too big or if number cannot be converted.

DATABASE_ERROR - If a database function fails.

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

Bind a NULL parameter to a prepared SQL statement.

Parameters:

statement - Prepared statement.

pos - Position of the bind variable (starting with 1).

Raises:

RANGE_ERROR - If the statement was not prepared or if pos is negative or too big.

DATABASE_ERROR - If a database function fails.

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

Bind a string parameter to a prepared SQL statement.

Parameters:

statement - Prepared statement.

pos - Position of the bind variable (starting with 1).

stri - Value for the binding.

Raises:

RANGE_ERROR - If the statement was not prepared or if pos is negative or too big or if stri cannot be converted.

DATABASE_ERROR - If a database function fails.

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

Bind a time parameter to a prepared SQL statement.

Parameters:

statement - Prepared statement.

pos - Position of the bind variable (starting with 1).

timeData - Value for the binding.

Raises:

RANGE_ERROR - If the statement was not prepared or if pos is negative or too big or if timeData cannot be converted.

DATABASE_ERROR - If a database function fails.

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

Bind a duration parameter to a prepared SQL statement.

Parameters:

statement - Prepared statement.

pos - Position of the bind variable (starting with 1).

durationData - Value for the binding.

Raises:

RANGE_ERROR - If the statement was not prepared or if pos is negative or too big or if durationData cannot be converted.

DATABASE_ERROR - If a database function fails.

const proc: execute (inout sqlStatement: statement)

Execute the specified prepared SQL statement. Bind variable can be assigned with the function bind before execute is called.

Parameters:

statement - Prepared statement, which should be executed.

Raises:

DATABASE_ERROR - If a database function fails.

const func boolean: fetch (in sqlStatement: statement)

Fetch a row from the result data of an executed statement. After the statement has been executed successfully the function fetch can be used to get the first and further rows of the statements result data. The columns of the result data can be obtained with the column functions.

Parameters:

statement - Prepared statement, which has been executed.

Returns:

TRUE if a row of result data could be fetched successfully. FALSE if no more result data is available.

Raises:

DATABASE_ERROR - If a database function fails.

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

Get the specified column of fetched data as bigInteger. If the column data is NULL it is interpreted as 0_. The function isNull can distinguish NULL from 0_.

Parameters:

statement - Prepared statement for which data was fetched.

column - Number of the column (starting with 1).

Returns:

the column data converted to a bigInteger or 0_, if the column data is NULL.

Raises:

RANGE_ERROR - If the statement was not prepared or if no data was successfully fetched or if the specified column does not exist or if the column cannot be converted.

DATABASE_ERROR - If a database function fails.

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

Get the specified column of fetched data as bigRational. If the column data is NULL it is interpreted as 0_/1_. The function isNull can distinguish NULL from 0_/1_.

Parameters:

statement - Prepared statement for which data was fetched.

column - Number of the column (starting with 1).

Returns:

the column data converted to a bigRational or 0_/1_, if the column data is NULL.

Raises:

RANGE_ERROR - If the statement was not prepared or if no data was successfully fetched or if the specified column does not exist or if the column cannot be converted.

DATABASE_ERROR - If a database function fails.

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

Get the specified column of fetched data as boolean. If the column data is NULL it is interpreted as FALSE. The function isNull can distinguish NULL from FALSE.

Parameters:

statement - Prepared statement for which data was fetched.

column - Number of the column (starting with 1).

Returns:

the column data converted to a boolean or FALSE, if the column data is NULL.

Raises:

RANGE_ERROR - If the statement was not prepared or if no data was successfully fetched or if the specified column does not exist or if the column cannot be converted.

DATABASE_ERROR - If a database function fails.

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

Get the specified column of fetched data as bstring. If the column data is NULL it is interpreted as empty bstring. The function isNull can distinguish NULL from an empty bstring.

Parameters:

statement - Prepared statement for which data was fetched.

column - Number of the column (starting with 1).

Returns:

the column data converted to a bstring or an empty bstring, if the column data is NULL.

Raises:

RANGE_ERROR - If the statement was not prepared or if no data was successfully fetched or if the specified column does not exist or if the column cannot be converted.

DATABASE_ERROR - If a database function fails.

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

Get the specified column of fetched data as duration. If the column data is NULL it is interpreted as empty duration. The function isNull can distinguish NULL from an empty duration.

Parameters:

statement - Prepared statement for which data was fetched.

column - Number of the column (starting with 1).

Returns:

the column data converted to a duration or an empty duration, if the column data is NULL.

Raises:

RANGE_ERROR - If the statement was not prepared or if no data was successfully fetched or if the specified column does not exist or if the column cannot be converted.

DATABASE_ERROR - If a database function fails.

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

Get the specified column of fetched data as float. If the column data is NULL it is interpreted as 0.0. The function isNull can distinguish NULL from 0.0.

Parameters:

statement - Prepared statement for which data was fetched.

column - Number of the column (starting with 1).

Returns:

the column data converted to a float or 0.0, if the column data is NULL.

Raises:

RANGE_ERROR - If the statement was not prepared or if no data was successfully fetched or if the specified column does not exist or if the column cannot be converted.

DATABASE_ERROR - If a database function fails.

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

Get the specified column of fetched data as integer. If the column data is NULL it is interpreted as 0. The function isNull can distinguish NULL from 0.

Parameters:

statement - Prepared statement for which data was fetched.

column - Number of the column (starting with 1).

Returns:

the column data converted to an integer or 0, if the column data is NULL.

Raises:

RANGE_ERROR - If the statement was not prepared or if no data was successfully fetched or if the specified column does not exist or if the column cannot be converted.

DATABASE_ERROR - If a database function fails.

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

Get the specified column of fetched data as string. If the column data is NULL it is interpreted as "". The function isNull can distinguish NULL from "".

Parameters:

statement - Prepared statement for which data was fetched.

column - Number of the column (starting with 1).

Returns:

the column data converted to a string or "", if the column data is NULL.

Raises:

RANGE_ERROR - If the statement was not prepared or if no data was successfully fetched or if the specified column does not exist or if the column cannot be converted.

DATABASE_ERROR - If a database function fails.

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

Get the specified column of fetched data as time. If the column data is NULL it is interpreted as 0-01-01 00:00:00. The function isNull can distinguish NULL from 0-01-01 00:00:00.

Parameters:

statement - Prepared statement for which data was fetched.

column - Number of the column (starting with 1).

Returns:

the column data converted to a time or 0-01-01 00:00:00, if the column data is NULL.

Raises:

RANGE_ERROR - If the statement was not prepared or if no data was successfully fetched or if the specified column does not exist or if the column cannot be converted.

DATABASE_ERROR - If a database function fails.

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

Determine if the specified column of fetched data is NULL.

Parameters:

statement - Prepared statement for which data was fetched.

column - Number of the column (starting with 1).

Returns:

TRUE if the column data is NULL, FALSE otherwise.

Raises:

RANGE_ERROR - If the statement was not prepared or if no data was successfully fetched or if the specified column does not exist.

const func boolean: getAutoCommit (in database: db)

Get the current auto-commit mode for the specified database 'database'.

const proc: setAutoCommit (in database: db, in boolean: autoCommit)

Set the auto-commit mode for the specified database 'database'.

const proc: commit (in database: db)

Execute a commit statement for the specified database db.

const proc: rollback (in database: db)

Execute a rollback statement for the specified database db.

const func integer: columnCount (in sqlStatement: statement)

Return the number of columns in the result data of a statement. It is not necessary to execute the prepared statement, before columnCount is called.

Parameters:

statement - Prepared statement.

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

Return the name of a column in the result data of a statement. It is not necessary to execute the prepared statement, before columnName is called.

Parameters:

statement - Prepared statement.

column - Number of the column (starting with 1).