Working synchronously with a local SQL database
Content
Created
24 February 2008
Requirements
Prerequisite knowledge
General experience building HTML-based applications is suggested. For more details on getting started with this Quick Start, refer to Building the Quick Start sample applications with HTML.
User level
Intermediate
Required products
Sample files
The application is intentionally simple. It creates a database in the computer's memory, creates a table in that database, and adds some data to the database. Clicking on the Load data button then retrieves the data and displays it on the screen (see Figure 1). Other than loading data when the button is clicked and displaying it on the screen, there is no additional user interaction available. This is intentional, to focus the application entirely on the database operations. This sample application demonstrates the following Adobe AIR features:
- Connecting to a local SQL database using synchronous execution mode
- Creating and executing SQL statements synchronously:
- Creating a table in the database
- Inserting data into the database table
- Retrieving data from the database table and displaying that data on the screen
Note: This is a sample application provided, as is, for instructional purposes.
Understanding the code
This section does not describe all of the HTML mark-up for the file—only the AIR-specific JavaScript code.
Connecting to a local SQL database
The init() method is called when the application finishes loading, as defined in the body tag’s onload event handler. Within this method, a SQLConnection instance name conn is created. (The variable conn is declared outside the method so that it is available to all the code in the application.) This SQLConnection object establishes the connection to a database, and is used by other objects to perform operations on that specific database. Once the SQLConnection instance is created the open() method is called to open the connection to the database in synchronous execution mode.
try
{
conn.open(null);
}
catch (error)
{
statustext.innerText = "Error opening database";
air.trace("error.message:", error.message);
air.trace("error.details:", error.details);
return;
}
createTable();
In this case null is passed as an argument to the open() method, indicating that the runtime will create a database in the computer’s memory rather than in a disk location. Alternatively, you could specify a file location (using a File instance). The runtime would then open the database file at that location (creating it first if it doesn’t exist). The code to do that would look like this:
var dbFile = air.File.applicationStorageDirectory.resolvePath("DBSample.db");
conn.open(dbFile);
air.File.applicationStorageDirectory points to the AIR application store directory, which is uniquely defined for each AIR application.
Because the open() call is surrounded in a try..catch block, if the call fails the init() method returns and execution ends. Assuming the open() operation succeeds and the database connection opens, the createTable() method is called to do the work of creating a table in the database.
Creating a table in the database
The createTable() method uses a SQLStatement instance to execute a SQL command against the database that was opened in the init() method. The specific SQL command creates a table in the database named “employees,” with four columns. Here is a breakdown of the code and what it does:
- Creates a SQLStatement instance named
createStmt:
createStmt = new air.SQLStatement();
- Specifies that the statement executes on the database that’s connected through the SQLConnection instance
conn:
createStmt.sqlConnection = conn;
- Defines the SQL statement text to create a database table. The table is named “employees.” It has four columns: “empId,” “firstName,” “lastName,” and “salary.”
var sql = "";
sql += "CREATE TABLE IF NOT EXISTS employees ( ";
sql += "empId INTEGER PRIMARY KEY AUTOINCREMENT, ";
sql += "firstName TEXT, ";
sql += "lastName TEXT, ";
sql += "salary NUMERIC CHECK (salary >= 0) DEFAULT 0";
sql += ")";
createStmt.text = sql;
- Executes the statement (surrounded in a
try..catchblock to determine if an error occurs):
createStmt.execute();
Assuming that the statement runs successfully, the “employees” table is created and the addData() method is called to perform the next step in the process, adding data into the newly created table.
Inserting data into the database table
Like the createTable() method, the addData() method creates a SQLStatement, in this case to insert a row of data into the “employees” table in the database. The application inserts two rows of data, using two different SQLStatement instances (insertStmt and insertStmt2):
statustext.innerText = "Adding data to table";
insertStmt = new air.SQLStatement();
insertStmt.sqlConnection = conn;
var sql = "";
sql += "INSERT INTO employees (firstName, lastName, salary) ";
sql += "VALUES ('Bob', 'Smith', 8000)";
insertStmt.text = sql;
insertStmt2 = new air.SQLStatement();
insertStmt2.sqlConnection = conn;
var sql2 = "";
sql2 += "INSERT INTO employees (firstName, lastName, salary) ";
sql2 += "VALUES ('John', 'Jones', 8200)";
insertStmt2.text = sql2;
try
{
insertStmt.execute();
insertStmt2.execute();
}
catch (error)
{
statustext.innerText = "Error inserting data";
air.trace("INSERT error:", error);
air.trace("error.message:", error.message);
air.trace("error.details:", error.details);
return;
}
statustext.innerText = "Ready to load data";
If both statements finish executing without errors, the status bar text (statustext.innerText) is updated to read “Ready to load data” and the application is ready to retrieve the data from the database and display it on the screen.
Retrieving data from the database table
Like creating a table and inserting data into the table, retrieving data from a table is carried out by creating a SQLStatement instance with a SQL SELECT statement as the SQLStatement instance’s text property. The following code, from the getData() method, creates and executes the SELECT statement that retrieves all the rows from the “employees” table:
selectStmt = new air.SQLStatement();
selectStmt.sqlConnection = conn;
var sql = "SELECT empId, firstName, lastName, salary FROM employees";
selectStmt.text = sql;
try
{
selectStmt.execute();
}
catch (error)
{
statustext.innerText = "Error loading data";
air.trace("SELECT error:", error);
air.trace("error.message:", error.message);
air.trace("error.details:", error.details);
return;
}
renderResult();
When the SELECT statement finishes executing the renderResult() method is called. In renderResult(), the result data that is retrieved by the SELECT statement is accessed by calling the SQLStatement instance’s getResult() method. Calling getResult() returns a SQLResult instance that is stored in the variable result; the actual result rows are contained in an array in its data property. The results are used to generate rows for an HTML table by looping over the rows of data in the data array and then over the columns in each row, generating tr and td elements for the rows and cells and appending them to a table named resultsGrid (which is already defined in the HTML):
function renderResult()
{
statustext.innerText = "Data loaded";
var result = selectStmt.getResult();
var row;
var cell;
var tbl = document.getElementById("resultsGrid");
tbl.innerHTML = "";
var numRows = result.data.length;
for (var i = 0; i < numRows; i++)
{
if (i == 0)
{
// add the table header
row = document.createElement("tr");
for (col in result.data[i])
{
cell = document.createElement("th");
cell.innerText = col;
row.appendChild(cell);
}
tbl.appendChild(row);
}
// iterate over the columns in the result row object
row = document.createElement("tr");
for (col in result.data[i])
{
cell = document.createElement("td");
cell.innerText = result.data[i][col];
row.appendChild(cell);
}
tbl.appendChild(row);
}
}
