SqlDatabase QML Type

Connection to a database. More...

Import Statement: import ArcGIS.AppFramework.Sql 1.0

List of all members, including inherited members

Properties

connectOptions : string
connectionName : string
databaseName : string
driverName : string
error : object
functions : List<SqlAbstractFunction>
hostName : string
isOpen : bool
isValid : bool
password : string
port : int
userName : string

Signals

databaseChanged()

Methods

bool beginTransaction()
close()
bool commitTransaction()
string escapeIdentifier(string identifier, identifiertype type)
string formatValue(sqlfield field, object value)
string formatValue(sqlfield field, object value, bool trim)
bool isIdentifierEscaped(string identifier, identifiertype type)
bool open()
SqlQuery query(string sql, ...)
SqlQueryModel queryModel(string sql, ...)
bool rollbackTransaction()
SqlTable table(string tableName)
SqlTableModel tableModel(string tableName)
SqlTableModel tableModel(string tableName, string filter)
SqlTableModel tableModel(string tableName, string filter, bool select)
QStringList tableNames()
QStringList tableNames(tabletype type)
string unescapeIdentifier(string identifier, identifiertype type)

Detailed Description

The SqlDatabase component provides an interface for accessing a database through a connection. Every SqlDatabase component represents a connection. This connection provides access to the database via one of the supported database drivers, such as SQLite, PostgreQL, MySQL, Microsoft SQL Server and other ODBC databases. By default, SqlDatabase uses SQLite.

SqlDatabase {
    id: db

    property FileInfo fileInfo: AppFramework.fileInfo("~/ArcGIS/Data/Sql/sample.sqlite")
    databaseName: fileInfo.filePath

    Component.onCompleted: {
        fileInfo.folder.makeFolder();
        db.open();
        db.exec( "DROP TABLE IF EXISTS Cities" );
        db.exec( "CREATE TABLE IF NOT EXISTS Cities ( name TEXT, country TEXT, population INT, latitude REAL, longitude REAL ); " );
        db.insertCity( "Melbourne", "Australia", 4186000, -37.9716929, 144.7729583 );
        db.insertCity( "London", "UK", 8788000000, 51.5287718, -0.2416804 );
        db.insertCity( "Paris", "France", 2241000, 48.8589507, 2.2770205 );
        db.insertCity( "New York City", "USA", 8538000000, 40.6976637, -74.1197639 );
        db.insertCity( "Tokyo", "Japan", 13617000, 35.6735408, 139.5703047 );
        db.exec( "SELECT COUNT(*) as count_cities FROM Cities" );
        db.exec( "SELECT * FROM Cities" );
    }

    function exec( sql, ...params ) {
        let q = db.query( sql, ...params );
        for ( let ok = q.first() ; ok ; ok = q.next() )
            console.log( "db.query.values: ", JSON.stringify( q.values ) );
        q.finish();
    }

    function insertCity( name, country, population, latitude, longitude ) {
        db.exec( "INSERT INTO Cities VALUES (:name, :country, :population, :latitude, :longitude) ",
             { name, country, population, latitude, longitude } );
    }
}

Enumerations

TableType enumeration

This enum describes types of SQL tables. This informs the tableNames method.

Name	Value
SqlDatabase.Tables	1
SqlDatabase.SystemTables	2
SqlDatabase.Views	4
SqlDatabase.AllTables	255

IdentifierType enumeration

This enum describes the SQL identifier types. It informs the escapeIdentifier, isIdentifierEscaped, and unescapeIdentifier methods.

Name	Value
SqlDatabase.FieldName	0
SqlDatabase.TableName	1

Property Documentation

connectOptions : string

Returns the connection options string used for this connection. This string may be empty.

connectionName : string

Returns the connection name (not to be confused with databaseName). This may be empty.

databaseName : string

Returns the name of the SQL database being written to and/or read from.

The value ":memory:" can be used to specify the in-memory database, rather than a set file location.

driverName : string

Returns the connection's driver name.

[read-only] error : object

Returns information about the last error that occurred in the database.

[read-only] functions : List<SqlAbstractFunction>

Returns a list of SqlAbstractFunction objects available in the SqlDatabase. These can be either SqlScalarFunction or SqlAggregateFunction objects.

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

    SqlScalarFunction {
        name: "toDegrees"
        method: function (radians) { return radians * 180.0 / Math.PI; }
    }

    SqlAggregateFunction {
        name: "average"
        initialize: function () { return { sum: 0, count: 0 }; }
        iterate: function (context, value) { context.sum += value; context.count++; }
        finalize: function (context) { return context.count ? context.sum / context.count : Number.NaN; }
    }

    Component.onCompleted: open()
}

Column {
    spacing: 5

    Text {
        text: qsTr("db.functions.length: %1").arg(db.functions.length)
    }

    // The expected output in this field: db.functions.length: 2

    TextArea {
        text: JSON.stringify(db.functions, undefined, 2)
    }

    // The expected output in this field:
    // {
    //   "0": {
    //     "objectName": "",
    //     "name": "toDegrees",
    //     "deterministic": true,
    //     "argumentTypes": {
    //       "0": "*"
    //     }
    //   },
    //   "1": {
    //     "objectName": "",
    //     "name": "average",
    //     "deterministic": true,
    //     "argumentTypes": {
    //       "0": "*"
    //     }
    //   }
    // }
}

hostName : string

Returns the connection's host name. It may be empty.

[read-only] isOpen : bool

Returns true if the database connection is currently open. Otherwise, returns false.

[read-only] isValid : bool

Returns true if the SqlDatabase has a valid driver.

password : string

Returns the connection's password.

port : int

Returns the connection's port number. If a port number has not been set, returns undefined.

userName : string

Returns the connection's username. This may be empty.

Signal Documentation

databaseChanged()

Signal emitted whenever a database is opened or closed.

Note: The corresponding handler is onDatabaseChanged.

Method Documentation

bool beginTransaction()

Begins a transaction. If successful, returns true; otherwise, returns false.

close()

Closes the database connection, freeing any resources acquired, and invalidating any existing SqlQuery objects that are used with the database.

bool commitTransaction()

Commits a transaction to the database, if transactions are supported and one has been started. Returns true if the transaction has succeeded, otherwise returns false.

string escapeIdentifier(string identifier, identifiertype type)

Returnes the designated identifier escaped according to database rules. The type parameter determines if the identifier is a table or field name.

The identifier parameter

The escaped identifier to return.

The type parameter

The type of identifier being returned. Informed by the IdentifierType enum.

string formatValue(sqlfield field, object value)

Returns a string representation of the given value from the field in the table.

If the value is character date, the value is returned enclosed in single quotation marks, with any embedded single-quote characters escaped and replaced with two single-quote characters. If the value is date/time data, the value is formatted in ISO format and enclosed in single quotation marks. If the value is bytearray data, and the driver can edit binary fields, the value is formatted as a hexidecimal string.

The field parameter

The field to format a value from.

The value parameter

The value to return a string representation of.

string formatValue(sqlfield field, object value, bool trim)

Returns a string representation of the given value from the field in the table. If the trim boolean is set to true, all trailing whitespace is trimmed from the field.

The field parameter

The field to format a value from.

The value parameter

The value to return a string representation of.

The trim parameter

If true, removes trailing whitespace from the field.

bool isIdentifierEscaped(string identifier, identifiertype type)

Returns whether the identifier parameter is escaped according to database rules.

The identifier parameter

The name of the identifier to check for.

The type parameter

Whether the identifier is a field or table name. Is informed by the IdentifierType enum.

bool open()

Opens the database connection using the current connection values. Returns true on success, otherwise returns false.

SqlQuery query(string sql, ...)

Returns an SqlQuery object to run a 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.

SqlQueryModel queryModel(string sql, ...)

Returns an SqlQueryModel object to run a 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 rollbackTransaction()

Rolls back a transaction to the database, if transactions are supported and one has been started. Returns true if the rollback has succeeded, otherwise returns false.

SqlTable table(string tableName)

Returns a SqlTable object populated with the names of all the fields in the table or view identified by tableName. If no such table or view exists, this method returns an empty SqlTable object.

The tableName parameter

Identifies a table object in the database. Depending on the driverName (i.e. QSQLITE, QPSQL, QODBC), the tableName may be prefixed with a database, an instance, or instance qualifiers. The tableName may also contain square brackets or quotes for table names that are keywords, or that contain special characters.

SqlTableModel tableModel(string tableName)

Returns a SqlTableModel object populated with the names of all the fields in the table or view identified by tableName. If no such table or view exists, this method returns an empty SqlTableModel.

The tableName parameter

SqlTableModel tableModel(string tableName, string filter)

Returns a SqlTableModel object populated with the names of all the fields in the table or view identified by tableName. If no such table or view exists, this method returns an empty SqlTableModel.

The tableName parameter

The filter parameter

An SQL WHERE clause, without the 'WHERE' keyword. For example, "num > 3".

SqlTableModel tableModel(string tableName, string filter, bool select)

Returns a SqlTableModel object populated with the names of all the fields in the table or view identified by tableName. If no such table or view exists, this method returns an empty SqlTableModel.

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

        ComboBox {
                id: comboBox
                textRole: "numText"
        }

        Component.onCompleted: {
                db.open()
                db.query("CREATE TABLE primes (num INT, numText TEXT) ");
                db.query("INSERT INTO primes VALUES (2, 'two') ");
                db.query("INSERT INTO primes VALUES (3, 'three') ");
                db.query("INSERT INTO primes VALUES (5, 'five') ");
                db.query("INSERT INTO primes VALUES (7, 'seven') ");
                comboBox.model = db.tableModel("primes", "num > 3", true);
                // Populates ComboBox with "five" and "seven".
        }
}

The tableName parameter

The filter parameter

An SQL WHERE clause, without the 'WHERE' keyword. For example, "num > 3".

The select parameter

If true, the table is populated as per the filter parameter. If false, the filter is not applied. The default value is true.

QStringList tableNames()

Returns a list of the database's tables, system tables and views.

QStringList tableNames(tabletype type)

Returns a list of the database's tables, system tables and views, as specified by the type parameter.

The type parameter

The type of SQL table being returned. This is informed by the TableType enum.

string unescapeIdentifier(string identifier, identifiertype type)

Reverses the escaping of the given identifier, of the defined type.

The identifier parameter

The identifier to reverse the escaping of.

The type parameter

The type of identifier to retrieve. Uses the IdentifierType enum.