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 various methods (ie. conn:query{}, conn:execute{}, conn:merge{}, etc.) on the connection object.

Creating a persistent database connection by making the db.connect{} call outside of main() is recommended to optimize performance. If not using a persistent database connection you must explicitly close() the connection.
Avoid hardcoding the database connection parameters. Use custom fields or environment variables for configurations.

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
- Port: If the MySQL server is not using the default port 3306, it can be specified in 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>', -- ODBC DSN
    user='<your_login>',
    password='<secret>',
    use_unicode = true,
    live = true
  }
 
  conn:execute{sql='SELECT * FROM <your table>', live=true}
end

Oracle - Oracle OCI (with TNS Alias, Easy Connect, or ODBC)

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', -- ODBC DSN
    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>', -- ODBC DSN
    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