SqlQuery QML Type

Executes and manipulates SQL statements. More...

Import Statement: import ArcGIS.AppFramework.Sql 1.0

List of all members, including inherited members

Properties

boundValues : object
count : int
currentIndex : int
error : object
fields : SqlFieldList
insertId : object
isActive : bool
isForwardOnly : bool
isPrepared : bool
isSelect : bool
isValid : bool
rowsAffected : int
values : object

Signals

queryChanged()

Methods

object boundValue(string name)
object boundValue(int index)
bool execute(string sql, ...)
bool executePrepared(...)
finish()
bool first()
bool last()
bool next()
bool prepare(string sql)
bool previous()
bool seek(int index)
bool seek(int index, bool relative)
object value(string name)
object value(int index)

Detailed Description

The SqlQuery component encapsulates the functionality involved in creating, navigating and retrieving data from SQL queries which are executed on a SqlDatabase. It can be used to execute DML (data manipulating language) statements, such as SELECT, INSERT, UPDATE and DELETE, as well as DDL (data definition language) statements, such as CREATE TABLE. It can also be used to execute database-specific commands which are not standard SQL (e.g. SET DATESTYLE=ISO for PostgreSQL).

Successfully executed SQL statements set the query state to active and reset the error object to null. Otherwise the query state is inactive and there will be an error object with meta data of the last database error.

Navigating records is performed with the next, previous, first, last and seek methods.

This code sample demonstrates usage of the SqlQuery component. An in-memory SqlDatabase is created, inside which a table 'cities' with columns 'city', 'country' and 'pop' is created. SqlQuery then prints the contents of this table into the console.

SqlDatabase {
    id: db
    databaseName: ":memory:"

    // Convert Sql error to JavaScript exception
    function throw_db_error(dbError) {
        throw new Error( "Error %1 (Type %2)\n%3\n%4\n"
           .arg(dbError.nativeErrorCode)
           .arg(dbError.type)
           .arg(dbError.driverText)
           .arg(dbError.databaseText)
        );
    }

    // Run SQL query with logging and error handling
    function db_exec_sql(db, sql, ...params) {
        let dbQuery = db.query(sql, ...params);
        if (dbQuery.error) throw_db_error(dbQuery.error);
        let ok = dbQuery.first();
        while (ok) {
            console.log(JSON.stringify(dbQuery.values));
            ok = dbQuery.next();
        }
        dbQuery.finish();
    }

    // Prepare a SQL query with error handling
    function db_prepare_sql(db, sql) {
        let dbQuery = db.query();
        dbQuery.prepare(sql);
        if (dbQuery.error) throw_db_error(dbQuery.error);
        return dbQuery;
    }

    // Execute prepared SQL query with logging and error handling
    function db_exec_prepared(dbQuery, ...params) {
        dbQuery.executePrepared( ...params);
        if (dbQuery.error) throw_db_error(dbQuery.error);
        let ok = dbQuery.first();
        while (ok) {
            console.log(JSON.stringify(dbQuery.values));
            ok = dbQuery.next();
        }
        dbQuery.finish();
    }

    Component.onCompleted: {
        db.open();

        db_exec_sql(db, "CREATE TABLE cities (ID integer PRIMARY KEY AUTOINCREMENT, city TEXT, country TEXT, pop INTEGER); ");
        db_exec_sql(db, "CREATE INDEX IX_cities_001 ON cities (pop); ");

        // Transactions and bulk loading using prepared SQL queries and named parameters
        db.beginTransaction();
        let insert = db_prepare_sql(db, "INSERT INTO cities (city, country, pop) VALUES (:city, :country, :pop); ");
        db_exec_prepared(insert, { city:'Redlands', country:'USA', pop:71554 } );
        db_exec_prepared(insert, { city:'Melbourne', country:'Australia', pop:4820000 } );
        db_exec_prepared(insert, { city:'Paris', country:'France', pop:2200000 } );
        db_exec_prepared(insert, { city:'Tokyo', country:'Japan', pop:9273000 } );
        db_exec_prepared(insert, { city:'London', country:'UK', pop:8136000 } );
        db_exec_prepared(insert, { city:'New York', country:'USA', pop:8623000 } );
        db.commitTransaction();

        // Query a database repeatedly with prepared SQL queries and unnamed parameters
        let lookup = db_prepare_sql(db, "SELECT country FROM cities WHERE city = ?");
        db_exec_prepared(lookup, "Redlands"); // qml: {"country":"USA"}
        db_exec_prepared(lookup, "Melbourne"); // qml: {"country":"Australia"}
        db_exec_prepared(lookup, "Paris"); // qml: {"country":"France"}
        db_exec_prepared(lookup, "Tokyo"); // qml: {"country":"Japan"}
        db_exec_prepared(lookup, "London"); // qml: {"country":"UK"}
        db_exec_prepared(lookup, "New York"); // qml: {"country":"USA"}

        // Execute a SQL query normally
        db_exec_sql(db, "SELECT * FROM cities ORDER BY pop; ");
        // qml: {"ID":1,"city":"Redlands","country":"USA","pop":71554}
        // qml: {"ID":3,"city":"Paris","country":"France","pop":2200000}
        // qml: {"ID":2,"city":"Melbourne","country":"Australia","pop":4820000}
        // qml: {"ID":5,"city":"London","country":"UK","pop":8136000}
        // qml: {"ID":6,"city":"New York","country":"USA","pop":8623000}
        // qml: {"ID":4,"city":"Tokyo","country":"Japan","pop":9273000}

        // Test SQL query error handling
        db_exec_sql(db, "SELECT foo FROM bar; ");
        // <Unknown File>: QML SqlQuery: SQL Error:1:Unable to execute statement:no such table: bar
        // ArcGIS.AppFramework.Player: Object: QObject(0x0) Text: "file:///C:/Users/stephen/ArcGIS/AppStudio/Apps/SqlQuery/MyApp.qml:7:1: QML SqlDatabase: SQL Error:1:Unable to execute statement:no such table: bar"
        // file:///C:/Users/stephen/ArcGIS/AppStudio/Apps/SqlQuery/MyApp.qml:7:1: QML SqlDatabase: SQL Error:1:Unable to execute statement:no such table: bar
        // ArcGIS.AppFramework.Player: Object: QObject(0x0) Text: "file:///C:/Users/stephen/ArcGIS/AppStudio/Apps/SqlQuery/MyApp.qml:12: Error: Error 1 (Type 2)\nUnable to execute statement\nno such table: bar\n"
        // file:///C:/Users/stephen/ArcGIS/AppStudio/Apps/SqlQuery/MyApp.qml:12: Error: Error 1 (Type 2)
        // Unable to execute statement
        // no such table: bar
    }
}

Property Documentation

[read-only] boundValues : object

Returns all bound values contained in the query.

[read-only] count : int

Returns the number of rows returned by the query. If the size cannot be determined, or the database does not support reporting information about library sizes, returns -1. Note that for non-SELECT queries, or if the query is not active, this property will return -1.

For non-SELECT statements, use the rowsAffected property.

currentIndex : int

Returns the current index location.

[read-only] error : object

Returns an object containing information about the last error (if any) that occurred with this query.

The object contains the following properties: isValid: This will always return 1 (true). type: The error type, as a number; either 0 (NoError), 1 (ConnectionError), 2 (StatementError), 3 (TransactionError), or 4 (UnknownError). databaseText: The text of the error as reported by the SQL database. This may contain database-specific descriptions. driverText: The text of the error as reported by the driver. This may contain database-specific descriptions. text: databaseText and driverText, concatenated into a single string. nativeErrorCode: The database-specific error code. If this cannot be determined, returns an empty string.

[read-only] fields : SqlFieldList

Returns an SqlFieldList object, to display fields available in the SQL database.

[read-only] insertId : object

Returns the object ID of the most recent inserted row, if the database supports it. An invalid value will be returned if the query did not insert any value or if the database does not report the id back. If the query didn't insert any value, or if the database doesn't report the ID back, an invalid value will be returned. If more than one row was touched by the insert, the behavior is undefined.

[read-only] isActive : bool

Returns true if the query is active. An active query is one that has been successfully executed but not yet finished with.

[read-only] isForwardOnly : bool

Returns true if you can only scroll forward through a result set. Otherwise, returns false.

[read-only] isPrepared : bool

Returns true if the current query has been prepared for execution. Otherwise, returns false. This property is influenced by both the prepare and execute methods.

[read-only] isSelect : bool

Returns true if the current query is a SELECT statement. Otherwise, returns false.

[read-only] isValid : bool

Returns true if the query is currently postitioned on a valid record. Otherwise, returns false.

[read-only] rowsAffected : int

Returns the number of rows affected by the result's SQL statement. If the number cannot be determined, returns -1.

For a SELECT statement, this value is undefined. For SELECT statements, use the count property.

[read-only] values : object

Returns an object representing values from a row of the current query. If there is no active query, returns an empty record.

Signal Documentation

queryChanged()

Signal emitted when the query has changed.

Note: The corresponding handler is onQueryChanged.

Method Documentation

object boundValue(string name)

Returns the value for the field name.

The name parameter

The name of the field.

object boundValue(int index)

Returns the value for the field name at the given index position.

The index parameter

The index number to return the field name for.

bool execute(string sql, ...)

Runs an SQL query. This accepts a variable number of arguments as well as the required name of the SQL, to define the query to execute.

The sql parameter

The name of the SQL database to run a query on.

bool executePrepared(...)

Executes a query on the SQL provided by the prepared method. This accepts a variable number of arguments, to define the query to execute.

finish()

Instructs the database driver that no more data will be fetched from this query until it is re-executed. There is normally no need to call this function, but it may be helpful in order to free resources such as locks or cursors if you intend to re-use the query at a later time. Bound values retain their values.

Two separate errors can be caused when a second query using the SQLQuery component is commenced, before a first query is completed: an error reporting the database is locked ("Unable to fetch row: database table is locked") or an error reporting the database is still in use ("QSqlDatabasePrivate::removeDatabase: connection '{XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}' is still in use, all queries will cease to work").

These errors can be avoided by using the finish method to let the app know that any remaining results of the first query are not required, or by ensuring all results are requested with iterative calls to the next method.

bool first()

Retrieves the first record in the result, if available, and positions the query on the retrieved record. Note that the isSelect property must return true before calling this function, or it will do nothing and the method will return false. Returns true if successful. If unsuccessful, the query position is set to an invalid position and the method returns false.

bool last()

Retrieves the final record in the result, if available, and positions the query on the retrieved record. Note that the isSelect property must return true before calling this function, or the method will do nothing and return false. If successful, returns true. If unsuccessful, the query position is set to an invalid position and the method returns false.

bool next()

Retrieves the next record in the result, if available, and positions the query on the retrieved record. Note that isSelect must return true before calling this method, or it will do nothing and return false.

If the result is currently located before the first record, an attempt is made to retrive the first record. If the result is currently located after the last record, there is no change and the meethod returns false. If the result is somewhere between the first and last records, an attempt is made to retrieve the next record.

If the record could not be retrieved for any of the above reasons, the result is positioned after the final record and the method returns false. If the record is successfully retrieved, the method returns true.

bool prepare(string sql)

Prepares the given SQL query for execution. If the query is prepared successfully, returns true. Otherwise, returns false.

This query may contain placeholders for binding values. Both colon-name placeholders (e.g. :firstname, :surname) and ODBC-style question mark placeholders (e.g. ?) are supported, but they cannot be mixed in the same query.

Note that some databases choose to delay preparing a query until it's executed the first time. In this case, preparing an invalid query succeeds, but every attempt to execute it will fail.

When using SQLite, the query string can only contain one statement at a time. If more than one statement is given, this method returns false.

The sql parameter

The name of the SQL query to prepare.

bool previous()

Retrieves the previous record in the result, if available, and positions the query on the retrieved record. Note that isSelect must return true before calling this method, or it will do nothing and return false.

If the result is currently located before the first record, there is no change and the method returns false. If the result is currently located after the last record, an attempt is made to retrieve the last record. If the result is somewhere between the first and last records, an attempt is made to retrieve the previous record.

If the record could not be retrieved for any of the above reasons, the result is positioned before the first record and the method returns false. If the record is successfully retrieved, the method returns true.

bool seek(int index)

Retrieves the record at the position defined by index, if available, and positions the query on the retrieved record. Fields are numbered from left to right, starting with 0.

If the relative boolean is undefined, it defaults to false, meaning that if the index parameter is negative, the result is positioned before the first record and the method returns false. Otherwise, this method attemps to move to the record at the position defined by the index parameter, returning true if successful. If the record could not be retrieved, the result is positioned after the last record and the method returns false.

The index parameter

The index number of the field the record is to be retrieved from.

bool seek(int index, bool relative)

Retrieves the record at the position defined by index, if available, and positions the query on the retrieved record. Fields are numbered from left to right, starting with 0.

If the relative boolean is false, the method behaves as follows: if the index parameter is negative, the result is positioned before the first record and the method returns false. Otherwise, this method attemps to move to the record at the position defined by the index parameter, returning true if successful. If the record could not be retrieved, the result is positioned after the last record and the method returns false.

If the relative boolean is true, the method behavior is dependent on where the result is currently positioned.

If the result is currently positioned before the first record, and the index parameter is negative or zero, there is no change and the method returns false. If the index parameter is positive, an attempt is made to position the result at the position 'index - 1', following the same rules as non-relative seeking above.

If the result is currently positioned after the last record, and the index parameter is positive or zero, there is no change and the method returns false. If the index parameter is negative, an attempt is made to position the result at the position 'index + 1', following the same rules as non-relative seeking above.

If the result is currently located somewhere in the middle, and the relative offset moves the result below zero, the result is positioned before the first record and false is returned.

Otherwise, an attempt is made to move to the recorded index ahead of the current record (or the index parameter records behind the current record, if the index parameter is negative). If the record at the given location could not be retrieved, the result is positioned after the last record if the index parameter is greater than or equal to zero 0 (or before the first record if index is negative), and false is returned. If the record is successfully retrieved, the method returns true.

The index parameter

The index number of the field the record is to be retrieved from.

The relative parameter

Determines the rules used when this method is attempting to retrieve records.

object value(string name)

Returns the value of the field defined by name in the current record.

The name parameter

The name of the field to return a value from.

object value(int index)

Returns the value of the field defined by the index number in the current record. When using an integer to perform this method, fields are numbered from left to right using the text of the SELECT statement, starting with 0.

The index parameter

The index number of the field to return a value from.