Page Comparison

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.

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 fieldsorenvironment variablesfor configurations.

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

Expand

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

Code Block

language	lua

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

Expand

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

Code Block

language	lua

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:

Code Block

language	lua

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

Expand

title	PostgreSQL - ODBC

Connect to PostgreSQL with ODBC:

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

Code Block

language	lua

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

Expand

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

Code Block

language	lua

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:

Code Block

language	lua

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:

Code Block

language	lua

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

Expand

title	Snowflake - ODBC

Connect to Snowflake with ODBC:

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

Code Block

language	lua

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

Expand

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

Code Block

language	lua

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

Versions Compared

Old Version 9

New Version Current

Key