This post was originally written for Iguana 5 so it contains version 5 screenshots, and may contain out of date references.

You can use transactions and conn:begin{}, conn:execute{} and conn:commit{} to insert data into a database in a specific order, also see out Database section.

You will notice that the transactions are not technically required for the ordering. So why do we need transactions?

• If you want to insert both the Patient and Kin together, or neither, then you need a transaction
• We need to insert the Patient first so we can get the Patient Id to use with Kin
• You run the risk of inserting the Patient without Kin, this is probably ok
• Using @@IDENTITY outside of a transaction does not guarantee the correct Patient Id

Technically we could write this (or similar) code without transactions, but it would make identifying the Patient difficult. The @@IDENTITY variable cannot be used. The @@IDENTITY variable returns the latest number is in an auto-number field, when used outside a transaction it is possible that another insert occurs before the Kin insert, therefore giving the wrong Id. We could try a query using the Patient names, but these are not guaranteed unique (there may be more than one “Fred Smith”). If you have a unique Patient identifier (like a social security number) you can use this to identify the correct Patient Id (no such data is shown in the example data so we might be out of luck in this case). It may be possible to look at other information, like address, phone etc. In the worst case human intervention is needed. So as you can see using a transaction just makes it much easier.

And here is the code:

local conn = db.connect{
   api=db.SQL_SERVER, 
   name='Test', 
   user='root', 
   password='password', 
   live = true
}

function main() 
   conn:begin{}
   conn:execute{
      sql=[[INSERT INTO Patient (GivenName, LastName) 
               VALUES('Fred', 'Smith')]],
      live=true
   }
   local ID=conn:execute{sql='SELECT @@IDENTITY', live=true}
   conn:execute{
      sql=[[INSERT INTO Kin (FirstName, LastName, PatientId) 
               VALUES('Fred', 'Smith', ]]..ID[1].Column_1..")", 
      live=true
   }
   conn:commit{}  
end