Database Transactions

Introduction

In the previous lessons, all data was stored in an object created in RAM. When the application was terminated, all transactions (objects) were deleted. This, of course, is not acceptable. Financial institutions should keep all historical financial records.

In this lesson, our transaction data will be stored in a database on a disk. A database on a disk may retain the data for later use. In this lesson, we will learn how a Xojo application can access a database and how data can be stored and retrieved from a database.

It is also our intention to make the application more realistic and also to optimize the code in the program.

User interface

The Graphical User Interface (GUI) has changed a bit. The DepositButton and WithdrawButton have been removed and replaced with a single EnterButton. The Pressed event of the EnterButton will take the amount entered in the DesktopTextField named AmountField. A positive amount is considered a deposit, a negative amount is considered a withdrawal.

The ShowButton was also removed. Each transaction should now automatically change the balance shown in the BalanceLabel control.

Databases

In computing, a database is an organized collection of data or a type of data store based on the use of a database management system (DBMS) (Wikipedia)

Relational databases are most commonly used. In a relational database, data is stored in tables. A table with the name Student contains all the characteristic data of all individual persons: his (her) first (FirstName) and last name (LastName) , sex (Sex), nationality and so on. One student’s data is kept in a row of the table. The individual data elements of one student are kept in columns. Each row in a table must be uniquely identified. This unique column (for instance the column named StudentID) is called the primary key.

StudentID	FirstName	LastName	Sex
1	John	Winter	M
2	Luis	Coleman	M
3	Ann	Weber	F
4	Peter	Cox	M

CourseID	CourseTitle	CourseOwner
1	Databases	Ken Davis
2	Networking	Julie Foster
3	Java	Margaret Walters
4	Scratch	Lina Louis

Relationships between two tables are realized by combining the primary keys of both tables. The primary key of a table named Course can be stored in a column named CourseID. A students’s enrollment in a particular course can be tracked in a new table named Enrollment by combining both primary keys (StudentID and CourseID) in one row (for example student with StudentID = 3 enrolled in course with CourseID = 2).

EnrollmentID	StudentID	CourseID
1	3	2
2	1	3
3	1	1
4	2	2

Xojo has built-in support for SQLite, MySQL and PostgreSQL databases. Other databases can be accessed via the ODBC (Open Database Connectivity) driver. Communication from Xojo to the DBMS is via SQL (Structured Query Language) commands. In this course, you will learn the basic SQL commands to manage data in a database. There are also a lot of tutorials available on the internet to learn SQL thoroughly.

In this course, we will use SQLite databases to illustrate Xojo database operations with SQL.

How to create a SQLite database?

Using an external app

There are many (free) applications available on the internet that can help you to create, search and manage SQLite database files. Examples are DB Browser for SQLite (DB4S), SQLiteStudio and so on. A customised version usable for your operating system can be downloaded from the above websites.

Creating a SQLite database using an app is limited to giving the database a name and deciding where to store the database file on disk. In my example, I created the TransactionsDB.sqlite file (New Database button on the toolbar) and saved it in a new folder (called Xojo) on the Desktop of my computer system.

A database file can store a number of related tables. In our example, we only need one table named Accounts to store our transaction data. You can add a new table to the database using the Create Table button. The table has 4 columns: AccountID (Integer – primary key), AccountName (Text), AccountDeposit (Real) and AccountWithdraw (Real).

Using Xojo statements

Xojo provides all the statements you need to create a SQLite database and its tables.

1. Define a Public property db in the App. The Type of the property db is SQLitedatabase (see screenshot above). This variable is declared but has no value;
2. Add the following code in the Opening event of the App:

//Opening event of App
  Var dbFile As FolderItem = SpecialFolder.Desktop.Child("Xojo").Child("TransactionsDB.sqlite")
  App.db = new SQLiteDatabase
  if dbFile <> Nil And dbFile.Exists Then  //Use the existing database file
    App.db.DatabaseFile = dbFile
    Try 
      App.db.Connect
    Catch error As DatabaseException
      MessageBox("Database not connected "+error.Message)
      Quit
    End Try
  Else //Create a new database file
    App.db.DatabaseFile = SpecialFolder.Desktop.Child("Xojo").Child("TransactionsDB.sqlite")
    Try
      App.db.CreateDatabase
    Catch error As IOException
      MessageBox("Database file not available " + error.Message)
      Quit
    End Try
    //Create a new table
    Var sql As String = "CREATE TABLE Accounts (AccountID INTEGER NOT NULL, AccountName VARCHAR," _
    + " AccountDeposit CURRENCY, AccountWithdraw CURRENCY, PRIMARY KEY (AccountID));"
    Try
      App.db.ExecuteSQL(sql)
    Catch error As DatabaseException
      MessageBox("Table creation error " + error.Message)
    End Try
  End If

Some instructions require some explanation:

Use an Existing database file

Var dbFile As FolderItem = SpecialFolder.Desktop.Child("Xojo").Child("TransactionsDB.sqlite")

Above statement declares a variable dbFile as an object (folder, file) of type FolderItem that can be found in the file structure on your computer system. The FolderItem is searched from a SpecialFolder with the foldername Desktop on your Operating System. The Child method looks for a file or folder with the name Xojo in the folder Desktop and in the folder named Xojo the system tries to find the file named TransactionsDB.sqlite.

SpecialFolder uses a starting point depending on the operating system in use. For example, the Desktop starting point refers under Windows to the \Users\UserName\Desktop directory, under macOS to the folder /Users/UserName/Desktop and under Linux to /home/UserName/Desktop.

If the file is found, the variable dbFile refers to the found file. If the file is not found, the variable dbFile is given the value Nil. This check is carried out by the instruction:

If dbFile <> Nil And dbFile.Exists Then 

The following piece of code initializes the db property declared in App. db is now an object from the class SQLiteDatabase.

App.db = New SQLiteDatabase

App.db.DatabaseFile = dbFile

The instruction above initializes the DatabaseFile property of the db object to the FolderItem found on the file system being used. Now everything is ready to use the database. This is done by the Connect method (see statement below) of the db object:

App.db.Connect

The Try…Catch error As DatabaseException…End Try construct checks if an error occurs during the execution of App.db.Connect. If an error occurs then a message is displayed (“Database not connected“) along with an error message (error.Message). Afterwards, the program is terminated (Quit).

To use an existing SQLiteDatabase, point a SQLiteDatabase object (db) to the database file on your file system (App.db.DatabaseFile = ) and connect to the database (App.db.Connect).

Create a New Database file

App.db.DatabaseFile = SpecialFolder.Desktop.Child("Xojo").Child("TransactionsDB.sqlite")

If the database file is not yet available (Else branch of the If statement), the file must be created. In the first instruction, the desired file (name and location in the file system) is assigned to the DatabaseFile property of the App.db object. Remember that the DatabaseFile property expects a FolderItem object.

App.db.CreateDatabase

The CreateDatabase method of the App.db object then attempts to create the file specified in the DatabaseFile property. If this fails, an error message is displayed via the Try…Catch error as IOException…End Try construct and the application terminates (Quit).

To create a SQLiteDatabase via Xojo code, initialize the DatabaseFile property of an SQLiteDatabase object with a FolderItem object that specifies the name and location of the new database file and use the method CreateDatabase to create the file on the file system.

Create a New table

A new table can be added to the database using a SQL command. This is the general SQL syntax to create a new table in a connected database:

CREATE TABLE table_name(column_name1 datatype, column_name2 datatype...);

SQLite uses the internal data types TEXT, INTEGER, REAL, BLOB and NULL to store data in the database. If you use Xojo data types (INTEGER, DOUBLE, CURRENCY, BOOLEAN, STRING…) in a SQL statement, SQLite will convert the Xojo datatype to one of their internal SQLite data types.

Each column_name can be completed with an additional constraint such as PRIMARY KEY (to indicate the unique key of the table), NOT NULL (if the column should not remain empty) or UNIQUE (if the column value must be unique in the table).

The data in an INTEGER column defined as a PRIMARY KEY contains a unique value. This value is automatically incremented by SQLite.

An INTEGER column defined as PRIMARY KEY is automatically incremented by SQLite.

The following SQL statement can be used to create the Accounts table:

CREATE TABLE Accounts(AccountID INTEGER PRIMARY KEY, AccountName VARCHAR, AccountDeposit CURRENCY, AccountWithdraw CURRENCY);

This SQL statement is stored in Xojo in a local String variable named sql :

Var sql As String = "CREATE TABLE Accounts (AccountID INTEGER PRIMARY KEY, AccountName VARCHAR," _
+" AccountDeposit CURRENCY, AccountWithdraw CURRENCY);"

The ExecuteSQL method with the sql variable as a parameter can be used to execute this SQL statement:

App.db.ExecuteSQL(sql)

And of course, we also check via the Try…Catch…End Try construct for possible errors while executing this SQL statement.

Insert a new row to a database table

The following piece of code shows the code in the Pressed event of the EnterButton:

//Pressed event of the EnterButton
Var amount As Currency
amount = Currency.FromString(AmountField.Text,Locale.Current)

If amount > 0.0 Then
  ActiveAccount.DepositMoney(amount)
  TransactionList.AddRow(AccountPopup.SelectedRowText,str(amount),"")
  TransactionList.SelectedRowIndex = TransactionList.LastAddedRowIndex
  AddDBRecord(AccountPopup.SelectedRowText,amount,0)
  ShowBalance
Else
  amount = ActiveAccount.WithdrawMoney(-amount)
  TransactionList.AddRow(AccountPopup.SelectedRowText,"",str(amount))
  TransactionList.SelectedRowIndex = TransactionList.LastAddedRowIndex
  AddDBRecord(AccountPopup.SelectedRowText,0,amount)
  ShowBalance
End If

AmountField.Text = ""

The Text property of a DesktopTextField control (AmountField) is a String. To convert this String to a Currency data type, the FromString method must be used. The Locale.Current parameter tells the FromString method that the String may contain a number in the local format set by the Operating System installed (for example, a number such as 1.234,75).

A positive amount is considered to be a deposit, a negative amount is considered to be a cash withdrawal.

A new row is added to the database with the AddDBRecord method. The AddDBRecord method can be developed with Xojo statements or can use a SQL command.

Using Xojo statements

The method AddDBRecord is a new method defined in AccountWindow:

The AddDBRecord method has 3 parameters: the account name (aName type STRING), the amount deposited (aDeposit type CURRENCY) and the amount withdrawn (aWithdraw type CURRENCY). This is the code:

//AddDBRecord(aName As String, aDeposit As Currency, aWithdraw As Currency)
Var aRecord As New DatabaseRow 

aRecord.Column("AccountName").StringValue = aName
aRecord.Column("AccountDeposit").CurrencyValue = aDeposit
aRecord.Column("AccountWithdraw").CurrencyValue = aWithdraw

Try
  App.db.AddRow("Accounts", aRecord)
Catch error As DatabaseException
  MessageBox("DB error " + error.Message)
End Try

The row to be inserted (aRecord) must be defined as a new DatabaseRow. Our DatabaseRow has 3 columns named AccountName, AccountDeposit and AccountWithdraw and is populated with the parameters aName, aDeposit and aWithdraw.

The SQLiteDatabaseAddRow method is used to insert a method to a database table. AddRow has two parameters: the name of the database table (Accounts) and the database row to be added (aRecord).

Using SQL commands

The AddDBRecord method can also be implemented with SQL statements. The SQL INSERT statement can be used to add a row to a table. The syntax is as follows:

INSERT INTO table_name (column_name1, column_name2...) VALUES (value1, value2...);

The list of values can also be replaced by question marks (“?”). In this case, the question marks should be replaced with actual values available during program execution. Using a parameter list with “?” is preferred to prevent SQL injection attacks.

INSERT INTO table_name (column_name1, column_name2...) VALUES (?, ?...)

The AddDBRecord method can be developed with a SQL command:

//AddDBRecord(aName As String, aDeposit As Currency, aWithdraw As Currency)
Var sql As String = "INSERT INTO Accounts (AccountName, AccountDeposit, AccountWithdraw) VALUES (?,?,?);"

Var myValues() As Variant
myValues.Add(aName)
myValues.Add(aDeposit)
myValues.Add(aWithdraw)

Try
  App.db.ExecuteSQL(sql, myValues)
Catch error As DatabaseException
  MessageBox("DB error "+error.Message)
End Try

myValues() is defined as an array. The size of the array is not defined. To add an element to the array, use the Add method. All elements of the array myValues are of type Variant. A Variant data type is a special data type that can contain any type of data. As you notice from the example above, the first element (index 0) contains a String and the second (index 1) and third (index 2) elements contain a Currency value. These are the data elements needed to replace the question marks in the SQL Insert statement.

App.db.ExecuteSQL(sql, myValues)

Use the ExecuteSQL method to execute the SQL statement defined in the string variable sql. The first parameter of the ExecuteSQL method is the SQL command defined in a String variable (sql), the second parameter is the array to replace the question marks (myValues).

 A Variant data type can contain all kind of data including arrays.

Reading data from a database table

Now all transactions are stored in the Accounts table in the database. When choosing an account, it would now be convenient to show all historical transactions, stored in the database, in the DesktopListBox (TransactionList). This can be accomplished via the SelectionChanged event from the AccountPopup control.

The SelectionChanged event should carry out a number of activities:

1. Retrieve all historical data for the selected account from the database table (Accounts);
2. Enter the retrieved data into the DesktopListBox (TransactionList);
3. Recalculate the account balance based on the retrieved historical data;
4. Initialize the AccountBalance property with this calculated balance.

The following code has been entered in the SelectionChanged event of AccountPopup:

//SelectionChanged(item As DesktopMenuItem)
ChangeActiveAccount(Me.SelectedRowText)

Var rs As RowSet = ReadDBData //Retrieve data from database table

PopulateListBox(rs) //Show retrieved data in DesktopListBox

Var balance As Currency = CalculateBalance  //Recalculate balance of the selected account
ActiveAccount.UpdateBalance(balance)
ShowBalance

ChangeActiveAccount method

ChangeActiveAccount(Me.SelectedRowText)

ActiveAccount (type class Account) is a new Private property of AccountWindow. This property contains a reference to the object selected by choosing an item in AccountPopup. From then on we can always use ActiveAccount to refer to the properties and methods of the class Account.

Since the code is part of the SelectionChanged event of the AccountPopupcontrol, the Me keyword refers to the AccountPopupcontrol.

The method ChangeActiveAccount in AccountWindow contains the following statements:

//ChangeActiveAccount(selectedAccount As String)
Select Case selectedAccount
Case "CheckingAccount"
  ActiveAccount = CheckingAccount
Case "SavingsAccount"
  ActiveAccount = SavingsAccount
Case "RetirementAccount"
  ActiveAccount = RetirementAccount
End 

Retrieve historical data from the database

To retrieve the historical data from the database for the account selected in AccountPopup, we use a SQL SELECT command. The general syntax for the SQL SELECT command is as follows:

SELECT * FROM table_name; //all columns from table_name are fetched

SELECT column_name1, column_name2... FROM table_name; //only the named columns from table_name are fetched

SELECT column_name1, column_name2... FROM table_name WHERE condition //only the named columns from table_name are fetched that meet the condition

We only need to retrieve the columns AccountName, AccountDeposit and AccountWithdraw from the table Accounts where the column AccountName equals the item selected in AccountPopup. This is our SQL command required:

SELECT AccountName, AccountDeposit, AccountWithdraw FROM Accounts WHERE AccountName = AccountPopup.SelectedRowText

Executing the above SQL statement on the database table Accounts is contained in a separate method named ReadDBData within AccountWindow. 

//ReadDBData As RowSet
Var rs As RowSet
Var sql As String = "SELECT AccountName, AccountDeposit, AccountWithdraw  FROM Accounts WHERE AccountName =  ?;" 
Try
  rs = App.db.SelectSQL(sql, AccountPopup.SelectedRowText)
Catch error As DatabaseException
  Messagebox("DB Select error "+error.Message)
  Quit
End Try
Return rs

A RowSet contains database rows that are the result of a database query. The Xojo method SelectSQL executes the SQL command (sql) and replaces the question mark (?) in the sql String with the selected item in AccountPopup. The selected rows are stored in a RowSet named rs. This RowSet is returned to the calling method.

Enter the RowSet data in a DesktopListBox

PopulateListBox(rs)

To populate the retrieved RowSet in the DesktopListBox (TransactionList) the following piece of code can be used:

//PopulateListBox(rs As RowSet)
TransactionList.RemoveAllRows
TransactionList.ColumnCount = rs.ColumnCount

If rs <> Nil And rs.RowCount <>  0 Then
  For Each row As DatabaseRow In rs
    TransactionList.AddRow
    For column As Integer = 0 To rs.ColumnCount - 1
      TransactionList.CellTextAt(TransactionList.LastAddedRowIndex, column) = row.ColumnAt(column).StringValue
    Next column 
  Next 
End If

Before populating the DesktopListBox all rows are removed (TransactionList.RemoveAllRows) and the number of columns in the DesktopListBox (TransactionList.ColumnCount) is set equal to the number of columns in the RowSet (rs.ColumnCount).

If the RowSet (rs) contains multiple rows (If rs <> Nil And rs.RowCount <> 0 Then), all the columns (For column As Integer = 0 To rs. ColumnCount – 1) of each row (For Each row As DatabaseRow In rs) are transferred to the newly added row (LastAddedRowIndex) of TransactionList (TransactionList.CellTextAt(TransactionList.LastAddedRowIndex, column) = row.ColumnAt(column).StringValue). The parameters of the CellTextAt method refer to a row and a column in the DesktopListBox. The parameter in the RowSet ColumnAt method refer to the index of a column in the RowSet.

The StringValue property attempts to convert all “special” values discovered in a column of the RowSet (for example, Currency data types) to a String. A String is accepted in a column of TransactionList.

Recalculate the balance

Var balance As Currency = CalculateBalance

Now we can use TransactionList to calculate the actual balance for the selected Account. For each row in the DesktopListBox, we make the difference between the Deposit value (column 1 in the ListBox) and the Withdraw value (column 2 in the ListBox) and add the result to the local variable balance. This value is returned to the calling method. We have to convert the DesktopListBox String values to a Currency data type (Currency.FromString).

The following code was used to calculate the balance:

//CalculateBalance As Currency
Var balance As Currency = 0.0
If TransactionList.RowCount > 0 Then
  For row As Integer = 0 To TransactionList.LastAddedRowIndex
    balance = balance + Currency.FromString(TransactionList.CellTextAt(row,1)) -  Currency.FromString(TransactionList.CellTextAt(row,2))
  Next
End If
Return balance

Update AccountBalance property

//UpdateBalance(balance As Currency)
ActiveAccount.UpdateBalance(balance)

To reuse our existing program, we need to update the AccountBalance property with the calculated value (balance). Because the AccountBalance property is private, we added a new public method to the Account class called UpdateBalance:

//UpdateBalance(balance As Currency)
AccountBalance = balance

ShowBalance method

//ShowBalance
BalanceLabel.Text = "The balance of the " + AccountPopup.SelectedRowText + " is: " + ActiveAccount.GetBalance.ToString(Locale.Current)

In the ShowBalance method, we can use the ActiveAccount property to reference the active account in use. The ToString(Locale.Current) converts Currency data (returned by GetBalance) into a String in the way data is displayed on your Operating System.

Some useful database methods

Method	Type	Description
CreateDatabase		Creates and opens a new database defined in the property DatabaseFile
Connect		Connects to an existing database
AddRow	table_name As String, row As DatabaseRow	inserts row in the table_name
ExecuteSQL	SQL command As String, [parameters() As Variant]	Executes the SQL command (no return)
SelectSQL	SQL command As String, [parameters() As Variant]	Selects data based on the SQL command and returns a RowSet