--CONNECT TO DATABASE
if not con then databaseconnect() end

-- BEGIN TRANSACTION --
assert(con:execute("BEGIN"))
local nextavailableuser
local transactionfailed = false

-- FIND AND RESERVE NEXT AVAILABLE USER NUMBER --
sql = "UPDATE user_reservation_table SET UsedYesNo = true, user_id="..userid..", updateddatetime='"..os.date("%Y-%m-%d %H:%M:%S").."' WHERE uservalue IN("
      .."SELECT uservalue FROM user_reservation_table WHERE UsedYesNo=false Order By id ASC Limit 1) RETURNING uservalue"

--print('the sql to update is: '..sql)
print('Attempting to update user_reservation_table table...')
local reserve_response, reserve_err = con:execute(sql)

if reserve_err then
         transactionfailed = true
         success = false
         print(reserve_err)
         print("rolling back...")
         assert(con:execute("ROLLBACK"))
else
         local row = reserve_response:fetch ({}, "a")
         nextavailableuser = row.uservalue
         print("Successfully reserved USER number "..nextavailableuser)

end

if not transactionfailed then
      sql = "INSERT INTO widget VALUES(DEFAULT,'"..nextavailableuser.."', '"
                     .."generated by script".."','123456778', "
                     .."1, '" --user id
                     ..servername.."',Null,'"
                     ..os.date("%Y-%m-%d %H:%M:%S").."', Null)"

      print('Attempting to create a new widget')

      local insert_response, insert_err = con:execute(sql)

      if insert_err then
         print(insert_err)
         print("rolling back...")
         assert(con:execute("ROLLBACK"))
      else
         assert(con:execute("COMMIT")) -- COMMIT TRANSACTION
         print("Transaction Committed!  widget Created")
      end
end

--DISCONNECT FROM DATABASE
if con then databasedisconnect() end
print("Goodbye")