SqlQueryModel QML Type

Read-only data model for SQL results sets. More...

• List of all members, including inherited members

Properties

• count : int
• error : object
• fields : SqlFieldList
• lastQuery : string
• roleNames : QStringList

Signals

• rowCountChanged()

Methods

• bool execute(string sql)
• object get(int row)

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

Signal Documentation

Method Documentation

Component.onCompleted: {
    console.log(queryModel.get(0).num);
}