Connecting to Databases

The process of connecting to a database involves two major steps:

Setup the appropriate database drivers on the server. Iguana supports three database APIs to connect to various databases: Open Database Connectivity (ODBC), MySQL API, and Oracle OCI.
Use a connection object to connect to the database. We strongly recommend using the db.connect function to create the connection object so that you can use the methods on the connection object. Methods exist to create local database tables, merge them with external databases, and perform queries on external databases.

Select the drop downs below for how to setup the required database drivers and connect IguanaX:

Microsoft SQL Server - ODBC

Connect to MS SQL Server with ODBC:

Set up the ODBC data source: Microsoft SQL Server ODBC Setup
Use a component to connect to the database:

function main()
      local conn = db.connect{   
      api=db.SQL_SERVER, 
      name='<your_odbc_server_name>', -- ODBC DSN
      user='',      -- use empty string for integrated security
      password='',  -- use empty string for integrated security
      use_unicode = true,
      live = true
   }
 
   conn:execute{sql='SELECT * FROM <your table>', live=true}
end

MySQL - ODBC or libmysql

Iguana can connect with MySQL in two methods:

Connect to MySQL with ODBC:

Set up the ODBC data source: MySQL ODBC Setup
Use a component to connect to the database:

function main()
      local conn = db.connect{   
      api=db.MY_SQL_ODBC, 
      name='<your_odbc_server_name>', -- ODBC DSN
      user='<your_login>',
      password='<secret>',
      use_unicode = true,
      live = true
   }
 
   conn:execute{sql='SELECT * FROM <your table>', live=true}
end

Connect to MySQL with libmysql:

Install the libmysql shared library: MySQL libmysql Setup
Use a component to connect to the database:

function main()
      local conn = db.connect{   
      api=db.MY_SQL, 
      name='<mydatabase@server_name>',
      user='<your_login>', 
      password='<secret>',
      use_unicode = true,
      live = true
   }
 
   conn:execute{sql='SELECT * FROM <your table>', live=true}
end

The name of the host can be specified as any of the following:
- The machine name: mydatabase@server_name
- The server name: mydatabase@server_name
- The IP address: mydatabase@10.10.10.10
If the MySQL server is using a port other the standard port of 3306, you can specify it as part of the name parameter: mydatabase@server_name:1111

PostgreSQL - ODBC

Connect to PostgreSQL with ODBC:

Set up the ODBC data source: PostgreSQL ODBC Setup
Use a component to connect to the database:

function main()
      local conn = db.connect{   
      api=db.POSTGRES,
      name='<your_odbc_server_name>',
      user='<your_login>',
      password='<secret>',
      use_unicode = true,
      live = true
   }
 
   conn:execute{sql='SELECT * FROM <your table>', live=true}
end

Oracle - ODBC, Oracle OCI, or Easy Connect

Connect to Oracle with OCI (TNS Alias):

Set up the Oracle OCI drivers and create a TNS Alias: Oracle TNS Alias OCI Configuration
Use a component to connect to the database:

function main()
      local conn = db.connect{   
      api=db.ORACLE_OCI, 
      name='<Oracle OCI name>', -- Oracle tns_alias
      user='<your_login>', 
      password='<secret>',
      use_unicode = true,
      live = true
   }
 
   conn:execute{sql='SELECT * FROM <your table>', live=true}
end

Connect to Oracle with Easy Connect (EZConnect):

Set up the Oracle OCI drivers and EZConnect String: Oracle OCI Easy Connect String (EZCONNECT)
Use a component to connect to the database:

function main()
      local conn = db.connect{   
      api=db.ORACLE_OCI, 
      name='<Oracle EZCONNECT string>', --[//]host[:port][/service_name]
      user='<your_login>', 
      password='<secret>',
      use_unicode = true,
      live = true
   }
 
   conn:execute{sql='SELECT * FROM <your table>', live=true}
end

Connect with Oracle with ODBC:

Set up the Oracle OCI and ODBC drivers: Oracle ODBC Setup
Use a component to connect to the database:

function main()
      local conn = db.connect{   
      api=db.ORACLE_ODBC, 
      name='your_odbc_server_name',
      user='your_login',
      password='secret',
      use_unicode = true,
      live = true
   }
 
   conn:execute{sql='SELECT * FROM <your table>', live=true}
end

Snowflake - ODBC

Connect to Snowflake with ODBC:

Set up the ODBC data source: Snowflake ODBC Setup
Use a component to connect to the database:

function main()  
      local conn = db.connect{   
      api=db.ACCESS, -- "ODBC Cheat" - this (or another ODBC connection type) will work with most ODBC connections
      name='<Snowflake>',
      user='<Username>', 
      password='<Password>',
      use_unicode = true,
      live = true
   }
 
   conn:execute{sql='SELECT * FROM <your table>', live=true}
end

SQLite

The great thing about SQLite is that it is low maintenance. It is a “server-less” database, you just run a query and if there is no database it immediately creates the file for you. This means you can write database dependent code in Iguana for SQLite that will just work – if there is no database it will be created automatically.

function main()
      local conn = db.connect{   
      api=db.SQLITE, 
      name='<databaseName.db>',
      live = true
   }
 
   conn:execute{sql='SELECT * FROM <your table>', live=true}
end