Best Practice: Database Connection Objects

When working with databases, we recommend using our database connection objects.

Is there any occasion not to use connection objects?

Advantages:

• Connections are no longer pooled, resulting in safer transactions:
  • Create dedicated connections using db.connect
    Note: If network connectivity to the database is lost a rollback will occur.
• The ability to check a connection to confirm that the database is still available:
  • Use the conn:check method to test a connection

Usage:

1. Create a connection using db.connect
   • Optionally check to see if a connection created previously is still live and create a new connection if needed
2. Do database tasks (insert/update data etc)
3. Use conn:close to close the connection after you have finished
   Note: The Lua garbage collector will eventually close connections, but it best practice to close them yourself.

How the live flag for db.connect affects the execution of database methods in the Iguana editor:

By default the connection handle returned from db.connect is live (live = true).

• If db.connect live = true (the default) connection methods will run in the editor (based on their default or live setting).
• If db.connect live = false it means that all database operations are always non-live.

For more information, see the help documentation for db.connect.

Examples

1. Download and import this project into a To/From Translator channel: db_connection.zip
2. Check to see if the connection is valid and create a new connection if needed.
3. Merge, update and read data.
4. Close the connection.

This is how to copy a connection… Easy!

See also:

• db – interacting with databases for information on new and legacy database functions
• db conn – new database methods
• db – old style database functions

Is there ever a case where it is best NOT to use connection objects? [top]

As long as you are using Iguana 5.5 or above, the answer is no.

For new code, we recommend using connection objects. If you are migrating from an older version of Iguana (before 5.5), there is no need to change but it strongly recommended that you update your code as soon as possible.