A customizable Lua database merge method

The Iguana Translator comes with a built-in database conn:merge() method to update and/or insert rows into a database table. For Iguana 5.5.1 and up, we have also put together a Lua implementation called customMerge(), found in custom_merge.lua, that you can customize to meet your needs. It’s all set up for the Translator’s built-in help feature, so even though it is a standalone Lua module, you still get documentation right inside the Translator.

Reasons to use customMerge():

  • The “merge_null” argument allows you to specify whether NULL variables are ignored or merged in
  • You can customize the code to add special handling for certain data types
  • You can change the logic that determines when to insert or update
  • You can add other operations as required

This implementation of customMerge() currently works with:

  • SQL Server ODBC
  • Oracle ODBC
  • Oracle OCI
  • MySQL
  • SQLite

Tip: For Iguana versions before 5.5.1

The customMerge() method relies on database connection objects, introduced in Iguana 5.5. If instead you have Iguana 5.0, there is an older module called db_merge.lua that, when loaded, replaces the built-in db.merge method with a Lua implementation that you can customize to meet your needs.

How It Works

At its core customMerge() does a SELECT call on the target table using its Primary Key(s). If a matching row is found it is updated, if no row is found a new row is inserted. This algorithm uses the same logic as the built-in conn:merge{} function, with one important difference: The addition of the merge_null parameter, which controls whether NULLs are written to the database.

How to use it:

  1. Download custom_merge.lua.
  2. Add it to your shared modules in any Translator project.
  3. Make sure to require 'custom_merge' at the top of your script.
  4. Use the Translator auto-completion to understand the parameters.
  5. Call the customMerge() method anywhere you would call the regular merge, either on database connection objects as conn:customMerge() or by itself as db.customMerge().
  6. Dive into the customMerge() code using the Translator annotations to help you make the changes you need.

Some implementation notes:

Code

Download the latest “custom_merge” module from our code repository: custom_merge.lua.

If you have any feedback or questions please contact us at support@interfaceware.com, or post in our Community Forums.

Leave A Comment?