PreparedStatement

Detailed Description

Represents a pre-compiled SQL statement for later execution.

A PreparedStatement is created by calling Connection::prepareStatement(). The SQL statement may contain in parameters of the form "?". Such parameters represent unspecified literal values (or "wildcards") to be filled in later by the bind methods defined in this class. Each in parameter has an associated index number which is its sequence in the statement. The first in '?' parameter has index 1, the next has index 2 and so on.

Consider this statement:

INSERT INTO employee(name, photo) VALUES(?, ?)

There are two in parameters in this statement, the parameter for setting the name has index 1 and the one for the photo has index 2. To set the values for the in parameters we use bindValues() with two values, one for each in parameter. Or we can use bind() to set the parameter values one by one.

Examples

The following examples demonstrate how to create and use a PreparedStatement.

Example: Binding all values at once

This example shows how to prepare a statement, bind multiple values at once, and execute it:

Connection con = pool.getConnection();
PreparedStatement stmt = con.prepareStatement("INSERT INTO employee(name, photo) VALUES(?, ?)");
stmt.bindValues("Kamiya Kaoru", jpeg);
stmt.execute();

Example: Binding values individually

Instead of binding all values at once, we can also bind values one by one by specifying the parameter index we want to set a value for:

PreparedStatement stmt = con.prepareStatement("INSERT INTO employee(name, photo) VALUES(?, ?)");
stmt.bind(1, "Kamiya Kaoru");
stmt.bind(2, jpeg);
stmt.execute();

Reuse

A PreparedStatement can be reused. That is, the method execute() can be called one or more times to execute the same statement. Clients can also set new in parameter values and re-execute the statement as shown in this example, where we also show use of a transaction and exception handling:

PreparedStatement stmt = con.prepareStatement("INSERT INTO employee(name, photo) VALUES(?, ?)");
try {
    con.beginTransaction();
    for (const auto& emp : employees) {
        stmt.bind(1, emp.name);
        if (emp.photo) {
            stmt.bind(2, *emp.photo);
        } else {
            stmt.bind(2, nullptr); // Set to SQL NULL if no photo
        }
        stmt.execute();
    }
    con.commit();
} catch (const sql_exception& e) {
    con.rollback();
    std::cerr << "Database error: " << e.what() << std::endl;
}

Date and Time

bindValues() or bind() can be used to set a Unix timestamp value as a time_t type. To set Date, Time or DateTime values, simply use one of the bind methods to set a time string in a format understood by your database. For instance to set a SQL Date value,

stmt.bind(parameterIndex, "2024-12-28");
// or using bindValues
stmt.bindValues("2019-12-28", ...);

Result Sets

See Connection::executeQuery()

SQL Injection Prevention

Prepared Statement is particularly useful when dealing with user-submitted data, as properly used Prepared Statements provide strong protection against SQL injection attacks. By separating SQL logic from data, PreparedStatements ensure that user input is treated as data only, not as part of the SQL command.

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

Note

Remember that parameter indices in PreparedStatement are 1-based, not 0-based.

To minimizes memory allocation and avoid unnecessary data copying, string and blob values are set by reference and MUST remain valid until PreparedStatement::execute() has been called.

Warning

PreparedStatement objects are internally managed by the Connection that created them and are not copyable or movable. Always ensure that the originating Connection object remains valid for the entire duration of the PreparedStatement's use. Basically, keep the Connection and PreparedStatement objects in the same scope. Do not attempt to use PreparedStatement objects (including through references or pointers) after their Connection has been closed and returned to the pool.

Public Member Functions
Parameters
template<typename T >
void	bind (int parameterIndex, T &&x)
	Binds a value to the prepared statement.
template<typename... Args>
void	bindValues (Args &&... args)
	Binds multiple values to the Prepared Statement at once.
Functions
void	execute ()
	Executes the prepared SQL statement.
long long	rowsChanged () noexcept
	Gets the number of rows affected by the most recent SQL statement.
Properties
int	getParameterCount () noexcept
	Gets the number of parameters in the prepared statement.

Member Function Documentation

bind()

template<typename T >

void bind	(	int	parameterIndex,
		T &&	x )

Binds a value to the prepared statement.

This method can bind different types of values:

• String-like types (convertible to std::string_view)
• Numeric types (integral or floating-point, excluding time_t)
• Blob-like types (contiguous ranges of bytes)
• time_t for timestamp values
• nullptr_t for SQL NULL values

Template Parameters

T	The type of the value to bind

Parameters

parameterIndex	The index of the parameter to bind (1-based)
x	The value to bind

Exceptions

sql_exception

If a database access error occurs or parameterIndex is invalid

Note

For string-like and blob-like types, the data must remain valid until execute() is called. This method does not copy the data but stores a reference to it.

This method will fail to compile for unsupported types, providing a clear error message.

bindValues()

template<typename... Args>

void bindValues

(

Args &&...

args

)

Binds multiple values to the Prepared Statement at once.

Parameters

args	Values to bind to the Prepared Statement.

Exceptions

sql_exception

If a database error occurs or if argument count is incorrect

Note

Reference types must remain valid until execute() is called. This method does not copy any data.

execute()

void execute

(

)

Executes the prepared SQL statement.

Exceptions

sql_exception

If a database access error occurs

rowsChanged()

long long rowsChanged ( )

nodiscardnoexcept

Gets the number of rows affected by the most recent SQL 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.

getParameterCount()

int getParameterCount ( )

nodiscardnoexcept

Gets the number of parameters in the prepared statement.

Returns

The number of in parameters in this prepared statement