SqlDatabase QML Type
Connection to a database. More...
Import Statement: | import ArcGIS.AppFramework.Sql 1.0 |
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
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
Returns the connection options string used for this connection. This string may be empty.
Returns the connection name (not to be confused with databaseName). This may be empty.
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.
[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": "*" // } // } // } }
Returns true if the database connection is currently open. Otherwise, returns false.
Returns true if the SqlDatabase has a valid driver.
Returns the connection's port number. If a port number has not been set, returns undefined.
Signal Documentation
Method Documentation
Begins a transaction. If successful, returns true; otherwise, returns false.
Closes the database connection, freeing any resources acquired, and invalidating any existing SqlQuery objects that are used with the database.
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.
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.
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.
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.
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.
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.
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.
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.
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.