Database Fundamentals

Best Practice: Database Connection Objects

Contents

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

Is there any occasion not to use connection objects?

Note: These objects are more reliable and easier to use than the older database functions (prior to Iguana version 5.5).

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:

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.

Note: The only time it makes sense is when you are migrating from an older version of Iguana (before 5.5), as all the legacy database functions still work. So you do not need to immediately change to using connection objects.

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.

Continue: db conn query{}: Reading from a database