Read-only data model for SQL results sets. More...
Import Statement: | import ArcGIS.AppFramework.Sql 1.0 |
Properties
Signals
Methods
Detailed Description
SqlQueryModel is a read-only data model for SQL results sets. It's returned by a call to queryModel on SqlDatabase with a SQL statement. This component is useful for rapidly populating components such as ListView and ComboBox, and behaves similar to ListModel. This component can also be used for combining content from two or more table into one result set, by pulling in data from SELECT with INNER JOIN.
This code sample demonstrates usage of the SqlQueryModel component. A TableView component is populated with data joined from two different tables, named 'Cities' and 'Demographics'. Be aware that, to support the TableView component, you will need to import QtQuick.Controls 1.4, rather than the latest version (you can import both).
Item { property FileFolder sqlFolder: AppFramework.userHomeFolder.folder("ArcGIS/Data/Sql") property SqlQueryModel sqlQueryModel TableView { id: tableView anchors.fill: parent horizontalScrollBarPolicy: Qt.ScrollBarAlwaysOn flickableItem.flickableDirection: Flickable.HorizontalAndVerticalFlick headerDelegate: TextField { text: styleData.value readOnly: true font.pointSize: 10 background: LinearGradient { start: Qt.point(0, 0) end: Qt.point(0, height) gradient: Gradient { GradientStop { position: 0.0; color: "white" } GradientStop { position: 1.0; color: "#e0e0e0" } } } } rowDelegate: Rectangle { color: styleData.selected ? "#0077CC" : styleData.row & 1 ? "white" : "#f5f5f5" height: 20 * AppFramework.displayScaleFactor } itemDelegate: Text { text: styleData.value color: styleData.textColor elide: styleData.elideMode } Component { id: columnComponent TableViewColumn { width: 100 * AppFramework.displayScaleFactor } } function setColumnsByModel(model) { while (columnCount > 0) { removeColumn(0); } model.roleNames.forEach(addColumnByName); } function addColumnByName(name) { addColumn(columnComponent.createObject(tableView, { role: name, title: capitalize(name) } )); } } SqlDatabase { id: db databaseName: sqlFolder.filePath("sample.sqlite") } function toUpperCase(c) { return c.toUpperCase(); } function capitalize(str) { return str.replace(/^./, toUpperCase); } Component.onCompleted: { sqlFolder.makeFolder(); db.open(); db.query("DROP TABLE IF EXISTS Demographics"); db.query("DROP TABLE IF EXISTS Cities"); db.query("CREATE TABLE IF NOT EXISTS Cities ( name TEXT, country TEXT, latitude REAL, longitude REAL ); "); db.query("INSERT INTO Cities VALUES (:name, :country, :latitude, :longitude) ", { name: "Melbourne", country: "Australia", latitude: -37.9716929, longitude: 144.7729583 } ); db.query("INSERT INTO Cities VALUES (:name, :country, :latitude, :longitude) ", { name: "London", country: "UK", latitude: 51.5287718, longitude: -0.2416804 } ); db.query("INSERT INTO Cities VALUES (:name, :country, :latitude, :longitude) ", { name: "Paris", country: "France", latitude: 48.8589507, longitude: 2.2770205 } ); db.query("INSERT INTO Cities VALUES (:name, :country, :latitude, :longitude) ", { name: "New York City", country: "USA", latitude: 40.6976637, longitude: -74.1197639 } ); db.query("INSERT INTO Cities VALUES (:name, :country, :latitude, :longitude) ", { name: "Tokyo", country: "Japan", latitude: 35.6735408, longitude: 139.5703047 } ); db.query("CREATE TABLE IF NOT EXISTS Demographics ( name TEXT, country TEXT, population INT ); "); db.query("INSERT INTO Demographics VALUES (:name, :country, :population) ", { name: "Melbourne", country: "Australia", population: 4186000 } ); db.query("INSERT INTO Demographics VALUES (:name, :country, :population) ", { name: "London", country: "UK", population: 8788000000 } ); db.query("INSERT INTO Demographics VALUES (:name, :country, :population) ", { name: "Paris", country: "France", population: 2241000 } ); db.query("INSERT INTO Demographics VALUES (:name, :country, :population) ", { name: "New York City", country: "USA", population: 8538000000 } ); db.query("INSERT INTO Demographics VALUES (:name, :country, :population) ", { name: "Tokyo", country: "Japan", population: 13617000 } ); var sql = [ "SELECT C.name, ", " C.country, ", " D.population, ", " C.latitude, ", " C.longitude ", "FROM Cities C ", "INNER JOIN Demographics D ", "ON D.name = C.name ", "AND D.country = C.country " ].join("\n"); tableView.model = sqlQueryModel = db.queryModel(sql); tableView.setColumnsByModel(sqlQueryModel); } }
Property Documentation
Returns an object containing information about the last error that occurred on the database. If there hasn't yet been an error, returns null.
[read-only] fields : SqlFieldList |
Returns the record containing information about the fields of the current query.
Returns the text of the current query being used. If there is no current query, returns an empty string.
Returns a list of field names. This will be consumed as a model role used in populating components (e.g. textRole in a ComboBox).
Signal Documentation
Signal emitted when the count property has changed.
Note: The corresponding handler is onRowCountChanged
.
Method Documentation
Executes the SQL query defined by the sql parameter. Returns true if the query was successful, otherwise returns false. The string must use syntax appropriate for the SQL database being queried (for example, standard SQL). Note that all properties (e.g. count, error, field, lastQuery) will change when this method is called.
The sql parameter
The SQL query you intend to execute. In the detailed description for this component, the sql parameter is "SELECT * FROM students ORDER BY name ASC; ", which returns all records in the table named 'students' and orders them alphabetically.
Returns the item at index in the data model. This allows the item data to be accessed or modified from JavaScript.
Component.onCompleted: { console.log(queryModel.get(0).num); }
The row parameter
The index number of a row in the SQL result set. In the example above, this parameter is 0, which returns the first record in the model.