Inserting data

To add data to a table in a database, you create and execute a SQLStatement instance whose text is a SQL INSERT statement.

The following example uses a SQLStatement instance to add a row of data to the already-existing employees table. This example demonstrates inserting data using asynchronous execution mode. Note that this listing assumes that there is a SQLConnection instance named conn that has already been instantiated and is already connected to a database. It also assumes that the “employees” table has already been created.

// ... create and open the SQLConnection instance named conn ... 
// create the SQL statement 
var insertStmt = new air.SQLStatement(); 
insertStmt.sqlConnection = conn; 
// define the SQL text 
var sql =  
    "INSERT INTO employees (firstName, lastName, salary) " +  
    "VALUES ('Bob', 'Smith', 8000)"; 
insertStmt.text = sql; 
// register listeners for the result and failure (status) events 
insertStmt.addEventListener(air.SQLEvent.RESULT, insertResult); 
insertStmt.addEventListener(air.SQLErrorEvent.ERROR, insertError); 
// execute the statement 
insertStmt.execute(); 
function insertResult(event) 
{ 
    air.trace("INSERT statement succeeded"); 
} 
function insertError(event) 
{ 
    air.trace("Error message:", event.error.message); 
    air.trace("Details:", event.error.details); 
}

The following example adds a row of data to the already-existing employees table, using synchronous execution mode. Note that this listing assumes that there is a SQLConnection instance named conn that has already been instantiated and is already connected to a database. It also assumes that the “employees” table has already been created.

// ... create and open the SQLConnection instance named conn ... 
// create the SQL statement 
var insertStmt = new air.SQLStatement(); 
insertStmt.sqlConnection = conn; 
// define the SQL text 
var sql =  
    "INSERT INTO employees (firstName, lastName, salary) " +  
    "VALUES ('Bob', 'Smith', 8000)"; 
insertStmt.text = sql; 
try 
{ 
    // execute the statement 
    insertStmt.execute(); 
    air.trace("INSERT statement succeeded"); 
} 
catch (error) 
{ 
    air.trace("Error message:", error.message); 
    air.trace("Details:", error.details); 
}

Often after inserting a row of data into a table, your code needs to know a database-generated primary key or row identifier value for the newly inserted row. For example, once you insert a row in one table, you might want to add rows in a related table. In that case you would want to insert the primary key value as a foreign key in the related table. The primary key of a newly inserted row can be retrieved using the SQLResult object generated by the statement execution. This is the same object that’s used to access result data after a SELECT statement is executed. As with any SQL statement, when the execution of an INSERT statement completes the runtime creates a SQLResult instance. You access the SQLResult instance by calling the SQLStatement object’s getResult() method if you’re using an event listener or if you’re using synchronous execution mode. Alternatively, if you’re using asynchronous execution mode and you pass a Responder instance to the execute() call, the SQLResult instance is passed as an argument to the result handler function. In any case, the SQLResult instance has a property, lastInsertRowID, that contains the row identifier of the most-recently inserted row if the executed SQL statement is an INSERT statement.

The following example demonstrates accessing the primary key of an inserted row in asynchronous execution mode:

insertStmt.text = "INSERT INTO ..."; 
insertStmt.addEventListener(air.SQLEvent.RESULT, resultHandler); 
insertStmt.execute(); 
function resultHandler(event) 
{ 
    // get the primary key 
    var result = insertStmt.getResult(); 
    var primaryKey = result.lastInsertRowID; 
    // do something with the primary key 
}

The following example demonstrates accessing the primary key of an inserted row in synchronous execution mode:

insertStmt.text = "INSERT INTO ..."; 
insertStmt.addEventListener(air.SQLEvent.RESULT, resultHandler); 
try 
{ 
    insertStmt.execute(); 
    // get the primary key 
    var result = insertStmt.getResult(); 
    var primaryKey = result.lastInsertRowID; 
    // do something with the primary key 
} 
catch (error) 
{ 
    // respond to the error 
}

Note that the row identifier may or may not be the value of the column that is designated as the primary key column in the table definition, according to the following rule:

• If the table is defined with a primary key column whose affinity (column data type) is INTEGER, the lastInsertRowID property contains the value that was inserted into that row (or the value generated by the runtime if it’s an AUTOINCREMENT column).

• If the table is defined with multiple primary key columns (a composite key) or with a single primary key column whose affinity is not INTEGER, behind the scenes the database generates a row identifier value for the row. That generated value is the value of the lastInsertRowID property.

• The value is always the row identifier of the most-recently inserted row. If an INSERT statement causes a trigger to fire which in turn inserts a row, the lastInsertRowID property contains the row identifier of the last row inserted by the trigger rather than the row created by the INSERT statement. Consequently, if you want to have an explicitly defined primary key column whose value is available after an INSERT command through the SQLResult.lastInsertRowID property, the column must be defined as an INTEGER PRIMARY KEY column. Note, however, that even if your table does not include an explicit INTEGER PRIMARY KEY column, it is equally acceptable to use the database-generated row identifier as a primary key for your table in the sense of defining relationships with related tables. The row identifier column value is available in any SQL statement by using one of the special column names ROWID, _ROWID_, or OID. You can create a foreign key column in a related table and use the row identifier value as the foreign key column value just as you would with an explicitly declared INTEGER PRIMARY KEY column. In that sense, if you are using an arbitrary primary key rather than a natural key, and as long as you don’t mind the runtime generating the primary key value for you, it makes little difference whether you use an INTEGER PRIMARY KEY column or the system-generated row identifier as a table’s primary key for defining a foreign key relationship with between two tables.

For more information about primary keys and generated row identifiers, see the sections titled ”CREATE TABLE” and “Expressions“ in the appendix ”SQL support in local databases“ in the Adobe AIR Language Reference for HTML Developers.