SQL Statement Management

Modules
	SQL Statement Request Parameter Binding
	SQL Statement Results

Typedefs
typedef struct M_sql_stmt	M_sql_stmt_t

Functions
M_sql_stmt_t *	M_sql_stmt_create (void)
void	M_sql_stmt_destroy (M_sql_stmt_t *stmt)
M_sql_error_t	M_sql_stmt_prepare (M_sql_stmt_t *stmt, const char *query)
M_sql_error_t	M_sql_stmt_prepare_buf (M_sql_stmt_t *stmt, M_buf_t *query)
M_sql_stmt_t *	M_sql_stmt_groupinsert_prepare (M_sql_connpool_t *pool, const char *query)
M_sql_stmt_t *	M_sql_stmt_groupinsert_prepare_buf (M_sql_connpool_t *pool, M_buf_t *query)
M_sql_error_t	M_sql_stmt_execute (M_sql_connpool_t *pool, M_sql_stmt_t *stmt)
M_bool	M_sql_stmt_set_max_fetch_rows (M_sql_stmt_t *stmt, size_t num)
M_bool	M_sql_stmt_set_master_only (M_sql_stmt_t *stmt)
M_bool	M_sql_stmt_has_remaining_rows (M_sql_stmt_t *stmt)
M_sql_error_t	M_sql_stmt_fetch (M_sql_stmt_t *stmt)
M_sql_error_t	M_sql_stmt_get_error (M_sql_stmt_t *stmt)
const char *	M_sql_stmt_get_error_string (M_sql_stmt_t *stmt)

Detailed Description

SQL Statement Management

Typedef Documentation

M_sql_stmt_t

typedef struct M_sql_stmt M_sql_stmt_t

Data type for prepared SQL statements

Function Documentation

M_sql_stmt_create()

M_sql_stmt_t * M_sql_stmt_create

(

void

)

Create a prepared statement object for executing queries.

The statement object holds both request data as well as response data from the server.

Use the M_sql_stmt_bind_*() series of functions to bind data to the statement handle matching the number of bound parameters referenced in the query. When binding parameters, they must be bound in order of appearance in the query.

Returns

Initialized M_sql_stmt_t object

M_sql_stmt_destroy()

void M_sql_stmt_destroy

(

M_sql_stmt_t *

stmt

)

Destroy a prepared statement object

Parameters

[in]

stmt

Initialized M_sql_stmt_t object

M_sql_stmt_prepare()

M_sql_error_t M_sql_stmt_prepare	(	M_sql_stmt_t *	stmt,
		const char *	query
	)

Prepare statement for execution.

This process will perform basic preprocessing and transformation on the statement query. Parameters for the query may be bound either before or after this call. A placeholder of a question mark (?) will be used for each bound parameter in the statement.

No inline text is allowed in a prepared statement, callers must ensure they bind any text values.

Only a single query per statement execution is allowed.

Parameters

[in]	stmt	Initialized M_sql_stmt_t object
[in]	query	Query to be prepared

Returns

M_SQL_ERROR_SUCCESS on sucess, or one of the M_sql_error_t values on failure.

M_sql_stmt_prepare_buf()

M_sql_error_t M_sql_stmt_prepare_buf	(	M_sql_stmt_t *	stmt,
		M_buf_t *	query
	)

Prepare statement for execution from an M_buf_t object that will be automatically free'd.

This process will perform basic preprocessing and transformation on the statement query. Parameters for the query may be bound either before or after this call. A placeholder of a question mark (?) will be used for each bound parameter in the statement.

No inline text is allowed in a prepared statement, callers must ensure they bind any text values.

Only a single query per statement execution is allowed.

Parameters

[in]	stmt	Initialized M_sql_stmt_t object
[in]	query	Query to be prepared held in an M_buf_t object. The M_buf_t passed to this function will be automatically destroyed so must not be used after a call to this function.

Returns

M_SQL_ERROR_SUCCESS on sucess, or one of the M_sql_error_t values on failure.

M_sql_stmt_groupinsert_prepare()

M_sql_stmt_t * M_sql_stmt_groupinsert_prepare	(	M_sql_connpool_t *	pool,
		const char *	query
	)

Create a "grouped" SQL statement for optimizing server round-trips and commits for "like" INSERT statements.

When multiple threads are performing similar actions, such as during transaction processing, it is very likely that those multiple threads might need to perform essentially the same insert action on the same table with the same number of bound parameters. Instead of sending these insertions individually, it is more efficient to group them together which could result in a single round trip and transaction instead of dozens or even hundreds.

This implementation will generate a hashtable entry in the pool with the query as the key and the statement handle as the value. If the entry already exists, it will use the existing statement handle and simply prepare it to take a new row then once M_sql_stmt_execute() is called, it wait on a thread conditional rather than trying to directly execute the statement, which will wake when a result is ready. If the entry is not already in the hashtable, it will add it, then on M_sql_stmt_execute() it will temporarily M_thread_yield() in order to allow other threads to obtain a lock and append additional rows, then finally execute and trigger the other threads waiting on the conditional that a result is ready.

All threads must still call M_sql_stmt_destroy() as it becomes reference counted when this function is used. All normal M_sql_stmt_*() functions, except M_sql_stmt_prepare() and M_sql_stmt_prepare_buf() may be called. It should be advised that M_sql_stmt_result_affected_rows() may not return an expected count since it would reflect the overall count of grouped rows. Also, if an error such as M_SQL_ERROR_QUERY_CONSTRAINT is returned, the error maybe for another row, so it is advisable to simply re-run the query without using M_sql_stmt_groupinsert_prepare() so you know if the record being inserted is the culprit or not.

Note

At a minimum, one of the M_sql_stmt_bind_*() functions should be called, as well as M_sql_stmt_execute() and M_sql_stmt_destroy().

Warning

If an error is triggered, such as M_SQL_ERROR_QUERY_CONSTRAINT, the caller must re-try the transaction using normal M_sql_stmt_create() and M_sql_stmt_prepare() rather than M_sql_stmt_groupinsert_prepare() to recover.

Parameters

[in]	pool	Initialized connection pool.
[in]	query	Query string to execute

Returns

M_sql_stmt_t handle to bind parameters and execute.

M_sql_stmt_groupinsert_prepare_buf()

M_sql_stmt_t * M_sql_stmt_groupinsert_prepare_buf	(	M_sql_connpool_t *	pool,
		M_buf_t *	query
	)

Create a "grouped" SQL statement for optimizing server round-trips and commits for "like" INSERT statements using an M_buf_t as the query string.

See M_sql_stmt_groupinsert_prepare() for additional information.

Parameters

[in]	pool	Initialized connection pool.
[in]	query	Query string to execute

Returns

M_sql_stmt_t handle to bind parameters and execute.

M_sql_stmt_execute()

M_sql_error_t M_sql_stmt_execute	(	M_sql_connpool_t *	pool,
		M_sql_stmt_t *	stmt
	)

Execute a single query against the database and auto-commit if appropriate.

Must call M_sql_stmt_prepare() or M_sql_stmt_prepare_buf() prior to execution. Must also bind any parameters using M_sql_stmt_bind_*() series of functions.

If executing as part of a transaction, use M_sql_trans_execute() instead.

Parameters

[in]	pool	Initialized M_sql_connpool_t object
[in]	stmt	Initialized and prepared M_sql_stmt_t object

Returns

M_SQL_ERROR_SUCCESS on success, or one of the M_sql_error_t values on failure.

M_sql_stmt_set_max_fetch_rows()

M_bool M_sql_stmt_set_max_fetch_rows	(	M_sql_stmt_t *	stmt,
		size_t	num
	)

Set the maximum number of rows to fetch/cache in the statement handle.

By default, all available rows are cached, if this is called, only up to this number of rows will be cached client-side. The M_sql_stmt_fetch() function must be called until there are no remaining rows server-side.

It is recommended that users use partial fetching for extremely large result sets (either by number of rows, or for extremely large rows).

Warning

If a user chooses not to call this function, and the dataset is very large (especially if it contains BLOBs), then the user risks running out of memory. However, if the user sets this value too low for small row sizes, it could significantly increase the query time on some servers (like Oracle).

Parameters

[in]	stmt	Initialized, but not yet executed M_sql_stmt_t object.
[in]	num	Number of rows to cache at a time.

Returns

M_TRUE on success, M_FALSE on failure (misuse, already executed).

M_sql_stmt_set_master_only()

M_bool M_sql_stmt_set_master_only

(

M_sql_stmt_t *

stmt

)

Enforce the selection of the master pool, not the read-only pool for this statement.

Queries will, by default, be routed to the read-only pool. In some instances, this may not be desirable if it is known that the query must be as fresh as possible and thus route to the read/write pool.

Another work around is simply to wrap the read request in a transaction, but if not performing other tasks, that may be overkill and this function simplifies that logic.

Parameters

[in]

stmt

Initialized and not yet executed M_sql_stmt_t object

Returns

M_TRUE on success, M_FALSE on failure (misuse, already executed).

M_sql_stmt_has_remaining_rows()

M_bool M_sql_stmt_has_remaining_rows

(

M_sql_stmt_t *

stmt

)

Retrieve whether there are still remaining rows on the server yet to be fetched by the client.

If there are remaining rows, the client must call M_sql_stmt_fetch() to cache the next result set.

Parameters

[in]

stmt

Initialized and executed M_sql_stmt_t object.

Returns

M_TRUE if there are remaining rows on the server that can be fetched, M_FALSE otherwise.

M_sql_stmt_fetch()

M_sql_error_t M_sql_stmt_fetch

(

M_sql_stmt_t *

stmt

)

Fetch additional rows from the server.

Any existing cached rows will be cleared.

Parameters

[in]

stmt

Initialized and executed M_sql_stmt_t object.

Returns

M_SQL_ERROR_SUCCESS_ROW on success when there may be additional remaining rows, or M_SQL_ERROR_SUCCESS if there are no remaining rows (if M_SQL_ERROR_SUCCESS is returned, it is guaranteed no additional rows can be fetched using M_sql_stmt_fetch()). However, there may still be additional rows in the buffer that need to be processed, please check with M_sql_stmt_result_num_rows(). Otherwise one of the M_sql_error_t error conditions will be returned.

M_sql_stmt_get_error()

M_sql_error_t M_sql_stmt_get_error

(

M_sql_stmt_t *

stmt

)

Retrieve the last recorded error.

Parameters

[in]

stmt

Initialized M_sql_stmt_t object.

Returns

last recorded M_sql_error_t for statement handle.

M_sql_stmt_get_error_string()

const char * M_sql_stmt_get_error_string

(

M_sql_stmt_t *

stmt

)

Retrieve the last recorded error message in string form.

Parameters

[in]

stmt

Initialized M_sql_stmt_t object.

Returns

last recorded error string, or NULL if none