Connection.h File Reference

Detailed Description

A Connection represents a connection to a SQL database system.

Use a Connection to execute SQL statements. There are three ways to execute statements: Connection_execute() is used to execute SQL statements that do not return a result set. Such statements are INSERT, UPDATE or DELETE. Connection_executeQuery() is used to execute a SQL SELECT statement and return a result set. These methods can only handle values which can be expressed as C-strings. If you need to handle binary data, such as inserting a blob value into the database, use a PreparedStatement object to execute the SQL statement. The factory method Connection_prepareStatement() is used to obtain a PreparedStatement object.

The method Connection_executeQuery() will return an empty ResultSet (not null) if the SQL statement did not return any values. A ResultSet is valid until the next call to Connection execute or until the Connection is returned to the Connection Pool. If an error occurs during execution, an SQLException is thrown.

Any SQL statement that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed at the conclusion of the command.

Transactions can also be started manually using Connection_beginTransaction(). Such transactions usually persist until the next call to Connection_commit() or Connection_rollback(). A transaction will also rollback if the database is closed or if an error occurs. Nested transactions are not allowed.

Examples

Basic Query Execution

Connection_T con = ConnectionPool_getConnection(pool);
if (con) {
    ResultSet_T result = Connection_executeQuery(con, "SELECT name, age FROM users WHERE id = %d", 1);
    if (ResultSet_next(result)) {
        const char* name = ResultSet_getString(result, 1);
        int age = ResultSet_getInt(result, 2);
        printf("Name: %s, Age: %d\n", valueOr(name, "N/A"), age);
    }
    Connection_close(con);
}

Transaction Example

Connection_T con = NULL;
TRY
{
    con = ConnectionPool_getConnectionOrException(pool);
    Connection_beginTransaction(con);
    Connection_execute(con, "UPDATE accounts SET balance = balance - %f WHERE id = %d", 100.0, 1);
    Connection_execute(con, "UPDATE accounts SET balance = balance + %f WHERE id = %d", 100.0, 2);
    Connection_commit(con);
    printf("Transfer successful\n");
}
ELSE
{
    // The error message in Exception_frame.message specify the error that occured
    printf("Transfer failed: %s\n", Exception_frame.message);
 
    // Connection_close() will automatically call Connection_rollback() if
    // the connection is in an uncommitted transaction
}
FINALLY
{
   if (con) Connection_close(con);
}
END_TRY;

Using PreparedStatement

Connection_T con = ConnectionPool_getConnection(pool);
if (con) {
    const char *sql = "INSERT INTO logs (message, timestamp) VALUES (?, ?)";
    PreparedStatement_T stmt = Connection_prepareStatement(con, sql);
    PreparedStatement_setString(stmt, 1, "User logged in");
    PreparedStatement_setTimestamp(stmt, 2, time(NULL));
    PreparedStatement_execute(stmt);
    printf("Rows affected: %lld\n", PreparedStatement_rowsChanged(stmt));
    Connection_close(con);
}

A Connection is reentrant, but not thread-safe and should only be used by one thread (at a time).

Note

When Connection_close() is called on a Connection object, it is automatically returned to the pool. If the connection is still in a transaction at this point, the transaction will be automatically rolled back. This ensures data integrity even when exceptions occur. It's recommended to always call Connection_close() in a FINALLY block to guarantee proper resource management and transaction handling. See the Transaction Example above for a practical demonstration of this behavior.

See also

ResultSet.h PreparedStatement.h SQLException.h

Macros
#define	T   Connection_T

Typedefs
typedef struct Connection_S *	T

Enumerations
enum	TRANSACTION_TYPE {   TRANSACTION_DEFAULT = 0 , TRANSACTION_READ_UNCOMMITTED , TRANSACTION_READ_COMMITTED , TRANSACTION_REPEATABLE_READ ,   TRANSACTION_SERIALIZABLE , TRANSACTION_IMMEDIATE , TRANSACTION_EXCLUSIVE }
	Enum representing different transaction isolation levels and behaviors. More...

Functions
Properties
void	Connection_setQueryTimeout (T C, int ms)
	Sets the query timeout for this Connection.
int	Connection_getQueryTimeout (T C)
	Gets the query timeout for this Connection.
void	Connection_setMaxRows (T C, int max)
	Sets the maximum number of rows for ResultSet objects.
int	Connection_getMaxRows (T C)
	Gets the maximum number of rows for ResultSet objects.
void	Connection_setFetchSize (T C, int rows)
	Sets the number of rows to fetch for ResultSet objects.
int	Connection_getFetchSize (T C)
	Gets the number of rows to fetch for ResultSet objects.
URL_T	Connection_getURL (T C)
	Gets this Connections URL.
Functions
bool	Connection_ping (T C)
	Pings the database server to check if the connection is alive.
void	Connection_clear (T C)
	Clears any ResultSet and PreparedStatements in the Connection.
void	Connection_close (T C)
	Returns the connection to the connection pool.
void	Connection_beginTransaction (T C)
	Begins a new (default) transaction.
void	Connection_beginTransactionType (T C, TRANSACTION_TYPE type)
	Begins a new specific transaction.
bool	Connection_inTransaction (T C)
	Checks if this Connection is in an uncommitted transaction.
void	Connection_commit (T C)
	Commits the current transaction.
void	Connection_rollback (T C)
	Rolls back the current transaction.
long long	Connection_lastRowId (T C)
	Gets the last inserted row ID for auto-increment columns.
long long	Connection_rowsChanged (T C)
	Gets the number of rows affected by the last execute() statement.
void	Connection_execute (T C, const char *sql,...)
	Executes a SQL statement, with or without parameters.
ResultSet_T	Connection_executeQuery (T C, const char *sql,...)
	Executes a SQL query and returns a ResultSet.
PreparedStatement_T	Connection_prepareStatement (T C, const char *sql,...)
	Prepares a SQL statement for execution.
const char *	Connection_getLastError (T C)
	Gets the last SQL error message.
Class functions
bool	Connection_isSupported (const char *url)
	Checks if the specified database system is supported.

Macro Definition Documentation

T

#define T   Connection_T

Typedef Documentation

T

typedef struct Connection_S* T

Enumeration Type Documentation

TRANSACTION_TYPE

enum TRANSACTION_TYPE

Enum representing different transaction isolation levels and behaviors.

Support for specific types varies depending on the database system being used.

Note: All transactions must be explicitly ended with either a commit or a rollback operation, regardless of the isolation level or database system.

Enumerator
TRANSACTION_DEFAULT	Use the default transaction behavior of the underlying database system. MySQL: REPEATABLE READ PostgreSQL: READ COMMITTED Oracle: READ COMMITTED SQLite: SERIALIZABLE
TRANSACTION_READ_UNCOMMITTED	Lowest isolation level. Transactions can read uncommitted data. Supported by: MySQL. Not supported by: PostgreSQL, Oracle, SQLite
TRANSACTION_READ_COMMITTED	Prevents dirty reads. A transaction only sees data committed before the transaction began. Supported by: MySQL, PostgreSQL, Oracle. Not applicable to SQLite (always SERIALIZABLE)
TRANSACTION_REPEATABLE_READ	Prevents non-repeatable reads. Supported by: MySQL, PostgreSQL. Not supported by: Oracle. Not applicable to SQLite (always SERIALIZABLE)
TRANSACTION_SERIALIZABLE	Highest isolation level. Prevents dirty reads, non-repeatable reads, and phantom reads. Supported by: MySQL, PostgreSQL, Oracle. Default and only level for SQLite
TRANSACTION_IMMEDIATE	SQLite-specific. Starts a transaction immediately, acquiring a RESERVED lock. Not applicable to other database systems.
TRANSACTION_EXCLUSIVE	SQLite-specific. Starts a transaction and acquires an EXCLUSIVE lock immediately. Not applicable to other database systems.

Function Documentation

Connection_setQueryTimeout()

void Connection_setQueryTimeout	(	T	C,
		int	ms )

Sets the query timeout for this Connection.

If the limit is exceeded, the statement will return immediately with an error. The timeout is set per connection/session. Not all database systems support query (SELECT) timeout. The default is no query timeout.

Parameters

C	A Connection object
ms	The query timeout in milliseconds; zero (the default) means there is no timeout limit.

Connection_getQueryTimeout()

int Connection_getQueryTimeout

(

T

C

)

Gets the query timeout for this Connection.

Parameters

C	A Connection object

Returns

The query timeout limit in milliseconds; zero means there is no timeout limit

Connection_setMaxRows()

void Connection_setMaxRows	(	T	C,
		int	max )

Sets the maximum number of rows for ResultSet objects.

If the limit is exceeded, the excess rows are silently dropped.

Parameters

C	A Connection object
max	The new max rows limit; 0 (the default) means there is no limit

Connection_getMaxRows()

int Connection_getMaxRows

(

T

C

)

Gets the maximum number of rows for ResultSet objects.

Parameters

C	A Connection object

Returns

The max rows limit; 0 means there is no limit

Connection_setFetchSize()

void Connection_setFetchSize	(	T	C,
		int	rows )

Sets the number of rows to fetch for ResultSet objects.

The default value is 100, meaning that a ResultSet will prefetch rows in batches of 100 rows to reduce the network roundtrip to the database. This value can also be set via the URL parameter fetch-size to apply to all connections. This method and the concept of pre-fetching rows are only applicable to MySQL and Oracle.

Parameters

C	A Connection object
rows	The number of rows to fetch (1..INT_MAX)

Exceptions

AssertException

If `rows` is less than 1

Connection_getFetchSize()

int Connection_getFetchSize

(

T

C

)

Gets the number of rows to fetch for ResultSet objects.

Parameters

C	A Connection object

Returns

The number of rows to fetch

Connection_getURL()

URL_T Connection_getURL

(

T

C

)

Gets this Connections URL.

Parameters

C	A Connection object

Returns

This Connections URL

See also

URL.h

Connection_ping()

bool Connection_ping

(

T

C

)

Pings the database server to check if the connection is alive.

Parameters

C	A Connection object

Returns

true if the connection is alive, false otherwise.

Connection_clear()

void Connection_clear

(

T

C

)

Clears any ResultSet and PreparedStatements in the Connection.

Normally it is not necessary to call this method, but for some implementations (SQLite) it may, in some situations, be necessary to call this method if an execution sequence error occurs.

Parameters

C	A Connection object

Connection_close()

void Connection_close

(

T

C

)

Returns the connection to the connection pool.

The same as calling ConnectionPool_returnConnection() on a connection. If the connection is in an uncommitted transaction, rollback is called. It is an unchecked error to attempt to use the Connection after this method is called

Parameters

C	A Connection object

Connection_beginTransaction()

void Connection_beginTransaction

(

T

C

)

Begins a new (default) transaction.

Parameters

C	A Connection object

Exceptions

SQLException

If a database error occurs

See also

SQLException.h

Note

All transactions must be ended with either Connection_commit() or Connection_rollback(). Nested transactions are not supported.

Connection_beginTransactionType()

void Connection_beginTransactionType	(	T	C,
		TRANSACTION_TYPE	type )

Begins a new specific transaction.

This method is similar to Connection_beginTransaction() except it allows you to specify the new transaction's isolation level explicitly. Connection_beginTransaction() uses the default isolation level for the database.

Parameters

C	A Connection object
type	The transaction type to start

See also

TRANSACTION_TYPE enum for available options.

Exceptions

SQLException

If a database error occurs

See also

SQLException.h

Note

All transactions must be ended with either Connection_commit() or Connection_rollback(). Nested transactions are not supported.

Connection_inTransaction()

bool Connection_inTransaction

(

T

C

)

Checks if this Connection is in an uncommitted transaction.

Parameters

C	A Connection object

Returns

true if in a transaction, false otherwise.

Connection_commit()

void Connection_commit

(

T

C

)

Commits the current transaction.

Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object.

Parameters

C	A Connection object

Exceptions

SQLException

If a database error occurs

See also

SQLException.h

Connection_rollback()

void Connection_rollback

(

T

C

)

Rolls back the current transaction.

Undoes all changes made in the current transaction and releases any database locks currently held by this Connection object. This method will first call Connection_clear() before performing the rollback to clear any statements in progress such as selects.

Parameters

C	A Connection object

Exceptions

SQLException

If a database error occurs

See also

SQLException.h

Connection_lastRowId()

long long Connection_lastRowId

(

T

C

)

Gets the last inserted row ID for auto-increment columns.

Parameters

C	A Connection object

Returns

The value of the rowid from the last insert statement

Connection_rowsChanged()

long long Connection_rowsChanged

(

T

C

)

Gets the number of rows affected by the last execute() statement.

If used with a transaction, this method should be called before commit is executed, otherwise 0 is returned.

Parameters

C	A Connection object

Returns

The number of rows changed by the last (DIM) SQL statement

Connection_execute()

void Connection_execute	(	T	C,
		const char *	sql,
			... )

Executes a SQL statement, with or without parameters.

Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement. Several SQL statements can be used in the sql parameter string, each separated with the ; SQL statement separator character. Note, calling this method clears any previous ResultSets associated with the Connection.

Parameters

C	A Connection object
sql	A SQL statement

Exceptions

SQLException

If a database error occurs.

See also

SQLException.h

Connection_executeQuery()

ResultSet_T Connection_executeQuery	(	T	C,
		const char *	sql,
			... )

Executes a SQL query and returns a ResultSet.

You may only use one SQL statement with this method. This is different from the behavior of Connection_execute() which executes all SQL statements in its input string. If the sql parameter string contains more than one SQL statement, only the first statement is executed, the others are silently ignored. A ResultSet a valid until the next call to Connection_executeQuery(), Connection_execute() or until the Connection is returned to the Connection Pool. This means that Result Sets cannot be saved between queries.

Parameters

C	A Connection object
sql	A SQL statement

Returns

A ResultSet object that contains the data produced by the given query.

Exceptions

SQLException

If a database error occurs.

See also

ResultSet.h

SQLException.h

Connection_prepareStatement()

PreparedStatement_T Connection_prepareStatement	(	T	C,
		const char *	sql,
			... )

Prepares a SQL statement for execution.

The sql parameter may contain IN parameter placeholders. An IN placeholder is specified with a '?' character in the sql string. The placeholders are then replaced with actual values by using the PreparedStatement's setXXX methods. Only one SQL statement may be used in the sql parameter, this in difference to Connection_execute() which may take several statements. A PreparedStatement is valid until the Connection is returned to the Connection Pool.

Parameters

C	A Connection object
sql	A single SQL statement that may contain one or more '?' IN parameter placeholders

Returns

A new PreparedStatement object containing the pre-compiled SQL statement.

Exceptions

SQLException

If a database error occurs.

See also

PreparedStatement.h

SQLException.h

Connection_getLastError()

const char * Connection_getLastError

(

T

C

)

Gets the last SQL error message.

This method can be used to obtain a string describing the last error that occurred. Inside a CATCH-block you can also find the error message directly in the variable Exception_frame.message. It is recommended to use this variable instead since it contains both SQL errors and API errors such as parameter index out of range etc, while Connection_getLastError() might only show SQL errors

Parameters

C	A Connection object

Returns

A string explaining the last error

Connection_isSupported()

bool Connection_isSupported

(

const char *

url

)

Checks if the specified database system is supported.

Clients may pass a full Connection URL, for example using URL_toString(), or for convenience only the protocol part of the URL. E.g. "mysql" or "sqlite".

Parameters

url	A database url string or database name

Returns

true if supported, false otherwise.