Creating a SQLite database

In this article, we will show you how easy it is to write a script that will automatically create its own supporting databases and tables. This feature of Iguana is made possible because of SQLite.

Before You Start [top]

Not really sure what SQLite is? Read this section to familiarize yourself with some basic knowledge.

What is SQLite?

Some quotes from the sqlite.org:

“SQLite is a in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is currently found in more applications than we can count…”

“SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.”

How do we use SQLite with Iguana Translator?

  • SQLite is embedded in Iguana Translator so you do not need to install it
  • SQLite reads and writes directly to ordinary disk files
  • SQLite will create a database file if it does not already exist

Additional Information

This article covers the basic concepts using the sample code found at the bottom of this page. If you want to study this topic in more depth, you can also do the following:

How It Works [top]

SQLite’s killer feature is that it will automatically create a new database file (if one does not already exist). The first time your script tries to access a database, SQLite will check to see if the file exists. If it doesn’t, SQLite will automatically create it (using the same name that your script is trying to access). Bottom line? You don’t need to do anything to create a database. Simply write your code as if the database already exists.

To demonstrate this, check out this snippet of code:

Note: Because we did not specify a directory path for the SQLITE_DB, it defaults to the Iguana install directory (more on this later).

Notice that this script only has a single call to db.connect(). Because Iguana already has SQLite embedded within it, this is all we need in order to create the database! Any attempt to access the database will work in the same way, regardless of the function used.

Need proof? Before we ran this code snippet, notice that the demo.sqlite file does not exist in the Iguana install directory:

After running the code, notice that the demo.sqlite file has magically appeared:

Examples

Let’s examine some example code that “creates” a database and tables, and then inserts some data. As we walk through the code, notice the following:

  1. Because it does not already exist, the call to db.connect() creates the database file.
  2. Because it does not already exist, the CreateVMDTables() function creates the tables.

Note: You can generate tables (step 2) for any database, but only SQLite will create the database for you (step 1).

Tip: Notice that we chose to use the default directory (Iguana install) for the database and VMD files. You can change this using the SQL_DB and VMD_FILE variables at the start of your code. For example, to use the “demo.sqlite” database in “D:\temp”, simply change the SQL_DB variable:

In the following example, the code uses db conn database methods to perform our calls. Both conn:query() and conn:execute() take an optional second parameter that sets the live execution status. We have set conn:execute{live=true} because it defaults to false (query defaults to true). The dialogs on the right show the results of the data we inserted:

In the next example, the code generates the tables in a VMD file:

Note: The SQL produced below is tailored to SQLite. If you want to generate SQL for a different database, you will need to modify the code.

Why did we only create one table at a time?

The code for CreateVMDTables() only generates a single table at a time. This is because SQLite does not support query batches, so it is only possible to run a single query at a time.

The initial code attempted to create both tables using a single call to conn:execute(); unfortunately this did not work and only the patient table was created.

The solution was simple: make separate calls to conn:execute(), one to create each table.

Note: conn:execute() will successfully run batches of queries against most databases (other than SQLite).

Troubleshooting

Running into problems when working with SQLite? Here are some possible causes:

  1. This error means the demo.vmd file was not found in the Iguana install directory:If it is missing, simply download demo.vmd and copy it into your Iguana install directory.

    Note: It is installed with Iguana so it should be there already.

  2. When customizing the code, you must use a VMD saved in XML format rather than the legacy VMD format. If you get an error similar to to the following, you are probably using a legacy VMD. You will need to change it to XML format.To change the format, simply open the VMD in Chameleon and save it as an “XML Message Definition File”:
  3. If you need to delete the test.sqlite database while testing your code, you must stop the “iNTERFACEWARE Iguana” service. You may also need to close SQLite command line tool or SQLite Database Browser (if you are using either of them to inspect the database file).

Tip: If you are using SQLite to access an existing database file (and you are not generating brand new tables), you might encounter a few hiccups due to SQLite’s automatic capabilities.

Specifically, if you accidentally use the incorrect database name in your code, SQLite will create a new database with no tables. Then all your queries will give errors indicating that tables that should exist do not exist. This error can be very puzzling the first time you see it, because other databases will give an error that the database does not exist (or is not available).

Luckily, the solution is simple: make sure your code is pointing to the correct database file (the one that contains your tables).

Sample Code

If you want to play around with databases and tables that are automatically created with SQLite, simply create a channel with a To/From Translator component and paste the Create a SQLite database and tables code from our repository into the Translator script. This code is “plug and play” and should run immediately without modification.

What’s Next? [top]

We have shown shown you how easy it is to automatically create a database and tables using SQLite. The next step is to modify the code so that you can create your own database and tables!

For more help, check out these examples that also use SQLite:

Please contact support at support@interfaceware.com if you need more help.

Solving the table creation issue [top]

The issue that arose is that the initial version of the code only created the patient table. The kin table was not created.

The initial code attempted to create both tables using a single call to conn:execute(); unfortunately this did not work and only the patient table was created.

Note: This is issue is specific to SQLite.

SQLite does not support query batches, so it is only possible to run a single query at a time.

Note: conn:execute() will successfully run batches of queries against most databases (other than SQLite).

The reason for was that initial code for CreateVMDTables() generated a single script to create both tables (see the picture below):

This script worked perfectly in the SQLite command line tool or SQLite Database Browser, but when I ran in it Iguana it only created the patient table.

This is because conn:execute() can only run a single query at a time, and stops when it reaches the first “;” in the script:

The solution was to change the code to run a separate script for each table: