Database Connection

• Preparation
• How it works
• Testing The Code
• Adapting the code for your own needs
• Sample Code

Our first example shows how we can retry an ODBC query to an MySQL database. If an error occurs we simply retry the query.

Preparation [top]

You will need to have a MySQL ODBC database connection for use with the sample code.

If you match the following credentials, as used in the code, it should work immediately:

Alternatively you could change the db.connect() parameters to connect to a different database.

The database will also need to contain a Patient table so this query from DoInsert() will work:

If you do not have a Patient table then substitute another SQL query that works against your database.

How it works [top]

We apply the retry module by performing the following steps:

1. Add require'retry' to the shared module source code, as shown below.
2. Invoke retry.call() with a table containing these entries:
   • func = The function you wish to call
   • arg1/2/3…N = Multiple arguments to pass in to the function
   • retry (optional) = The maximum number of failed retries
   • pause (optional) = The pause between each retry attempt (in seconds)
   • funcname (optional) = The name of the function ( informational for errors and logging)
3. Place the call to db.connect() in the DoInsert() function so it runs every time Iguana polls

   Warning: If db.connect() is outside DoInsert() it will raise an error before retry.call() is called, and therefore the retry logic will never be invoked.

If the MySQL database is unavailable then you will get a connection error and retry message. We stopped the MySQL database service/daemon to simulate a connection error:

Testing The Code [top]

We ran the code in an From Translator component, and then verified that it worked as expected by stopping and starting the MySQL database. Below you can see how the channel dashboard light changes, and how the errors are logged.

The orange light comes on when an error occurs:

The green light comes on when we recover from the error:

Here is how the errors look in the Iguana Logs:

function myError(Success, ErrMsgOrReturnCode)
   local funcSuccess
   if Success then
      -- successfully read the data
      funcSuccess = true -- don't retry
   else
      -- these are MySQL error codes - they will be different for other databases
      if ErrMsgOrReturnCode.code == 2002 or ErrMsgOrReturnCode.code == 2006 then
         -- retry *only* for failed connection (error 2002 or 2006)
         iguana.logInfo('Retrying DB connection: '..tostring(ErrMsgOrReturnCode))
         funcSuccess = false -- retry
      else
         -- then raise error for all other DB issue (error ~= 2002 or 2006)
         error(tostring(ErrMsgOrReturnCode))
      end
   end
   return funcSuccess
end

Adapting the code for your own needs [top]

You can easily adapt the code we supplied to meet your needs. If you are retrying a database (a very common scenario) then you can just add your own database code to the DoInsert() function. If you want to retry another operation then replace DoInsert() with your own function. For example: to access a RESTful web service you could create a DoHttpGet() function using net.http.get(), using our net interface module.

Sample Code [top]

Import the Retry_Database_From_Translator.zip project into a From Translator component, it contains all the necessary code.

Otherwise you can get the code for How to retry a database connection from our repository.
Note: If you don’t already have the retry module you can get it from our repository server.