Creating and modifying a database

To create a database file, you first create a SQLConnection instance. You call its open() method to open it in synchronous execution mode, or its openAsync() method to open it in asynchronous execution mode. The open() and openAsync() methods are used to open a connection to a database. If you pass a File instance that refers to a non-existent file location for the reference parameter (the first parameter), the open() or openAsync() method creates a database file at that file location and open a connection to the newly created database.

Whether you call the open() method or the openAsync() method to create a database, the database file’s name can be any valid filename, with any filename extension. If you call the open() or openAsync() method with null for the reference parameter, a new in-memory database is created rather than a database file on disk.

The following code listing shows the process of creating a database file (a new database) using asynchronous execution mode. In this case, the database file is saved in the Pointing to the application storage directory , with the filename “DBSample.db”:

// Include AIRAliases.js to use air.* shortcuts 
     
var conn = new air.SQLConnection(); 
     
conn.addEventListener(air.SQLEvent.OPEN, openHandler); 
conn.addEventListener(air.SQLErrorEvent.ERROR, errorHandler); 
     
// The database file is in the application storage directory 
var folder = air.File.applicationStorageDirectory; 
var dbFile = folder.resolvePath("DBSample.db"); 
     
conn.openAsync(dbFile); 
     
function openHandler(event) 
{ 
    air.trace("the database was created successfully"); 
} 
     
function errorHandler(event) 
{ 
    air.trace("Error message:", event.error.message); 
    air.trace("Details:", event.error.details); 
}

To execute operations synchronously, when you open a database connection with the SQLConnection instance, call the open() method. The following example shows how to create and open a SQLConnection instance that executes its operations synchronously:

// Include AIRAliases.js to use air.* shortcuts 
     
var conn = new air.SQLConnection(); 
     
// The database file is in the application storage directory 
var folder = air.File.applicationStorageDirectory; 
var dbFile = folder.resolvePath("DBSample.db"); 
     
try 
{ 
    conn.open(dbFile); 
    air.trace("the database was created successfully"); 
} 
catch (error) 
{ 
    air.trace("Error message:", error.message); 
    air.trace("Details:", error.details); 
}

Creating a table in a database involves executing a SQL statement on that database, using the same process that you use to execute a SQL statement such as SELECT , INSERT , and so forth. To create a table, you use a CREATE TABLE statement, which includes definitions of columns and constraints for the new table. For more information about executing SQL statements, see Working with SQL statements .

The following example demonstrates creating a table named “employees” in an existing database file, using asynchronous execution mode. Note that this code assumes there is a SQLConnection instance named conn that is already instantiated and is already connected to a database.

// Include AIRAliases.js to use air.* shortcuts 
     
// ... create and open the SQLConnection instance named conn ... 
     
var createStmt = new air.SQLStatement(); 
createStmt.sqlConnection = conn; 
     
var sql =  
    "CREATE TABLE IF NOT EXISTS employees (" +  
    "    empId INTEGER PRIMARY KEY AUTOINCREMENT, " +  
    "    firstName TEXT, " +  
    "    lastName TEXT, " +  
    "    salary NUMERIC CHECK (salary > 0)" +  
    ")"; 
createStmt.text = sql; 
     
createStmt.addEventListener(air.SQLEvent.RESULT, createResult); 
createStmt.addEventListener(air.SQLErrorEvent.ERROR, createError); 
     
createStmt.execute(); 
     
function createResult(event) 
{ 
    air.trace("Table created"); 
} 
     
function createError(event) 
{ 
    air.trace("Error message:", event.error.message); 
    air.trace("Details:", event.error.details); 
}

The following example demonstrates how to create a table named “employees” in an existing database file, using synchronous execution mode. Note that this code assumes there is a SQLConnection instance named conn that is already instantiated and is already connected to a database.

// Include AIRAliases.js to use air.* shortcuts 
     
// ... create and open the SQLConnection instance named conn ... 
     
var createStmt = new air.SQLStatement(); 
createStmt.sqlConnection = conn; 
     
var sql =  
    "CREATE TABLE IF NOT EXISTS employees (" +  
    "    empId INTEGER PRIMARY KEY AUTOINCREMENT, " +  
    "    firstName TEXT, " +  
    "    lastName TEXT, " +  
    "    salary NUMERIC CHECK (salary > 0)" +  
    ")"; 
createStmt.text = sql; 
     
try 
{ 
    createStmt.execute(); 
    air.trace("Table created"); 
} 
catch (error) 
{ 
    air.trace("Error message:", error.message); 
    air.trace("Details:", error.details); 
}