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...

virtual	~SqlQuery ()

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...

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

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.

◆ 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

◆ 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.

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

◆ ~SqlQuery()

virtual Isis::SqlQuery::~SqlQuery ( )

inlinevirtual

Member Function Documentation

◆ exec() [1/2]

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.

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

◆ exec() [2/2]

bool Isis::SqlQuery::exec ( )

inline

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

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

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.

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.

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.

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.

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.

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

◆ 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.

◆ setNoThrowOnFailure()

void Isis::SqlQuery::setNoThrowOnFailure ( )

inline

Turns throwing of iExceptions off on errors.

◆ setThrowOnFailure()

void Isis::SqlQuery::setThrowOnFailure ( )

inline

Sets throwing of exceptions on errors to true.

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

/usgs/pkgs/isis3.9.0_docs/ISIS3/isis/src/database/objs/SqlQuery/SqlQuery.h
/usgs/pkgs/isis3.9.0_docs/ISIS3/isis/src/database/objs/SqlQuery/SqlQuery.cpp

Public Member Functions

Detailed Description

Constructor & Destructor Documentation

◆ SqlQuery() [1/4]

◆ SqlQuery() [2/4]

◆ SqlQuery() [3/4]

◆ SqlQuery() [4/4]

◆ ~SqlQuery()

Member Function Documentation

◆ exec() [1/2]

◆ exec() [2/2]

◆ fieldIndex()

◆ fieldName()

◆ fieldNameList()

◆ fieldTypeList()

◆ getQuery()

◆ getRecord()

◆ isThrowing()

◆ nFields()

◆ nRows()

◆ setNoThrowOnFailure()

◆ setThrowOnFailure()