Page Comparison

db.tables and conn:merge

...

what are db.tables and conn:merge. how are VDBs used.

A call to conn:merge{} will either insert new rows in a database table or update existing rows. This depends on how the VDB was configured, see Key Fields and Examples.

...

In addition to executing SQL statements directly, Iguana offers another method for writing to databases using a Database Schema File (*.vdb), db.tables{} and the conn:merge{} function.

This method allows you to easily map data to multiple rows in a single Lua table and then perform a bulk insert or update without writing complex and long SQL statements. Data INSERTs and UPDATEs are simplified as it automatically handles the logic for determining whether to insert new rows or update existing ones, depending on the configuration of key columns in the VDB.

• If key columns are defined, Iguana performs a SELECT

...

• query to check if a row with matching key values exists. If a matching row

...

• is found, an UPDATE operation is performed

...

• ; otherwise, an INSERT is performed.

...

separate page on bulk inserts?

1. Bulk insert is an optional bulk_insert boolean flag for conn:merge{} that can be used with Microsoft SQL Server and MySQL. It ignores the key columns defined in the VMD files and always does an INSERT using the bulk insert syntax. If your table has a large number of rows, this can improve performance.

   Note: Bulk insert does inserts only, if you need to do updates you must handle these separately. You will get an error if you try to merge a duplicate record.

db:merge does not support spaces in column names

...

• If no key columns are defined, new rows are always inserted. This is useful when you don’t want to overwrite existing data.

We see a great example of this in the introductory article: Understanding Databases in Iguana.

...

Expand
title STEP 1: Create a Database Schema File
A VDB acts as an intermediary table mirroring your database table structure. It is used to define how data is structured and how it maps to actual database columns. SeeCreating a VDB for building this simple Patients table inside report.vdb. Image Added In this case, we have not defined any keys for the database table - seeKey Fields and Examples. Info conn:merge{} does not support spaces in column names. If your database schema contains spaces in column names, you’ll need to modify the column names to remove or replace spaces before using conn:merge{}.

local T = db.tables{vdb='report.vdb, name ='Patients'} 

-- map vdb table values
T.Patients[1].FirstName = 'John' 
T.Patients[1].LastName = 'Doe' 
T.Patients[1].ID = 1

-- Insert or update rows based on key column configuration 
conn:merge{data=T, live=true}

function doMerge(conn, T) 
  local R = conn:merge{data=T, live=true}
end 

function main()
  -- create empty table
  local T = db.tables{vdb='report.vdb, name ='Patients'} 
  
  -- map vdb table values
  T.Patients[1].FirstName = 'John' 
  T.Patients[1].LastName = 'Doe' 
  T.Patients[1].ID = 1
  
  -- call merge function and handle response
  local Success, R = pcall(doMerge, conn, T) 
  if not Success then 
    iguana.log('Error during merge: ', R.message) 
  end
end