Isis 3 Programmer Reference
Isis::SqlQuery Class Reference

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

#include <SqlQuery.h>

Inheritance diagram for Isis::SqlQuery:
Inheritance graph
Collaboration diagram for Isis::SqlQuery:
Collaboration graph

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,
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().

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

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 152 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 43 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
dbDatabase to use for subsequent database queries

Definition at line 55 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
queryIString containing a valid SQL query to execute
dbAn 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 88 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
otherA SqlQuery object used to create this one from

Definition at line 103 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
queryAn 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 125 of file SqlQuery.cpp.

References _FILEINFO_, 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
nameName of column to get index for
Returns
int Zero-based index of named column

Definition at line 194 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
indexZero-based starting index of column name ot retreive
Returns
std::string Name of column at given index

Definition at line 179 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 211 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 228 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 146 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 267 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 167 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 163 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 250 of file SqlQuery.cpp.

◆ setNoThrowOnFailure()

void Isis::SqlQuery::setNoThrowOnFailure ( )
inline

Turns throwing of iExceptions off on errors.

Definition at line 180 of file SqlQuery.h.

References _throwIfFailed.

◆ setThrowOnFailure()

void Isis::SqlQuery::setThrowOnFailure ( )
inline

Sets throwing of exceptions on errors to true.

Definition at line 174 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
messageContext of the error to report
fRoutine name issuing the error
lLine number of the offending error

Definition at line 291 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 200 of file SqlQuery.h.

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


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