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(identifier, identifiertype type)
string formatValue(field, object value)
string formatValue(field, object value, bool trim)
bool isIdentifierEscaped(identifier, identifiertype type)
bool open()
SqlQuery query(sql, ...)
SqlQueryModel queryModel(sql, ...)
bool rollbackTransaction()
SqlTable table(tableName)
SqlTableModel tableModel(tableName)
SqlTableModel tableModel(tableName, string filter)
SqlTableModel tableModel(tableName, string filter, bool select)
QStringList tableNames()
QStringList tableNames(type)
string unescapeIdentifier(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 user name. This may be empty.

Signal Documentation

databaseChanged()

Signal emitted whenever a database is opened or closed.

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(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(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(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(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(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(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(tableName)

Creates an SqlTable object using the defined table name.

The tableName parameter

The name of the table to create.

SqlTableModel tableModel(tableName)

Creates an SqlTableModel object using the defined name.

The tableName parameter

The name of the table to create.

SqlTableModel tableModel(tableName, string filter)

Creates an SqlTableModel object using the defined name and filter.

The tableName parameter

The name of the table to create.

The filter parameter

The filter to apply to the table model.

SqlTableModel tableModel(tableName, string filter, bool select)

Returns an editable data model for a single database table.

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 name of the table that will be used to provide values for the data model.

The filter parameter

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

The select parameter

If true, this will revert any unsubmitted changes and remove any inserted columns.

QStringList tableNames()

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

QStringList tableNames(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(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.

AppStudio

SqlDatabase QML Type

Properties

Signals

Methods

Detailed Description

Enumerations

TableType enumeration

IdentifierType enumeration

Property Documentation

Signal Documentation

Method Documentation