Back to Concepts and HOW TOs

Contents 1 Description 2 Prerequisites 3 Procedure 3.1 Logging in to a Database at Runtime 3.1.1 Entering Config Info Directly into the Config Object 3.1.2 Using the Config Settings from a SQL Project 3.2 Retrieving Data from a Database and Storing it in a Collection 4 Error Handling 5 Examples 6   7 Next Steps 8 Tags

[edit] Description

This document describes how to log in to a database at runtime, retrieve the data from a table and store the data in a collection of objects.

[edit] Prerequisites

Connecting to an SQL Database
Creating a Class to Contain Row Data from an SQL Table

[edit] Procedure

There are two Runtime Types named MySQL and Postgres that both inherit from SQLConnection. The SQLConnection superclass contains fields and functions for logging into and executing SQL queries on a database. You create an object of the type MySQL or Postgres depending on which type of database you are connecting to and, the inherited fields and functions will then know how to communicate with the MySQL or Postgres database.

[edit] Logging in to a Database at Runtime

In the class where you want to log in to your database, you will need to add a field of type MySQL or Postgres. For this example, we will use MySQL. You can then use the login function, inherited from SQLConnection, to log in to your database. The SQLConnection.login function has two arguments, Config info and boolean success. Config is a Runtime Type which you need to populate with the credentials for your database. Two ways of populating the Config are shown below. second, use the Config settings from an SQL Project.

[edit] Entering Config Info Directly into the Config Object

You can enter all of the values for connecting to your database directly into the Config object, passed directly to the login function.

You can use the onCreate function override to log in to the database as your application loads.

In the following screen shot:

1. We have a field of type MySQL named db.
2. In the onCreate function override, we are calling MySQL's inherited function, login.
3. For the Config info argument to the login function, we are creating a new Object.
4. By clicking the detail button [...] next to new Config in the Property Editor, we bring up the Edit Object dialog where we can set the credentials for connecting to a database.
    

[edit] Using the Config Settings from a SQL Project

You can use the Config settings from a SQL Project you previously created.

In the following screen shot:

1. We have a field of type MySQL named db.
2. In the onCreate function override, we are calling MySQL's inherited function, login.
3. We have created a var of Type, SQLDatasource and set its Default Value to point to the MySQL Datasource in our MySQL_DB project.
4. For the Config info argument to the login function, we are using the config field in our SQLDatasource var.
   

[edit] Retrieving Data from a Database and Storing it in a Collection

In Creating a Class to Contain Row Data from an SQL Table , we described how to create a Task class to contain row data from a task table in your database. To contain more than one row of data at a time, we can use a collection of objects of type Task, Collection(Task) task.

In the following screen shot:

1. We are making a function call using SQLConnection's queryObjectResults function to retrieve all the tasks from the task table.
2. The queryObjectResults function maps each row to an instance of the Task class.
   1. To set the Class objectType argument to the Task class, select Type from the drop-down box.
   2. Click the detail button [...]. In the dialog box that's displayed, choose the Task class.
3. The queryObjectResults function will return a collection of objects of type Task.
   

[edit] Error Handling

 

[edit] Examples

[edit]  

[edit] Next Steps

Adding CRUD Operations to a Class
Using SQL Query Escaping
Debugging SQL

[edit] Tags