mlib::Query Class Reference

Wrapper for SQL prepared sentences. More...

#include <sqlitepp.h>

Public Member Functions
	Query ()
	Default constructor.
	Query (Database &db, const std::string &sql=std::string())
	Build a prepared statement.
	Query (const Query &other)
	Copy constructor.
	Query (Query &&other)
	Move constructor.
Query &	operator= (Query &&rhs)
	Move assignment operator.
Query &	operator= (const Query &rhs)
	Principal assignment operator.
	operator sqlite3_stmt * ()
	Return the underlining statement handle.
Query &	operator= (const std::string &str)
	Assign SQL text to a query.
Query &	sql (const std::string &str)
	Assign SQL text to a query.
std::string	sql () const
	Retrieve SQL text.
	operator std::string () const
	Retrieve SQL text.
erc	step ()
	Evaluate the statement.
int	columns ()
	Return number of columns in the result set.
erc	reset ()
	Reset statement to initial state.
void	clear ()
	Finalizes the statement and removes the database connection.
Bind functions
SQL statements can have parameters specified by number (? or ?nnn) or by name (:aaa or @aaa or $aaa) where nnn is a number (starting from 1) and aaa is an alphanumeric identifier. bind functions assign values to these parameters. If a parameter with that name or number is not found the functions throw an erc with code SQLITE_RANGE. All bind functions return a reference to the query object, allowing them to be chained together like in the following example: Query q(db, "SELECT name FROM table WHERE (price > :price and kind = :kind)"); q.bind (":price", 5) .bind (":kind", "toy"); //select all toys with price > 5
Query &	bind (int par, const std::string &val)
	Bind a parameter specified by number to a character string.
Query &	bind (const std::string &parname, const std::string &val)
	Bind a parameter specified by name to a character string.
Query &	bind (int par, int val)
	Bind a parameter specified by number to an integer value.
Query &	bind (const std::string &parname, int val)
	Bind a parameter specified by name to an integer value.
Query &	bind (int par, double val)
	Bind a parameter specified by number to a floating point value.
Query &	bind (const std::string &parname, double val)
	Bind a parameter specified by name to a floating point value.
Query &	bind (int par, sqlite3_int64 val)
	Bind a parameter specified by number to a large integer.
Query &	bind (const std::string &parname, sqlite3_int64 val)
	Bind a parameter specified by name to a large integer.
Query &	bind (int par, void *val, int len)
	Bind a parameter specified by number to an arbitrary memory area (BLOB)
Query &	bind (const std::string &parname, void *val, int len)
	Bind a parameter specified by name to an arbitrary memory area (BLOB)
Query &	clear_bindings ()
	Reset all parameters to NULL values.
Data retrieval functions
columm_... functions retrieve data from a column specified by name or number (starting from 0). Data type of the result is determined by the result function called (column_int returns an integer, column_double returns a double, and so on). Note that column name does not include table name. If you need to disambiguate column names, use an AS clause in the SELECT statement. If a column with that name cannot be found, the functions throw an erc with code SQLITE_RANGE. For a detailed discussion of the conversions applied see SQLITE documentation
int	column_int (int nc) const
	Return column value converted to an integer.
int	column_int (const std::string &colname) const
	Return column value converted to an integer.
std::string	column_str (int nc) const
	Return column value as an UTF-8 encoded string.
std::string	column_str (const std::string &colname) const
	Return column value as an UTF-8 encoded string.
const char *	column_text (int nc) const
	Return a pointer to a NULL-terminated text with the column content.
const char *	column_text (const std::string &colname) const
	Return a pointer to a NULL-terminated text with the column content.
double	column_double (int nc) const
	Return floating point value of the column.
double	column_double (const std::string &colname) const
	Return floating point value of the column.
sqlite3_int64	column_int64 (int nc) const
	Return column value converted to a 64-bit integer.
sqlite3_int64	column_int64 (const std::string &name) const
	Return column value converted to a 64-bit integer.
const void *	column_blob (int nc) const
	Return a pointer to a BLOB with the column content.
const void *	column_blob (const std::string &name) const
	Return a pointer to a BLOB with the column content. (int) const
Other column functions
int	column_type (int nc) const
	Return data type for a column specified by name or number.
int	column_type (const std::string &colname) const
	Return data type for a column specified by name or number.
int	column_size (int nc) const
	Return number of bytes in a column that contains a BLOB or a string.
int	column_size (const std::string &colname) const
	Return number of bytes in a column that contains a BLOB or a string. (int) const
std::string	decl_type (int nc) const
std::string	decl_type (const std::string &colname) const
std::string	table_name (int nc) const
	Return originating table name.
std::string	table_name (const std::string &colname) const
	Return originating table name.
std::string	database_name (int nc) const
	Return originating schema name.
std::string	database_name (const std::string &colname) const
	Return originating schema name.
std::string	column_name (int nc) const
	Return column name.

Friends
class	Database

Detailed Description

Wrapper for SQL prepared sentences.

The Query class is a wrapper around a prepared sqlite3 statement.

The class provides a number of overloaded bind functions that can be used to bind parameters specified either by name or by number (first parameter is 1) to values of different types.

All bind functions return a reference to the object allowing them to be syntactically chained as in the following example:

Query  (db, "INSERT INTO table (col1, col2) VALUES (:param1, :param2);")
  .bind (":param1", value1)
  .bind (":param2", value2)
  .step ();

For data extraction, the class provides column_xxx functions that return the value of a column specified either by name or by index (first column index is 0).

Constructor & Destructor Documentation

Query()

mlib::Query::Query	(	Database &	db,
		const std::string &	sql = std::string ()
	)

Build a prepared statement.

Parameters

db	database connection
sql	SQL statement. It can be empty, in which case the query is attached to a database but the SQL has to be set later using the string assignment operator.

Member Function Documentation

bind() [1/2]

Query & mlib::Query::bind	(	const std::string &	parname,
		void *	val,
		int	len
	)

Bind a parameter specified by name to an arbitrary memory area (BLOB)

Parameters

parname	parameter name
val	pointer to BLOB
len	size of BLOB

The function makes a local copy of the value so user can free it immediately.

bind() [2/2]

Query & mlib::Query::bind	(	int	par,
		void *	val,
		int	len
	)

Bind a parameter specified by number to an arbitrary memory area (BLOB)

Parameters

par	parameter index (starting from 1)
val	pointer to BLOB
len	size of BLOB

The function makes a local copy of the value so user can free it immediately.

clear()

void mlib::Query::clear

(

)

Finalizes the statement and removes the database connection.

Statements are automatically finalized when Query objects are destructed or assigned a new SQL text.

Occasionally user might need to manually clear a query (for instance if he needs to close a database connection).

column_blob() [1/2]

const void * mlib::Query::column_blob

(

const std::string &

colname

)

const

Return a pointer to a BLOB with the column content. (int) const

The memory for the BLOB is freed automatically. Call column_size() function after calling this function to determine the size of the object returned. (int) const

column_blob() [2/2]

const void * mlib::Query::column_blob

(

int

nc

)

const

Return a pointer to a BLOB with the column content.

The memory for the BLOB is freed automatically. Call column_size() function after calling this function to determine the size of the object returned.

column_double() [1/2]

double mlib::Query::column_double

(

const std::string &

colname

)

const

Return floating point value of the column.

The following rules apply:

• NULL value is converted to 0.0
• INTEGER value is promoted to double
• TEXT or BLOB value is converted to number. Longest possible prefix of the value that can be interpreted as a real number is extracted and the remainder ignored. Any leading spaces are ignored.

column_double() [2/2]

double mlib::Query::column_double

(

int

nc

)

const

Return floating point value of the column.

The following rules apply:

• NULL value is converted to 0.0
• INTEGER value is promoted to double
• TEXT or BLOB value is converted to number. Longest possible prefix of the value that can be interpreted as a real number is extracted and the remainder ignored. Any leading spaces are ignored.

column_int() [1/2]

int mlib::Query::column_int

(

const std::string &

colname

)

const

Return column value converted to an integer.

The following rules apply:

• NULL value is converted to 0
• REAL value is rounded to nearest integer
• TEXT or BLOB value is converted to number. Longest possible prefix of the value that can be interpreted as an integer number is extracted and the remainder ignored. Any leading spaces are ignored.

column_int() [2/2]

int mlib::Query::column_int

(

int

nc

)

const

Return column value converted to an integer.

The following rules apply:

• NULL value is converted to 0
• REAL value is rounded to nearest integer
• TEXT or BLOB value is converted to number. Longest possible prefix of the value that can be interpreted as an integer number is extracted and the remainder ignored. Any leading spaces are ignored.

column_int64() [1/2]

sqlite3_int64 mlib::Query::column_int64

(

const std::string &

colname

)

const

Return column value converted to a 64-bit integer.

The following rules apply:

• NULL value is converted to 0
• REAL value is rounded to nearest integer
• TEXT or BLOB value is converted to number. Longest possible prefix of the value that can be interpreted as an integer number is extracted and the remainder ignored. Any leading spaces are ignored.

column_int64() [2/2]

sqlite3_int64 mlib::Query::column_int64

(

int

nc

)

const

Return column value converted to a 64-bit integer.

The following rules apply:

• NULL value is converted to 0
• REAL value is rounded to nearest integer
• TEXT or BLOB value is converted to number. Longest possible prefix of the value that can be interpreted as an integer number is extracted and the remainder ignored. Any leading spaces are ignored.

column_name()

std::string mlib::Query::column_name

(

int

nc

)

const

Return column name.

Parameters

nc	- column number

If the query is a SELECT statement and nc is a valid column number, the function returns the name of the column. Otherwise the function returns an empty string. The name of a result column is the value of the AS clause for that column, if there is an AS clause. If there is no AS clause then the name of the column is unspecified and may change from one release of SQLite to the next.

The returned string is always UTF-8 encoded.

column_size() [1/2]

int mlib::Query::column_size

(

const std::string &

colname

)

const

Return number of bytes in a column that contains a BLOB or a string. (int) const

If the result is NULL the function returns 0. For a numerical column, the function returns the size of the string representation of the value. (int) const

column_size() [2/2]

int mlib::Query::column_size

(

int

nc

)

const

Return number of bytes in a column that contains a BLOB or a string.

If the result is NULL the function returns 0. For a numerical column, the function returns the size of the string representation of the value.

column_str() [1/2]

string mlib::Query::column_str

(

const std::string &

colname

)

const

Return column value as an UTF-8 encoded string.

If the column is NULL, the result is an empty string.

column_str() [2/2]

string mlib::Query::column_str

(

int

nc

)

const

Return column value as an UTF-8 encoded string.

If the column is NULL, the result is an empty string.

column_text() [1/2]

const char * mlib::Query::column_text

(

const std::string &

colname

)

const

Return a pointer to a NULL-terminated text with the column content.

The memory for the string is freed automatically.

If the column value is NULL the result is a NULL pointer

column_text() [2/2]

const char * mlib::Query::column_text

(

int

nc

)

const

Return a pointer to a NULL-terminated text with the column content.

The memory for the string is freed automatically.

If the column value is NULL the result is a NULL pointer

column_type() [1/2]

int mlib::Query::column_type

(

const std::string &

colname

)

const

Return data type for a column specified by name or number.

Return values

SQLITE_INTEGER	64-bit signed integer
SQLITE_FLOAT	64-bit IEEE floating point
SQLITE_TEXT	string
SQLITE_BLOB	BLOB
SQLITE_NULL	NULL

column_type() [2/2]

int mlib::Query::column_type

(

int

nc

)

const

Return data type for a column specified by name or number.

Return values

SQLITE_INTEGER	64-bit signed integer
SQLITE_FLOAT	64-bit IEEE floating point
SQLITE_TEXT	string
SQLITE_BLOB	BLOB
SQLITE_NULL	NULL

database_name() [1/2]

std::string mlib::Query::database_name

(

const std::string &

colname

)

const

Return originating schema name.

Parameters

colname

- column name

If the query is a SELECT statement and colname is a table column (not an expression or subquery) the function returns the name of the schema where the column originated from. Otherwise the function returns an empty string.

The returned string is always UTF-8 encoded.

Example:

Database db ("");                         // create temporary database
db.exec ("CREATE TABLE tbl(c1 TEXT)");    // schema
db.exec ("ATTACH \":memory:\" AS db2");
db.exec ("CREATE TABLE db2.tbl2(c2 TEXT)");
Query q (db, "SELECT c1, c2 FROM tbl JOIN tbl2");  // SELECT query
auto s = q.database_name ("c1");          // s == "main"
s = q.database_name ("c2");               // s == "db2"

SQLITE must be compiled with the SQLITE_ENABLE_COLUMN_METADATA for this function to be available.

database_name() [2/2]

std::string mlib::Query::database_name

(

int

nc

)

const

Return originating schema name.

Parameters

nc	- column number

If the query is a SELECT statement and nc is a table column (not an expression or subquery) the function returns the name of the schema where the column originated from. Otherwise the function returns an empty string.

The returned string is always UTF-8 encoded.

Example:

Database db ("");                         // create temporary database
db.exec ("CREATE TABLE tbl(c1 TEXT)");    // schema
db.exec ("ATTACH \":memory:\" AS db2");
db.exec ("CREATE TABLE db2.tbl2(c2 TEXT)");
Query q (db, "SELECT c1, c2 FROM tbl JOIN tbl2");  // SELECT query
auto s = q.database_name (0);             // s == "main"
s = q.database_name (1);                  // s == "db2"

SQLITE must be compiled with the SQLITE_ENABLE_COLUMN_METADATA for this function to be available.

decl_type() [1/2]

std::string mlib::Query::decl_type

(

const std::string &

colname

)

const

Parameters

colname

- column name If the query is a SELECT statement and colname is a table column (not an expression or subquery) the function returns the declared type of the table column. Otherwise the function returns an empty string.

The returned string is always UTF-8 encoded.

Example:

Database db("");                          // create temporary database
db.exec ("CREATE TABLE t1(c1 VARIANT)");  // schema
Query q (db, "SELECT c1 FROM t1");        // SELECT query
auto s = q.decl_type("c1");               // s == "VARIANT"

decl_type() [2/2]

std::string mlib::Query::decl_type

(

int

nc

)

const

Parameters

nc	- column number If the query is a SELECT statement and nc is a table column (not an expression or subquery) the function returns the declared type of the table column. Otherwise the function returns an empty string.

The returned string is always UTF-8 encoded.

Example:

Database db("");                          // create in-memory database
db.exec ("CREATE TABLE t1(c1 VARIANT)");  // schema
Query q (db, "SELECT c1 FROM t1");        // SELECT query
auto s = q.decl_type(0);                  // s == "VARIANT"

operator std::string()

mlib::Query::operator std::string ( ) const

inline

Retrieve SQL text.

This is just syntactic sugar over Query::sql() function.

operator=()

Query & mlib::Query::operator= ( const std::string &  str )

inline

Assign SQL text to a query.

The object must have the database reference set before calling this function.

If a previous statement was attached to the object, it is finalized before the new statement is prepared.

sql() [1/2]

std::string mlib::Query::sql

(

)

const

Retrieve SQL text.

If the object is not connected with a prepared statement returns an empty string.

sql() [2/2]

Query & mlib::Query::sql

(

const std::string &

str

)

Assign SQL text to a query.

The object must have the database reference set before calling this function.

If a previous statement was attached to the object, it is finalized before the new statement is prepared.

step()

erc mlib::Query::step

(

)

Evaluate the statement.

When a new row of results is available the function returns SQLITE_ROW.

When there are no more result the function returns SQLITE_DONE. Both codes are wrapped in erc objects with priority level ERROR_PRI_INFO that normally don't throw an exception. Any other error is return at the normal priority level erc::error.

table_name() [1/2]

std::string mlib::Query::table_name

(

const std::string &

colname

)

const

Return originating table name.

Parameters

colname

- column name If the query is a SELECT statement and colname is a table column (not an expression or subquery) the function returns the name of the table where the column originated from. Otherwise the function returns an empty string.

The returned string is always UTF-8 encoded.

Example:

Database db ("");                         // create temporary database
db.exec ("CREATE TABLE tbl(c1 TEXT)");    // schema
Query q (db, "SELECT c1 FROM tbl");       // SELECT query
auto s = q.table_name ("c1");             // s == "tbl"

SQLITE must be compiled with the SQLITE_ENABLE_COLUMN_METADATA for this function to be available.

table_name() [2/2]

std::string mlib::Query::table_name

(

int

nc

)

const

Return originating table name.

Parameters

nc	- column number If the query is a SELECT statement and nc is a table column (not an expression or subquery) the function returns the name of the table where the column originated from. Otherwise the function returns an empty string.

The returned string is always UTF-8 encoded.

Example:

Database db ("");                         // create temporary database
db.exec ("CREATE TABLE tbl(c1 TEXT)");    // schema
Query q (db, "SELECT c1 FROM tbl");       // SELECT query
auto s = q.table_name (0);                // s == "tbl"

SQLITE must be compiled with the SQLITE_ENABLE_COLUMN_METADATA for this function to be available.

---

The documentation for this class was generated from the following files:

• include/mlib/sqlitepp.h
• src/sqlitepp.cpp