Execute queries on the database. More...

#include <mcs.hh>

Inheritance diagram for mcs::Query:

Public Member Functions
void	close ()
	Close the statement and frees anything has been allocated. More...

void	customFillBuffer (char *buf, unsigned int &chunksize, bool firstTime)
	Fills a buffer with a result-set. More...

void	execute (bool StoreResult=false)
	Execute the prepared statement. More...

vector< string >	ExecutionDetails (string pre="")
	Returns a vector of strings containing details of query execution. More...

bool	gotRecordSet ()

long long int	last_id ()
	Returns the last value generated for an AUTO_INCREMENT column. More...

Data &	lookup (string field, string table, string where="")
	Returns the current record set. More...

unsigned int	nAffectedRows ()
	Returns number of affected records by the last execute() call. More...

Query &	operator= (const Query &)
	Declared to avoid using of default assignment operator. More...

Record &	param ()
	Returns a reference to a Record object containing all the input parameters. More...

void	prepare (string SQL="")
	Prepare the SQL statement. More...

void	prepare_with_parameters (int op, string fields, string table, string where="", int nrec=1)
	Prepare a query with input parameters. More...

void	prepare_with_parameters (int op, char **fields, int nfields, string table, string where="", int nrec=1)
	Wrapper around prepare_with_parameters(int, string, string, string). More...

	Query (const Query &)
	Declared to avoid using of default copy constructor. More...

	Query (DBConn *lconn, bool call_newDBConn=false)
	Constructor. More...

void	query (string SQL, bool StoreResult=false)
	Wrapper aropund prepare() and execute(). More...

void	readTableList ()
	Reads the list of tables in the current database. More...

void	Result2Ascii (string fn)
	Writes a table into an ASCII file using the customFillBuffer() method. More...

vector< string >	simpleQuery (string SQL, unsigned int &nrows, unsigned int &nfields)
	Execute a query and return result in a vector of strings. More...

vector< string >	simpleQuery (string SQL)
	Wrapper for simpleQuery(string, unsigned int&, unsigned int&). More...

vector< string >	tableInfo (string tbl)
	Retrieve information about a table. More...

	~Query ()

Public Member Functions inherited from mcs::RecordSet
bool	alwaysSameStructure ()

void	dump (string fn)

bool	eof ()

void	insert (Record *rec)
	Insert a new record in the set. More...

void	insert (Record &rec)
	Copy a new record in the set. More...

bool	know_nRows ()

Record &	metarec ()

int	nFields ()
	Returns number of fields. More...

unsigned int	nRows ()

RecordSet &	operator= (const RecordSet &)
	Declared to avoid using of default assignment operator. More...

unsigned int	pos ()

Record *	prepRecToSend ()

Record &	rec ()

	RecordSet (const RecordSet &)
	Declared to avoid using of default copy constructor. More...

	RecordSet ()
	Build an empty record set. More...

void	setFieldMap (string s="")
	Set up a field map for each record in the recordset. More...

bool	setFirst ()
	Set the record pointer to the first position. More...

bool	setLast ()
	Set the record pointer to the last position. More...

bool	setNext ()
	Set the record pointer to the next position. More...

bool	setPos (unsigned int i)
	Set the record pointer to the i-th position. More...

bool	setPrev ()
	Set the record pointer to the previous position in the set. More...

bool	setWhere (int i, string equalTo)
	Set the record pointer to the position which satisfies a string condition. More...

bool	setWhere (int field, int equalTo)
	Set the record pointer to the position which satisfies an integer condition. More...

virtual	~RecordSet ()
	Destructor. More...

Public Attributes
vector< string >	tableList
	A vector<string> containing the table list. More...

Private Member Functions
void	bind ()
	Bind parameters. More...

bool	fetch (unsigned int newpos, bool random)

Record *	newRecord ()

void	parseFieldList (int op, string &fields, string &values, int &autoIncr)

vector< string >	printResultSet (unsigned int &nrows, unsigned int &nfields, MYSQL_RES *res=NULL)
	Prints all the result set in a vector of string. More...

Private Attributes
bool	gotResult
	True if we got a result from the last query. More...

bool	gotStmtInitialized
	True if the statement has been initialized. More...

unsigned int	laffectedRows
	Rows affected by the last INSERT-LIKE query. More...

long long int	lastid
	Last generated id on an AUTO_INCREMENT column. More...

MYSQL_BIND *	lbparam
	Record of binding structure for input (parameters). More...

MYSQL_BIND *	lbrec
	Record of binding structure for output. More...

MYSQL *	lconn
	Mysql connection to use. More...

DBConn *	ldbc
	Internal reference to the DBConn object used to connect to db. More...

bool	lhandleNewDBConn
	Flag to indicate that a new DBConn object has been created. More...

Record	lparam
	Record of binded Data objects for input (parameters). More...

MYSQL_STMT *	lstmt
	Mysql statement structure. More...

	MCS_DEBUG_ALLOC

Record *	myrec

int	nlparam

string	SQL

Additional Inherited Members
Protected Member Functions inherited from mcs::RecordSet
virtual void	hk_dump (string fn)
	Virtual method to provide dump facility to a file. More...

void	init (unsigned char code, unsigned int nrows=0, Record *meta=NULL, short int id=0)
	Initailize the Record set. More...

void	startFetch ()
	Must be called when record fetching is possible. More...

Detailed Description

Execute queries on the database.

You can execute two types of query with this class:

simple queries (see simpleQuery())
prepared statement (prepare(), execute())

The result-set (if any is present) will be returned as a whole inside a vector<string> object in the first case, as a DBRecordSet object in the second (see recordSet() method). With prepared statement you can also prepare a parametric query, then execute it any number of time changing just the parameters.

Definition at line 5544 of file mcs.hh.

Constructor & Destructor Documentation

◆ Query() [1/2]

mcs::Query::Query ( const Query & )

Declared to avoid using of default copy constructor.

Warning: This constructor is declared but not implemented. If you try to use it you will get a compilation error.

◆ Query() [2/2]

mcs::Query::Query	(	DBConn *	lconn,
		bool	call_newDBConn = `false`
	)

Constructor.

This creates a new Query object that connects to the db server using the DBConn object used as parameter. If the second parameter is true then the newDBConn() method will be called to create a new DBConn object. In this case the newly created object is available only inside the Query object, so it will be destroyed inside the Query destructor.

Parameters

lconn	DBConn object used to connect to db server;
call_newDBConn	Flag to eventually call the newDBConn() method on lconn.

Definition at line 132 of file Db.cc.

◆ ~Query()

mcs::Query::~Query ( )

brief Destructor.

This call the close() method and eventually destroy the DBConn object (if lhandleNewDBConn is true).

Definition at line 165 of file Db.cc.

Member Function Documentation

◆ bind()

void mcs::Query::bind ( )

private

Bind parameters.

Exceptions

ERROR MSG_BIND_FAILED.

Definition at line 301 of file Db.cc.

◆ close()

void mcs::Query::close ( )

Close the statement and frees anything has been allocated.

Definition at line 391 of file Db.cc.

◆ customFillBuffer()

void mcs::Query::customFillBuffer	(	char *	buf,
		unsigned int &	chunksize,
		bool	firstTime
	)

Fills a buffer with a result-set.

This method can be used to obtain a printable dump of the result set. The fields are separated by tabs, the records are separated by newlines. Successive calls are necessary because the buffer has a maximum size equal to chunksize. As an example see the Result2Ascii() method. When the reference to "chunksize" return zero means that all the result set has been dumped.

Parameters

buf	Pointer to buffer to fill;
chunksize	Size of the buffer, upon exit it will contain the number of bytes actually written in the buffer.
firstTime	A boolean variable to be set to true the first time na dto false all the rest.

Exceptions

ERROR	MSG_NO_RESULT;
ERROR	MSG_NOT_ENOUGH_SPACE.

Definition at line 723 of file Db.cc.

◆ execute()

void mcs::Query::execute ( bool StoreResult = false )

Execute the prepared statement.

This will thrown Event exceptions if the statement hasn't be prepared yet (see prepare()) or if an error during execution occur.

If the prepared statement is a SELECT query the server can execute it in two ways:

read all the record set at once, then send all data to the client;
read just the first record and send it to the client, the remnant records will be read when needed;

You choose which method use by the "StoreResult" parameter, if it is true the first method will be used, the second otherwise.

In both cases the Query class behave almost the same, except that in the first case the RecordSet::know_nRows() method will return true (and RecordSet::nRows() will return the number of records), in the second case will return false.

Parameters

StoreResult If true storeResult() will automatically be called.

Exceptions

ERROR	MSG_STMT_NOT_YET_PREPARED;
ERROR	MSG_MYSQL_ERROR;
ERROR	MSG_BIND_FAILED.

Definition at line 332 of file Db.cc.

◆ ExecutionDetails()

vector< string > mcs::Query::ExecutionDetails ( string pre = "" )

Returns a vector of strings containing details of query execution.

Definition at line 686 of file Db.cc.

◆ last_id()

long long int mcs::Query::last_id ( )

Returns the last value generated for an AUTO_INCREMENT column.

Use this function after you have performed an INSERT statement into a table that contains an AUTO_INCREMENT field.

The return value is undefined if last executed statement wasn't an INSERT statement.

Definition at line 916 of file Db.cc.

◆ lookup()

Data & mcs::Query::lookup	(	string	field,
		string	table,
		string	where = `""`
	)

Returns the current record set.

Return the value of a field on a record satisfying a where condition.

This method execute a lookup on the table "table", for a record that satisfy the condition "where". Then if this record is found it returns the value of the field "field" as a Data reference, otherwise it throws an exception.

Parameters

field	The field whose value is to be returned.
table	The table to be used for search.
where	The condition that should be satisfied by the record.

Returns: Reference to a Data object containing the value of "field".

Exceptions

ERROR MSG_NO_RESULT.

Definition at line 311 of file Db.cc.

◆ nAffectedRows()

unsigned int mcs::Query::nAffectedRows ( )

Returns number of affected records by the last execute() call.

This has meaning only for non-SELECT queries.

Definition at line 199 of file Db.cc.

◆ operator=()

Query& mcs::Query::operator= ( const Query & )

Declared to avoid using of default assignment operator.

Warning: This operator is declared but not implemented. If you try to use it you will get a compilation error.

◆ param()

Record & mcs::Query::param ( )

Returns a reference to a Record object containing all the input parameters.

Definition at line 205 of file Db.cc.

◆ prepare()

void mcs::Query::prepare ( string SQL = "" )

Prepare the SQL statement.

Before executing a prepared statement you must prepare it. Any call to this method will destroy all parameter binding, result-set and prepared statement previously present.

To prepare a query with input parameters you should call the method prepare_with_parameters() instead of this.

After this method has executed you can use the recordSet() method to get access to the recordset which already contain all fields, but no data.

Parameters

SQL	The query to be prepared.

Exceptions

ERROR	MSG_EMPTY_STRING;
ERROR	MSG_OUT_OF_MEMORY;
ERROR	MSG_MYSQL_ERROR;
ERROR	MSG_TYPE_NOT_HANDLED.

Definition at line 221 of file Db.cc.

◆ prepare_with_parameters() [1/2]

void mcs::Query::prepare_with_parameters	(	int	op,
		string	fields,
		string	table,
		string	where = `""`,
		int	nrec = `1`
	)

Prepare a query with input parameters.

Due to the fact that the mysql_stmt_param_metadata() function is not yet implemented, not all the queries with input parameters can be prepared, but only those queries which performs simple insert or update operations on one table. See the following code for an example:

//Suppose the the table 'mytable' has been created with the
//following SQL istruction:
//
//CREATE TABLE MYTABLE (C CHAR(10), I INTEGER, F FLOAT, D
//DATETIME)
//Prepare an insert query with four parameters.
qry->prepare_with_parameters(MCS_PAB_INSERT, "C I F D", "MYTABLE");
//Set the values
qry->param()[0] = "hello";
qry->param()[1] = 12;
qry->param()[2] = 3.14156;
qry->param()[3] = "2006-03-03 12:00:00";
//Execute the query
qry->execute();

The first parameter identify which kind of query you want to execute: MCS_PAB_INSERT ==> insert queries; MCS_PAB_REPLACE ==> replace queries; MCS_PAB_UPDATE ==> update queries;

Parameters

op	One of MCS_PAB_INSERT, MCS_PAB_REPLACE or MCS_PAB_UPDATE;
fields	List of fields to insert or update, separated by blanks;
table	Table name;
where	Where clause, used only if op = MCS_PAB_UPDATE.

Definition at line 846 of file Db.cc.

◆ prepare_with_parameters() [2/2]

void mcs::Query::prepare_with_parameters	(	int	op,
		char **	fields,
		int	nfields,
		string	table,
		string	where = `""`,
		int	nrec = `1`
	)

Wrapper around prepare_with_parameters(int, string, string, string).

Parameters

op	One of MCS_PAB_INSERT, MCS_PAB_REPLACE or MCS_PAB_UPDATE;
fields	Array of pointer to null terminated strings, each containing a field name;
nfields	Element in the "field" array;
table	Table name;
where	Where clause, used only if op = MCS_PAB_UPDATE.

Definition at line 803 of file Db.cc.

◆ printResultSet()

vector< string > mcs::Query::printResultSet	(	unsigned int &	nrows,
		unsigned int &	nfields,
		MYSQL_RES *	res = `NULL`
	)

private

Prints all the result set in a vector of string.

The index is ordered by field, then for row, so the index of the r-th record and f-th field is: r*nfields + f. The number of element in the vector is (r+1) * f, because the first row contains the field names.

Parameters

res	Address of the MYSQL_RES containing the result-set.
nrows	Reference to an integer that will contain the number of rows in the result-set.
nfields	Reference to an integer that will contain the number of fields in the result-set.

Returns: A vector<string> with all the information in the result-set.

Definition at line 455 of file Db.cc.

◆ query()

void mcs::Query::query	(	string	SQL,
		bool	StoreResult = `false`
	)

Wrapper aropund prepare() and execute().

Parameters

SQL	The query to execute;
StoreResult	Flag passed to execute().

Definition at line 367 of file Db.cc.

◆ readTableList()

void mcs::Query::readTableList ( )

Reads the list of tables in the current database.

The tables list is then stored in the public vector<string> tableList.

Definition at line 421 of file Db.cc.

◆ Result2Ascii()

void mcs::Query::Result2Ascii ( string fn )

Writes a table into an ASCII file using the customFillBuffer() method.

Exceptions

ERROR MSG_CANT_OPEN_FILE;

Definition at line 781 of file Db.cc.

◆ simpleQuery() [1/2]

vector< string > mcs::Query::simpleQuery	(	string	SQL,
		unsigned int &	nrows,
		unsigned int &	nfields
	)

Execute a query and return result in a vector of strings.

This executes the query using mysql_query, no placeholders can be present in the query. Then it stores the result on a vector<string> object which contains the entire result-set. An example to print all the contents of a table follows:

DBConn conn();
conn.connect("user, "pass", "dbname");
Query qry(&conn, false);
int nrecs, nfields, i, j;
vector<string> res = qry.simpleQuery("SELECT * FROM MyTable",
                                     nrecs, nfields);
for (i=0; i<nrecs; i++) {
  for (j=0; j<nfields; j++)
    cout << res[i*nfields + j];
  cout << endl;
}

Remember that all the result-set will be retrieved, so it is better to specify a LIMIT clause on the query.

Parameters

SQL	The query to execute.
nrows	Reference to an integer that will contain the number of rows in the result-set.
nfields	Reference to an integer that will contain the number of fields in the result-set.

Returns: A vector<string> containing all the result set as returned by printResultSet().

Exceptions

ERROR MSG_MYSQL_ERROR.

Definition at line 533 of file Db.cc.

◆ simpleQuery() [2/2]

vector< string > mcs::Query::simpleQuery ( string SQL )

Wrapper for simpleQuery(string, unsigned int&, unsigned int&).

Definition at line 526 of file Db.cc.

◆ tableInfo()

vector< string > mcs::Query::tableInfo ( string tbl )

Retrieve information about a table.

Informations are returned as a vector<string> containing the same data that would be returned by a mysql client if you give the command "DESCRIBE ...".

Parameters

tbl	Name of the table.

Returns: A vector<string> describing the table.

Definition at line 442 of file Db.cc.

Member Data Documentation

◆ gotResult

bool mcs::Query::gotResult

private

True if we got a result from the last query.

Definition at line 5562 of file mcs.hh.

◆ gotStmtInitialized

bool mcs::Query::gotStmtInitialized

private

True if the statement has been initialized.

Definition at line 5559 of file mcs.hh.

◆ laffectedRows

unsigned int mcs::Query::laffectedRows

private

Rows affected by the last INSERT-LIKE query.

Definition at line 5565 of file mcs.hh.

◆ lastid

long long int mcs::Query::lastid

private

Last generated id on an AUTO_INCREMENT column.

Definition at line 5568 of file mcs.hh.

◆ lbparam

MYSQL_BIND* mcs::Query::lbparam

private

Record of binding structure for input (parameters).

Warning: see warning in prepare().

Definition at line 5608 of file mcs.hh.

◆ lbrec

MYSQL_BIND* mcs::Query::lbrec

private

Record of binding structure for output.

Definition at line 5614 of file mcs.hh.

◆ lconn

MYSQL* mcs::Query::lconn

private

Mysql connection to use.

Definition at line 5556 of file mcs.hh.

◆ ldbc

DBConn* mcs::Query::ldbc

private

Internal reference to the DBConn object used to connect to db.

Definition at line 5550 of file mcs.hh.

◆ lhandleNewDBConn

bool mcs::Query::lhandleNewDBConn

private

Flag to indicate that a new DBConn object has been created.

Definition at line 5553 of file mcs.hh.

◆ lparam

Record mcs::Query::lparam

private

Record of binded Data objects for input (parameters).

Warning: see warning in prepare().

Definition at line 5574 of file mcs.hh.

◆ lstmt

MYSQL_STMT* mcs::Query::lstmt

private

Mysql statement structure.

Warning: see warning in prepare().

Definition at line 5602 of file mcs.hh.

◆ tableList

vector<string> mcs::Query::tableList

A vector<string> containing the table list.

Definition at line 5914 of file mcs.hh.

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

mcs.hh
Db.cc

Public Member Functions

Public Attributes

Private Member Functions

Private Attributes

Additional Inherited Members

Detailed Description

Constructor & Destructor Documentation

◆ Query() [1/2]

◆ Query() [2/2]

◆ ~Query()

Member Function Documentation

◆ bind()

◆ close()

◆ customFillBuffer()

◆ execute()

◆ ExecutionDetails()

◆ last_id()

◆ lookup()

◆ nAffectedRows()

◆ operator=()

◆ param()

◆ prepare()

◆ prepare_with_parameters() [1/2]

◆ prepare_with_parameters() [2/2]

◆ printResultSet()

◆ query()

◆ readTableList()

◆ Result2Ascii()

◆ simpleQuery() [1/2]

◆ simpleQuery() [2/2]

◆ tableInfo()

Member Data Documentation

◆ gotResult

◆ gotStmtInitialized

◆ laffectedRows

◆ lastid

◆ lbparam

◆ lbrec

◆ lconn

◆ ldbc

◆ lhandleNewDBConn

◆ lparam

◆ lstmt

◆ tableList