Page Comparison

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.

...

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.

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{}`.

Expand

title	STEP 2: Use db.tables to create an empty set of tables to populate

Now we can use the db.tables to generate an empty set of tables based on the provided VDB and map our data to the database tables.

Code Block

language	lua

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

Expand

title	STEP 3: Use conn:merge{} to insert new rows or update existing rows in your database

A call to conn:merge{} allows you to either insert new rows into a database table or update existing rows depending on the keys configured in the VDB.

Code Block

language	lua

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

With some databases, you can also perform a Bulk Insert on Merge.

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{}.

Expand

title	Error Handling Merges

The merge process all happens within a single database transaction. If it fails, the transaction is rolled back and nothing is altered in the database. This behavior can be optionally turned off if you pass in transaction=false as an argument.

Just like with conn:execute{}, you can use pcall() to catch and gracefully handle any errors that arise from conn:merge{} operations:

Code Block

language	lua

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

...

Versions Compared

Old Version 3

New Version 4

Key