...
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. Methods exist to create local database tables, merge them with external databases, and perform queries on external databases
| 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 |
|---|
| 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 |
|---|
| 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 |
|---|
| 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 |
|
| Expand |
|---|
|
Connect to PostgreSQL with ODBC: Set up the ODBC data source: PostgreSQL ODBC Setup Use a component to connect to the database:
| Code Block |
|---|
| 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 |
|---|
| 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 |
|---|
| 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 |
|---|
| 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 |
|---|
|
Connect to Snowflake with ODBC: Set up the ODBC data source: Snowflake ODBC Setup Use a component to connect to the database:
| Code Block |
|---|
| 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 |
|---|
|
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 |
|---|
| function main()
local conn = db.connect{
api=db.SQLITE,
name='<databaseName.db>',
live = true
}
conn:execute{sql='SELECT * FROM <your table>', live=true}
end |
|
...