Construct and execute a query on a database and manage result. More...

#include <SqlQuery.h>

Inheritance diagram for Isis::SqlQuery:

Collaboration diagram for Isis::SqlQuery:

Public Member Functions
	SqlQuery ()
	Default constructor. More...

	SqlQuery (Database &db)
	Construtor using a specified database. More...

	SqlQuery (const std::string &query, Database db=Database(Database::Connect))
	Construct a query object and execute the provided query string. More...

	SqlQuery (const SqlQuery &other)
	Constructor using an existing SqlQuery object. More...

bool	isThrowing () const
	Report error status when executing queries. More...

void	setThrowOnFailure ()
	Sets throwing of exceptions on errors to true. More...

void	setNoThrowOnFailure ()
	Turns throwing of iExceptions off on errors. More...

bool	exec (const std::string &query)
	Execute an SQL query provided in the query string. More...

bool	exec ()

std::string	getQuery () const
	Returns the executed query string. More...

int	nFields () const
	Returns the number of fields (columns) from query. More...

std::string	fieldName (int index) const
	Returns the column name of the resulting query at the given index. More...

int	fieldIndex (const std::string &name) const
	Returns index of column for given name. More...

std::vector< std::string >	fieldNameList () const
	Returns the names of all fields in the resulting query. More...

std::vector< std::string >	fieldTypeList () const
	Returns the types of each field/column in a resutling query. More...

int	nRows () const
	Returns the count of rows resulting from the query. More...

SqlRecord	getRecord () const
	Returns a SqlRecord for the current query row. More...

Private Member Functions
void	tossQueryError (const std::string &message, const char *f, int l) const
	Issues an IException from various sources of error states in this class. More...

Private Attributes
bool	_throwIfFailed
	User can select action on query results. More...

Detailed Description

Construct and execute a query on a database and manage result.

This class is derived from the Qt QSqlQuery class. It is designed to be used in conjunction with the functionality of that class.

The major features are to to make it easier to specify what happens when errors occurs (setThrowOnFailure()) and return and accept query strings and results using the standard C++ library and classes (vector, string). It exists mainly as a convenience class interface to the Qt QSqlQuery class, providing standard C++ elements as opposed to Qt elements.

NOTE all constructors initially set exception throwing as the default.

Some examples follow:

This example uses some of Qt's features, which you can use at will throughout this and other classes. This one uses the named/positional binding features"

 Database mptdb("matchpoints");
 
  //  We now have a match point table.  Populate it with the data read in.
  SqlQuery inserter(mptdb);
  inserter.setThrowOnFailure();
  inserter.prepare("INSERT INTO "
                   "matchpt (pointid, fsc, line, samp, class, diameter) "
                   "VALUES (:pointid, :fsc, :line, :samp, :class,:diameter)");
  mptdb.transaction();
  for (int row = 0 ; row < mpt.rows() ; row++) {
    CSVReader::CSVAxis matchpt = mpt.getRow(row);
    for (int i = 0 ; i < 6 ; i++) {*
      inserter.bindValue(i, QVariant(matchpt[i].c_str()));
    }
    cout << "Inserting row " << row << ", status..." << inserter.exec()
         << endl;
 
  }
  mptdb.commit();
  mptdb.close();
}

Thats all well and good but Qt's binding is restricted to using it in the INSERT/VALUES constructs. They have no support for binding used outside this SQL operation. Below is an example with a more general approach provided with this API, although a bit less robust.

auto_ptr<Database> db = auto_ptr<Database> (new Database(dbProf,
                                                         Database::Connect));
 
SqlQuery finder(*db);
finder.setThrowOnFailure();
string pntDist  = "distance(giscpt,UPCPoint(%longitude,%latitude))";
string pntQuery = "SELECT pointid, latitude, longitude, radius FROM "
                  + pntTable + " WHERE (%distance <= " + QString(maxDist) +
                  ")";
 
Progress progress;
progress.SetText("lodbnet");
progress.SetMaximumSteps(pnts.rows());
for (int row = 0 ; row < pnts.rows() ; row++) {
  CSVReader::CSVAxis pntR = pnts.getRow(row);
 
  // Convert longitude to proper system if requested
  double longitude = QString(pntR[1]).ToDouble();
  if ((make360) && (longitude < 0.0)) { longitude += 360.0; }
  double latitude = QString(pntR[0]).ToDouble();
  double radius = QString(pntR[2]).ToDouble();
 
  // Prepare the query, converting the longitude
  string dcheck(QString::Replace(pntDist, "%longitude",
  QString(longitude))); dcheck = QString::Replace(dcheck, "%latitude",
  QString(latitude)); string query = QString::Replace(pntQuery, "%distance",
  dcheck);
 
  finder.exec(query);
  if (finder.size() > 0) {
    Statistics stats;
    vector<OutPoint> pointList;
    while (finder.next()) {
      SqlRecord record = finder.getRecord();
      OutPoint point;
      point.latitude = QString(record.getValue("latitude")).ToDouble(),
      point.longitude = QString(record.getValue("longitude")).ToDouble(),
      point.radius = QString(record.getValue("radius")).ToDouble(),
      point.pointid = QString(record.getValue("pointid")),
      stats.AddData(&point.radius, 1);
      pointList.push_back(point);
    }
  }
  progress.CheckStatus();
}

See also: QString

Author: 2006-11-09 Kris Becker

History:: 2007-04-19 Kris Becker - Reordered return of getQuery() result to first try lastQuery(), then executedQuery().

History:: 2007-06-05 Brendan George - Modified to work with QString/StringTools merge

History:: 2008-10-30 Steven Lambright - tossQueryError now accepts a const char* for a filename, issue pointed out by "novus0x2a" (Support Board Member)

Definition at line 138 of file SqlQuery.h.

Constructor & Destructor Documentation

◆ SqlQuery() [1/4]

Isis::SqlQuery::SqlQuery ( )

Default constructor.

This constructor prepares a query using the default database as established through the Database class. It will also ensure that on any error, an exception is thrown, in line with the Isis way of doing things.

Definition at line 30 of file SqlQuery.cpp.

◆ SqlQuery() [2/4]

Isis::SqlQuery::SqlQuery ( Database & db )

Construtor using a specified database.

This constructor should be used for preparing for queries using a specified database. This would be used when using a database other than the default.

Turns exception throwing on.

Parameters

db	Database to use for subsequent database queries

Definition at line 42 of file SqlQuery.cpp.

◆ SqlQuery() [3/4]

Isis::SqlQuery::SqlQuery	(	const std::string &	query,
		Database	db = `Database(Database::Connect)`
	)

Construct a query object and execute the provided query string.

This constructor will take a query string and an optional database specification and execute the query all after the initial construction. If the caller does not provide a Database, the default one is used and a connection is automatically attempted.

It can be used to execute an initial query and is perhaps the most powerful example of the Isis database design. It could be used as the starting point for any database access and an initial query in one line of code. For example, below is a line of code that executes a line of code showing all the tables in a PostgreSQL database using the default database:

SqlQuery pgtables("select tblname from pg_tables");

This will use the Database() default constructor since one is not explicitly provide. That constructor attempts a connection using the default database access profile as read from your IsisPreferences file. Its default behaviour is to initiate the connection to the database. If that suceeds, the query is issued. The resulting pgtables SqlQuery object can now be contiually used for other queries until it goes out of scope.

Parameters

query	IString containing a valid SQL query to execute
db	An optional database object to issue the query to. This database now becomes the one used in all subsequent queries using this SqlQuery object.

Definition at line 75 of file SqlQuery.cpp.

References exec().

◆ SqlQuery() [4/4]

Isis::SqlQuery::SqlQuery ( const SqlQuery & other )

Constructor using an existing SqlQuery object.

This constructor creates a new SqlQuery object from an existing one, inheriting all its characteristics - including a Database if one is associated to the object.

Parameters

other A SqlQuery object used to create this one from

Definition at line 90 of file SqlQuery.cpp.

Member Function Documentation

◆ exec()

bool Isis::SqlQuery::exec ( const std::string & query )

Execute an SQL query provided in the query string.

This method executes the given query in the string. This method assumes this query object has a valid and open Database connection associated with it. It will also check the result for valid completion and toss an iException if the caller has established this course of action when it fails.

Results are ready for processing on completion.

See also: SqlRecord.

Parameters

query An SQL query string to issue to a database

Returns: bool True if successful, false if it fails. This will only return valse if setNoThrowOnFailure() has been issued, otherwise an iException is thrown if it fails.

Definition at line 112 of file SqlQuery.cpp.

References isThrowing(), Isis::IString::ToQt(), and tossQueryError().

Referenced by SqlQuery().

◆ fieldIndex()

int Isis::SqlQuery::fieldIndex ( const std::string & name ) const

Returns index of column for given name.

This method returns the index of the given column name.

NOTE this is not valid until after the first next() is issued.

Parameters

name	Name of column to get index for

Returns: int Zero-based index of named column

Definition at line 181 of file SqlQuery.cpp.

References Isis::IString::ToQt().

◆ fieldName()

std::string Isis::SqlQuery::fieldName ( int index ) const

Returns the column name of the resulting query at the given index.

This method returns the name of the column heading as a result of the query at the given index.

NOTE this is not valid until after the first next() is issued.

Parameters

index Zero-based starting index of column name ot retreive

Returns: std::string Name of column at given index

Definition at line 166 of file SqlQuery.cpp.

References Isis::IString::ToStd().

◆ fieldNameList()

std::vector< std::string > Isis::SqlQuery::fieldNameList ( ) const

Returns the names of all fields in the resulting query.

After a query has been issued, this method will return the names of all fields/columns in the resulting query.

NOTE this is not valid until after the first next() is issued.

See also: SqlRecord::getFieldName().

Returns: std::vector<std::string> Vector of strings of all fields/columns in a query.

Definition at line 198 of file SqlQuery.cpp.

References Isis::IString::ToStd().

◆ fieldTypeList()

std::vector< std::string > Isis::SqlQuery::fieldTypeList ( ) const

Returns the types of each field/column in a resutling query.

After a query has been issued, this method will return the types of all fields/columns. These types are defined by the SqlRecord::getType() method.

Returns: std::vector<std::string> IString vector of all field types.

Definition at line 215 of file SqlQuery.cpp.

References getRecord(), and Isis::SqlRecord::getType().

◆ getQuery()

std::string Isis::SqlQuery::getQuery ( ) const

Returns the executed query string.

This method returns the last executed query string as it was issued to the database. Note that some database systems do not support this option directly. This routine will attempt to return the last executed query first from the Qt QSqlQuery class. If this is empty/undefined, then the last current query will be returned.

Returns: std::string The last executed query string. If undetermined or undefined, an empty string is returned.

Definition at line 133 of file SqlQuery.cpp.

References Isis::IString::ToStd().

◆ getRecord()

SqlRecord Isis::SqlQuery::getRecord ( ) const

Returns a SqlRecord for the current query row.

While traversing through the resulting query row set, this method returns a lower level interface to individual rows. The returned object is provided by the SqlRecord class.

NOTE this is not valid until after the first next() is issued.

See also: SqlRecord.

Returns: SqlRecord A single resulting row of a query after an next() is issued.

Definition at line 254 of file SqlQuery.cpp.

Referenced by fieldTypeList().

◆ isThrowing()

bool Isis::SqlQuery::isThrowing ( ) const

inline

Report error status when executing queries.

Returns: bool True if iExceptions are thrown upon errors, otherwise returns false.

Definition at line 153 of file SqlQuery.h.

References _throwIfFailed.

Referenced by exec().

◆ nFields()

int Isis::SqlQuery::nFields ( ) const

Returns the number of fields (columns) from query.

The method returns the number of fields or columns returned by the last issued query string. Note that if the query has not been issued, it will return 0 or an undefined value (-1?).

NOTE this is not valid until after the first next() is issued.

Returns: int Number of fields/columns in last query issued

Definition at line 150 of file SqlQuery.cpp.

◆ nRows()

int Isis::SqlQuery::nRows ( ) const

Returns the count of rows resulting from the query.

This returns the number of rows returned/accessable as a result of the issued query. Its value is governed by the Qt QsqlQuery::size() method. Namely, a -1 can be returned under many different conditions of the queray and the database (driver) support. Check the documentation for details.

Returns: int Number of rows returned by the query. -1 is typically returned under conditions where the value is not available or undefined.

Definition at line 237 of file SqlQuery.cpp.

◆ setNoThrowOnFailure()

void Isis::SqlQuery::setNoThrowOnFailure ( )

inline

Turns throwing of iExceptions off on errors.

Definition at line 166 of file SqlQuery.h.

References _throwIfFailed.

◆ setThrowOnFailure()

void Isis::SqlQuery::setThrowOnFailure ( )

inline

Sets throwing of exceptions on errors to true.

Definition at line 160 of file SqlQuery.h.

References _throwIfFailed.

◆ tossQueryError()

void Isis::SqlQuery::tossQueryError	(	const std::string &	message,
		const char *	f,
		int	l
	)		const

private

Issues an IException from various sources of error states in this class.

This method is provided to issue a consistant error message format from this class. The user of this class can decide at runtime to issue Isis::IException exceptions when error condition or query failures are detected or handle the errors themselves. All exceptions go through this method for deployment to simplify the process.

Note callers of this method within this class provide the context of the error, such as name and line of code, to preserve accuracy of the error context.

See also: setThrowOnFailure(), setNoThrowOnFailure().

Parameters

message	Context of the error to report
f	Routine name issuing the error
l	Line number of the offending error

Definition at line 278 of file SqlQuery.cpp.

References Isis::IString::ToStd(), and Isis::IException::User.

Referenced by exec().

Member Data Documentation

◆ _throwIfFailed

bool Isis::SqlQuery::_throwIfFailed

private

User can select action on query results.

Definition at line 186 of file SqlQuery.h.

Referenced by isThrowing(), setNoThrowOnFailure(), and setThrowOnFailure().

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

src/database/objs/SqlQuery/SqlQuery.h
src/database/objs/SqlQuery/SqlQuery.cpp

Public Member Functions

Private Member Functions

Private Attributes

Detailed Description

Constructor & Destructor Documentation

◆ SqlQuery() [1/4]

◆ SqlQuery() [2/4]

◆ SqlQuery() [3/4]

◆ SqlQuery() [4/4]

Member Function Documentation

◆ exec()

◆ fieldIndex()

◆ fieldName()

◆ fieldNameList()

◆ fieldTypeList()

◆ getQuery()

◆ getRecord()

◆ isThrowing()

◆ nFields()

◆ nRows()

◆ setNoThrowOnFailure()

◆ setThrowOnFailure()

◆ tossQueryError()

Member Data Documentation

◆ _throwIfFailed