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(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
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
Signal emitted whenever a database is opened or closed.
Note: The corresponding handler is onDatabaseChanged
.
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(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.
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
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, 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
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.
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
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.
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.
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.