GettingStarted:SQLite Basics

Learn the foundations to using Xojo with SQLite - the universal, public domain and embedded database engine used for all kind of apps and solutions. No previous experience with database management needed. Once you have finished this tutorial, you’ll have the foundations to create your own multiplatform database apps and to better explore and understand the related Xojo classes.

In this tutorial, we will create a basic but functional database app to manage customers and invoices. Read each section and watch the accompanying videos (in Spanish with English subtitles).

Introduction

SQLite and Xojo are a great team for managing large amounts of information easily. Users of these Xojo apps don’t have to install any additional components or libraries. In addition, Xojo solutions using SQLite can be easily deployed to any of the supported platforms without the need of making changes in the base code (or with minimal changes).

What Xojo licenses support the use of SQLite in your products? Absolutely all of them: Xojo Lite, Desktop, Pro, Web, iOS, Pi and Enterprise!

A few reasons the SQLite a database engine so popular is:

It is an embedded library. This means that the users of the apps don’t need to use additional hardware or install additional or external components in order to use them. What's more, thanks to Xojo you can forget about the need to include or add any SQLite libraries to the deployed executable package. Xojo takes care of everything, offering the same behavior in all the supported platforms!
It is Public Domain. This means that you will not have to pay a license or royalties for using SQLite in your developed solutions, even if they are commercial.
SQLite has a low footprint, so your deployed executables will not need a lot of storage; this is paramount if you need to deploy your Xojo solutions on embedded hardware with strict requirements.
It is truly Universal. SQLite is included by default in a wide range of devices, even in the main Operating Systems used for Desktop, Smartphones, tablets, Smart TV, etc.
The databases generated with SQLite are common files, whose size is only limited by the Operating System used to store them.

Xojo + SQLite offers great flexibility:

We can connect with one or several already existing SQLite databases, working with the data contained in their respective tables.
We can design an SQLite database from scratch, using the integrated Database Editor in the Xojo IDE.
We can create an SQLite database from scratch via code from the app.

In order to accomplish these operations, among others involved with database management, Xojo offers two classes to choose from depending of the target platform:

SQLiteDatabase. This is the class we need to use for Console, Desktop and Web apps.
iOSSQLiteDatabase. This is the class we need to use for our iOS apps.

In both cases —and at the time this course has been published— Xojo uses the 3.20.1 (2017, August 24) version of the SQLite engine, while the officially latest available from the SQLite Website is the 3.22.0 (2018, January 22) version. Why this difference between the official and the supported SQLite library? The answer is that Xojo adds the most stable, tested and trustable SQLite release to the framework, avoiding some of the problems, backwards compatibility issues or bugs that may arise when adopting the latest available releases.

In addition, sharing the same database library (or engine) release also means that our apps will be 100% portable between all the supported platforms. Thus, the most important fact we have to remember is that, while we may not be using the latest available SQLite release, we will not be using a release far away either; and with the assurance that we will get the same behavior no matter that the solution will be running under macOS, Windows or Linux, for example.

Of course, Xojo also updates the implemented SQLite library from time to time. For example, when this course was published, we already knew that the Xojo 2018.1 release will support 256 bits ciphering, being significantly more strong in comparison with the 128 bits used until Xojo 2017r3 release.

Anyway, if you find yourself in a situation where you need additional features not included in the Xojo provided SQLite implementation, you can turn to any of the available third parties add-ons and components that expand the use of SQLite even more. For example, Monkeybread Software offers two Multiplatform plug-ins. The first of these is the MBS Xojo SQL Plugin; and the second one is MBS SQLite Extension, adding the following features over the ones you can find in the default SQLite implementation:

Additional mathematical functions as Round or Pow.
Functions to save Blob values (large Binary Objects) in files, outside of the own database file; something that is always preferred and even a good practice.
Functions to calculate Blob values checksums in the server.
String functions as SoundEx or not using accented characters for text search.
Use of Regular Expressions for search.

Independent of the SQLite implementation you decide to use, notice that all the database operations made on SQLite use the SQL language, in fact the SQL/92 and SQL/99 subset.

Multiuser support

Unlike other database engines that offer a Client/Server architecture, SQLite was not developed from its inception to support several simultaneous accesses over a database file residing under a network server. However, you may be surprised to know that through the WAL (Write Ahead Logging) feature activation —also supported by Xojo— it is possible to provide this functionality if there are not too many simultaneous accesses, and the use from the clients (apps) is done not directly accessing the database file residing in a network server, but through an app, residing in the same server network that the database file. In fact, that it is better to act as an intermediate, communicating between the clients and the database file itself.

An example of this configuration can be a Web app created with Xojo that communicates with a SQLite database located in the same server, or any other Xojo app acting as a server to one (or more) SQLite databases.

You also can find third parties products able to serve SQLite database files from a server, as for example CubeSQL from SQLabs and Valentina Server from Valentina, among others.

In-Memory Databases

A peculiarity to SQLite databases is that they don't necessarily need to be create or saved as files on physical storage; in fact, we can create them as in-memory databases. This gives us an idea about the kind of uses, the flexibility and performance you can get… if the hardware you use has enough memory to support them! (as probably is the case with the current available hardware).

You can also create and use this kind of in-memory databases in Xojo apps, with the advantage that you will find the features that enables the apps to dump, save or backup these in-memory databases to physical files.

SQLite Database Anatomy

What makes a SQLite database after all? As we have seen, it's no more than a regular file whose content represent one or more tables. At the same time, these tables are composed of a series of columns whose label or name (identification) and type are used to define their structure. Then, we will populate these columns, or some of them, through the creation of records (or rows) for the defined tables in our database file.

If you think about it, this representation doesn’t differ too much from the typical spreadsheet, while it is true that the database files have other aspects that separate them from this other kind of software. Anyway, it helps to visually understand the underlying basic structure of a SQLite database. In fact, it is through the table definition as we start to give structure to the database. For that, during the table definition it is mandatory to name or label for every column; once done that, we can start creating new records for the newly created table.

However, the same way we have at our disposal several kind of Types in Xojo that we can use in order to define our variables or labels, and that will be used both to identify and limit the kind of data we can assign to them, or in combination with other variables, properties and method parameters or events, SQLite also offers their own Types set, but with a main and very important difference:

Xojo is strict in the management of their Types. This means that, once you declare a variable, for example as Text, you will not be able to assign a different type to it, just other Text values or previously converted to Text values.

Under SQLite the Type declared in association with a column is just intentional. This is, when we create the SQLite database tables we can assign any of the supported types to the columns, but this will not impede to insert or update the columns of the records using values with completely different types to the expected or declared ones. However, when possible, the SQLite engine will try to implicitly convert the received value to the one declared for the column, but this behavior is not guaranteed at all!

Once we know this, we also have to notice that the available data Types are:

Null.
Integer.
Real.
Text.
Blob.

As you can see, SQLite lacks for example the Boolean type, while internally manages it as 1 or 0 for the columns defined as Integer, or as True and False for the columns defined as Text. In addition, Xojo provides the mechanism (as we will see in next sections) that allows us to expand, in certain way, the amount of types we can work with when dealing with SQLite databases, easing this way the retrieval and saving of data into the records with the expected results.

When it comes to the structure creation for a database, it is possible to add new tables to an already created database, delete any of the existing ones, and modifying any of the already defined tables in the database, but in this last case only by adding new columns and not deleting any of the previously defined columns.

Creating Our First Database

As we have seen, we can create the SQLite database structure using any tool we want to, ranging from any of the existing commercial or free SQLite database Editors, or even using the command line from the Terminal or system prompt. However, the Xojo IDE includes its own database Editor, and this will be the one we will use in order to create our first SQLite database! For that, choose the Insert > Database > New SQLite Database… from the menu.

As result, we will get a Dialog to enter the name of the database file and select the path or folder to save it. After confirmation, Xojo will present the Database Editor, adding the corresponding object reference as a Database Type —i.e.: an alias to the file on disk— to the Navigator (1), showed in the following screenshot. If we want, we can even change the name of the referenced object from the Inspector (A), but notice that we will be renaming the internal reference used by the Xojo code and not the name of the real file on disk:

The database files created and added to the Xojo project in this way will expect to always find the database file in the same absolute path. This means that, when deploying the apps using database files added in this way, we will have to be sure that the deployed SQLite file will be reachable using the same path (or location), and the final user has the proper read and write privileges to access the database file.

The operations available in the Database Editor are not as complete as those found in the dedicated SQLite management apps, but they are good enough to put the main wires to our database in order to prototype our apps, or to define the structure of really simple SQLite database based apps. For these cases, we will be able to add new tables (2), new column definitions for the selected table (3), and apply the changes made on the Database (4); using the buttons found in the upper toolbar.

Don’t forget to apply the changes made after adding or modifying new tables or columns. It may seem something trivial, but the flexibility to change between the objects listed under the Navigator will result in losing the changes made to the database if they are not previously saved.

As we add new columns (or fields) to the database, we’ll see that the Inspector Panel will bring a new range of options we can use to define the name of the column, the associated Type and other attributes. Let’s see what they mean and which of these make sense when used in combination with a SQLite database:

Type. You will notice that this popup menu offers more types that the ones supported by SQLite seen in the previous section. It is recommendable to always adhere to the only types officially supported by SQLite.
Primary Key. All the SQLite tables have one or several columns to uniquely identify every record in the table. In fact, all the tables include implicitly and by default a column named rowid that, if there is no other column doing the same function, will act as the Primary Key for the table. If during our table definition we create a column of Integer type, also activating this checkbox in the Inspector Panel, then rowid will become an alias for the column defined by us. Notice that, in our example, the id column is acting as the Primary Key, being visually indicated with the icon of a little key under the Key column in the Editor.
Mandatory. When we activate this Constraint, we are telling SQLite that we don’t want to admit the creation of new records that omits a valid value for this column. It's a good way to make sure that the record will have all the needed information we expect to find in future operations on it. Thus, if SQLite finds that the operation omits a value for this column, the operation will not be completed and we will get an error that we can catch from the Xojo code.
Index. When this option is activated for a Column, the associated table will create an Index based on it. The purpose of this index is improving some operations, like the queries' performance, or those resulting from combining data, as we can get when using the SQL instruction Join. A table may create indexes from just one column or multiple columns. In the second case, the order we use to include the columns in the index is very important because SQLite will sort the data based on the first defined column for the index. The assignation, quantity or kind of indexes created for the tables in the database is kind of an art, and will depend mostly of the operations and queries we will expect to do on the stored data.
Default Value. We also can define the default value assigned to a column when it's not provided as result of the new record creation and insertion. This is a good way to make sure that we will not have empty fields; also to get a known and expected value if none is provided. We only need to be sure of assigning a default value compatible with the Type defined for the column.
Length. This has no relevance when working with SQLite database because SQLite doesn’t accept field length restrictions, in comparison to what's doable when using other database engines. Having said this, SQLite imposes length limits for Text and Blob columns, but they are big enough to not be considered a real limitation for most of the SQLite based solutions.

Creating the Schema with SQL … From the Editor

We can create the database schema (tables structure) adding the tables and columns as we have shown in the previous section, but this has two main downsides: it is slow and doesn't allow access to all of the flexibility of SQL.

The good news is that if we have enough SQL syntax knowledge, we can use the toolbar button to execute complex SQL sentences. This button will not be enabled until we have added and selected a Table.

However this is not a big issue, mainly because the newly added tables are not saved to the database until we expressly push the corresponding button. Thus, in order to create the same schema for our example database, we only need to add a new empty table (we don’t have to change its name either), clicking the SQL Statements button (5) after that, and writing the following sentence in the resulting Dialog box:

CREATE TABLE person(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, surname TEXT, age INTEGER);

Push the Execute button, close the dialog and you will see how the database will reflect the just created table, with the structure and column definitions as result of the operation. This requires less time in comparison to the same procedure using the Database Editor buttons combined with the Panel Inspector attributes.

Let’s take advantage of this feature again to create a second table. While the last table is still selected, use the SQL Statements button again and enter this sentence:

CREATE TABLE invoices(id INTEGER PRIMARY KEY AUTOINCREMENT, customer TEXT NOT NULL, amount INTEGER);

Execute the sentence and be sure that the database has the two tables: person and invoices. Once this is done, we will have finished our example database. Remember: while the integrated database editor is useful to define simple database or for prototyping, you’ll probably want to use specific tools or apps devoted to the creation, edition and management of more complex SQLite databases projects.

Nevertheless, the integrated Database Editor doesn’t offer options to add new records to the database. This is something we will take care of in the next section!

DataControl

Xojo Desktop apps can use a control, under the Library, that greatly eases the designing and implementation of simple database driven apps, reducing the amount of code we have to write to navigate through the records of a table, create new records, update or delete a record. After all, less code means less bugs and time to maintain it! I’m talking about the DataControl control.

We use the DataControl in this tutorial because it is the simplest way to learn and assimilate several database concepts, and because it eases the creation of really simple database driven apps. But you should avoid the use of this control in the design of more complex apps, opting to directly use the classes SQLiteDatabase and iOSSQLiteDatabase, combined with their respective methods, in order to query the SQLite databases and execute complex SQL sentences.

The big advantage to using this control is that we only need to assign the name of the database we want to use, the name of the table over which we are going to act, and the SQL sentence that we want to execute in order to retrieve a RecordSet matching the query; all of this simply setting the available properties through the Inspector.

Once we get the RecordSet (or cursor), we are able to use the DataControl interface widgets to navigate through the records: jumping to the first record, moving to the next or the previous, or jumping to the last record available for the set. But we are not limited to assign the DataControl properties via the Inspector. We also can set all of them (or modify them) via our Xojo code at runtime. This means that we can, for example, modify the SQL query we want to execute in order to get a record set, or even change the database table name we want to act on.

In some cases, as you will see in this tutorial, we can omit the provided navigation features, setting the DataControl instance visibility property to False. This is useful, for example, if we want to have two or more DataControl instances in the same window, using only the navigation features provided by one of them.

The main advantage of the DataControl is that we can establish bindings or links between our DataControl instance and several user interface controls, so they can display the data from the columns of the table associated with the DataControl instance. Of course, the DataControl instance will also use this bindings in the opposite direction: use the data contained in the linked user interface controls to update the current displayed record or during the insertion of a new record into the database table.

In theory, we can use this binding feature between the DataControl and the following user interface controls:

TextField
TextArea
Label
ListBox
PopupMenu
ComboBox
CheckBox

For our example application, we will exclusively use TextField, label and, indirectly, ListBox instances; and the needed associated code so the DataControl can create, insert, update and delete the records for the tables defined in our example database.

In addition, the DataControl also gives us a great security feature when it comes to dealing with data entered through TextField controls: SQL injection protection.

This means that the DataControl will be in charge of verifying and sanitizing the data provided by the user, for every field in the query, in those operations where the data has to be written to the associated table and database. So, our database integrity will be protected without us needing to do anything special!

Setting the DataControl Source

In order to see how the DataControl works with our example database, start an example app that will offer a set of basic features reflecting the database schema: we will be able to add new customers, new invoices and also access all the invoices for the currently selected customer, calculating the sum. The best part is that we will be able to do all of this using just DataControl instances and the bindings feature when possible; with a minimum amount of code.

Layout the user interface for our example app as shown in the following screenshot. This is the same desktop example that we previously added to the SQLite database. Don't forget to name the highlighted controls via the Inspector Panel. The remaining controls will have their default names:

All buttons are PushButton instances.
All the labels are, well, Label instances (including the one placed at the right of "ID", and whose default text has been deleted).
We will use three TextField instances, both for showing and capturing the data associated with their respective labels: "Name", "Surname" and "Age".
Add a DataControl instance, named DataControl1 with its size adjusted to the width of the containing window. Change its Caption property to “Customers” and the align property to Center.

At this point, set the database we want to manage via the DataControl instance, DataControl1. We simply have to select the DataControl instance, entering the following values under the Behavior section of the Inspector Panel.

Database. This popup menu shows all the databases added to the project. Select the example database we created in the previous section. Table. Once the database has been selected, this menu is populated with the name of all the available (and selectable) tables for the database. Select the people table, so this DataControl can add, update and delete customers records to it.

SQLQuery. Optionally, this is the field where we can write the SQL sentence or sentences in order to get the matching record set from the selected database table. Remember that we can modify this query at any point from our code.

ReadOnly. Leave this property set to False to be sure that we will be able to modify the data from the managed records. If we set this to True, then we would have only Read access to the managed records.

Commit. This property is enabled by default, insuring that the add, insert and update operations are implicitly saved to the database. Otherwise, you should do this operation from code.

Once this is done, we will have linked the DataControl instance with the table of the database we want to operate on. In addition, we have wrote the sentence to get the records we will be able to navigate using the widgets from the DataControl instance.

In the next section, we will see how to get the UI controls to show the data from the fields (columns) of the table managed by the DataControl.

Showing Data in the UI Controls

We don’t need to write a line of code in order to instruct the user interface controls to show and act as sources for the data from the corresponding fields of the database table (TextField instances in our example app). I mean, if we have the ReadOnly property from the DataControl instance set to False, then the linked control will show the data corresponding to the assigned column from the table during the record navigation, acting at the same time as the data source for the Insert and Update operations executed by the DataControl.

Select the first TextField (probably named TextField1) placed to the right of the “Name:” label in the user interface layout. Next, choose the Attributes section from the Inspector Panel (the one showing a cog wheel), and put the following values under the Database Binding section:

DataSource. Write the name of the DataControl instance whose column/field we want to link with the control. In this case it is DataControl1.

DataField. Write the name of the column/field we want to link from the database table managed by the DataControl. In this case it is name. We will repeat these steps to set the link for these controls:

TextField2. This is the TextField placed to the right of the "Surname" Label. Write surname for the DataField and DataControl1 for the DataSource.

TextField3. This is the TextField placed to the right of the "Age" Label. Write age for the DataField, and DataControl1 for the DataSource. Now, we have linked all the controls we are interested in with their respective fields from the database.

However, you may have have noticed that all of these are TextField instances except the age column from the person table were declared as Integer. How is the type conversion made? The answer is: automatically! Remember that the type definition is not strict under SQLite, but by affinity. SQLite will automatically convert the data type for you. In this case, it will extract and convert the numeric value from the text we write in the TextField. We can enforce this behavior using a Mask for the TextField3 we linked to the age column in the previous section. For that:

Select the TextField3 instance.
Write ## in the Mask field under the Behavior section from the Inspector.

Adding New records, Inserting, Updating and Deleting

Our user interface already knows how to show the information from the Person table of the example database and how to act as the source during the creation of new records for the database table. However, the example database is still empty, without records. In this section we will see how to do all these operations exclusively using the DataControl.

We will start implementing the option to add a New Record. For that, add the Action event to the pbNewRecord button and write the following snippet of code in the resulting Code Editor:

DataControl1.NewRecord

pbInsert.Enabled = True
pbUpdate.Enabled = False
pbDelete.Enabled = False

lid.Text = ""

The most important line of code is the first one: DataControl1.NewRecord. This is all we need to add a new record to the associated table from the database. This record, however, will not be available for the underlying table until we Insert it. Think about it as an intermediate step, in charge of cleaning the previously displayed data in the linked user interface controls and preparing them for the creation of a new record. Thus, the user also has a visual hint of the new record operation.

The following three lines of code are in charge of properly setting other related interface controls. For example, we probably expect being able to Insert the new record, but not to click the button in charge of updating it. After all, we can’t update a record that has not been inserted yet. We don’t expect to be able to click the Delete button for the same reason.

With the last line of code we clear the content in charge of showing the record ID, mainly because we don’t know its value until we insert the just created record.

Insert Record

Now write the code for inserting the new created record. For that, select the pbInsert button and write the following snippet of code in the associated Code Editor:

DataControl1.Insert

DataControl1.RunQuery
DataControl1.MoveLast

pbDelete.Enabled = True

The DataControl uses the Insert method to insert the record into the associated database table. Think about this as the Save action for a newly created record. The effect of executing this method is that the current RecordSet becomes invalid. So, after inserting a new record we have to run the query against the database in order to get a new RecordSet.

This is what the method RunQuery does. This DataControl method is in charge of executing the SQL statement set in the SQLQuery property. This is exactly the same property we have used before in the Inspector Panel for the DataControl.

As a result of executing the RunQuery method, the DataControl will receive a new RecordSet and will display the first record to the user interface. What we probably want for our app is to show the same current record we were inserting. This is precisely what the MoveLast method does, showing the last record inserted. That is, the last record from the RecordSet.

Finally, as we have effectively inserted a new record in the database table, it now makes sense to enable the button that can delete it.

Updating Record

Let’s see now how we can update a record. Select the pbUpdate button and add the Action event to it, writing the following code in the resulting Code Editor:

DataControl1.Update

Dim currentRow As Integer = DataControl1.Row

DataControl1.RunQuery
DataControl1.MoveTo(currentRow)

The method that uses the DataControl to update a record is straightforward: Update. After executing this method, the DataControl will still show the same record (i.e.: the same table row); however, as result of the updating process, the RecordSet will become invalid so we need to execute the SQL query again, using the MoveTo method after that in order to navigate to the record we where working on (and whose index was previously stored into the currentRow variable).

Deleting Records

The last CRUD (Create, Read, Update, Delete) operation related to databases is the one in charge of deleting the current record. In this example we will omit the more than advisable Dialog Box showing a warning to the user about this action. To implement this, select the pbDelete button and add the Action event to write the following snippet of code in the resulting Code Editor:

DataControl1.Delete
DataControl1.RunQuery

In order to delete a record managed by a DataControl instance we only need to call the Delete method. Once again, this operation invalidates the current RecordSet so we have to run the SQL query again in order to get a new, updated, RecordSet.

As you have seen, we needed to write very little code to add the feature for Creating, Inserting, Updating and Deleting records for the people table in our example database!

Capturing Records Reposition

We intentionally left an item not updated with the Binding mechanism, this is the one in charge of showing the ID value for every record. Why it is not advisable to establish this bind as we did with the other table fields? While this is a known value during the database table reading operations, we are not in the same situation when we are creating a new record. I mean, as we established that in our database table the ID column automatically incremented its value via the SQLite engine, it is not recommended to directly assign it via code.

Then, what can we do to display this value? The answer is simple: adding the Reposition Event to our DataControl instance. This is the event that fires every time the user uses the navigation widgets to change the viewed record from the RecordSet.

Choose the DataControl instance and add the Reposition Event, writing the following code in the resulting Code Editor:

lID.Text = Me.RecordSet.Field("id").StringValue
pbBorrar.Enabled = True

The interesting part is the first line: we are accessing directly the RecordSet using the Field method and passing the name of the column we are interested in (ID in this case) as argument to it. Then we will simply instruct to retrieve the column value for the record as a text String (StringValue).

How do we know that we are retrieving the column value for the current record? Because every time we use the navigation widgets from the DataControl instance, or any of the available methods for navigating the record set, the RecordSet updates its internal pointer to the current record. Thus, always we access a field (Field) from the RecordSet we will be acting on the current record.

Notice in the second line of code that we are also enabling the delete button. After all, it makes sense that if this event gets fired it is because we are navigating through already available records.

Working with Two Tables

In this section we will add a second window to the project. This window will be in charge of adding new records to the Invoices table. It will also serve to show us how to relate values from two tables, because the new invoice records have to be paired to the id of an existing customer from the "people" table. We will see also how to use the DataControl instance without needing to query the database table, simply to insert records and acting, thus, as a controller or synchronizer between the user interface and the associated database table (Invoices) from our example database.

Add a new window to the project. Next, set the Type property to Modal Dialog, and the Title property to New Invoice, both of them under the Frame section of the Inspector Panel. Change also the Name property to Invoice, under the ID section.

Next, layout the new window as showed in the following screenshot, leaving all the names for the control instances as their default values, except the ones highlighted in red.

As you can see, the DataControl instance (DataControl1) is placed outside the physical bounds of the window. That's because we are not interested in the provided functionality to navigate between the records, but just as a controller. In fact, set to False the Visible property under the Appearance section. While we are still in the Inspector Panel, set the binding to the database under the Behavior section:

Database. Set this property to the item matching the name of our example database: test.
Table. Set this property to the name of the table we are going to work with: customers.
SQLQuery. Leave this field empty. We don’t want to get a record set, just sending information from the user interface to the database table to insert new records and not in a two way mode, as we did with the persona table.
Commit. Leave this property at its default True value, ensuing this way that all the insert operations will apply a Commit.

Now, we need to establish the binding between the user interface controls and the columns from the chosen database table.

Choose the Label control with the name lbCustomerID and select the section Attributes from the Inspector Panel (the one identified by a cog wheel icon). Set the value DataControl1 for the DataSource field, and the value customer for the DataField, both under the Database Binding section.

Next, choose the TextField1 control and under the Attributes section from the Inspector Panel and enter the values DataControl1 for the DataSource field and amount for the DataField field.

This way we have established the data to be used as source when inserting the new records into the Invoices database table.

The record insertion itself will be done via the code associated with the Action event added to the PushButton1 labeled “OK”:

DataControl1.Insert
Self.Close

Why in this case do we only need to execute the Insert method, while in the main Customers window we had to previously create a new record? The main difference is that in this case our user interface doesn’t navigate between several records from a RecordSet, so we don’t need to clean the user interface controls acting in a bi-directional way. Now, our linked user interface control just needs to be used as the source for the database record insertion and not for also displaying the data received from the table. So, using the Insert method we will create a new register using the data entered in the source controls. That’s all.

The second line of code will close the window once the Insertion operation is completed.

This same line of code (self.Close) will be the one we need to also use for the Action Event of the Cancel button.

We said that our new invoice has to be associated with an id from the current customer in the main window; so we have to assign that information to the Label control named lbCustomerID from the Invoice window. How we can do this? Really easily!

Again select the main window, choose the button labeled New Invoice and add the Action Event to it with the following code in the resulting Code Editor:

Invoice.Show
Invoice.lbCustomerID.Text = lID.Text

With the first line of code we will show the Invoice as a modal Window. Once active, we just need to assign the content from the Label control named lID, from the main window, to the Label control named lbCustomerID. We could also write:

Invoice.lbCustomerID.Text = DataControl1.RecordSet.Field("id").StringValue

In both cases we have passed the id from the current record to the Invoice window.

Iterating the RecordSet

Let’s add a third (and last) window to the project. This is the one in charge of listing all the invoices for the current customer, displaying every item in its own ListBox row, and providing the total accumulated amount at the end of this list.

Once the window has been added to the project, use the following settings in the Inspector:

Name: Invoices.
Type: Movable Modal.
Title: Invoices Listing.

Next, layout the user interface as shown in the following screenshot, leaving all the controls' identifications with their default names, except for those highlighted in red color for the Label and the ListBox:

Choose the DataControl1 instance. Once again, we are just interested in using this DataControl instance to access the managed DataRecord from the associated table, except that in this case we will use the SQL sentence from code. This will act as the bridge between this course and next one about Databases with Xojo. Another main difference is that none of these controls will be linked with the fields on the DataControl1.

Set the DataControl1 properties to these values:

Database: Test.
Table: Invoices.
ReadOnly: On (True).

As you can see, in this case we also omit the query and also set the ReadOnly property to True for first time (no writes allowed to the database: new records, updates, deletions or insert operations).

How we can get the DataRecord we are interested in and use the record data to populate the user interface controls? Via code!

Add a new method to the window using the following signature:

getInvoicesForCustomer(id As Text)

Next, write the following fragment of code in the resulting Code Editor:

lbID.Text = id

DataControl1.SQLQuery = "SELECT id, amount FROM invoices WHERE customer=" + id
DataControl1.RunQuery

Dim total As Double

If DataControl1.RecordSet <> Nil Then

While Not DataControl1.RecordSet.EOF
lInvoices.AddRow DataControl1.RecordSet.Field("id").StringValue
Dim t As String = DataControl1.RecordSet.Field("amount").StringValue
lInvoices.Cell(lInvoices.LastIndex, 1) = t
total = total + CDbl(t)
DataControl1.Recordset.MoveNext

Wend

lTotal.Text = Format(total, "##########.00")
End If

The first thing we can see is that we are assigning the value from the received parameter to the Label control named lbID. This is the one matching the ID of the current record from the Customers window.

The most interesting thing, however, is what we find in the next lines of code:

The next two lines are the ones setting the SQL sentence to the SQLQuery property of the DataControl instance, executing it to get the matching RecordSet. It is a simple SQL statement where we simply use as variable the ID value corresponding to the current record from the Customers' window.

Then, after checking if we have a valid RecordSet objects (non Nil), we iterate all the records adding their fields to both columns of theListBox1instance, accessing the record fields as we have seen in the previous section.

We will take advantage of iterating the records to total the amount from every invoice, using for that the CDbl command that returns a Double type from a text String. Then, we simply show the total addition using the Format function for that.

Finally, add the Action Event to thePushButton1` control and insert the following instruction in the corresponding Code Editor: self.Close.

We just need to select the main Customers' Window, select the pbInvoicesListing, adding the Action Event to the button and putting the following code in the resulting Code Editor:

Invoices.Show
Invoices.getInvoicesForCustomer(lid.Text.ToText)

The first line of code will show the Invoices window and the second line of code will call the method we have created, previously passing as argument the ID for the current record.

The Last Word

Throughout this course we have reviewed the main foundations of SQLite database, their structure and the available Types for tables definition. We have seen how we can use the integrated Database Editor to create from scratch new, basic, SQLite databases and the way we can create and define their tables and their fields. Lastly, we have seen the fastest way to create a functional database front-end or user interface able to Create, Insert, Modify and Delete records from the database, using the DataControl control included by default.

We also have introduced the RecordSet class and how we can access the record fields using the available methods; and how we can execute complete and dynamically created SQL sentences through the DataControl (and RecordSet).

Quizzes

Take these quizzes to test your knowledge of SQLite basics with Xojo. Check your answers on the SQLite Basics Quiz Answers page.

Quiz 1: Introduction to SQLite

Use this Quiz to check what you have learned about SQLite database foundations. Notice that the questions may have more than one right answer.

Question 1.1: Check all the right affirmations.

SQLite is a Client/Server database engine.
SQLite can be used for free.
SQLite doesn’t requires additional installations.
SQLite doesn’t support multiple users.

Question 1.2: Xojo always implements the latest available SQLite Library.

No.
Yes.

Question 1.3: Xojo supports multiple SQLite databases use.

No.
Yes, they always have the same number of Tables in their structure.
Yes.

Question 1.4: What Xojo Licenses offer support for SQLite?

Desktop Lite, Desktop, Console and iOS.
All of them, except Web.
All of them, you pay the required royalties for each deployment platform.
All of them.

Question 1.5: SQLite supports in-memory databases.

Yes, they are always saved to disk before exiting the app.
No.
Only if it is also backed by a database on disk.
Yes.

Question 1.6: Xojo supports saving an in-memory database to disk.

No. We have to have previously created a new database in disk, backing the in-memory data to it.
Yes.

Question 1.7: What is the name of the Class (or Classes) in Xojo we can use to work with SQLite databases?

XQLite.
SQLiteDesktopBase.
SQLiteDatabase.
iOSDatabase.
iOSSQLite.
iOSSQLiteDatabase.

Question 1.8: Is it possible to place a SQLite database in a Server? Check all the right answers.

Only using third-party products.
Using third-party products.
The access to the database file is always made from an app located on the same server.
We need to activate the WAL feature.
No.

Question 1.9: What are the advantages of using the WAL feature on SQLite?

Multiple simultaneous accesses from multiple users.
A better integrity protection for the stored database data.
Reduced memory consumption.
It is mandatory in order to create and use in-memory databases.

Question 1.10: Can we use RegEx (Regular Expressions) in the SQLite queries?

Only if we add third-party components or add-ons.
Yes, Xojo includes RegEx support for SQLite.
No.

Quiz 2: SQLite Database Structure

This quiz lets you check the knowledge acquired about SQLite databases structure. Notice that the questions may have more than one right answer.

Question 2.1: Does SQLite use strict Types checking for columns definition?

Yes.
No.
In some cases.

Question 2.2: A table definition consist fundamentally of…

Cells.
Rows.
Columns and Rows.
Columns.

Question 2.3: The SQLite data Types supported by SQLite are:

Nil.
Integer.
Double.
Text.
Null.
Boolean.
Real.
Picture.
Bolb.
Blob.

Question 2.4: Once a table has been created, we can…

Delete columns.
Modify some of the current columns.
Add new columns to the table.

Question 2.5: SQLite admits creating columns that will be empty during the database use.

Yes.
No.

Quiz 3: Integrated Database Editor

Verify with this quiz the acquired knowledge about SQLite database creation using the integrated Database Editor. Notice that the questions may have more than one right answer.

Question 3.1: Xojo just supports adding already created SQLite databases to the project.

That’s true for databases using a different SQLite library version than the one used by Xojo.
No.
Yes.

Question 3.2: Once we have created a new SQLite database using the integrated Database Editor, the path to the database file will be resolved at execution time; so we can rely on this for our products' deployment.

No. It is an absolute path that we can’t modify.
Only for multiplatform deployments.
Only if the database file has been moved from its original location.

Question 3.3: Xojo automatically saves every change made using the integrated Database Editor, so we can rely on this.

Yes.
Only when changing from table in the same database.
No, we have to make sure to manually save all the changes.

Question 3.4: We can limit the field length for SQLite tables using the Length property.

This has no effect in SQLite.
Of course, we can find this ability under the Inspector Panel and it is applied to the database file schema.
It only applies to Integer Type columns.

Question 3.5: Index creation for a Table…

Allows ordering alphabetically the records.
Extends the kind of queries we can make on the database.
Improves the queries performance over columns included in indexes.

Quiz 4: DataControl

Verify with this quiz the acquired knowledge about how to use the DataControl control. Notice that the questions may have more than one right answer.

Question 4.1: What project types support the DataControl?

All.
Desktop and iOS.
Desktop and Web.
Only Desktop.

Question 4.2: DataControl is the recommended control to access and navigate SQLite databases from Xojo.

Yes, in all possible scenarios.
No. It is marked as deprecated.
Only for simple database based apps or prototype apps.

Question 4.3: Once we execute the SQL sentence assigned to a DataControl, we get the matching records in a…

DataRecord.
Dictionary.
RecordSet.
RecordControl.

Question 4.4: DataControl offers SQL Injection attacks protection.

Partially.
Yes.
No.

Question 4.5: DataControl offers databinding for all the available Framework controls.

Only for those enumerated in the Xojo documentation.
Mainly with TextField, Label and TextArea.
With all of those implementing the DatabaseBinding Class Interface.

Question 4.6: We can only set the Database name and table used by the DataControl via the Inspector Panel.

Also from code, including the SQL queries.
Yes.

Question 4.7: For a DataControl to be functional, it has to be always visible and placed inside the limits of the Window where it is used.

False.
True.

Quiz 5: Setting the DataControl Source

This quiz lets you verify the acquired knowledge about how to set the data source for a DataControl instance. Notice that the questions may have more than one right answer.

Question 5.1: The DataControl Inspector Panel allows selecting any database we want to work with.

Yes.
Only those available in the local storage.
Only SQLite databases.
Only those previously added to the project.

Question 5.2: The DataControl Table property allows choosing several tables from the selected database.

Yes.
Yes, they are always linked or related each other.
No, we have to manually enter the name of every table.
No.

Question 5.3: The DataControl SQLQuery property only admits the use of simple queries.

No.
Yes.

Question 5.4: What is the function of the Commit property?

Implicitly saves the changes made on the database.
Implicitly saves the changes made on the database, only when the ReadOnly property is set to False.
Sets the option for manually applying the changes on the database.

Quiz 6: DataSource

This quiz lets you verify the acquired knowledge about how to bind or link the data source to the user interface controls. Notice that the questions may have more than one right answer.

Question 6.1: We can set the data source from…

From the Attributes section in the Inspector Panel.
Writing the name of the DataControl instance in the DataSource property.
Dragging the DataControl instance over the control.
Choosing the Control instance from those available in the popup menu from the Inspector Panel.
Only those previously added to the project.

Question 6.2: How to set a data column to a user interface Control?

Write the SQL sentence that gets the data into the DataField field.
Write the name of the columns, separated by comma, into the DataField field.
Write the column name, for the selected DataSource, into the DataField field.
Drag the DataControl over the DataField field in the Inspector Panel.

Question 6.3: The communication between the DataField for the selected control and the column is…

Always bi-directional.
Always from the database to the control.
Always from the control to the database.
It is determined by the ReadOnly property for the associated DataControl instance.

Question 6.4: When we save the information for the Control on the associated DataField…

We need to make sure to convert the data to the expected type for the column in the database table.
We need to execute the RunQuery instruction for the DataControl instance.
The type conversion is implicitly made by the SQLite engine, when possible.

Question 6.5: Is it possible to set a Control DataSource and DataField from code?

No.
Yes, but only if it is the same DataControl.
Yes, but only after creating a new control instance.
Yes.

Quiz 7: Creating, Inserting, Updating and Deleting Records

This quiz lets you verify the acquired knowledge about how to Create, Insert, Update and delete records using the DataControl. Notice that the questions may have more than one right answer.

Question 7.1: The NewRecord method…

Creates a new record in the database table, using the data from the associated fields.
Creates a new record, only if RunQuery is executed afterwards.
Creates a new record, cleaning the available data from the binded user interface controls.

Question 7.2: The Delete method from a DataControl…

Deletes the record designated by the passed index.
Invalidates the current RecordSet.
Deletes all the records from the RecordSet.
Deletes the current record.

Question 7.3: The Insert method from the DataControl.

Inserts a record in the DataControl associated database table.
Keeps the RecordSet pointer in the current record.
Invalidates the current RecordSet.

Question 7.4: The Update method from the DataControl…

Updates the record pointed by the passed index value.
Updates the current record from the RecordSet.
Invalidates the current RecordSet.
Updates the record only if there is valid data in all the binded controls.

Quiz 8: Using Multiple Tables

This quiz lets you verify the acquired knowledge about how to use multiple database tables using several DataControl instances. Notice that the questions may have more than one right answer.

Question 8.1: In order to use multiple tables…

We have to set all of them, comma separated, in the DataControl instance Inspector Panel.
We construct the SQLQuery, involving the required database tables, to get a matching RecordSet.
We can use several DataControl instance, each one associated with a particular database table.
We duplicate the controls associated to the required columns in several windows.

Question 8.2: Can we affect several database tables using the Insert method?

Yes, always that we construct the right SQLQuery for that.
No.
Yes, always that the DataControl instances are placed in the same window.

Question 8.3: Can we delete records on multiple database tables from the same DataControl instance?

Only if we construct the right SQL sentence for the SQLQuery and execute it with RunQuery.
Setting the database tables in the DataSource property of the DataControl instance.
Only if the linked control are common to both database tables.

Quiz 9: RecordSet

This quiz lets you verify the acquired knowledge about the RecordSet associated to a DataControl. Notice that the questions may have more than one right answer.

Question 9.1: A DataControl RecordSet is still valid after…

An Insert operation.
A Delete operation.
A New Record operation.
An Update operation.

Question 9.2: How can we get a fresh RecordSet for a DataControl?

Call the Run method.
Modify the SQLQuery property.
Call the RunQuery method.
Call Invalidate.

Question 9.3: In the correct order, sort the following lines of code to populate a ListBox from a DataControl RecordSet.

While Not DataControl1.RecordSet.EOF
DataControl1.Recordset.MoveNext
if DataControl1.RecordSet <> nil then
Wend
ListBox1.AddRow DataControl1.RecordSet.Field("valor").StringValue
End If

GettingStarted

SQLite Basics

From Xojo Documentation

Contents