Connecting to Databases

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

1. 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.

2. 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.

Tip

• 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:

1. Set up the ODBC data source: Microsoft SQL Server ODBC Setup

2. 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:

1. Set up the ODBC data source: MySQL ODBC Setup

2. 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:

1. Install the libmysql shared library: MySQL libmysql Setup

2. 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:

1. Set up the ODBC data source: PostgreSQL ODBC Setup

2. 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):

1. Set up the Oracle OCI drivers and create a TNS Alias: Oracle TNS Alias OCI Configuration

2. 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):

1. Set up the Oracle OCI drivers and EZConnect String: Oracle OCI Easy Connect String (EZCONNECT)

2. 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:

1. Set up the Oracle OCI and ODBC drivers: Oracle ODBC Setup

2. 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:

1. Set up the ODBC data source: Snowflake ODBC Setup

2. 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