Connection

Detailed Description

Represents a connection to a SQL database system.

Use a Connection to execute SQL statements. There are three ways to execute statements: execute() is used to execute SQL statements that do not return a result set. Such statements are INSERT, UPDATE or DELETE. executeQuery() is used to execute a SQL SELECT statement and return a result set. These methods can handle various data types, including binary data, by automatically creating a PreparedStatement when arguments are provided. For more complex scenarios or when reusing statements, you can explicitly create a PreparedStatement object using the prepareStatement() method.

The method 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 ConnectionPool. If an error occurs during execution, an sql_exception 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 beginTransaction(). Such transactions usually persist until the next call to commit() or 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 con = pool.getConnection();
ResultSet result = con.executeQuery("SELECT name, age FROM users WHERE id = ?", 1);
if (result.next()) {
    std::cout << "Name: " << result.getString("name").value_or("N/A")
              << ", Age: " << result.getInt("age") << std::endl;
}

Transaction Example

try {
    Connection con = pool.getConnection();
    con.beginTransaction();
    con.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", 100.0, 1);
    con.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", 100.0, 2);
    con.commit();
    std::cout << "Transfer successful" << std::endl;
} catch (const sql_exception& e) {
    // See note below why we don't have to explicit call rollback here
    std::cerr << "Transfer failed: " << e.what() << std::endl;
}

Using PreparedStatement

Connection con = pool.getConnection();
auto stmt = con.prepareStatement("INSERT INTO logs (message, timestamp) VALUES (?, ?)");
stmt.bindValues("User logged in", std::time(nullptr));
stmt.execute();
std::cout << "Rows affected: " << stmt.rowsChanged() << std::endl;

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

Note

When a Connection object goes out of scope, 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, ensuring data integrity even in the face of exceptions.

Warning

Connection objects are internally managed by the ConnectionPool that created them and are not copyable or movable. Always ensure that the originating ConnectionPool object remains valid for the entire duration of the Connection's use. It is recommended to obtain a Connection, use it for a specific task, and then close it (return it to the pool) as soon as possible, rather than holding onto it for extended periods.

Public Member Functions
Properties
void	setQueryTimeout (int ms) noexcept
	Sets the query timeout for this Connection.
int	getQueryTimeout () noexcept
	Gets the query timeout for this Connection.
void	setMaxRows (int max) noexcept
	Sets the maximum number of rows for ResultSet objects.
int	getMaxRows () noexcept
	Gets the maximum number of rows for ResultSet objects.
void	setFetchSize (int rows) noexcept
	Sets the number of rows to fetch for ResultSet objects.
int	getFetchSize () noexcept
	Gets the number of rows to fetch for ResultSet objects.
Functions
bool	ping () noexcept
	Pings the database server to check if the connection is alive.
void	clear () noexcept
	Clears any ResultSet and PreparedStatements in the Connection.
void	close () noexcept
	Returns the connection to the connection pool.
void	beginTransaction (TRANSACTION_TYPE type=TRANSACTION_DEFAULT)
	Begins a new transaction with optional isolation level.
bool	inTransaction () const noexcept
	Checks if this Connection is in an uncommitted transaction.
void	commit ()
	Commits the current transaction.
void	rollback ()
	Rolls back the current transaction.
long long	lastRowId () noexcept
	Gets the last inserted row ID for auto-increment columns.
long long	rowsChanged () noexcept
	Gets the number of rows affected by the last execute() statement.
template<typename... Args>
void	execute (const std::string &sql, Args &&... args)
	Executes a SQL statement, with or without parameters.
template<typename... Args>
ResultSet	executeQuery (const std::string &sql, Args &&... args)
	Executes a SQL query and returns a ResultSet.
PreparedStatement	prepareStatement (const std::string &sql)
	Prepares a SQL statement for execution.
std::optional< std::string_view >	getLastError () const noexcept
	Gets the last SQL error message.

Static Public Member Functions
static bool	isSupported (const std::string &url) noexcept
	Checks if the specified database system is supported.

Member Function Documentation

setQueryTimeout()

void setQueryTimeout ( int ms )

noexcept

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 timeout. The default is no query timeout.

Parameters

ms	Timeout in milliseconds.

getQueryTimeout()

int getQueryTimeout ( )

nodiscardnoexcept

Gets the query timeout for this Connection.

Returns

The query timeout in milliseconds.

setMaxRows()

void setMaxRows ( int max )

noexcept

Sets the maximum number of rows for ResultSet objects.

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

Parameters

max	Maximum number of rows.

getMaxRows()

int getMaxRows ( )

nodiscardnoexcept

Gets the maximum number of rows for ResultSet objects.

Returns

The maximum number of rows.

setFetchSize()

void setFetchSize ( int rows )

noexcept

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

rows	Number of rows to fetch.

getFetchSize()

int getFetchSize ( )

nodiscardnoexcept

Gets the number of rows to fetch for ResultSet objects.

Returns

The number of rows to fetch.

ping()

bool ping ( )

nodiscardnoexcept

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

Returns

true if the connection is alive, false otherwise.

clear()

void clear ( )

noexcept

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.

close()

void close ( )

noexcept

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 was called

beginTransaction()

void beginTransaction

(

TRANSACTION_TYPE

type = TRANSACTION_DEFAULT

)

Begins a new transaction with optional isolation level.

Example usage:

// Use default isolation level
connection.beginTransaction();
 
// Specify isolation level
connection.beginTransaction(TRANSACTION_SERIALIZABLE);

Parameters

type	The transaction isolation level (default: TRANSACTION_DEFAULT).

See also

TRANSACTION_TYPE enum for available options.

Exceptions

sql_exception

If a database error occurs or if a transaction is already in progress.

Note

All transactions must be ended with either commit() or rollback(). Nested transactions are not supported.

inTransaction()

bool inTransaction ( ) const

nodiscardnoexcept

Checks if this Connection is in an uncommitted transaction.

Returns

true if in a transaction, false otherwise.

commit()

void commit

(

)

Commits the current transaction.

Exceptions

sql_exception

If a database error occurs.

rollback()

void rollback

(

)

Rolls back the current transaction.

This method will first call clear() before performing the rollback to clear any statements in progress such as selects.

Exceptions

sql_exception

If a database error occurs.

lastRowId()

long long lastRowId ( )

nodiscardnoexcept

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

Returns

The last inserted row ID.

rowsChanged()

long long rowsChanged ( )

nodiscardnoexcept

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.

Returns

The number of rows changed.

execute()

template<typename... Args>

void execute	(	const std::string &	sql,
		Args &&...	args )

Executes a SQL statement, with or without parameters.

This method can be used in two ways:

1. With only a SQL string, which directly executes the statement(s).
2. With a SQL string and additional arguments, which creates a PreparedStatement, binds the provided parameters, and then executes it.

Parameters

sql	The SQL statement to execute.
args	(Optional) Arguments to bind to the statement. These can be of various types, including string-like types, numeric types, blob-like types, time_t, and nullptr.

Exceptions

sql_exception

If a database access error occurs or if the types of the provided arguments don't match the expected types in the SQL statement.

Note

When used without arguments, this method is more efficient as it doesn't create a PreparedStatement. When used with arguments, it provides protection against SQL injection.

Example usage:

// Without parameters
con.execute("DELETE FROM users WHERE inactive = true");
 
// With parameters
con.execute("INSERT INTO users (name, age) VALUES (?, ?)", "John Doe", 30);

executeQuery()

template<typename... Args>

ResultSet executeQuery ( const std::string & sql, Args &&... args )

nodiscard

Executes a SQL query and returns a ResultSet.

This method can be used in two ways:

1. With only a SQL string, which directly executes the query.
2. With a SQL string and additional arguments, which creates a PreparedStatement, binds the provided parameters, and then executes it.

Parameters

sql	The SQL query to execute.
args	(Optional) Arguments to bind to the query. These can be of various types, including string-like types, numeric types, blob-like types, time_t, and nullptr.

Returns

A ResultSet containing the query results.

Exceptions

sql_exception

If a database access error occurs or if the types of the provided arguments don't match the expected types in the SQL query.

Note

When used without arguments, this method is more efficient as it doesn't create a PreparedStatement. When used with arguments, it provides protection against SQL injection.

Example usage:

// Without parameters
auto result1 = con.executeQuery("SELECT * FROM users");
 
// With parameters
auto result2 = con.executeQuery("SELECT * FROM users WHERE age > ? AND name LIKE ?", 18, "John%");

prepareStatement()

PreparedStatement prepareStatement ( const std::string & sql )

nodiscard

Prepares a SQL statement for execution.

This method creates a PreparedStatement object that can be reused with different parameters. It's particularly useful for statements that will be executed multiple times with different values.

Parameters

sql	The SQL statement to prepare.

Returns

A PreparedStatement object.

Exceptions

sql_exception

If a database error occurs during preparation.

Example usage:

auto stmt = con.prepareStatement("INSERT INTO users (name, age) VALUES (?, ?)");
for (const auto& user : users) {
    stmt.bindValues(user.name, user.age);
    stmt.execute();
}

getLastError()

std::optional< std::string_view > getLastError ( ) const

nodiscardnoexcept

Gets the last SQL error message.

Returns

The last error message as a string view.

isSupported()

static bool isSupported ( const std::string & url )

staticnodiscardnoexcept

Checks if the specified database system is supported.

Parameters

url	A database URL string or protocol.

Returns

true if supported, false otherwise.