Cleanup database access to always use transactions and properly close/rollback on exceptions

Note: Scripts are now called within transactions, so they cannot contain transactions themselves

1 files changed, 237 insertions, 186 deletions

diff --git a/provisioning-model.lua b/provisioning-model.lua
index ebec3da..0a86e97 100644
--- a/provisioning-model.lua
+++ b/provisioning-model.lua
@@ -36,15 +36,15 @@ local functions
 -- LOCAL FUNCTIONS
 
 -- This function is used by scripts, do not change prototype
-local runsqlcommand = function(sql, transaction)
+local runsqlcommand = function(sql)
 	mymodule.logevent(sql)
-	return provdb.runsqlcommand(sql, transaction)
+	return provdb.runsqlcommand(sql, true)
 end
 
 -- This function is used by scripts, do not change prototype
-local getselectresponse = function(sql, transaction)
+local getselectresponse = function(sql)
 	mymodule.logevent(sql)
-	return provdb.getselectresponse(sql, transaction)
+	return provdb.getselectresponse(sql, true)
 end
 
 local function assert (v, m)
@@ -117,16 +117,42 @@ local databaseconnect = function()
 			-- Let's create all the tables from the start
 			for n,v in pairs(provdb.table_creation_scripts) do
 				if not string.match(n, "^_") then
-					runsqlcommand("SELECT * FROM "..provdb.escape(n).." LIMIT 1")
+					provdb.runsqlcommand("SELECT * FROM "..provdb.escape(n).." LIMIT 1")
 				end
 			end
 		else
 			assert(res, err)
 		end
 	end
+	if result then
+		local sql = "BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE"
+		mymodule.logevent(sql)
+		provdb.runsqlcommand(sql)
+	end
 	return result
 end
 
+local databasedisconnect = function()
+	local sql = "COMMIT"
+	mymodule.logevent(sql)
+	provdb.runsqlcommand(sql)
+	provdb.databasedisconnect()
+end
+
+local handlesqlexception = function(connected)
+	-- Can't trust cache, so clear it
+	saved_devices = nil
+	saved_device_params = nil
+	if connected then
+		local sql = "ROLLBACK"
+		mymodule.logevent(sql)
+		pcall(function() con:execute(sql) end)
+		provdb.databasedisconnect()
+	else
+		error(err)
+	end
+end
+
 local validateparam
 validateparam = function(p, allowdefault)
 	if p.type == "group" then
@@ -280,13 +306,14 @@ end
 
 -- This function is used by scripts, do not change prototype
 local get_device = function(device_id, create)
+	local connected
 	local retval = {}
 	retval.device_id = cfe({value=device_id or "", label="Device ID", seq=1})
 	retval.classes = cfe({type="group", value={}, label="Classes", seq=2})
 	local errtxt
 	local res, err = pcall(function()
 		local classes={}
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		if not create and device_id and device_id ~= "" then
 			-- Get the device-to-class mappings
 			local sql = "SELECT class_id FROM devices_to_classes WHERE device_id='"..provdb.escape(device_id).."'"
@@ -308,26 +335,28 @@ local get_device = function(device_id, create)
 				class.value = c.class_id
 			end
 		end
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		errtxt = err
 	end
 
 	-- Save the device for later use
 	local output = cfe({ type="group", value=retval, label="Provisioning Device", errtxt=errtxt })
-	if not create and device_id and device_id ~= "" then saved_devices[device_id] = duplicatestructure(output) end
+	if not create and device_id and device_id ~= "" and not errtxt then saved_devices[device_id] = duplicatestructure(output) end
 	return output
 end
 
 -- This function is used by scripts, do not change prototype
 local get_device_params = function(device_id, editable)
+	local connected
 	local retval = {}
 	retval.device_id = cfe({value=device_id or "", label="Device ID", seq=0})
 	local errtxt = "Cannot find device"
 	if device_id and device_id ~= "" then
 		local res, err = pcall(function()
-			local connected = databaseconnect()
+			connected = databaseconnect()
 			-- First, just check to see if device_id exists
 			local sql = "SELECT * FROM devices_to_classes WHERE device_id='"..provdb.escape(device_id).."' LIMIT 1"
 			local tmp = getselectresponse(sql)
@@ -382,9 +411,10 @@ local get_device_params = function(device_id, editable)
 				-- Even more finally, add in a flag to show if group defaults have been overridden
 				retval.groupdefaultoverride = cfe({ type="boolean", value=checkgroupdefaultoverride(device_id), label="Group defaults have been overridden", readonly=true })
 			end
-			if connected then provdb.databasedisconnect() end
+			if connected then databasedisconnect() end
 		end)
 		if not res and err then
+			handlesqlexception(connected)
 			errtxt = err
 		end
 	end
@@ -396,13 +426,14 @@ local get_device_params = function(device_id, editable)
 end
 
 -- This function is used by scripts, do not change prototype
-local set_device_params = function(params, editable, intransaction)
+local set_device_params = function(params, editable)
+	local connected
 	-- Validate the settings
 	local success = validateparam(params)
 	local errtxt
 	if success then
         	local res, err = pcall(function()
-			local connected = databaseconnect()
+			connected = databaseconnect()
 			success = validateparamcoded(params)
 			local sql = "SELECT * FROM devices_to_classes WHERE device_id='"..provdb.escape(params.value.device_id.value).."' LIMIT 1"
 			local tmp = getselectresponse(sql)
@@ -413,12 +444,11 @@ local set_device_params = function(params, editable, intransaction)
 			if success then
 				if not saved_device_params[params.value.device_id.value] then get_device_params(params.value.device_id.value) end
 
-				local sql = "BEGIN TRANSACTION"
-				if not intransaction then runsqlcommand(sql) end
+				local sql
 				if not editable then
 					-- Delete all values for this device (can't do this if only updating editable)
 					sql = "DELETE FROM provisioning_values WHERE device_id='"..provdb.escape(params.value.device_id.value).."'"
-					runsqlcommand(sql, true)
+					runsqlcommand(sql)
 				end
 				-- Loop through the groups and params
 				for group,v in pairs(params.value) do
@@ -426,18 +456,15 @@ local set_device_params = function(params, editable, intransaction)
 						for name,param in pairs(v.value) do
 							if editable then
 								sql = "DELETE FROM provisioning_values WHERE device_id='"..provdb.escape(params.value.device_id.value).."' AND group_name='"..provdb.escape(group).."' AND param_id='"..provdb.escape(param.param_id).."'"
-								runsqlcommand(sql, true)
+								runsqlcommand(sql)
 							end
 							if param.value ~= param.default then
 								sql = "INSERT INTO provisioning_values VALUES('"..provdb.escape(params.value.device_id.value).."', '"..provdb.escape(group).."', '"..provdb.escape(param.param_id).."', '"..provdb.escape(tostring(param.value)).."')"
-								runsqlcommand(sql, true)
+								runsqlcommand(sql)
 							end
 						end
 					end
 				end
-
-				sql = "COMMIT"
-				if not intransaction then runsqlcommand(sql) end
 				
 				local tmp = saved_device_params[params.value.device_id.value]
 				local p = params
@@ -448,10 +475,10 @@ local set_device_params = function(params, editable, intransaction)
 				end
 				callscript(updatedeviceparamsscriptfile, p, tmp)
 			end
-			if connected then provdb.databasedisconnect() end
+			if connected then databasedisconnect() end
 		end)
 		if not res and err then
-			pcall(function() con:execute("ROLLBACK") end)
+			handlesqlexception(connected)
 			success = false
 			errtxt = err
 		end
@@ -463,11 +490,12 @@ local set_device_params = function(params, editable, intransaction)
 end
 
 local get_device_values = function(device_id)
+	local connected
 	local retval = {}
 	local errtxt
 	if device_id and device_id ~= "" then
 		local res, err = pcall(function()
-			local connected = databaseconnect()
+			connected = databaseconnect()
 			local sql = "SELECT g.name AS group, p.name, p.type, CASE WHEN v.value IS NOT NULL THEN v.value WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END AS value "..
 				"FROM (devices_to_classes d2t JOIN provisioning_classes t USING(class_id) JOIN classes_to_param_groups t2g USING(class_id) JOIN provisioning_groups g USING(group_id) "..
 					"JOIN param_groups_to_params g2p USING(group_id) JOIN provisioning_params p USING(param_id)) LEFT JOIN provisioning_values v ON(d2t.device_id=v.device_id AND p.param_id=v.param_id AND g.name=v.group_name ) "..
@@ -483,9 +511,10 @@ local get_device_values = function(device_id)
 				end
 				retval[p.group][p.name] = p.value
 			end
-			if connected then provdb.databasedisconnect() end
+			if connected then databasedisconnect() end
 		end)
 		if not res and err then
+			handlesqlexception(connected)
 			errtxt = err
 		end
 	else
@@ -497,6 +526,7 @@ end
 
 -- These are the functions that may be called from within loaded Lua code
 -- The functions must be added after they're declared
+-- All scripts are called within DB transactions
 functions = {
 	getselectresponse=getselectresponse,
 	runsqlcommand=runsqlcommand,
@@ -510,16 +540,18 @@ functions = {
 -- PUBLIC FUNCTIONS
 
 mymodule.list_templates = function()
+	local connected
 	local retval = {}
 	local errtxt
 	-- Get the templates from the DB
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		local sql = "SELECT value AS filename, label, seq FROM provisioning_options WHERE param_id = (SELECT param_id FROM provisioning_params WHERE name = 'template') ORDER BY seq ASC, label ASC, value ASC"
 		retval = getselectresponse(sql)
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		errtxt = err
 	end
 	local reversed = {}
@@ -547,6 +579,7 @@ mymodule.list_templates = function()
 end
 
 mymodule.get_template = function(self, clientdata)
+	local connected
 	clientdata = clientdata or {}
 	local filename = clientdata.filename
 	local retval = {}
@@ -559,7 +592,7 @@ mymodule.get_template = function(self, clientdata)
 	local errtxt
 	if filename and filename ~= "" then
 	        local res, err = pcall(function()
-			local connected = databaseconnect()
+			connected = databaseconnect()
 			local sql = "SELECT * FROM provisioning_options WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name = 'template') AND value='"..provdb.escape(filename).."' ORDER BY seq ASC, label ASC, value ASC"
 			local tmp = getselectresponse(sql)
 			if tmp and #tmp > 0 then
@@ -571,9 +604,10 @@ mymodule.get_template = function(self, clientdata)
 					end
 				end
 			end
-			if connected then provdb.databasedisconnect() end
+			if connected then databasedisconnect() end
 		end)
 		if not res and err then
+			handlesqlexception(connected)
 			errtxt = err
 		end
 	end
@@ -590,6 +624,7 @@ mymodule.create_template = function(self, template, action)
 end
 
 mymodule.update_template = function(self, template, action, create)
+	local connected
 	local success = true
 	local errtxt
 	-- Validate the settings
@@ -610,7 +645,7 @@ mymodule.update_template = function(self, template, action, create)
 	end
 	if success then
         	local res, err = pcall(function()
-			local connected = databaseconnect()
+			connected = databaseconnect()
 			local sql = "SELECT * FROM provisioning_options WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name = 'template') AND value='"..provdb.escape(template.value.filename.value).."'"
 			local tmp = getselectresponse(sql)
 			if not create and (not tmp or #tmp == 0) then
@@ -630,9 +665,10 @@ mymodule.update_template = function(self, template, action, create)
 
 				fs.write_file(template.value.filename.value, string.gsub(format.dostounix(template.value.filecontent.value), "\n+$", ""))
 			end
-			if connected then provdb.databasedisconnect() end
+			if connected then databasedisconnect() end
 		end)
 		if not res and err then
+			handlesqlexception(connected)
 			success = false
 			errtxt = err
 		end
@@ -655,10 +691,11 @@ mymodule.get_delete_template = function(self, clientdata)
 end
 
 mymodule.delete_template = function(self, delreq)
+	local connected
 	local filename = delreq.value.filename.value
 	delreq.errtxt = "Failed to delete template"
        	local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		local sql = "SELECT * FROM provisioning_options WHERE param_id=(SELECT param_id FROM provisioning_params WHERE name = 'template') AND value='"..provdb.escape(filename).."'"
 		local tmp = getselectresponse(sql)
 		if #tmp == 0 then
@@ -671,9 +708,10 @@ mymodule.delete_template = function(self, delreq)
 			os.remove(filename)
 			delreq.errtxt = nil
 		end
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		delreq.errtxt = err
 	end
 
@@ -681,16 +719,18 @@ mymodule.delete_template = function(self, delreq)
 end
 
 mymodule.list_class_groups = function()
+	local connected
 	local retval = {}
 	local errtxt
 	-- Get the groups from the DB
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		local sql = "SELECT * FROM provisioning_class_groups ORDER BY seq ASC, label ASC"
 		retval = getselectresponse(sql)
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		errtxt = err
 	end
 
@@ -698,6 +738,7 @@ mymodule.list_class_groups = function()
 end
 
 mymodule.get_class_group = function(self, clientdata)
+	local connected
 	clientdata = clientdata or {}
 	local class_group_id = clientdata.class_group_id
 	local retval = {}
@@ -707,7 +748,7 @@ mymodule.get_class_group = function(self, clientdata)
 	retval.seq = cfe({label="Sequence", seq=4})
 	local errtxt
 	local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		if class_group_id and class_group_id ~= "" then
 			sql = "SELECT * FROM provisioning_class_groups WHERE class_group_id='"..provdb.escape(class_group_id).."'"
 			tmp = getselectresponse(sql)
@@ -721,9 +762,10 @@ mymodule.get_class_group = function(self, clientdata)
 		else
 			retval.class_group_id = nil
 		end
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		errtxt = err
 	end
 
@@ -735,6 +777,7 @@ mymodule.create_class_group = function(self, group, action)
 end
 
 mymodule.update_class_group = function(self, group, action, create)
+	local connected
 	local success = true
 	local errtxt
 	-- Validate the settings
@@ -755,7 +798,7 @@ mymodule.update_class_group = function(self, group, action, create)
 	end
 	if success then
         	local res, err = pcall(function()
-			local connected = databaseconnect()
+			connected = databaseconnect()
 			if not create then
 				local sql = "SELECT * FROM provisioning_class_groups WHERE class_group_id='"..provdb.escape(group.value.class_group_id.value).."'"
 				local tmp = getselectresponse(sql)
@@ -765,13 +808,12 @@ mymodule.update_class_group = function(self, group, action, create)
 				end
 			end
 			if success then
-				local sql = "BEGIN TRANSACTION"
-				runsqlcommand(sql)
+				local sql
 				if create then
 					sql = "INSERT INTO provisioning_class_groups VALUES(DEFAULT, '"..provdb.escape(group.value.name.value).."', '"..provdb.escape(group.value.label.value).."', '"..provdb.escape(group.value.seq.value).."')"
-					runsqlcommand(sql, true)
+					runsqlcommand(sql)
 					sql = "SELECT class_group_id FROM provisioning_class_groups WHERE label='"..provdb.escape(group.value.label.value).."'"
-					local tmp = getselectresponse(sql, true)
+					local tmp = getselectresponse(sql)
 					if tmp and #tmp>0 then
 						group.value.class_group_id = cfe({value=tmp[1].class_group_id, label="Class Group ID", readonly=true, seq=1})
 					else
@@ -779,16 +821,13 @@ mymodule.update_class_group = function(self, group, action, create)
 					end
 				else
 					sql = "UPDATE provisioning_class_groups SET (name, label, seq) = ('"..provdb.escape(group.value.name.value).."', '"..provdb.escape(group.value.label.value).."', '"..provdb.escape(group.value.seq.value).."') WHERE class_group_id='"..provdb.escape(group.value.class_group_id.value).."'"
-					runsqlcommand(sql, true)
+					runsqlcommand(sql)
 				end
-
-				sql = "COMMIT"
-				runsqlcommand(sql)
 			end
-			if connected then provdb.databasedisconnect() end
+			if connected then databasedisconnect() end
 		end)
 		if not res and err then
-			pcall(function() con:execute("ROLLBACK") end)
+			handlesqlexception(connected)
 			success = false
 			errtxt = err
 		end
@@ -811,10 +850,11 @@ mymodule.get_delete_class_group = function(self, clientdata)
 end
 
 mymodule.delete_class_group = function(self, delreq)
+	local connected
 	local class_group_id = delreq.value.class_group_id.value
 	delreq.errtxt = "Failed to delete class group"
        	local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		local sql = "SELECT * FROM provisioning_class_groups WHERE class_group_id='"..provdb.escape(class_group_id).."'"
 		local tmp = getselectresponse(sql)
 		if #tmp == 0 then
@@ -824,9 +864,10 @@ mymodule.delete_class_group = function(self, delreq)
 			runsqlcommand(sql)
 			delreq.errtxt = nil
 		end
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		delreq.errtxt = err
 	end
 
@@ -834,16 +875,18 @@ mymodule.delete_class_group = function(self, delreq)
 end
 
 mymodule.list_classes = function()
+	local connected
 	local retval = {}
 	local errtxt
 	-- Get the classes from the DB
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		local sql = "SELECT class_id, g.label AS group, g.name, c.label, c.seq FROM provisioning_classes c JOIN provisioning_class_groups g USING(class_group_id) ORDER BY g.seq ASC, g.label ASC, c.seq ASC, c.label ASC"
 		retval = getselectresponse(sql)
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		errtxt = err
 	end
 
@@ -851,6 +894,7 @@ mymodule.list_classes = function()
 end
 
 mymodule.get_class = function(self, clientdata)
+	local connected
 	clientdata = clientdata or {}
 	local class_id = clientdata.class_id
 	local retval = {}
@@ -862,7 +906,7 @@ mymodule.get_class = function(self, clientdata)
 	local errtxt
 	local res, err = pcall(function()
 		local groups = {}
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		if class_id and class_id ~= "" then
 			local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..provdb.escape(class_id).."'"
 			local tmp = getselectresponse(sql)
@@ -901,9 +945,10 @@ mymodule.get_class = function(self, clientdata)
 				group.value = g.group_id
 			end
 		end
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		errtxt = err
 	end
 
@@ -915,6 +960,7 @@ mymodule.create_class = function(self, class, action)
 end
 
 mymodule.update_class = function(self, class, action, create)
+	local connected
 	local success = true
 	local errtxt
 	-- Validate the settings
@@ -932,7 +978,7 @@ mymodule.update_class = function(self, class, action, create)
 	end
 	if success then
         	local res, err = pcall(function()
-			local connected = databaseconnect()
+			connected = databaseconnect()
 			if not create then
 				local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..provdb.escape(class.value.class_id.value).."'"
 				local tmp = getselectresponse(sql)
@@ -942,13 +988,12 @@ mymodule.update_class = function(self, class, action, create)
 				end
 			end
 			if success then
-				local sql = "BEGIN TRANSACTION"
-				runsqlcommand(sql)
+				local sql
 				if create then
 					sql = "INSERT INTO provisioning_classes VALUES(DEFAULT, '"..provdb.escape(class.value.class_group_id.value).."', '"..provdb.escape(class.value.label.value).."', '"..provdb.escape(class.value.seq.value).."')"
-					runsqlcommand(sql, true)
+					runsqlcommand(sql)
 					sql = "SELECT class_id FROM provisioning_classes WHERE class_group_id='"..provdb.escape(class.value.class_group_id.value).."' AND label='"..provdb.escape(class.value.label.value).."'"
-					local tmp = getselectresponse(sql, true)
+					local tmp = getselectresponse(sql)
 					if tmp and #tmp>0 then
 						class.value.class_id = cfe({value=tmp[1].class_id, label="Class ID", readonly=true, seq=1})
 					else
@@ -956,25 +1001,22 @@ mymodule.update_class = function(self, class, action, create)
 					end
 				else
 					sql = "UPDATE provisioning_classes SET (class_group_id, label, seq) = ('"..provdb.escape(class.value.class_group_id.value).."', '"..provdb.escape(class.value.label.value).."', '"..provdb.escape(class.value.seq.value).."') WHERE class_id='"..provdb.escape(class.value.class_id.value).."'"
-					runsqlcommand(sql, true)
+					runsqlcommand(sql)
 					sql = "DELETE FROM classes_to_param_groups WHERE class_id='"..provdb.escape(class.value.class_id.value).."'"
-					runsqlcommand(sql, true)
+					runsqlcommand(sql)
 				end
 				-- Insert the class to group entries
 				for n,g in pairs(class.value.groups.value) do
 					if g.value ~= "" then
 						sql = "INSERT INTO classes_to_param_groups VALUES('"..provdb.escape(class.value.class_id.value).."', '"..provdb.escape(g.value).."')"
-						runsqlcommand(sql, true)
+						runsqlcommand(sql)
 					end
 				end
-
-				sql = "COMMIT"
-				runsqlcommand(sql)
 			end
-			if connected then provdb.databasedisconnect() end
+			if connected then databasedisconnect() end
 		end)
 		if not res and err then
-			pcall(function() con:execute("ROLLBACK") end)
+			handlesqlexception(connected)
 			success = false
 			errtxt = err
 		end
@@ -997,29 +1039,26 @@ mymodule.get_delete_class = function(self, clientdata)
 end
 
 mymodule.delete_class = function(self, delreq)
+	local connected
 	local class_id = delreq.value.class_id.value
 	delreq.errtxt = "Failed to delete class"
        	local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..provdb.escape(class_id).."'"
 		local tmp = getselectresponse(sql)
 		if #tmp == 0 then
 			delreq.value.class_id.errtxt = "Class does not exist"
 		else
-			sql = "BEGIN TRANSACTION"
-			runsqlcommand(sql)
 			sql = "DELETE FROM classes_to_param_groups WHERE class_id='"..provdb.escape(class_id).."'"
-			runsqlcommand(sql, true)
+			runsqlcommand(sql)
 			sql = "DELETE FROM provisioning_classes WHERE class_id='"..provdb.escape(class_id).."'"
-			runsqlcommand(sql, true)
-			sql = "COMMIT"
 			runsqlcommand(sql)
 			delreq.errtxt = nil
 		end
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
-		pcall(function() con:execute("ROLLBACK") end)
+		handlesqlexception(connected)
 		delreq.errtxt = err
 	end
 
@@ -1027,16 +1066,18 @@ mymodule.delete_class = function(self, delreq)
 end
 
 mymodule.list_groups = function()
+	local connected
 	local retval = {}
 	local errtxt
 	-- Get the groups from the DB
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		local sql = "SELECT * FROM provisioning_groups ORDER BY seq ASC, name ASC, label ASC"
 		retval = getselectresponse(sql)
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		errtxt = err
 	end
 
@@ -1044,6 +1085,7 @@ mymodule.list_groups = function()
 end
 
 mymodule.get_group = function(self, clientdata)
+	local connected
 	clientdata = clientdata or {}
 	local group_id = clientdata.group_id
 	local retval = {}
@@ -1056,7 +1098,7 @@ mymodule.get_group = function(self, clientdata)
 	retval.defaults = cfe({type="group", value={}, label="Parameter Defaults", seq=8})
 	local errtxt
 	local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		-- First, let's get all the parameters to set up the params.options and defaults
 		local sql = "SELECT * FROM provisioning_params ORDER BY seq ASC, name ASC"
 		local tmp = getselectresponse(sql)
@@ -1103,9 +1145,10 @@ mymodule.get_group = function(self, clientdata)
 		else
 			retval.group_id = nil
 		end
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		errtxt = err
 	end
 
@@ -1117,6 +1160,7 @@ mymodule.create_group = function(self, group, action)
 end
 
 mymodule.update_group = function(self, group, action, create)
+	local connected
 	local success = true
 	local errtxt
 	-- Validate the settings
@@ -1147,7 +1191,7 @@ mymodule.update_group = function(self, group, action, create)
 	end
 	if success then
         	local res, err = pcall(function()
-			local connected = databaseconnect()
+			connected = databaseconnect()
 			local devices = {}
 			if not create then
 				local sql = "SELECT * FROM provisioning_groups WHERE group_id='"..provdb.escape(group.value.group_id.value).."'"
@@ -1168,13 +1212,11 @@ mymodule.update_group = function(self, group, action, create)
 				end
 			end
 			if success then
-				local sql = "BEGIN TRANSACTION"
-				runsqlcommand(sql)
 				if create then
 					sql = "INSERT INTO provisioning_groups VALUES(DEFAULT, '"..provdb.escape(group.value.name.value).."', '"..provdb.escape(group.value.label.value).."', '"..provdb.escape(group.value.seq.value).."')"
-					runsqlcommand(sql, true)
+					runsqlcommand(sql)
 					sql = "SELECT group_id FROM provisioning_groups WHERE name='"..provdb.escape(group.value.name.value).."' AND label='"..provdb.escape(group.value.label.value).."'"
-					local tmp = getselectresponse(sql, true)
+					local tmp = getselectresponse(sql)
 					if tmp and #tmp>0 then
 						group.value.group_id = cfe({value=tmp[1].group_id, label="Group ID", readonly=true, seq=1})
 					else
@@ -1182,9 +1224,9 @@ mymodule.update_group = function(self, group, action, create)
 					end
 				else
 					sql = "UPDATE provisioning_groups SET (name, label, seq) = ('"..provdb.escape(group.value.name.value).."', '"..provdb.escape(group.value.label.value).."', '"..provdb.escape(group.value.seq.value).."') WHERE group_id='"..provdb.escape(group.value.group_id.value).."'"
-					runsqlcommand(sql, true)
+					runsqlcommand(sql)
 					sql = "DELETE FROM param_groups_to_params WHERE group_id='"..provdb.escape(group.value.group_id.value).."'"
-					runsqlcommand(sql, true)
+					runsqlcommand(sql)
 				end
 				-- Reverse the editable table for ease of use below
 				local reverseeditable = {}
@@ -1200,12 +1242,9 @@ mymodule.update_group = function(self, group, action, create)
 						sql = sql.."null"
 					end
 					sql = sql..", '"..provdb.escape(tostring(reverseeditable[p] ~= nil)).."')"
-					runsqlcommand(sql, true)
+					runsqlcommand(sql)
 				end
 
-				sql = "COMMIT"
-				runsqlcommand(sql)
-
 				-- Notify the devices that their params might have changed
 				for i,d in ipairs(devices) do
 					local tmp = saved_device_params[d.device_id]
@@ -1213,10 +1252,10 @@ mymodule.update_group = function(self, group, action, create)
 					callscript(updatedeviceparamsscriptfile, p, tmp)
 				end
 			end
-			if connected then provdb.databasedisconnect() end
+			if connected then databasedisconnect() end
 		end)
 		if not res and err then
-			pcall(function() con:execute("ROLLBACK") end)
+			handlesqlexception(connected)
 			success = false
 			errtxt = err
 		end
@@ -1239,29 +1278,26 @@ mymodule.get_delete_group = function(self, clientdata)
 end
 
 mymodule.delete_group = function(self, delreq)
+	local connected
 	local group_id = delreq.value.group_id.value
 	delreq.errtxt = "Failed to delete parameter group"
        	local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		local sql = "SELECT * FROM provisioning_groups WHERE group_id='"..provdb.escape(group_id).."'"
 		local tmp = getselectresponse(sql)
 		if #tmp == 0 then
 			delreq.value.group_id.errtxt = "Group does not exist"
 		else
-			sql = "BEGIN TRANSACTION"
-			runsqlcommand(sql)
 			sql = "DELETE FROM param_groups_to_params WHERE group_id='"..provdb.escape(group_id).."'"
-			runsqlcommand(sql, true)
+			runsqlcommand(sql)
 			sql = "DELETE FROM provisioning_groups WHERE group_id='"..provdb.escape(group_id).."'"
-			runsqlcommand(sql, true)
-			sql = "COMMIT"
 			runsqlcommand(sql)
 			delreq.errtxt = nil
 		end
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
-		pcall(function() con:execute("ROLLBACK") end)
+		handlesqlexception(connected)
 		delreq.errtxt = err
 	end
 
@@ -1269,16 +1305,18 @@ mymodule.delete_group = function(self, delreq)
 end
 
 mymodule.list_params = function()
+	local connected
 	local retval = {}
 	local errtxt
 	-- Get the params from the DB
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		local sql = "SELECT * FROM provisioning_params ORDER BY seq ASC, name ASC, label ASC"
 		retval = getselectresponse(sql)
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		errtxt = err
 	end
 
@@ -1286,6 +1324,7 @@ mymodule.list_params = function()
 end
 
 mymodule.get_param = function(self, clientdata)
+	local connected
 	clientdata = clientdata or {}
 	local param_id = clientdata.param_id
 	local retval = {}
@@ -1300,7 +1339,7 @@ mymodule.get_param = function(self, clientdata)
 	retval.seq = cfe({label="Sequence", seq=9})
 	local errtxt
 	local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		if param_id and param_id ~= "" then
 			sql = "SELECT * FROM provisioning_params WHERE param_id='"..provdb.escape(param_id).."'"
 			tmp = getselectresponse(sql)
@@ -1314,9 +1353,10 @@ mymodule.get_param = function(self, clientdata)
 		else
 			retval.param_id = nil
 		end
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		errtxt = err
 	end
 
@@ -1328,6 +1368,7 @@ mymodule.create_param = function(self, param, action)
 end
 
 mymodule.update_param = function(self, param, action, create)
+	local connected
 	local success = true
 	local errtxt
 	-- Validate the settings
@@ -1354,7 +1395,7 @@ mymodule.update_param = function(self, param, action, create)
 	end
 	if success then
         	local res, err = pcall(function()
-			local connected = databaseconnect()
+			connected = databaseconnect()
 			local devices = {}
 			if not create then
 				local sql = "SELECT * FROM provisioning_params WHERE param_id='"..provdb.escape(param.value.param_id.value).."'"
@@ -1375,13 +1416,12 @@ mymodule.update_param = function(self, param, action, create)
 				end
 			end
 			if success then
-				local sql = "BEGIN TRANSACTION"
-				runsqlcommand(sql)
+				local sql
 				if create then
 					sql = "INSERT INTO provisioning_params VALUES(DEFAULT, '"..provdb.escape(param.value.name.value).."', '"..provdb.escape(param.value.type.value).."', '"..provdb.escape(param.value.label.value).."', '"..provdb.escape(param.value.descr.value).."', '"..provdb.escape(param.value.value.value).."', '"..provdb.escape(param.value.seq.value).."', '"..provdb.escape(param.value.regexp.value).."', '"..provdb.escape(format.dostounix(param.value.validate.value)).."')"
-					runsqlcommand(sql, true)
+					runsqlcommand(sql)
 					sql = "SELECT param_id FROM provisioning_params WHERE name='"..provdb.escape(param.value.name.value).."' AND label='"..provdb.escape(param.value.label.value).."'"
-					local tmp = getselectresponse(sql, true)
+					local tmp = getselectresponse(sql)
 					if tmp and #tmp>0 then
 						param.value.param_id = cfe({value=tmp[1].param_id, label="Param ID", readonly=true, seq=1})
 					else
@@ -1389,12 +1429,9 @@ mymodule.update_param = function(self, param, action, create)
 					end
 				else
 					sql = "UPDATE provisioning_params SET (name, type, label, descr, value, seq, regexp, validate) = ('"..provdb.escape(param.value.name.value).."', '"..provdb.escape(param.value.type.value).."', '"..provdb.escape(param.value.label.value).."', '"..provdb.escape(param.value.descr.value).."', '"..provdb.escape(param.value.value.value).."', '"..provdb.escape(param.value.seq.value).."', '"..provdb.escape(param.value.regexp.value).."', '"..provdb.escape(format.dostounix(param.value.validate.value)).."') WHERE param_id='"..provdb.escape(param.value.param_id.value).."'"
-					runsqlcommand(sql, true)
+					runsqlcommand(sql)
 				end
 
-				sql = "COMMIT"
-				runsqlcommand(sql)
-
 				-- Notify the devices that their params might have changed
 				for i,d in ipairs(devices) do
 					local tmp = saved_device_params[d.device_id]
@@ -1402,10 +1439,10 @@ mymodule.update_param = function(self, param, action, create)
 					callscript(updatedeviceparamsscriptfile, p, tmp)
 				end
 			end
-			if connected then provdb.databasedisconnect() end
+			if connected then databasedisconnect() end
 		end)
 		if not res and err then
-			pcall(function() con:execute("ROLLBACK") end)
+			handlesqlexception(connected)
 			success = false
 			errtxt = err
 		end
@@ -1428,29 +1465,26 @@ mymodule.get_delete_param = function(self, clientdata)
 end
 
 mymodule.delete_param = function(self, delreq)
+	local connected
 	local param_id = delreq.value.param_id.value
 	delreq.errtxt = "Failed to delete parameter"
        	local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		local sql = "SELECT * FROM provisioning_params WHERE param_id='"..provdb.escape(param_id).."'"
 		local tmp = getselectresponse(sql)
 		if #tmp == 0 then
 			delreq.value.param_id.errtxt = "Parameter does not exist"
 		else
-			sql = "BEGIN TRANSACTION"
-			runsqlcommand(sql)
 			sql = "DELETE FROM provisioning_options WHERE param_id='"..provdb.escape(param_id).."'"
-			runsqlcommand(sql, true)
+			runsqlcommand(sql)
 			sql = "DELETE FROM provisioning_params WHERE param_id='"..provdb.escape(param_id).."'"
-			runsqlcommand(sql, true)
-			sql = "COMMIT"
 			runsqlcommand(sql)
 			delreq.errtxt = nil
 		end
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
-		pcall(function() con:execute("ROLLBACK") end)
+		handlesqlexception(connected)
 		delreq.errtxt = err
 	end
 
@@ -1458,6 +1492,7 @@ mymodule.delete_param = function(self, delreq)
 end
 
 mymodule.list_devices = function(self, clientdata)
+	local connected
 	local retval = cfe({ type="group", value={}, label="Provisioning Devices" })
 	retval.value.page = cfe({ value=0, label="Page Number", descr="0 indicates ALL", key=true })
 	retval.value.pagesize = cfe({ value=10, label="Page Size", key=true })
@@ -1478,7 +1513,7 @@ mymodule.list_devices = function(self, clientdata)
 
 	-- Get the devices from the DB
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 
 		-- First, get the columns / class_groups
 		local sql = "SELECT * from provisioning_class_groups ORDER BY seq ASC"
@@ -1536,9 +1571,10 @@ mymodule.list_devices = function(self, clientdata)
 			retval.value.rowcount.value = #retval.value.result.value
 		end
 
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		retval.errtxt = err
 	end
 
@@ -1559,7 +1595,8 @@ mymodule.create_device = function(self, device, action)
 	return mymodule.update_device(self, device, action, true)
 end
 
-mymodule.update_device = function(self, device, action, create, intransaction)
+mymodule.update_device = function(self, device, action, create)
+	local connected
 	local success = true
 	local errtxt
 	-- Validate the settings
@@ -1573,7 +1610,7 @@ mymodule.update_device = function(self, device, action, create, intransaction)
 	end
 	if success then
         	local res, err = pcall(function()
-			local connected = databaseconnect()
+			connected = databaseconnect()
 			if not create then
 				local sql = "SELECT * FROM devices_to_classes WHERE device_id='"..provdb.escape(device.value.device_id.value).."' LIMIT 1"
 				local tmp = getselectresponse(sql)
@@ -1586,36 +1623,32 @@ mymodule.update_device = function(self, device, action, create, intransaction)
 				if not saved_devices[device.value.device_id.value] then get_device(device.value.device_id.value) end
 				if not saved_device_params[device.value.device_id.value] then get_device_params(device.value.device_id.value) end
 
-				local sql = "BEGIN TRANSACTION"
-				if not intransaction then runsqlcommand(sql) end
 				if create then
 					sql = "SELECT nextval('provisioning_device_seq')"
-					local tmp = getselectresponse(sql, true)
+					local tmp = getselectresponse(sql)
 					if tmp and #tmp>0 then
 						device.value.device_id.value = tmp[1].nextval
 					end
 				end
 				sql = "DELETE FROM devices_to_classes WHERE device_id='"..provdb.escape(device.value.device_id.value).."'"
-				runsqlcommand(sql, true)
+				runsqlcommand(sql)
 				-- Insert the device to class entries
 				for n,c in pairs(device.value.classes.value) do
 					if c.value ~= "" then
 						sql = "INSERT INTO devices_to_classes VALUES('"..provdb.escape(device.value.device_id.value).."', '"..provdb.escape(c.value).."')"
-						runsqlcommand(sql, true)
+						runsqlcommand(sql)
 					end
 				end
 
-				sql = "COMMIT"
-				if not intransaction then runsqlcommand(sql) end
-
 				local s = saved_device_params[device.value.device_id.value]
 				callscript(updatedevicescriptfile, device, saved_devices[device.value.device_id.value], get_device_params(device.value.device_id.value), s)
+
 				saved_devices[device.value.device_id.value] = device
 			end
-			if connected then provdb.databasedisconnect() end
+			if connected then databasedisconnect() end
 		end)
 		if not res and err then
-			pcall(function() con:execute("ROLLBACK") end)
+			handlesqlexception(connected)
 			success = false
 			errtxt = err
 		end
@@ -1638,10 +1671,11 @@ mymodule.get_delete_device = function(self, clientdata)
 end
 
 mymodule.delete_device = function(self, delreq)
+	local connected
 	local device_id = delreq.value.device_id.value
 	delreq.errtxt = "Failed to delete device"
        	local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		local sql = "SELECT * FROM devices_to_classes WHERE device_id='"..provdb.escape(device_id).."' LIMIT 1"
 		local tmp = getselectresponse(sql)
 		if #tmp == 0 then
@@ -1650,13 +1684,9 @@ mymodule.delete_device = function(self, delreq)
 			if not saved_device_params[device_id] then get_device_params(device_id) end
 			if not saved_devices[device_id] then get_device(device_id) end
 			
-			sql = "BEGIN TRANSACTION"
-			runsqlcommand(sql)
 			sql = "DELETE FROM provisioning_values WHERE device_id='"..provdb.escape(device_id).."'"
-			runsqlcommand(sql, true)
+			runsqlcommand(sql)
 			sql = "DELETE FROM devices_to_classes WHERE device_id='"..provdb.escape(device_id).."'"
-			runsqlcommand(sql, true)
-			sql = "COMMIT"
 			runsqlcommand(sql)
 			delreq.errtxt = nil
 
@@ -1664,10 +1694,10 @@ mymodule.delete_device = function(self, delreq)
 			saved_devices[device_id] = nil
 			saved_device_params[device_id] = nil
 		end
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
-		pcall(function() con:execute("ROLLBACK") end)
+		handlesqlexception(connected)
 		delreq.errtxt = err
 	end
 
@@ -1693,12 +1723,13 @@ mymodule.get_class_options = function(self, clientdata)
 end
 
 mymodule.get_class_values = function(self, retval)
+	local connected
 	retval.errtxt = "Failed to find class"
 	if not validator.is_integer(retval.value.class_id.value) then
 		retval.value.class_id.errtxt = "Invalid class ID"
 	else
 		local res, err = pcall(function()
-			local connected = databaseconnect()
+			connected = databaseconnect()
 			-- First, just check to see if class_id exists
 			local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..provdb.escape(retval.value.class_id.value).."'"
 			local tmp = getselectresponse(sql)
@@ -1725,9 +1756,10 @@ mymodule.get_class_values = function(self, retval)
 			else
 				retval.value.class_id.errtxt = "Failed to find Class ID"
 			end
-			if connected then provdb.databasedisconnect() end
+			if connected then databasedisconnect() end
 		end)
 		if not res and err then
+			handlesqlexception(connected)
 			retval.errtxt = err
 		end
 	end
@@ -1744,8 +1776,9 @@ mymodule.set_all_device_params = function(self, params)
 end
 
 mymodule.fetch_device_values = function(self, search)
+	local connected
 	local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		search = mymodule.search_device_values(self, search)
 		if search.errtxt then
 			-- Just return the error
@@ -1757,9 +1790,10 @@ mymodule.fetch_device_values = function(self, search)
 			search.value.values = get_device_values(search.value.result.value[1].device_id)
 			search.value.values.seq = 5
 		end
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		search.errtxt = err
 	end
 
@@ -1768,13 +1802,14 @@ mymodule.fetch_device_values = function(self, search)
 end
 
 mymodule.get_search_options = function()
+	local connected
 	local errtxt
 	retval = {}
 	retval.id = cfe({type="select", value="device_id", label="Parameter", option={"device_id"}, seq=1})
 	retval.comparison = cfe({type="select", value="=", label="Comparison", option={"=", "!=", "~", "!~", "~*", "!~*"}, seq=2})
 	retval.value = cfe({label="Parameter Value", descr="Parameter value or SQL regular expression", seq=3})
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		-- Get the group/parameter options
 		local sql = "SELECT g.name AS group_name, p.name AS param_name FROM provisioning_groups g JOIN param_groups_to_params USING(group_id) JOIN provisioning_params p USING(param_id) GROUP BY g.name, p.name ORDER BY g.name ASC, p.name ASC"
 		local tmp = getselectresponse(sql)
@@ -1791,15 +1826,17 @@ mymodule.get_search_options = function()
 		for i,o in ipairs(blankopt) do
 			retval.id.option[#retval.id.option + 1] = o
 		end
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		errtxt = err
 	end
 	return cfe({type="group", value=retval, label="Device Search", errtxt=errtxt})
 end
 
 mymodule.search_device_values = function(self, search)
+	local connected
 	local success = true
 	success = modelfunctions.validateselect(search.value.id) and success
 	success = modelfunctions.validateselect(search.value.comparison) and success
@@ -1816,7 +1853,7 @@ mymodule.search_device_values = function(self, search)
 	if success then
 		retval.result = cfe({type="structure", value={}, label="Devices", seq=4 })
         	local res, err = pcall(function()
-			local connected = databaseconnect()
+			connected = databaseconnect()
 			-- Get the devices from the DB
 			sql = "SELECT d2t.device_id, "
 			local group, param = string.match(search.value.id.value, "([^%.]*)%.(.*)")
@@ -1837,9 +1874,10 @@ mymodule.search_device_values = function(self, search)
 			end
 			sql = sql.." ORDER BY d2t.device_id ASC"
 			search.value.result.value = getselectresponse(sql)
-			if connected then provdb.databasedisconnect() end
+			if connected then databasedisconnect() end
 		end)
 		if not res and err then
+			handlesqlexception(connected)
 			search.errtxt = err
 		end
 	else
@@ -1849,6 +1887,7 @@ mymodule.search_device_values = function(self, search)
 end
 
 mymodule.get_param_options = function(self, clientdata)
+	local connected
 	clientdata = clientdata or {}
 	local param_id = clientdata.param_id
 	local retval = {}
@@ -1859,7 +1898,7 @@ mymodule.get_param_options = function(self, clientdata)
 	local errtxt = "Cannot find parameter"
 	if param_id and param_id ~= "" then
 		local res, err = pcall(function()
-			local connected = databaseconnect()
+			connected = databaseconnect()
 			-- First, just check to see if param_id exists
 			local sql = "SELECT * FROM provisioning_params WHERE param_id='"..provdb.escape(param_id).."'"
 			local tmp = getselectresponse(sql)
@@ -1874,9 +1913,10 @@ mymodule.get_param_options = function(self, clientdata)
 					retval.options.value[#retval.options.value + 1] = t.value..","..t.label
 				end
 			end
-			if connected then provdb.databasedisconnect() end
+			if connected then databasedisconnect() end
 		end)
 		if not res and err then
+			handlesqlexception(connected)
 			errtxt = err
 		end
 	end
@@ -1884,13 +1924,14 @@ mymodule.get_param_options = function(self, clientdata)
 end
 
 mymodule.set_param_options = function(self, options)
+	local connected
 	local success = true
 	local errtxt
 	-- Validate the settings
 --FIXME
 	if success then
         	local res, err = pcall(function()
-			local connected = databaseconnect()
+			connected = databaseconnect()
 			local sql = "SELECT * FROM provisioning_params WHERE param_id='"..provdb.escape(options.value.param_id.value).."'"
 			local tmp = getselectresponse(sql)
 			if not tmp or #tmp == 0 then
@@ -1898,11 +1939,9 @@ mymodule.set_param_options = function(self, options)
 				errtxt = "Parameter does not exist"
 			end
 			if success then
-				local sql = "BEGIN TRANSACTION"
-				runsqlcommand(sql)
 				-- Delete all options for this device
-				sql = "DELETE FROM provisioning_options WHERE param_id='"..provdb.escape(options.value.param_id.value).."'"
-				runsqlcommand(sql, true)
+				local sql = "DELETE FROM provisioning_options WHERE param_id='"..provdb.escape(options.value.param_id.value).."'"
+				runsqlcommand(sql)
 				-- Loop through the options
 				for i,o in ipairs(options.value.options.value) do
 					local v,l = string.match(o, "^%s*([^,]*),%s*(.*%S?)%s*$")
@@ -1913,15 +1952,13 @@ mymodule.set_param_options = function(self, options)
 						l = v
 					end
 					sql = "INSERT INTO provisioning_options VALUES('"..provdb.escape(options.value.param_id.value).."', '"..provdb.escape(l).."', '"..provdb.escape(v).."', '"..i.."')"
-					runsqlcommand(sql, true)
+					runsqlcommand(sql)
 				end
-				sql = "COMMIT"
-				runsqlcommand(sql)
 			end
-			if connected then provdb.databasedisconnect() end
+			if connected then databasedisconnect() end
 		end)
 		if not res and err then
-			pcall(function() con:execute("ROLLBACK") end)
+			handlesqlexception(connected)
 			success = false
 			errtxt = err
 		end
@@ -1958,8 +1995,9 @@ function mymodule.get_database()
 end
 
 function mymodule.dump_database(self, db)
+	local connected
 	local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		local lines = {}
 		local sql = "SELECT name, label, seq FROM provisioning_class_groups ORDER BY name, label"
 		local tmp = getselectresponse(sql)
@@ -2014,15 +2052,17 @@ function mymodule.dump_database(self, db)
 			lines[#lines+1] = "SELECT pg_catalog.setval('provisioning_device_seq', "..provdb.escape(#devices)..", true);"
 		end
 		db.value.data = cfe({ type="longtext", value=table.concat(lines, "\n") or "", label="Database Values" })
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		db.errtxt = err
 	end
 	return db
 end
 
 function mymodule.get_file(self, clientdata)
+	local connected
 	clientdata = clientdata or {}
 	local mac = clientdata.mac
 	local ip = clientdata.ip
@@ -2036,7 +2076,7 @@ function mymodule.get_file(self, clientdata)
 	end
 
 	local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 
 		-- Add the device to the table of requests
 		local sql = "SELECT * FROM provisioning_requests WHERE mac='"..provdb.escape(string.upper(mac)).."'"
@@ -2069,9 +2109,10 @@ function mymodule.get_file(self, clientdata)
 			end
 		end
 
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		result.errtxt = err
 	end
 
@@ -2079,6 +2120,7 @@ function mymodule.get_file(self, clientdata)
 end
 
 function mymodule.put_file(self, clientdata)
+	local connected
 	clientdata = clientdata or {}
 	local mac = clientdata.mac
 	local data = clientdata.data
@@ -2091,7 +2133,7 @@ function mymodule.put_file(self, clientdata)
 	end
 
 	local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		
 		-- Now, let's see if this device exists
 		local search = mymodule.get_search_options()
@@ -2109,15 +2151,17 @@ function mymodule.put_file(self, clientdata)
 			-- If the script doesn't exist, allow the write
 			retval.value = retval.value or data
 		end
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		retval.errtxt = err
 	end
 	return retval
 end
 
 mymodule.list_requests = function(self, clientdata)
+	local connected
 	local retval = cfe({ type="group", value={}, label="Requests" })
 	retval.value.page = cfe({ value=0, label="Page Number", descr="0 indicates ALL", key=true })
 	retval.value.pagesize = cfe({ value=10, label="Page Size", key=true })
@@ -2147,7 +2191,7 @@ mymodule.list_requests = function(self, clientdata)
 	end
 
         local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		
 		local filter = {}
 		columns.data = nil -- Cannot regex filter based on date because of the timestamp type
@@ -2182,9 +2226,10 @@ mymodule.list_requests = function(self, clientdata)
 			retval.value.rowcount.value = #retval.value.result.value
 		end
 
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		retval.errtxt = err
 	end
 
@@ -2199,10 +2244,11 @@ mymodule.get_delete_request = function(self, clientdata)
 end
 
 mymodule.delete_request = function(self, delreq)
+	local connected
 	local mac = delreq.value.mac.value
 	delreq.errtxt = "Failed to delete request"
        	local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		local sql = "SELECT * FROM provisioning_requests WHERE mac='"..provdb.escape(string.upper(mac)).."'"
 		local tmp = getselectresponse(sql)
 		if #tmp == 0 then
@@ -2213,9 +2259,10 @@ mymodule.delete_request = function(self, delreq)
 			runsqlcommand(sql)
 			delreq.errtxt = nil
 		end
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		delreq.errtxt = err
 	end
 
@@ -2231,9 +2278,10 @@ mymodule.get_request = function(self, clientdata)
 end
 
 mymodule.create_from_request = function(self, request)
+	local connected
 	success = false
        	local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 		local sql = "SELECT * FROM provisioning_requests WHERE mac='"..provdb.escape(string.upper(request.value.mac.value)).."'"
 		local tmp = getselectresponse(sql)
 		if #tmp == 0 then
@@ -2276,9 +2324,10 @@ mymodule.create_from_request = function(self, request)
 				request.errtxt = "Failed to create device - could not determine class"
 			end
 		end
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		request.errtxt = err
 	end
 	
@@ -2295,13 +2344,14 @@ mymodule.get_bulk_create_request = function(self, clientdata)
 end
 
 mymodule.bulk_create_devices = function(self, devicelist)
+	local connected
 	-- To allow uploading a file, check the bulkdevicedata format
 	-- Haserl will pass a temporary file name if a file is uploaded
 	if #devicelist.value.bulkdevicedata.value == 1 and string.find(devicelist.value.bulkdevicedata.value[1], "^/tmp/[^.]+$") and fs.is_file(devicelist.value.bulkdevicedata.value[1]) then
 		devicelist.value.bulkdevicedata.value = fs.read_file_as_array(devicelist.value.bulkdevicedata.value[1]) or {}
 	end
 	local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 
 		-- Need to split on ",", but not if found in quoted field
 		function csv_parseline(line,sep)
@@ -2342,7 +2392,7 @@ mymodule.bulk_create_devices = function(self, devicelist)
 			return res
 		end
 
-		local groups = mymodule.list_class_groups()
+		local groups = mymodule.list_class_groups(true)
 
 		local headers = csv_parseline(devicelist.value.bulkdevicedata.value[1], ",")
 		local reverseheaders = {}
@@ -2350,7 +2400,6 @@ mymodule.bulk_create_devices = function(self, devicelist)
 			reverseheaders[h] = i
 		end
 
-		runsqlcommand("BEGIN TRANSACTION")
 		for i=2,#devicelist.value.bulkdevicedata.value do
 			local values = csv_parseline(devicelist.value.bulkdevicedata.value[i], ",")
 
@@ -2403,7 +2452,7 @@ mymodule.bulk_create_devices = function(self, devicelist)
 			end
 
 			-- Set the params
-			local params = get_device_params(device.value.device_id.value, false)
+			local params = get_device_params(device.value.device_id.value)
 			for j,h in ipairs(headers) do
 				if values[j] and string.find(h, "%.") then
 					local class,param = string.match(h, "^([^.]+)%.(.*)")
@@ -2453,11 +2502,10 @@ mymodule.bulk_create_devices = function(self, devicelist)
 				error(table.concat(err, "\n"))
 			end
 		end
-		runsqlcommand("COMMIT")
-		if connected then provdb.databasedisconnect() end
+		if connected then databasedisconnect() end
 	end)
 	if not res and err then
-		pcall(function() runsqlcommand("ROLLBACK") end)
+		handlesqlexception(connected)
 		devicelist.errtxt = err
 	end
 
@@ -2470,9 +2518,10 @@ mymodule.get_bulk_dump_request = function(self, clientdata)
 end
 
 mymodule.bulk_dump_devices = function(self, dumprequest)
+	local connected
 	dumprequest.value.devices = cfe({type="raw", value={}, label="devices.csv"})
 	local res, err = pcall(function()
-		local connected = databaseconnect()
+		connected = databaseconnect()
 
 		local classes = {}
 		local reverseclasses = {}
@@ -2500,6 +2549,9 @@ mymodule.bulk_dump_devices = function(self, dumprequest)
 				end
 			end
 		end
+
+		if connected then databasedisconnect() end
+
 		table.sort(classes)
 		table.sort(columns)
 
@@ -2533,10 +2585,9 @@ mymodule.bulk_dump_devices = function(self, dumprequest)
 
 			dumprequest.value.devices.value[#dumprequest.value.devices.value+1] = table.concat(device, ",")
 		end
-
-		if connected then provdb.databasedisconnect() end
 	end)
 	if not res and err then
+		handlesqlexception(connected)
 		dumprequest.errtxt = err
 	end