 |
Isis Developer Reference
|
|
Isis Developer Reference
|
Construct and execute a query on a database and manage result. More...
#include <SqlQuery.h>
Public Member Functions | |
| SqlQuery () | |
| Default constructor. | |
| SqlQuery (Database &db) | |
| Construtor using a specified database. | |
| SqlQuery (const std::string &query, Database db=Database(Database::Connect)) | |
| Construct a query object and execute the provided query string. | |
| SqlQuery (const SqlQuery &other) | |
| Constructor using an existing SqlQuery object. | |
| virtual | ~SqlQuery () |
| bool | isThrowing () const |
| Report error status when executing queries. | |
| void | setThrowOnFailure () |
| Sets throwing of exceptions on errors to true. | |
| void | setNoThrowOnFailure () |
| Turns throwing of iExceptions off on errors. | |
| bool | exec (const std::string &query) |
| Execute an SQL query provided in the query string. | |
| bool | exec () |
| std::string | getQuery () const |
| Returns the executed query string. | |
| int | nFields () const |
| Returns the number of fields (columns) from query. | |
| std::string | fieldName (int index) const |
| Returns the column name of the resulting query at the given index. | |
| int | fieldIndex (const std::string &name) const |
| Returns index of column for given name. | |
| std::vector< std::string > | fieldNameList () const |
| Returns the names of all fields in the resulting query. | |
| std::vector< std::string > | fieldTypeList () const |
| Returns the types of each field/column in a resutling query. | |
| int | nRows () const |
| Returns the count of rows resulting from the query. | |
| SqlRecord | getRecord () const |
| Returns a SqlRecord for the current query row. | |
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" @code 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(); } @endcode 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. @code 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(); }
| Isis::SqlQuery::SqlQuery | ( | ) |
| 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.
| db | Database to use for subsequent database queries |
| 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:
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.
| 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().
| Isis::SqlQuery::SqlQuery | ( | const SqlQuery & | other | ) |
|
inlinevirtual |
|
inline |
Referenced by SqlQuery().
| 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.
| query | An SQL query string to issue to a database |
References _FILEINFO_, isThrowing(), and Isis::IString::ToQt().
| 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.
| name | Name of column to get index for |
References Isis::IString::ToQt().
| 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.
| index | Zero-based starting index of column name ot retreive |
References fieldName(), and Isis::IString::ToStd().
Referenced by fieldName().
| 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.
References Isis::IString::ToStd().
| 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.
References getRecord().
| 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.
References Isis::IString::ToStd().
| 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.
Referenced by fieldTypeList().
|
inline |
Report error status when executing queries.
Referenced by exec().
| 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.
| 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.
|
inline |
Turns throwing of iExceptions off on errors.
|
inline |
Sets throwing of exceptions on errors to true.