Tutorial: Databases

This tutorial shows us how to manually commit data onto a database

Commiting New Data on the Database

By default, Quorum automatically commits new data added onto our database. What this means is that everytime we use a Database action such as Insert() or CreateTable(), the database we contained inside mySQL also changes in real time. Most of the time, auto-commiting is handy so databases can get updated with any modification quickly. However, sometimes as a database administrator, we may want to review such data such as if there are mistakes or redundancy, therefore it is always a good practice to check out data before adding it onto the database.

We will want to connect our database once again and primarily use actions from our Database object such as SetAutoCommit(boolean trueOrfalseValue), GetAutoCommit(), Rollback() andCommit(). We will also be using the Update object to add change some data in our table and to reference Update, we can view this link.

Committing Data Actions
Action Description Usage
databaseObject:SetAutoCommit(boolean)This action sets the auto-commit behavior of the database. Auto-commit can be thought of as an autosave feature for documents but for databases. Auto-commit is where transactions on the database are automatically saved on the database. This can be compared to having a document automatically save every change made so the user does not have to press the save button. If this is set to false in order to save changes to the database the Commit() action needs to be used.db:SetAutoCommit(false)
databaseObject:GetAutoCommit()This action returns whether or not the database will automatically commit with every transaction on the database. An example of a "transaction" would be an Insert or Update. If the database has auto commit set to true then an insert will immediately take effect after being sent to the database. Auto-commit can be thought of as an autosave feature for documents but for databases.db:GetAutoCommit()
databaseObject:Rollback()This action will remove any nonpermanent changes currently on the database and revert the database to a previous state. This action will only work if auto-commit is set to false. If auto-commit is not enabled a query such as an Update can be undone before a Commit() is called.db:Rollback()
databaseObject:Commit()Any changes made through Inserts, Updates, or Deletes that occur when auto-commit is disabled will not be saved unless Commit() is used. A commit is like saving changes on a document. If auto commit is set to true this action does not need to be used.db:Commit()
use Libraries.Data.Database.Database
use Libraries.Data.Database.Update

Database database
database:Connect("localhost", "myfirstdatabase", "root", "password")

database:SetAutoCommit(false)
output "Status of Auto-Commit:" + database:GetAutoCommit()

Now, what we will want to do is modify our table normally. We can use Inserts, Deletes, and Updates using our objects to change our table and we can reference these tutorials within earlier sections: Insert tutorial, Delete tutorial, Update tutorial. For now, we can use this sample Update code to make changes. What this snippet of code is doing is selecting our "grocerylist" table and filtering the entries so that we are selecting a specific row. Then, we will want to update the selected row then update it onto our database. One thing we want to note is that database:Update(updateObject) returns a value of how many rows have changed, therefore we will want to keep track of that number, so let's save that into a variable called "rows:" 

 Update update
 update:SetTable("grocerylist")
 update:Filter("FoodQuantity = 7")
 update:Add("FoodQuantity", 1)

integer rows = database:Update(update)

Now, let's set up an if statement checking on how many updates we have made. We want to make sure that in our update, we are only changing one row at a time. Why this matters is that if our filtering does not narrow down specific data, we may end up modifying records we never have meant to touch which in some cases, data cannot be replicated. Let's use our "rows" variable to test out how many rows that have been changed; if it is greater than 1, we will call the action Rollback() which is essentially an undo state, else we know that only one row has been updated so we can commit this change onto the database.

Lastly, we want to make sure that we go back to have auto-commits inside of Quorum Studio for the rest of the tutorials. Again, let's call SetAutoCommit() on our database object and pass in true as the parameter value. This action will turn back the feature where new or updated records / tables will be automatically reflected on mySQL.

 if rows > 1
        database:Rollback()
    else
        database:Commit()
    end

database:SetAutoCommit(true)
output "Status of Auto-Commit:" + database:GetAutoCommit()

Congrats! We finally know how to manually commit data onto our database. To view the entire code, it will be displayed below.


use Libraries.Data.Database.Database
use Libraries.Data.Database.Update

Database database
database:Connect("localhost", "myfirstdatabase", "root", "password")

database:SetAutoCommit(false)
output "Status of Auto-Commit:" + database:GetAutoCommit()

 Update update
 update:SetTable("grocerylist")
 update:Filter("FoodQuantity = 7")
 update:Add("FoodQuantity", 1)

integer rows = database:Update(update)

    if rows > 1
        database:Rollback()
    else
        database:Commit()
    end

database:SetAutoCommit(true)
output "Status of Auto-Commit:" + database:GetAutoCommit()

Next Tutorial

In the next tutorial, we will discuss FindingSingle, which describes how to find a single column of data in a data table.

Go BackNext Tutorial