Understanding Databases in Iguana

In addition to allowing you to work with databases like Oracle, SQL Server and MySQL etc.

Iguana comes with built in support for the SQLlite database format which is a good choice for learning before you use your preferred database.

This can be very helpful for simple requirements when you are operating Iguana in an environment which does not make it easy to operate other databases.

Note: It is recommended that column names do not contain spaces (ex. First Name). If column names have spaces, db.merge will not be able to be used and instead will have to write and execute SQL statements from the Translator.

If you want to use merge with spaces in column names you can use the custom merge module (you will need to modify it to use the correct column name delimiters for your target DB) https://help.interfaceware.com/v6/customize-custom_merge-lua-for-table-and-field-names-with-spaces

Here are steps to use the SQLite database in IguanaX GUI:

a) Edit the VDB with the built in GUI:

b) Add the Table Definition:

c) Edit the table to look like below and Save:

Note: Spaces in column names (ex. First Name) is not recommended. If column names contain spaces, db.merge can not be used and SQL statements will need to be written and executed instead.

d) Click Done and then click Export:

e) Add the name ‘patients.db’ to the Datasource and click Execute:

In Step 2, we had created this table below:

If you noticed we did not use the Key field on the right side of the new parameters.
If you would like to learn more about using the Key fields and the importance of them please have a read of this page: Key Fields and Examples

local D = db.connect{api=db.SQLITE, name='patients.db'}
local R = D:query{sql='SELECT * FROM Patients LIMIT 50;'}

NOTE: This will only display an empty table, to populate the table continue the next steps.

Next Author Info

This is part of your product