Ordering Database Inserts
How to use transactions and conn:begin{}, conn:execute{} and conn:commit{} to insert data into a database in a specific order.
local conn = db.connect{
api=db.MY_SQL,
name='Test',
user='root',
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
This code uses a Transaction to insert a Patient record, then retrieve the PatientId and insert a matching Kin record for the new Patient. The @@IDENTITY variable is used to retrieve the PatientId last inserted Patient. If we did not use a Transaction another Patient could be inserted before we select @@IDENTITY, and we could add the Kin record to the wrong Patient (if this is confusing talk to a DBA or contact us for further explanation).
Note: The @@IDENTITY variable works for Microsoft SQL Server, MySQL, SYBASE and Microsoft Access. Other databases use different methods of retrieving Primary Key values so will need to adapt the code.
How to use the code:
- Create/use a SQL Server (or other) database
- The database credentials must match those used in the code
- Create a Patient table with fields PatientId (Identity PK), FirstName, LastName
- Paste the code into a component script to try it out