CLASSES

UIKIT

SQLiteDatabase

super: Database

SQLite is an embedded relational database management system. It is ACID-compliant and implements most of the SQL standard, using a dynamically and weakly typed SQL syntax. SQLite is a popular choice as embedded database software for local/client storage in application software such as web browsers. It is arguably the most widely deployed database engine, as it is used today by several widespread browsers, operating systems, and embedded systems (such as mobile phones), among others.

Events

  • Load() This event is called when the object becames available in the current runtime system.

  • DidConnect() Handle this event if you want to perform some operations when database is connected.

  • DidDisconnect() Handle this event if you want to perform some operations when database is disconnected.

  • Unload() This event is called when the object has been removed from the current runtime system (but not yet deallocated).

Properties

  • var password: String Database password.

  • var database: String Database name.

  • var url: String Database url.

  • var affectedRows: Int Returns the number of rows modified, inserted or deleted by the most recently completed INSERT, UPDATE or DELETE statement. Executing any other type of SQL statement does not modify the value returned by this function. (read-only)

  • var lastInsertedRowID: Int Returns the rowid of the most recent successful INSERT into a rowid table. (read-only)

  • var errorCode: Int Returns a numeric error code if the latest sql operation failed. (read-only)

  • var encryption: Int Use this property to set an encryption database connection.

  • var isError: Bool Check if latest operation failed. (read-only)

  • var errorMessage: String Returns an error message if the latest sql operation failed. (read-only)

  • var isConnected: Bool A flag that checks if database is connected. (read-only)

  • var useSchema: Bool A flag to check if current database engines supports schemas. (read-only)

  • var objectName: String The name of the object.

Class Methods

  • func open(srcfile: Object): Object Initializes and returns the SQLiteDatabase object with the content of the source file. The source file can be an URL or a File object.

Methods

  • func connect(onSuccess: Closure = null, onError: Closure = null): Bool Connect to the specified database. The optional onSuccess and onError closures can be used to execute the statement in a background thread so that the app remains responsive. The onSuccess closure, if set, is executed when the connection ends without errors. The onError closure, if set, is executed when the connection fails. The Database object is passed as the only argument in both closures. If at least one of the closures is set then the connection is executed in the UI thread (not recommended for time-consuming tasks) and this method immediately returns false; otherwise, the statement is executed in the UI thread (not recommended for time-consuming tasks) and the method returns true in case of success, false in case of failure.

  • func select(sql: String, onSuccess: Closure = null, onError: Closure = null): SQLiteRecordSet Perform a SQL query. The optional onSuccess and onError closures can be used to perform the query in a background thread so that the app remains responsive. The onSuccess closure, if set, is executed when the query ends without errors; this closure is invoked with two arguments: the Database itself and the produced RecordSet. The onError closure, if set, is executed when the query fails and the Database object is passed as the only argument. If at least one of the closures is set then the query is executed in a background thread and this method immediately returns a null value; otherwise, the query is executed in the UI thread (not recommended for time-consuming tasks) and the method returns the produced RecordSet in case of success, null in case of failure. The query fails if the Database is not connected.

  • func execute(sql: String, onSuccess: Closure = null, onError: Closure = null): Bool Execute a INSERT, UPDATE, DELETE SQL statement. The optional onSuccess and onError closures can be used to execute the statement in a background thread so that the app remains responsive. The onSuccess closure, if set, is executed when the statement ends without errors. The onError closure, if set, is executed when the statement fails. The Database object is passed as the only argument in both closures. If at least one of the closures is set then the statement is executed in a background thread and this method immediately returns false; otherwise, the statement is executed in the UI thread (not recommended for time-consuming tasks) and the method returns true in case of success, false in case of failure. The execution of the statement fails if the Database is not connected.

  • func schemas(onSuccess: Closure = null, onError: Closure = null): SQLiteRecordSet Retrieve a list of database schemas. The optional onSuccess and onError closures can be used to execute the request in a background thread so that the app remains responsive. The onSuccess closure, if set, is executed when list of schemas is retrieved without errors; this closure is invoked with two arguments: the Database itself and the produced RecordSet. The onError closure, if set, is executed when the operation fails and the Database object is passed as the only argument of the closure. If at least one of the closures is set then the request is executed in a background thread and this method immediately returns a null value; otherwise, the statement is executed in the UI thread (not recommended for time-consuming tasks) and the method returns the produced RecordSet in case of success, null in case of failure. The request fails if the Database is not connected.

  • func tables(onSuccess: Closure = null, onError: Closure = null): SQLiteRecordSet Retrieve a list of database tables. The optional onSuccess and onError closures can be used to execute the request in a background thread so that the app remains responsive. The onSuccess closure, if set, is executed when list of schemas is retrieved without errors; this closure is invoked with two arguments: the Database itself and the produced RecordSet. The onError closure, if set, is executed when the operation fails and the Database object is passed as the only argument of the closure. If at least one of the closures is set then the request is executed in a background thread and this method immediately returns a null value; otherwise, the statement is executed in the UI thread (not recommended for time-consuming tasks) and the method returns the produced RecordSet in case of success, null in case of failure. The request fails if the Database is not connected.

  • func tablesWithSchema(schema: String, onSuccess: Closure = null, onError: Closure = null): SQLiteRecordSet Retrieve a list of database tables within the specified schema. The optional onSuccess and onError closures can be used to execute the request in a background thread so that the app remains responsive. The onSuccess closure, if set, is executed when list of schemas is retrieved without errors; this closure is invoked with two arguments: the Database itself and the produced RecordSet. The onError closure, if set, is executed when the operation fails and the Database object is passed as the only argument of the closure. If at least one of the closures is set then the request is executed in a background thread and this method immediately returns a null value; otherwise, the statement is executed in the UI thread (not recommended for time-consuming tasks) and the method returns the produced RecordSet in case of success, null in case of failure. The request fails if the Database is not connected.

  • func columnsForTableAndSchema(table: String, schema: String, extended: Bool, onSuccess: Closure = null, onError: Closure = null): SQLiteRecordSet Retrieve information about a given table in the specified schema. The optional onSuccess and onError closures can be used to execute the request in a background thread so that the app remains responsive. The onSuccess closure, if set, is executed when list of schemas is retrieved without errors; this closure is invoked with two arguments: the Database itself and the produced RecordSet. The onError closure, if set, is executed when the operation fails and the Database object is passed as the only argument of the closure. If at least one of the closures is set then the request is executed in a background thread and this method immediately returns a null value; otherwise, the statement is executed in the UI thread (not recommended for time-consuming tasks) and the method returns the produced RecordSet in case of success, null in case of failure. The request fails if the Database is not connected.

  • func addRecord(databaseRecord: DatabaseRecord, table: String, onSuccess: Closure = null, onError: Closure = null): Bool Add DatabaseRecord to the current Database. The optional onSuccess and onError closures can be used to perform the query in a background thread so that the app remains responsive.

  • func updateRecord(databaseRecord: DatabaseRecord, table: String, whereClause: String, onSuccess: Closure = null, onError: Closure = null): Bool Update current Database using values from DatabaseRecord class. The whereClause String parameter is used to determine which rows will be affacted bu the UPDATE statement. If all rows need to be update then pass "1". The optional onSuccess and onError closures can be used to perform the query in a background thread so that the app remains responsive.

  • func close() Close connection to the database.

  • func escape(identifier: String): String Escape an identifier.

Examples

Connect the database

func onSuccess(db) {
    // code to execute when the connection completes
}

func onError(db) {
    // code to execute in case of connection error
}

MyDatabase.connect(onSuccess, onConnectionError)

Execute an INSERT statement (with explicit closures)

How to execute a SQL INSERT statement, the code to execute when the statement completes or fails is defined in the onSuccess and onError closures.

func onSuccess(db) {
    // code to execute when the connection when the connection completes
    Console.write("INSERT: Success")
}

func onError(db) {
    // code to execute when the connection when the connection completes
    Console.write("INSERT: Error \(db.errorMessage)")
}

var sql = 'INSERT INTO MyTable (Column1,Column2) VALUES ("value1","value2")'
MyDatabase.execute(sql, onSuccess, onError);

Execute an INSERT statement (with anonymous closures)

This is another way to execute the same SQL statement of the previous example. In this case we use two anonymous closures.

var sql = 'INSERT INTO MyTable (Column1,Column2) VALUES ("value1","value2")'
MyDatabase.execute(sql, {Console.write("INSERT: Success")}, {Console.write("INSERT: Error \(MyDatabase.errorMessage)")});

Execute a SELECT statement

How to make a query and use the returned RecordSet (sample from the New DataSet in each row technote). 

func onSuccess(db,rs) {
    // The sender Database is the first argument of the closure.
    // The new RecordSet is the second argument of the closure,
    // it can be used, for example, to set the dataSet property of a control
    // (for example a Table, a Chart, ecc.)
    cell.CustomView1.Chart1.dataSet = rs
    cell.CustomView1.Chart1.reload(false)
}

func onError(db) {
    // The sender Database is the first argument of the closure,
    // it can be used to get the errorMessage
    cell.CustomView1.Chart1.dataSet = null
    cell.CustomView1.Chart1.reload(false)
    Console.write("Error: \(db.errorMessage)")
}

var sql = 'SELECT Total FROM main."invoices" where CustomerId = \(cell.identifier)'
MyDatabase.select(sql, onSuccess, onError)

Make sure the Database is connected and then execute an SQL statement

How to execute a SQL statement, in this case an INSERT, but first make sure the Database is connected otherwise the execution will fail. Using the onSuccess and onError closures for each database method call guarantees that each database operation is performed in a background thread so that the app remains responsive even if the operation is time-consuming.

// This closure will be called when the database completes its connection,
// now I can execute my SQL statement
func onConnectionSuccess(db) {
    // Here you can customize the SQL statement
    var sql = 'INSERT INTO MyTable (Column1,Column2) VALUES ("value1","value2")'

    // Log the last inserted row if the SQL statement is executed
    func onExecuteSuccess(db) {
        Console.write("MyDatabase did execute '\(sql)' successfully. Last inserted row \(db.lastInsertedRowID).")
    }

    // Show an alert if the execution ends with an error
    func onExecuteError(db) {
        var alert = Alert("MyDatabase execute error","\(db.errorCode) \(db.errorMessage)")
        alert.show()
    }

    // Perform the SQL statement
    MyDatabase.execute(sql, onExecuteSuccess, onExecuteError);
}

// Show an alert if the connection fails
func onConnectionError(db) {
  var alert = Alert("MyDatabase connection error","\(db.errorMessage)")
  alert.show()
}

// Connect the database and then run the onConnectionSuccess closure when connected.
// If the database is already connected then the onConnectionSuccess closure is
// immediately executed.
MyDatabase.connect(onConnectionSuccess, onConnectionError)