{"id":415,"date":"2025-05-31T14:01:23","date_gmt":"2025-05-31T14:01:23","guid":{"rendered":"https:\/\/xojo.itbib4you.be\/?page_id=415"},"modified":"2025-05-31T14:17:05","modified_gmt":"2025-05-31T14:17:05","slug":"database-transactions","status":"publish","type":"page","link":"https:\/\/xojo.itbib4you.be\/index.php\/database-transactions\/","title":{"rendered":"Database Transactions"},"content":{"rendered":"\n
\n
Overview<\/a><\/div>\n<\/div>\n\n\n\n

Introduction<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

It is also our intention to make the application more realistic and also to optimize the code in the program.<\/p>\n\n\n\n

User interface<\/h2>\n\n\n
\n
\"GUI\"<\/figure>\n<\/div>\n\n\n

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

The ShowButton<\/mark> was also removed. Each transaction should now automatically change the balance shown in the BalanceLabel<\/mark> control.<\/p>\n\n\n\n

Databases<\/h2>\n\n\n\n
\n

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)<\/p>\n<\/blockquote>\n\n\n\n

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

StudentID<\/th>FirstName<\/th>LastName<\/th>Sex<\/th><\/tr><\/thead>
1<\/td>John<\/td>Winter<\/td>M<\/td><\/tr>
2<\/td>Luis<\/td>Coleman<\/td>M<\/td><\/tr>
3<\/td>Ann<\/td>Weber<\/td>F<\/td><\/tr>
4<\/td>Peter<\/td>Cox<\/td>M<\/td><\/tr><\/tbody><\/table>
Student <\/figcaption><\/figure>\n\n\n\n
CourseID<\/th>CourseTitle<\/th>CourseOwner<\/th><\/tr><\/thead>
1<\/td>Databases<\/td>Ken Davis<\/td><\/tr>
2<\/td>Networking<\/td>Julie Foster<\/td><\/tr>
3<\/td>Java<\/td>Margaret Walters<\/td><\/tr>
4<\/td>Scratch<\/td>Lina Louis<\/td><\/tr><\/tbody><\/table>
Course<\/figcaption><\/figure>\n\n\n\n

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

EnrollmentID<\/th>StudentID<\/th>CourseID<\/th><\/tr><\/thead>
1<\/td>3<\/td>2<\/td><\/tr>
2<\/td>1<\/td>3<\/td><\/tr>
3<\/td>1<\/td>1<\/td><\/tr>
4<\/td>2<\/td>2<\/td><\/tr><\/tbody><\/table>
Enrollment<\/figcaption><\/figure>\n\n\n\n

Xojo has built-in support for SQLite<\/mark>, MySQL<\/mark> and PostgreSQL<\/mark> databases. Other databases can be accessed via the ODBC<\/mark> (Open Database Connectivity<\/mark>) driver. Communication from Xojo to the DBMS is via SQL<\/mark> (Structured Query Language<\/mark>) 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.<\/p>\n\n\n\n

In this course, we will use SQLite databases to illustrate Xojo database operations with SQL.<\/p>\n\n\n\n

How to create a SQLite database?<\/h2>\n\n\n\n

Using an external app<\/h3>\n\n\n\n

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 <\/a>(DB4S), SQLiteStudio<\/a> and so on. A customised version usable for your operating system can be downloaded from the above websites.<\/p>\n\n\n

\n
\"DB
DB Browser for SQLite<\/figcaption><\/figure>\n<\/div>\n\n\n

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<\/mark> file (New Database<\/mark> button on the toolbar) and saved it in a new folder (called Xojo<\/mark>) on the Desktop<\/mark> of my computer system.<\/p>\n\n\n\n

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

Using Xojo statements<\/h3>\n\n\n\n

Xojo provides all the statements you need to create a SQLite database and its tables.<\/p>\n\n\n

\n
\"Define<\/figure>\n<\/div>\n\n\n
    \n
  1. Define a Public<\/mark> property db<\/mark> in the App<\/mark>. The Type<\/mark> of the property db<\/mark> is SQLitedatabase<\/mark> (see screenshot above). This variable is declared but has no value;<\/li>\n\n\n\n
  2. Add the following code in the Opening<\/mark> event of the App<\/mark>:<\/li>\n<\/ol>\n\n\n\n
    \/\/Opening event of App<\/mark>
      Var<\/mark> dbFile As<\/mark> FolderItem = SpecialFolder.Desktop.Child(\"Xojo\").Child(\"TransactionsDB.sqlite\")
      App.db = new<\/mark> SQLiteDatabase
      if<\/mark> dbFile <> Nil<\/mark> And<\/mark> dbFile.Exists Then<\/mark>  \/\/Use the existing database file<\/mark>
        App.db.DatabaseFile = dbFile
        Try <\/mark>
          App.db.Connect
        Catch<\/mark> error As<\/mark> DatabaseException
          MessageBox(\"Database not connected \"+error.Message)
          Quit
        End Try<\/mark>
      Else<\/mark> \/\/Create a new database file<\/mark>
        App.db.DatabaseFile = SpecialFolder.Desktop.Child(\"Xojo\").Child(\"TransactionsDB.sqlite\")
        Try<\/mark>
          App.db.CreateDatabase
        Catch<\/mark> error As<\/mark> IOException
          MessageBox(\"Database file not available \" + error.Message)
          Quit
        End Try<\/mark>
        \/\/Create a new table<\/mark>
        Var<\/mark> sql As String<\/mark> = \"CREATE TABLE Accounts (AccountID INTEGER NOT NULL, AccountName VARCHAR,\" _
        + \" AccountDeposit CURRENCY, AccountWithdraw CURRENCY, PRIMARY KEY (AccountID));\"
        Try<\/mark>
          App.db.ExecuteSQL(sql)
        Catch<\/mark> error As<\/mark> DatabaseException
          MessageBox(\"Table creation error \" + error.Message)
        End Try<\/mark>
      End If<\/mark>
    <\/pre>\n\n\n\n
    Some instructions require some explanation:<\/p>\n\n\n\n
    Use an Existing database file<\/h4>\n\n\n\n
    Var<\/mark> dbFile As<\/mark> FolderItem = SpecialFolder.Desktop.Child(\"Xojo\").Child(\"TransactionsDB.sqlite\")<\/pre>\n\n\n\n
    Above statement declares a variable dbFile<\/mark> as an object (folder, file) of type FolderItem<\/mark> that can be found in the file structure on your computer system. The FolderItem<\/mark> is searched from a SpecialFolder<\/mark> with the foldername Desktop<\/mark> on your Operating System. The Child<\/mark> method looks for a file or folder with the name Xojo<\/mark> in the folder Desktop<\/mark> and in the folder named Xojo<\/mark> the system tries to find the file named TransactionsDB.sqlite<\/mark>.<\/p>\n\n\n\n
    SpecialFolder<\/mark> uses a starting point depending on the operating system in use. For example, the Desktop<\/mark> starting point refers under Windows<\/mark> to the \\Users\\UserName\\Desktop<\/mark> directory, under macOS<\/mark> to the folder \/Users\/UserName\/Desktop<\/mark> and under Linux<\/mark> to \/home\/UserName\/Desktop<\/mark>.<\/p>\n\n\n\n
    If the file is found, the variable dbFile<\/mark> refers to the found file. If the file is not found, the variable dbFile<\/mark> is given the value Nil<\/mark>. This check is carried out by the instruction:<\/p>\n\n\n\n
    If<\/mark> dbFile <> Nil And<\/mark> dbFile.Exists Then<\/mark> <\/pre>\n\n\n\n
    The following piece of code initializes the db<\/mark> property declared in App<\/mark>. db<\/mark> is now an object from the class SQLiteDatabase<\/mark>.<\/p>\n\n\n\n
    App.db = New<\/mark> SQLiteDatabase<\/pre>\n\n\n\n
    App.db.DatabaseFile = dbFile<\/pre>\n\n\n\n
    The instruction above initializes the DatabaseFile<\/mark> property of the db<\/mark> object to the FolderItem<\/mark> found on the file system being used. Now everything is ready to use the database. This is done by the Connect<\/mark> method (see statement below) of  the db<\/mark> object:<\/p>\n\n\n\n
    App.db.Connect<\/pre>\n\n\n\n
    The Try\u2026Catch error As DatabaseException\u2026End Try<\/mark> construct checks if an error occurs during the execution of App.db.Connect<\/mark>. If an error occurs then a message is displayed (“Database not connected<\/mark>“) along with an error message (error.Message<\/mark>). Afterwards, the program is terminated (Quit<\/mark>).<\/p>\n\n\n\n
    To use an existing SQLiteDatabase, point a SQLiteDatabase object (db<\/mark>) to the database file on your file system (App.db.DatabaseFile =<\/mark> ) and connect to the database (App.db.Connect<\/mark>).<\/pre>\n\n\n\n
    Create a New Database file<\/h4>\n\n\n\n
    App.db.DatabaseFile = SpecialFolder.Desktop.Child(\"Xojo\").Child(\"TransactionsDB.sqlite\")<\/pre>\n\n\n\n
    If the database file is not yet available (Else<\/mark> branch of the If<\/mark> 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<\/mark> property of the App.db<\/mark> object. Remember that the DatabaseFile<\/mark> property expects a FolderItem<\/mark> object.<\/p>\n\n\n\n
    App.db.CreateDatabase<\/pre>\n\n\n\n
    The CreateDatabase<\/mark> method of the App.db<\/mark> object then attempts to create the file specified in the DatabaseFile<\/mark> property. If this fails, an error message is displayed via the Try\u2026Catch error as IOException\u2026End Try<\/mark> construct and the application terminates (Quit<\/mark>).<\/p>\n\n\n\n
    To create a SQLiteDatabase via Xojo code, initialize the DatabaseFile<\/mark> property of an SQLiteDatabase object with a FolderItem<\/mark> object that specifies the name and location of the new database file and use the method CreateDatabase<\/mark> to create the file on the file system.<\/pre>\n\n\n\n
    Create a New table<\/h4>\n\n\n\n
    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:<\/p>\n\n\n\n
    CREATE TABLE table_name(column_name1 datatype, column_name2 datatype...);<\/pre>\n\n\n\n
    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\u2026) in a SQL statement, SQLite will convert the Xojo datatype to one of their internal SQLite data types.<\/p>\n\n\n\n
    Each column_name<\/mark> can be completed with an additional constraint such as PRIMARY KEY<\/mark> (to indicate the unique key of the table), NOT NULL<\/mark> (if the column should not remain empty) or UNIQUE<\/mark> (if the column value must be unique in the table).<\/p>\n\n\n\n
    The data in an INTEGER<\/mark> column defined as a PRIMARY KEY<\/mark> contains a unique value. This value is automatically incremented by SQLite. <\/p>\n\n\n\n
    An INTEGER column defined as PRIMARY KEY is automatically incremented by SQLite.<\/pre>\n\n\n\n
    The following SQL statement can be used to create the Accounts<\/mark> table:<\/p>\n\n\n\n
    CREATE TABLE Accounts(AccountID INTEGER PRIMARY KEY, AccountName VARCHAR, AccountDeposit CURRENCY, AccountWithdraw CURRENCY);<\/pre>\n\n\n\n
    This SQL statement is stored in Xojo in a local String variable named sql<\/mark> :<\/p>\n\n\n\n
    Var sql As String = \"CREATE TABLE Accounts (AccountID INTEGER PRIMARY KEY, AccountName VARCHAR,\" _\n+\" AccountDeposit CURRENCY, AccountWithdraw CURRENCY);\"<\/pre>\n\n\n\n
    The ExecuteSQL<\/mark> method with the sql<\/mark> variable as a parameter can be used to execute this SQL statement:<\/p>\n\n\n\n
    App.db.ExecuteSQL(sql)<\/pre>\n\n\n\n
    And of course, we also check via the Try\u2026Catch\u2026End Try<\/mark> construct for possible errors while executing this SQL statement.<\/p>\n\n\n\n
    Insert a new row to a database table<\/h2>\n\n\n\n
    The following piece of code shows the code in the Pressed<\/mark> event of the EnterButton<\/mark>:<\/p>\n\n\n\n
    \/\/Pressed event of the EnterButton<\/mark>
    Var<\/mark> amount As<\/mark> Currency<\/mark>
    amount = Currency<\/mark>.FromString(AmountField.Text,Locale.Current)

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

    AmountField.Text = \"\"<\/pre>\n\n\n\n
    The Text<\/mark> property of a DesktopTextField<\/mark> control (AmountField<\/mark>) is a String<\/mark>. To convert this String<\/mark> to a Currency<\/mark> data type, the FromString<\/mark> method must be used. The Locale.Current<\/mark> parameter tells the FromString<\/mark> 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).<\/p>\n\n\n\n
    A positive amount<\/mark> is considered to be a deposit, a negative amount is considered to be a cash withdrawal.<\/p>\n\n\n\n
    A new row is added to the database with the AddDBRecord<\/mark> method. The AddDBRecord<\/mark> method can be developed with Xojo statements or can use a SQL command.<\/p>\n\n\n\n
    Using Xojo statements<\/h3>\n\n\n\n
    The method AddDBRecord<\/mark> is a new method defined in AccountWindow<\/mark>:<\/p>\n\n\n
    \n
    \"AddDBRecord\"<\/figure>\n<\/div>\n\n\n
    <\/p>\n\n\n\n
    The AddDBRecord<\/mark> method has 3 parameters: the account name (aName<\/mark> type STRING), the amount deposited (aDeposit<\/mark> type CURRENCY) and the amount withdrawn (aWithdraw<\/mark> type CURRENCY). This is the code:<\/p>\n\n\n\n
    \/\/AddDBRecord(aName As String, aDeposit As Currency, aWithdraw As Currency)<\/mark>
    Var<\/mark> aRecord As New<\/mark> DatabaseRow 

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

    Try<\/mark>
      App.db.AddRow(\"Accounts\", aRecord)
    Catch<\/mark> error As<\/mark> DatabaseException
      MessageBox(\"DB error \" + error.Message)
    End Try
    <\/mark><\/pre>\n\n\n\n
    The row to be inserted (aRecord<\/mark>) must be defined as a new DatabaseRow<\/mark>. Our DatabaseRow has 3 columns named AccountName<\/mark>, AccountDeposit<\/mark> and AccountWithdraw<\/mark> and is populated with the parameters aName<\/mark>, aDeposit<\/mark> and aWithdraw<\/mark>.<\/p>\n\n\n\n
    The SQLiteDatabaseAddRow<\/mark> method is used to insert a method to a database table. AddRow<\/mark> has two parameters: the name of the database table (Accounts<\/mark>) and the database row to be added (aRecord<\/mark>).<\/p>\n\n\n\n
    Using SQL commands<\/h3>\n\n\n\n
    The AddDBRecord<\/mark> 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:<\/p>\n\n\n\n
    INSERT INTO table_name (column_name1, column_name2...) VALUES (value1, value2...);<\/pre>\n\n\n\n
    The list of values can also be replaced by question marks (\u201c?<\/mark>\u201d). In this case, the question marks should be replaced with actual values available during program execution. Using a parameter list with “?<\/mark>” is preferred to prevent SQL injection attacks<\/mark>.<\/p>\n\n\n\n
    INSERT INTO table_name (column_name1, column_name2...) VALUES (?, ?...)<\/pre>\n\n\n\n
    The AddDBRecord <\/mark>method can be developed with a SQL command:<\/p>\n\n\n\n
    \/\/AddDBRecord(aName As String, aDeposit As Currency, aWithdraw As Currency)<\/mark>
    Var<\/mark> sql As String<\/mark> = \"INSERT INTO Accounts (AccountName, AccountDeposit, AccountWithdraw) VALUES (?,?,?);\"

    Var<\/mark> myValues() As Variant
    myValues.Add(aName)
    myValues.Add(aDeposit)
    myValues.Add(aWithdraw)

    Try<\/mark>
      App.db.ExecuteSQL(sql, myValues)
    Catch<\/mark> error As<\/mark> DatabaseException
      MessageBox(\"DB error \"+error.Message)
    End Try<\/mark><\/pre>\n\n\n\n
    myValues()<\/mark> is defined as an array<\/mark>. The size of the array is not defined. To add an element to the array, use the Add<\/mark> method. All elements of the array myValues<\/mark> are of type Variant<\/mark>. A Variant<\/mark> 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<\/mark> and the second (index 1) and third (index 2) elements contain a Currency<\/mark> value. These are the data elements needed to replace the question marks in the SQL Insert statement.<\/p>\n\n\n\n
    App.db.ExecuteSQL(sql, myValues)<\/pre>\n\n\n\n
    Use the ExecuteSQL<\/mark> method to execute the SQL statement defined in the string variable sql<\/mark>. The first parameter of the ExecuteSQL<\/mark> method is the SQL command defined in a String variable (sql<\/mark>), the second parameter is the array to replace the question marks (myValues<\/mark>).<\/p>\n\n\n\n
     A Variant<\/mark> data type can contain all kind of data including arrays.<\/pre>\n\n\n\n
    Reading data from a database table<\/h2>\n\n\n\n
    Now all transactions are stored in the Accounts<\/mark> 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<\/mark> (TransactionList<\/mark>). This can be accomplished via the SelectionChanged<\/mark> event from the AccountPopup<\/mark> control.<\/p>\n\n\n\n
    The SelectionChanged<\/mark> event should carry out a number of activities:<\/p>\n\n\n\n
      \n
    1. Retrieve all historical data for the selected account from the database table (Accounts<\/mark>);<\/li>\n\n\n\n
    2. Enter the retrieved data into the DesktopListBox<\/mark> (TransactionList<\/mark>);<\/li>\n\n\n\n
    3. Recalculate the account balance based on the retrieved historical data;<\/li>\n\n\n\n
    4. Initialize the AccountBalance<\/mark> property with this calculated balance.<\/li>\n<\/ol>\n\n\n\n
      The following code has been entered in the SelectionChanged<\/mark> event of AccountPopup<\/mark>:<\/p>\n\n\n\n
      \/\/SelectionChanged(item As DesktopMenuItem)<\/mark>
      ChangeActiveAccount(Me<\/mark>.SelectedRowText)

      Var<\/mark> rs As<\/mark> RowSet = ReadDBData \/\/Retrieve data from database table<\/mark>

      PopulateListBox(rs) \/\/Show retrieved data in DesktopListBox<\/mark>

      Var<\/mark> balance As Currency<\/mark> = CalculateBalance  \/\/Recalculate balance of the selected account<\/mark>
      ActiveAccount.UpdateBalance(balance)
      ShowBalance
      <\/pre>\n\n\n\n
      ChangeActiveAccount method<\/h3>\n\n\n\n
      ChangeActiveAccount(Me.SelectedRowText)<\/pre>\n\n\n\n
      ActiveAccount<\/mark> (type class Account<\/mark>) is a new Private property of AccountWindow<\/mark>. This property contains a reference to the object selected by choosing an item in AccountPopup<\/mark>. From then on we can always use ActiveAccount<\/mark> to refer to the properties and methods of the class Account<\/mark>.<\/p>\n\n\n\n
      Since the code is part of the SelectionChanged<\/mark> event of the AccountPopup<\/mark>control, the Me<\/mark> keyword refers to the AccountPopup<\/mark>control.<\/p>\n\n\n\n
      The method ChangeActiveAccount<\/mark> in AccountWindow<\/mark> contains the following statements:<\/p>\n\n\n\n
      \/\/ChangeActiveAccount(selectedAccount As String)<\/mark>
      Select Case<\/mark> selectedAccount
      Case<\/mark> \"CheckingAccount\"
        ActiveAccount = CheckingAccount
      Case<\/mark> \"SavingsAccount\"
        ActiveAccount = SavingsAccount
      Case<\/mark> \"RetirementAccount\"
        ActiveAccount = RetirementAccount
      End <\/mark><\/pre>\n\n\n\n
      Retrieve historical data from the database<\/h3>\n\n\n\n
      To retrieve the historical data from the database for the account selected in AccountPopup<\/mark>, we use a SQL SELECT command. The general syntax for the SQL SELECT command is as follows:<\/p>\n\n\n\n
      SELECT * FROM table_name; \/\/all columns from table_name are fetched<\/mark>

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

      SELECT column_name1, column_name2... FROM table_name WHERE condition \/\/only the named columns from table_name are fetched that meet the condition<\/mark><\/pre>\n\n\n\n
      We only need to retrieve the columns AccountName<\/mark>, AccountDeposit<\/mark> and AccountWithdraw <\/mark> from the table Accounts<\/mark> where the column AccountName<\/mark> equals the item selected in AccountPopup<\/mark>. This is our SQL command required:<\/p>\n\n\n\n
      SELECT AccountName, AccountDeposit, AccountWithdraw FROM Accounts WHERE AccountName = AccountPopup.SelectedRowText<\/pre>\n\n\n\n
      Executing the above SQL statement on the database table Accounts<\/mark> is contained in a separate method named ReadDBData<\/mark> within AccountWindow<\/mark>. <\/p>\n\n\n\n
      \/\/ReadDBData As RowSet<\/mark>
      Var<\/mark> rs As<\/mark> RowSet
      Var<\/mark> sql As<\/mark> String = \"SELECT AccountName, AccountDeposit, AccountWithdraw  FROM Accounts WHERE AccountName =  ?;\" 
      Try<\/mark>
        rs = App.db.SelectSQL(sql, AccountPopup.SelectedRowText)
      Catch<\/mark> error As<\/mark> DatabaseException
        Messagebox(\"DB Select error \"+error.Message)
        Quit
      End Try<\/mark>
      Return<\/mark> rs<\/pre>\n\n\n\n
      A RowSet<\/mark> contains database rows that are the result of a database query. The Xojo method SelectSQL<\/mark> executes the SQL command (sql<\/mark>) and replaces the question mark  (?<\/mark>) in the sql<\/mark> String with the selected item in AccountPopup<\/mark>. The selected rows are stored in a RowSet<\/mark> named rs<\/mark>. This RowSet<\/mark> is returned to the calling method.<\/p>\n\n\n\n
      Enter the RowSet data in a DesktopListBox<\/h3>\n\n\n\n
      PopulateListBox(rs)<\/pre>\n\n\n\n
      To populate the retrieved RowSet<\/mark> in the DesktopListBox<\/mark> (TransactionList<\/mark>) the following piece of code can be used:<\/p>\n\n\n\n
      \/\/PopulateListBox(rs As RowSet)<\/mark>
      TransactionList.RemoveAllRows
      TransactionList.ColumnCount = rs.ColumnCount

      If<\/mark> rs <> Nil<\/mark> And<\/mark> rs.RowCount <>  0 Then<\/mark>
        For Each<\/mark> row As DatabaseRow In<\/mark> rs
          TransactionList.AddRow
          For<\/mark> column As Integer<\/mark> = 0 To<\/mark> rs.ColumnCount - 1
            TransactionList.CellTextAt(TransactionList.LastAddedRowIndex, column) = row.ColumnAt(column).StringValue
          Next column 
        Next 
      End If<\/pre>\n\n\n\n
      Before populating the DesktopListBox<\/mark> all rows are removed (TransactionList.RemoveAllRows<\/mark>)  and the number of columns in the DesktopListBox<\/mark> (TransactionList.ColumnCount<\/mark>) is set equal to the number of columns in the RowSet<\/mark> (rs.ColumnCount<\/mark>).<\/p>\n\n\n\n
      If the RowSet<\/mark> (rs<\/mark>) contains multiple rows (If rs <> Nil And rs.RowCount <> 0 Then<\/mark>), all the columns (For column As Integer = 0 To rs. ColumnCount – 1<\/mark>) of each row (For Each row As DatabaseRow In rs<\/mark>) are transferred to the newly added row (LastAddedRowIndex<\/mark>) of TransactionList<\/mark> (TransactionList.CellTextAt(TransactionList.LastAddedRowIndex, column) = row.ColumnAt(column).StringValue<\/mark>). The parameters of the CellTextAt<\/mark> method refer to a row and a column in the DesktopListBox<\/mark>. The parameter in the RowSet ColumnAt<\/mark> method refer to the index of a column in the RowSet<\/mark>.<\/p>\n\n\n\n
      The StringValue<\/mark> property attempts to convert all \u201cspecial\u201d values discovered in a column of the RowSet<\/mark> (for example, Currency<\/mark> data types) to a String<\/mark>. A String<\/mark> is accepted in a column of TransactionList<\/mark>.<\/p>\n\n\n\n
      Recalculate the balance<\/h3>\n\n\n\n
      Var balance As Currency = CalculateBalance<\/pre>\n\n\n\n
      Now we can use TransactionList<\/mark> to calculate the actual balance for the selected Account<\/mark>. For each row in the DesktopListBox<\/mark>, we make the difference between the Deposit<\/mark> value (column 1 in the ListBox) and the Withdraw<\/mark> value (column 2 in the ListBox) and add the result to the local variable balance<\/mark>. This value is returned to the calling method. We have to convert the DesktopListBox String<\/mark> values to a Currency<\/mark> data type (Currency.FromString<\/mark>).<\/p>\n\n\n\n
      The following code was used to calculate the balance:<\/p>\n\n\n\n
      \/\/CalculateBalance As Currency<\/mark>
      Var<\/mark> balance As Currency<\/mark> = 0.0
      If<\/mark> TransactionList.RowCount > 0 Then<\/mark>
        For<\/mark> row As<\/mark> Integer = 0 To<\/mark> TransactionList.LastAddedRowIndex
          balance = balance + Currency<\/mark>.FromString(TransactionList.CellTextAt(row,1)) -  Currency<\/mark>.FromString(TransactionList.CellTextAt(row,2))
        Next<\/mark>
      End If<\/mark>
      Return<\/mark> balance<\/pre>\n\n\n\n
      Update AccountBalance property<\/h3>\n\n\n\n
      \/\/UpdateBalance(balance As Currency)<\/mark>
      ActiveAccount.UpdateBalance(balance)<\/pre>\n\n\n\n
      To reuse our existing program, we need to update the AccountBalance <\/mark>property with the calculated value (balance<\/mark>). Because the AccountBalance<\/mark> property is private, we added a new public method to the Account<\/mark> class called UpdateBalance<\/mark>:<\/p>\n\n\n\n
      \/\/UpdateBalance(balance As Currency)<\/mark>
      AccountBalance = balance<\/pre>\n\n\n\n
      ShowBalance method<\/h3>\n\n\n\n
      \/\/ShowBalance<\/mark>
      BalanceLabel.Text = \"The balance of the \" + AccountPopup.SelectedRowText + \" is: \" + ActiveAccount.GetBalance.ToString(Locale.Current)<\/pre>\n\n\n\n
      In the ShowBalance<\/mark> method, we can use the ActiveAccount<\/mark> property to reference the active account in use. The ToString(Locale.Current)<\/mark> converts Currency<\/mark> data (returned by GetBalance<\/mark>) into a String<\/mark> in the way data is displayed on your Operating System.<\/p>\n\n\n\n
      Some useful database methods<\/h2>\n\n\n\n
      Method<\/th>Type<\/th>Description<\/th><\/tr><\/thead>
      CreateDatabase<\/td><\/td>Creates and opens a new database defined in the property DatabaseFile<\/td><\/tr>
      Connect<\/td><\/td>Connects to an existing  database<\/td><\/tr>
      AddRow<\/td>table_name As String, row As DatabaseRow<\/td>inserts row in the table_name<\/td><\/tr>
      ExecuteSQL<\/td>SQL command As String,
      [parameters() As Variant]<\/td>      		Executes the SQL command (no return)<\/td><\/tr>
      SelectSQL<\/td>SQL command As String,
      [parameters() As Variant]<\/td>      		Selects data based on the SQL command and returns a RowSet<\/td><\/tr><\/tbody><\/table>
      Important SQLiteDatabase methods<\/figcaption><\/figure>\n\n\n\n
      <\/p>\n","protected":false},"excerpt":{"rendered":"
      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 … Continue reading “Database Transactions”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-415","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/xojo.itbib4you.be\/index.php\/wp-json\/wp\/v2\/pages\/415","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xojo.itbib4you.be\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/xojo.itbib4you.be\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/xojo.itbib4you.be\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xojo.itbib4you.be\/index.php\/wp-json\/wp\/v2\/comments?post=415"}],"version-history":[{"count":90,"href":"https:\/\/xojo.itbib4you.be\/index.php\/wp-json\/wp\/v2\/pages\/415\/revisions"}],"predecessor-version":[{"id":513,"href":"https:\/\/xojo.itbib4you.be\/index.php\/wp-json\/wp\/v2\/pages\/415\/revisions\/513"}],"wp:attachment":[{"href":"https:\/\/xojo.itbib4you.be\/index.php\/wp-json\/wp\/v2\/media?parent=415"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}