Libraries.Data.Database.Database Documentation

The Database class is used to connect to a live database and either get information about the Database or run queries on the database and return results. After calling Connect running a query or using a designated helper function will send a SQL query to that database.

Example Code

use Libraries.Data.Database.Database
use Libraries.Data.Database.Find

Database database
database:Connect("localhost", "myDatabase", "user", "SuperSecret456")

Find query
query:SetTable("employees")
query:AddColumn("lastName")
query:AddColumn("firstName")

QueryResult results = database:Find(query)

Inherits from: Libraries.Language.Object

Summary

Actions Summary Table

ActionsDescription
Close()This action closes the active connection to the database.
Commit()Any changes made through Inserts, Updates, or Deletes that occur when auto-commit is disabled will not be saved unless Commit() is used.
Compare(Libraries.Language.Object object)This action compares two object hash codes and returns an integer.
Connect(Libraries.Data.Database.Connection connection)Takes a connection object used internally to connect to a database server.
Connect(text location, text database, text username, text password)This action attempts to create a connection to a database using the given parameters.
ConvertResultToDataFrame(Libraries.Data.Database.QueryResult result)This action takes a QueryResult and converts it to a DataFrame.
CreateTable(Libraries.Data.Database.CreateTable creation)This action takes a CreateTable query and sends it to the database.
CreateView(Libraries.Data.Database.Find query, text name)This action will create a view on the database from the passed Find query.
CreateView(Libraries.Data.Database.CreateView view)This action takes a CreateView query and sends it to the database.
Delete(Libraries.Data.Database.Delete query)This takes an Delete query and sends it to the database and returns the result.
Delete(text table, text filter)This action runs a delete query where rows are removed from a table if the columns of the row make the filter true.
DropTable(Libraries.Data.Database.DropTable drop)This action takes a DropTable query and sends it to the database.
EditTable(Libraries.Data.Database.EditTable edit)This action takes a EditTable query and sends it to the database.
EmptyTable(text table)This action removes all rows in a table.
Equals(Libraries.Language.Object object)This action determines if two objects are equal based on their hash code values.
Find(text table, text filter)This action runs a find based on the given parameters and returns the QueryResult.
Find(text table)This action runs a find based on the given parameters and returns the QueryResult.
Find(Libraries.Containers.Array<text> columns, text table, text filter)This action runs a find based on the given parameters and returns the QueryResult.
Find(Libraries.Containers.Array<text> columns, text table)This action runs a find based on the given parameters and returns the QueryResult.
Find(Libraries.Data.Database.Find query)This takes a Find query object and builds a query to send to the database.
FindAsDataFrame(text table)This action runs a find based on the given parameter and returns the result as a DataFrame.
FindAsDataFrame(Libraries.Data.Database.Find query)This action runs a find query and returns the result as a DataFrame.
FindAsDataFrame(text table, text filter)This action runs a find based on the given parameters and returns the result as a DataFrame.
FindCrossJoin(Libraries.Containers.Array<text> columns, text table, text joinedTable, text filter)This action runs a Find query using a join.
FindCrossJoin(text table, text joinedTable, text filter)This action runs a Find query using a join.
FindCrossJoin(text table, text joinedTable)This action runs a Find query using a join.
FindInnerJoin(Libraries.Containers.Array<text> columns, text table, text joinedTable, text joinCondition, text filter)This action runs a Find query using a join.
FindInnerJoin(text table, text joinedTable, text joinCondition)This action runs a Find query using a join.
FindInnerJoin(text table, text joinedTable, text joinCondition, text filter)This action runs a Find query using a join.
FindInnerJoinUsingColumn(text table, text joinedTable, text column)This action runs a Find query using a join.
FindInnerJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column)This action runs a Find query using a join.
FindInnerJoinUsingColumn(text table, text joinedTable, text column, text filter)This action runs a Find query using a join.
FindInnerJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column, text filter)This action runs a Find query using a join.
FindLeftJoin(text table, text joinedTable, text joinCondition, text filter)This action runs a Find query using a join.
FindLeftJoin(text table, text joinedTable, text joinCondition)This action runs a Find query using a join.
FindLeftJoin(Libraries.Containers.Array<text> columns, text table, text joinedTable, text joinCondition, text filter)This action runs a Find query using a join.
FindLeftJoinUsingColumn(text table, text joinedTable, text column)This action runs a Find query using a join.
FindLeftJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column)This action runs a Find query using a join.
FindLeftJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column, text filter)This action runs a Find query using a join.
FindLeftJoinUsingColumn(text table, text joinedTable, text column, text filter)This action runs a Find query using a join.
FindRightJoin(text table, text joinedTable, text joinCondition)This action runs a Find query using a join.
FindRightJoin(Libraries.Containers.Array<text> columns, text table, text joinedTable, text joinCondition, text filter)This action runs a Find query using a join.
FindRightJoin(text table, text joinedTable, text joinCondition, text filter)This action runs a Find query using a join.
FindRightJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column, text filter)This action runs a Find query using a join.
FindRightJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column)This action runs a Find query using a join.
FindRightJoinUsingColumn(text table, text joinedTable, text column, text filter)This action runs a Find query using a join.
FindRightJoinUsingColumn(text table, text joinedTable, text column)This action runs a Find query using a join.
GetAutoCommit()This action returns whether or not the database will automatically commit with every transaction on the database.
GetDatabaseMetaData()Returns the DatabaseMetaData object which contains more information about the currently connected database such as what tables exist and how many tables there are.
GetHashCode()This action gets the hash code for an object.
Insert(Libraries.Data.Database.Insert query)This takes an Insert query and sends it to the database and returns the result.
IsConnected()Returns whether or not this database object is currently connected to a database
RemoveTable(text table)This action removes a table on the database.
RenameTable(text table, text newName)This action renames a table on the database.
Rollback()This action will remove any nonpermanent changes currently on the database and revert the database to a previous state.
SetAutoCommit(boolean commit)This action sets the auto-commit behavior of the database.
Update(Libraries.Data.Database.Update query)This takes an Update query and sends it to the database and returns the result.

Actions Documentation

Close()

This action closes the active connection to the database. If there are any uncommited changes they will be commited.

Example Code

use Libraries.Data.Database.Database

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    output db:IsConnected()

    db:Close()

    output db:IsConnected()

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.

Example Code

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

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    db:SetAutoCommit(false)

    Update update
    update:SetTable("tasks")
    update:Add("priority", 1)
    update:Filter("priority = 4")

    db:Update(update)

    db:Commit()

Compare(Libraries.Language.Object object)

This action compares two object hash codes and returns an integer. The result is larger if this hash code is larger than the object passed as a parameter, smaller, or equal. In this case, -1 means smaller, 0 means equal, and 1 means larger. This action was changed in Quorum 7 to return an integer, instead of a CompareResult object, because the previous implementation was causing efficiency issues.

Example Code

Object o
        Object t
        integer result = o:Compare(t) //1 (larger), 0 (equal), or -1 (smaller)

Parameters

Return

integer: The Compare result, Smaller, Equal, or Larger.

Connect(Libraries.Data.Database.Connection connection)

Takes a connection object used internally to connect to a database server. A connection object holds the user and location information. If the port of the database server is 3306 this action does not need to used an the other Connect action with more parameter removes the need to create a Connection object.

Example Code

use Libraries.Data.Database.Database
    use Libraries.Data.Database.Connection

    Database db

    Connection connection
    connection:SetLocation("localhost")
    connection:SetDatabase("myDB")
    connection:SetUsername("user2")
    connection:SetPassword("myPassword")
    db:Connect(connection)

Parameters

Connect(text location, text database, text username, text password)

This action attempts to create a connection to a database using the given parameters. If the connection is made successfully IsConnected will return true.

Example Code

use Libraries.Data.Database.Database

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    output db:IsConnected()

Parameters

ConvertResultToDataFrame(Libraries.Data.Database.QueryResult result)

This action takes a QueryResult and converts it to a DataFrame. The conversion will try to have the columns of the DataFrame be the same type as returned by the result.

Example Code

use Libraries.Data.Database.Database

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    db:ConvertResultToDataFrame(db:Find("myDB_table"))

Parameters

Return

Libraries.Compute.Statistics.DataFrame: Returns a DataFrame containing the results of the query

CreateTable(Libraries.Data.Database.CreateTable creation)

This action takes a CreateTable query and sends it to the database. If successful the metadata will be updated to include the new table. The CreateTable query adds new tables to the database. For more information on creating tables there is documentation in the CreateTable class.

Example Code

use Libraries.Data.Database.Database
    use Libraries.Data.Database.CreateTable

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    CreateTable create
    create:SetTable("newTable")
    create:AddIntegerColumn("id")
    db:CreateTable(create)

Parameters

CreateView(Libraries.Data.Database.Find query, text name)

This action will create a view on the database from the passed Find query.

Example Code

use Libraries.Data.Database.Database
    use Libraries.Data.Database.Find

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    Find find
    find:SetTable("myDB_table")
    find:AddColumn("id")

    db:CreateView(findQuery, "id_view")

Parameters

CreateView(Libraries.Data.Database.CreateView view)

This action takes a CreateView query and sends it to the database. If successful the metadata will be updated to include the new view. Creating a view typically consists of setting a Find query so the other helper function can simplify adding a view.

Example Code

use Libraries.Data.Database.Database
    use Libraries.Data.Database.CreateView
    use Libraries.Data.Database.Find

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    Find find
    find:SetTable("myDB_table")
    find:AddColumn("id")

    CreateView view
    view:SetQuery(find)
    view:SetName("id_view")

    db:CreateView(view)

Parameters

Delete(Libraries.Data.Database.Delete query)

This takes an Delete query and sends it to the database and returns the result. A Delete removes rows from a database table based on a given filter and then returns the number of rows deleted. A delete at minimum needs a table and a filter so there are helper functions in this Database class to make it easier to run a delete but using an object is also accepted. For more information on the deleting there is documentation on the Delete query.

Example Code

use Libraries.Data.Database.Database
    use Libraries.Data.Database.Delete

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    Delete delete
    delete:SetTable("tasks")
    delete:Filter("priority = 4")
    db:Delete(delete)

Parameters

Return

integer: Returns the number of rows affected by the Delete

Delete(text table, text filter)

This action runs a delete query where rows are removed from a table if the columns of the row make the filter true.

Example Code

use Libraries.Data.Database.Database

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    integer rowsAffected = db:Delete("myDB_table", "id = 100")

Parameters

Return

integer: Returns the number of rows affected by this delete

DropTable(Libraries.Data.Database.DropTable drop)

This action takes a DropTable query and sends it to the database. DropTable is used to remove tables or to empty the data out of a table. If successful the metadata will be updated to reflect the changes. Most of what can be through a DropTable query can be done through other actions in the class such as RemoveTable().

Example Code

use Libraries.Data.Database.Database
    use Libraries.Data.Database.DropTable

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    DropTable drop
    drop:SetTable("oldTable")
    db:DropTable(drop)

Parameters

EditTable(Libraries.Data.Database.EditTable edit)

This action takes a EditTable query and sends it to the database. The EditTable query allows for changes such as table renaming, or the adding and modifying of columns in a table. If successful the metadata will be updated to reflect any changes.

Example Code

use Libraries.Data.Database.Database
    use Libraries.Data.Database.EditTable

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    EditTable edit
    db:EditTable(edit)

Parameters

EmptyTable(text table)

This action removes all rows in a table. The table will still appear in the database but all rows of data will be gone.

Example Code

use Libraries.Data.Database.Database

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    db:EmptyTable("myDB_table")

Parameters

Equals(Libraries.Language.Object object)

This action determines if two objects are equal based on their hash code values.

Example Code

use Libraries.Language.Object
        use Libraries.Language.Types.Text
        Object o
        Text t
        boolean result = o:Equals(t)

Parameters

Return

boolean: True if the hash codes are equal and false if they are not equal.

Find(text table, text filter)

This action runs a find based on the given parameters and returns the QueryResult. This action will return all columns and rows from a database table where the filter expression is true.

Example Code

use Libraries.Data.Database.Database
    use Libraries.Data.Database.QueryResult

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    QueryResult result = db:Find("myDB_table", "id > 200")

Parameters

Return

Libraries.Data.Database.QueryResult: Returns a QueryResult containing the results of the query

Find(text table)

This action runs a find based on the given parameters and returns the QueryResult. This action will return all columns and rows from a database table.

Example Code

use Libraries.Data.Database.Database
    use Libraries.Data.Database.QueryResult

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    QueryResult result = db:Find("myDB_table")

Parameters

Return

Libraries.Data.Database.QueryResult: Returns a QueryResult containing the results of the query

Find(Libraries.Containers.Array<text> columns, text table, text filter)

This action runs a find based on the given parameters and returns the QueryResult. This action will return all columns and rows from a database table that match an expression if one is given and are of the column specified.

Example Code

use Libraries.Data.Database.Database
    use Libraries.Data.Database.QueryResult
    use Libraries.Containers.Array

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    Array<text> columns
    columns:Add("id")
    columns:Add("roomNumber")
    QueryResult result = db:Find(columns, "myDB_table", "id > 100")

Parameters

Return

Libraries.Data.Database.QueryResult: Returns a DataFrame containing the results of the query

Find(Libraries.Containers.Array<text> columns, text table)

This action runs a find based on the given parameters and returns the QueryResult. This action will return all columns and rows from a database table that match an expression if one is given and are of the column specified.

Example Code

use Libraries.Data.Database.Database
    use Libraries.Data.Database.QueryResult
    use Libraries.Containers.Array

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    Array<text> columns
    columns:Add("id")
    columns:Add("roomNumber")
    QueryResult result = db:Find(columns, "myDB_table")

Parameters

Return

Libraries.Data.Database.QueryResult: Returns a QueryResult containing the results of the query

Find(Libraries.Data.Database.Find query)

This takes a Find query object and builds a query to send to the database. A Find query is used to grab data from the database. When interacting with a database a Find is usually the most common of type of query to use.

Example Code

use Libraries.Data.Database.Database
    use Libraries.Data.Database.Find

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    Find query
    query:SetTable("employees")
    query:AddColumn("id")
    QueryResult result = db:Find(query)

Parameters

Return

Libraries.Data.Database.QueryResult: Returns a query result which for a Find will be the rows that matched with the query.

FindAsDataFrame(text table)

This action runs a find based on the given parameter and returns the result as a DataFrame. This action will return all columns and rows from a database table.

Example Code

use Libraries.Data.Database.Database
    use Libraries.Compute.Statistics.DataFrame

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    DataFrame frame = db:FindAsDataFrame("myDB_table")

    output frame:ToText()

Parameters

Return

Libraries.Compute.Statistics.DataFrame: Returns a DataFrame containing the results of the query

FindAsDataFrame(Libraries.Data.Database.Find query)

This action runs a find query and returns the result as a DataFrame.

Example Code

use Libraries.Data.Database.Database
    use Libraries.Data.Database.Find
    use Libraries.Compute.Statistics.DataFrame

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    Find find
    find:SetTable("myDB_table")

    DataFrame frame = db:FindAsDataFrame(find)

    output frame:ToText()

Parameters

Return

Libraries.Compute.Statistics.DataFrame: Returns a DataFrame containing the results of the query

FindAsDataFrame(text table, text filter)

This action runs a find based on the given parameters and returns the result as a DataFrame. This action will return all columns and rows from a database table where the filter expression is true.

Example Code

use Libraries.Data.Database.Database
    use Libraries.Compute.Statistics.DataFrame

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    DataFrame frame = db:FindAsDataFrame("myDB_table", "roomNumber = 1001")
    output frame:ToText()

Parameters

Return

Libraries.Compute.Statistics.DataFrame: Returns a DataFrame containing the results of the query

FindCrossJoin(Libraries.Containers.Array<text> columns, text table, text joinedTable, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Cross Join is different from the other joins in that there is no join condition. A Cross Join will return the Cartesian product of rows from the two tables. In other words, every row from the first table will be combined with every row from the second table and that will be returned. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("StoreName") columns:Add("ProductName") QueryResult result = db:FindCrossJoin(columns, "Products", "Stores", "price >= 4.99")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindCrossJoin(text table, text joinedTable, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Cross Join is different from the other joins in that there is no join condition. A Cross Join will return the Cartesian product of rows from the two tables. In other words, every row from the first table will be combined with every row from the second table and that will be returned. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindCrossJoin("Products", "Stores", "price >= 4.99")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindCrossJoin(text table, text joinedTable)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Cross Join is different from the other joins in that there is no join condition. A Cross Join will return the Cartesian product of rows from the two tables. In other words, every row from the first table will be combined with every row from the second table and that will be returned. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindCrossJoin("Products", "Stores")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindInnerJoin(Libraries.Containers.Array<text> columns, text table, text joinedTable, text joinCondition, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. An Inner Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult use Libraries.Containers.Array Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindInnerJoin(columns, "Orders", "Customers", "Orders:CustomerID = Customers:CustomerID", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindInnerJoin(text table, text joinedTable, text joinCondition)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. An Inner Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindInnerJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindInnerJoin(text table, text joinedTable, text joinCondition, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. An Inner Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindInnerJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindInnerJoinUsingColumn(text table, text joinedTable, text column)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a condition it takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. An Inner Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindInnerJoinUsingColumn("Orders", "Customers", "CustomerID")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindInnerJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a condition it takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. An Inner Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult use Libraries.Containers.Array Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindInnerJoinUsingColumn(columns, "Orders", "Customers", "CustomerID")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindInnerJoinUsingColumn(text table, text joinedTable, text column, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a condition it takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. An Inner Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindInnerJoinUsingColumn("Orders", "Customers", "CustomerID", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindInnerJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a condition takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. An Inner Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult use Libraries.Containers.Array Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindInnerJoinUsingColumn(columns, "Orders", "Customers", "CustomerID", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindLeftJoin(text table, text joinedTable, text joinCondition, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Left Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the first table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindLeftJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindLeftJoin(text table, text joinedTable, text joinCondition)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Left Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the first table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindLeftJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindLeftJoin(Libraries.Containers.Array<text> columns, text table, text joinedTable, text joinCondition, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Left Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the first table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindLeftJoin(columns, "Orders", "Customers", "Orders:CustomerID = Customers:CustomerID", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindLeftJoinUsingColumn(text table, text joinedTable, text column)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a conditions takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. A Left Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the first table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindLeftJoinUsingColumn("Orders", "Customers", "CustomerID")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindLeftJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a conditions takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. A Left Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the first table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindLeftJoinUsingColumn(columns, "Orders", "Customers", "CustomerID")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindLeftJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a conditions takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. A Left Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the first table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindLeftJoinUsingColumn(columns, "Orders", "Customers", "CustomerID", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindLeftJoinUsingColumn(text table, text joinedTable, text column, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a conditions takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. A Left Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the first table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindLeftJoinUsingColumn("Orders", "Customers", "CustomerID", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindRightJoin(text table, text joinedTable, text joinCondition)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Right Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the second table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindRightJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindRightJoin(Libraries.Containers.Array<text> columns, text table, text joinedTable, text joinCondition, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Right Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the second table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindRightJoin(columns, "Orders", "Customers", ""Orders:CustomerID = Customers:CustomerID"", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindRightJoin(text table, text joinedTable, text joinCondition, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Right Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the second table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindRightJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindRightJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a conditions takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. A Right Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the second table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindRightJoinUsingColumn("Orders", "Customers", "CustomerID", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindRightJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a conditions takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. A Right Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the second table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindRightJoinUsingColumn(columns, "Orders", "Customers", "CustomerID")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindRightJoinUsingColumn(text table, text joinedTable, text column, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a conditions takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. A Right Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the second table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindRightJoinUsingColumn("Orders", "Customers", "CustomerID", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindRightJoinUsingColumn(text table, text joinedTable, text column)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a conditions takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. A Right Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the second table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindRightJoinUsingColumn("Orders", "Customers", "CustomerID")

Parameters

Return

Libraries.Data.Database.QueryResult:

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 like an auto save feature for documents but for databases.

Example Code

use Libraries.Data.Database.Database

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    output db:GetAutoCommit()

Return

boolean: Returns true if auto commit is on and false otherwise.

GetDatabaseMetaData()

Returns the DatabaseMetaData object which contains more information about the currently connected database such as what tables exist and how many tables there are.

Example Code

use Libraries.Data.Database.Database
    use Libraries.Data.Database.DatabaseMetaData

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    DatabaseMetaData meta = db:GetDatabaseMetaData()
    output "Number of tables: " + meta:GetSize()

Return

Libraries.Data.Database.DatabaseMetaData: Returns a DatabaseMetaData object filled with information from the connected database.

GetHashCode()

This action gets the hash code for an object.

Example Code

Object o
        integer hash = o:GetHashCode()

Return

integer: The integer hash code of the object.

Insert(Libraries.Data.Database.Insert query)

This takes an Insert query and sends it to the database and returns the result. An Insert allows for adding more rows to a table and if the insert was successful the database will return the number of rows affected. For more information and how to do an insert look into the documentation of the Insert query class.

Example Code

use Libraries.Data.Database.Database
    use Libraries.Data.Database.Insert

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    Insert insert
    insert:SetTable("tasks")
    insert:Add("title", "Come up with Resolution")
    insert:Add("priority", 4)
    insert:Add("start_date", "2021-12-31")
    integer rowsAffected = db:Insert(insert)

    output "Inserted " + rowsAffected + "row(s)"

Parameters

Return

integer: Returns the number of rows that were affected

IsConnected()

Returns whether or not this database object is currently connected to a database

Example Code

use Libraries.Data.Database.Database

    Database db
    output db:IsConnected()

Return

boolean: Returns true if currently connected to a database

RemoveTable(text table)

This action removes a table on the database. The table will no longer appear on the database along with any data that was on it.

Example Code

use Libraries.Data.Database.Database

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    db:RemoveTable("myDB_table")

Parameters

RenameTable(text table, text newName)

This action renames a table on the database.

Example Code

use Libraries.Data.Database.Database

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    db:RenameTable("myDB_table", "tableWithNewName")

Parameters

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.

Example Code

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

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    db:SetAutoCommit(false)

    Update update
    update:SetTable("tasks")
    update:Add("priority", 1)
    update:Filter("priority = 4")

    integer rows = db:Update(update)

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

SetAutoCommit(boolean commit)

This action sets the auto-commit behavior of the database. Auto-commit can be thought like an auto save 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.

Example Code

use Libraries.Data.Database.Database

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    db:SetAutoCommit(false)

Parameters

Update(Libraries.Data.Database.Update query)

This takes an Update query and sends it to the database and returns the result. An Update allows for changing the data in rows that already exist on the database. For more information and how to do an Update look into the documentation of the Update class.

Example Code

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

    Database db
    db:Connect("localhost","myDB","root","superSecretPassword")

    Update update
    update:SetTable("tasks")
    update:AddExpression("priority", "priority + 1")
    update:Filter("priority >= 4 ")

    integer rowsAffected = db:Update(update)
    output rowsAffected + "row(s) updated"

Parameters

Return

integer: Returns the number of rows affected by the Update