A customizable Lua database merge method

Introduction

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

If you have feedback or questions please contact us at support@interfaceware.com or post in our Community Forums. You may also want to look at our Database section.

Task [top]

Create a customized database merge process.

For example you can modify the code to accept spaces in table and field names.

Implementation [top]

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.

How it works [top]

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.

Some implementation notes:

More information [top]