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.

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.

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

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:

• The code works efficiently by building up a table of string fragments which are concatenated together at the end for efficiency.
• The error checking code uses the (optional) “level” argument for the Lua error() function to raise the error at the point the function is called, rather than within the function itself.

Code

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