ArcGIS for Developers

SqlQueryModel QML Type

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

[read-only] count : int

Returns the number of rows in the query.


[read-only] error : object

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.


[read-only] lastQuery : string

Returns the text of the current query being used. If there is no current query, returns an empty string.


[read-only] roleNames : QStringList

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

rowCountChanged()

Signal emitted when the count property has changed.


Method Documentation

bool execute(sql)

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.


object get(row)

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.