Packageflash.data
Classpublic class SQLStatement
InheritanceSQLStatement Inheritance EventDispatcher Inheritance Object

Language version: ActionScript 3.0
Runtime version: AIR 1.0

A SQLStatement instance is used to execute a SQL statement against a local SQL database that is open through a SQLConnection instance.

A SQLStatement instance is linked to a SQLConnection instance by setting the SQLConnection instance as the value of the SQLStatement instance's sqlConnection property. The text property is populated with the actual text of the SQL statement to execute. If necessary, SQL statement parameter values are specified using the parameters property, and the statement is carried out by calling the execute() method.

For a complete description of the SQL dialect supported in local SQL databases, see the appendix SQL support in local databases.

In asynchronous execution mode, the execute() and next() methods are executed in the background, and the runtime dispatches events to registered event listeners or to a specified Responder instance when the operations complete or fail. In synchronous mode, the methods are executed on the main application thread, meaning that no other code executes until the database operations are completed. In addition, in synchronous mode if the methods fail the runtime throws an exception rather than dispatching an error event.

See also

flash.data.SQLConnection


Public Properties
 PropertyDefined by
 Inheritedconstructor : Object
A reference to the class object or constructor function for a given object instance.
Object
  executing : Boolean
[read-only] Indicates whether the statement is currently executing.
SQLStatement
  itemClass : Class
Indicates a class (data type) that is used for each row returned as a result of the statement's execution.
SQLStatement
  parameters : Object
[read-only] Serves as an associative array to which you add values for the parameters specified in the SQL statement's text property.
SQLStatement
 Inheritedprototype : Object
[static] A reference to the prototype object of a class or function object.
Object
  sqlConnection : SQLConnection
The SQLConnection object that manages the connection to the database or databases on which the statement is executed.
SQLStatement
  text : String
The actual SQL text of the statement.
SQLStatement
Public Methods
 MethodDefined by
  
Creates a SQLStatement instance.
SQLStatement
 Inherited
addEventListener(type:String, listener:Function, useCapture:Boolean = false, priority:int = 0, useWeakReference:Boolean = false):void
Registers an event listener object with an EventDispatcher object so that the listener receives notification of an event.
EventDispatcher
  
Cancels execution of this statement.
SQLStatement
  
Clears all current parameter settings.
SQLStatement
 Inherited
Dispatches an event into the event flow.
EventDispatcher
  
execute(prefetch:int = -1, responder:Responder = null):void
Executes the SQL in the text property against the database that is connected to the SQLConnection object in the sqlConnection property.
SQLStatement
  
Provides access to a SQLResult object containing the results of the statement execution, including any result rows from a SELECT statement, and other information about the statement execution for all executed statements.
SQLStatement
 Inherited
Checks whether the EventDispatcher object has any listeners registered for a specific type of event.
EventDispatcher
 Inherited
Indicates whether an object has a specified property defined.
Object
 Inherited
Indicates whether an instance of the Object class is in the prototype chain of the object specified as the parameter.
Object
  
next(prefetch:int = -1, responder:Responder = null):void
Retrieves the next portion of a SELECT statement's result set.
SQLStatement
 Inherited
Indicates whether the specified property exists and is enumerable.
Object
 Inherited
removeEventListener(type:String, listener:Function, useCapture:Boolean = false):void
Removes a listener from the EventDispatcher object.
EventDispatcher
 Inherited
Sets the availability of a dynamic property for loop operations.
Object
 Inherited
Returns the string representation of this object, formatted according to locale-specific conventions.
Object
 Inherited
Returns the string representation of the specified object.
Object
 Inherited
Returns the primitive value of the specified object.
Object
 Inherited
Checks whether an event listener is registered with this EventDispatcher object or any of its ancestors for the specified event type.
EventDispatcher
Events
 EventSummaryDefined by
 Inherited [broadcast event] Dispatched when the Flash Player or AIR application gains operating system focus and becomes active.EventDispatcher
 Inherited [broadcast event] Dispatched when the Flash Player or AIR application operating loses system focus and is becoming inactive.EventDispatcher
   Dispatched when an error occurs during an operation.SQLStatement
   Dispatched when an execute() or next() method call's operation completes successfully.SQLStatement
Property detail
executingproperty
executing:Boolean  [read-only]

Language version: ActionScript 3.0
Runtime version: AIR 1.0

Indicates whether the statement is currently executing.

This property is true if execute() has been called and not all of the results have been returned from the database.

Implementation
    public function get executing():Boolean

See also

itemClassproperty 
itemClass:Class  [read-write]

Language version: ActionScript 3.0
Runtime version: AIR 1.0

Indicates a class (data type) that is used for each row returned as a result of the statement's execution.

This property is intended for use in SWF-based AIR application only. You cannot use it in JavaScript. (ActionScript lets you define custom classes, which you can use with this property.)

By default, each row returned by a SELECT statement is created as an Object instance, with the result set's column names as the names of the properties of the object, and the value of each column as the value of its associated property.

By specifying a class for the itemClass property, each row returned by a SELECT statement executed by this SQLStatement instance is created as an instance of the designated class. Each property of the itemClass instance is assigned the value from the column with the same name as the property.

Any class assigned to this property must have a constructor that does not require any parameters. In addition, the class must have a single property for each column returned by the SELECT statement. It is considered an error if a column in the SELECT list does not have a matching property name in the itemClass class.

Implementation
    public function get itemClass():Class
    public function set itemClass(value:Class):void

See also


Example
The following code demonstrates using the itemClass property to have the runtime create instances of a custom class from SQL SELECT statement results.
// Employee class definition
package
{
    public class Employee
    {
        public var name:String;
        public var ssn:String;
        public var id:uint;
        public override function toString():String
        {
            return "id: "+ id.toString() + " name: " + name + " ssn: " + ssn;
        }
    }
}


// using the Employee class as SQLStatement.itemClass
var conn:SQLConnection;
var dbStatement:SQLStatement;

function init():void
{
    conn = new SQLConnection();
    conn.addEventListener(SQLEvent.OPEN, connOpenHandler);
	
	dbStatement = new SQLStatement();
    dbStatement.sqlConnection = conn;
    dbStatement.text = "SELECT id, name, ssn FROM employees";
    dbStatement.itemClass = Employee;
	
    var dbFile:File = new File(File.separator + "employee.db");
    conn.open(dbFile);
}

function connOpenHandler(event:SQLEvent):void
{
    dbStatement.addEventListener(SQLEvent.RESULT, resultHandler);
    dbStatement.execute();
}

function resultHandler(event:SQLEvent):void
{
    var result:SQLResult = dbStatement.getResult();
    if (result != null)
    {
        var emp:Employee;
		var numRows:int = result.data.length;
        for (var i:int = 0; i < numRows; i++)
        {
            emp = result.data[i];
            trace(emp.toString());
        }
    }
}

parametersproperty 
parameters:Object  [read-only]

Language version: ActionScript 3.0
Runtime version: AIR 1.0

Serves as an associative array to which you add values for the parameters specified in the SQL statement's text property. The array keys are the names of the parameters. If an unnamed parameter is specified in the statement text, its key is the index of the parameter.

Within the text of a SQL statement, a parameter is indicated with one of the following characters: "?", ":", or "@".

The ":" and "@" tokens indicate a named parameter; the characters following the token designate the name of the parameter.

For example, in the following SQL statement, a parameter named firstName is specified using the ":" character:

SELECT FROM employees WHERE firstName = :firstName

The "?" token indicates an indexed (numbered) parameter; each parameter is automatically given an index according to the sequence of parameters in the statement text. Parameter index values are zero based. In other words, the first parameter's index is 0.

Parameters are used to allow for typed substitution of values that are unknown at the time the SQL statement is constructed. The use of parameters is the only way to guarantee the storage class for a value passed in to the database. When parameters are not used, all values are converted from their text representation to a storage class based on the associated column's type affinity. For more information on storage classes and column affinity, see the "Data type support" section in the appendix "SQL support in local databases".

Parameters are also used as a security measure to prevent a malicious technique known as a SQL injection attack. In a SQL injection attack, a user enters SQL code in a user-accessible location (for example, a data entry field). If application code constructs a SQL statement by directly concatenating user input into the SQL text, the user-entered SQL code is executed against the database. The following listing shows an example of concatenating user input into SQL text. Do not use this technique:

	 // assume the variables "username" and "password"
     // contain user-entered data
     var sql:String =
         "SELECT userId " +
         "FROM users " +
         "WHERE username = '" + username + "' " +
         "    AND password = '" + password + "'";
     var statement:SQLStatement = new SQLStatement();
     statement.text = sql;
     
	 // assume the variables "username" and "password"
     // contain user-entered data
     var sql =
         "SELECT userId " +
         "FROM users " +
         "WHERE username = '" + username + "' " +
         "    AND password = '" + password + "'";
     var statement = new air.SQLStatement();
     statement.text = sql;
     

Using statement parameters instead of concatenating user-entered values into a statement's text prevents a SQL injection attack, because the parameter values are treated explicitly as substituted values, rather than becoming part of the literal statement text. The following is the recommended alternative to the previous listing:

	 // assume the variables "username" and "password"
     // contain user-entered data
     var sql:String =
         "SELECT userId " +
         "FROM users " +
         "WHERE username = :username " +
         "    AND password = :password";
     var statement:SQLStatement = new SQLStatement();
     statement.text = sql;
     // set parameter values
     statement.parameters[":username"] = username;
     statement.parameters[":password"] = password;
     
	 // assume the variables "username" and "password"
     // contain user-entered data
     var sql =
         "SELECT userId " +
         "FROM users " +
         "WHERE username = :username " +
         "    AND password = :password";
     var statement = new air.SQLStatement();
     statement.text = sql;
     // set parameter values
     statement.parameters[":username"] = username;
     statement.parameters[":password"] = password;
     

All parameter values must be set before the statement is executed. Parameter values specified in the parameters array are bound (that is, combined with the statement text) when the execute() method is called. Once execute() has been called, any subsequent changes to the values are not applied to the executing statement. However, on a subsequent execute() call the changed values are used. If the statement text includes a parameter that doesn't have a value specified in the parameters property, an error occurs.

To clear all the parameter values from the parameters property, use the clearParameters() method.

Implementation
    public function get parameters():Object

See also


Example
The following example shows the use of a named parameter, :firstName, in a SQL statement.
// employees is a SQLStatement instance
employees.text = "SELECT FROM employees WHERE first = :firstName";
employees.parameters[":firstName"] = "Sam";
employees.execute();

The following example shows the use of an unnamed parameter in a SQL statement.
// employees is a SQLStatement instance
employees.text = "SELECT FROM employees WHERE first = ?";
employees.parameters[0] = "Sam";
employees.execute();

sqlConnectionproperty 
sqlConnection:SQLConnection  [read-write]

Runtime version: AIR 1.0

The SQLConnection object that manages the connection to the database or databases on which the statement is executed.

Implementation
    public function get sqlConnection():SQLConnection
    public function set sqlConnection(value:SQLConnection):void

Throws
Error — When an attempt is made to change the value of this property while the statement is executing.
textproperty 
text:String  [read-write]

Language version: ActionScript 3.0
Runtime version: AIR 1.0

The actual SQL text of the statement.

The text can be any supported SQL. For a complete description of the SQL dialect supported in local SQL databases, see the appendix "SQL support in local databases".

Implementation
    public function get text():String
    public function set text(value:String):void

Throws
Error — When an attempt is made to change the text property while the statement is executing.
Constructor detail
SQLStatement()constructor
public function SQLStatement()

Language version: ActionScript 3.0
Runtime version: AIR 1.0

Creates a SQLStatement instance.


Throws
SecurityError — If the constructor is called from any sandbox outside of the main application sandbox.
Method detail
cancel()method
public function cancel():void

Language version: ActionScript 3.0
Runtime version: AIR 1.0

Cancels execution of this statement. Like SQLConnection.cancel() this method is used to stop a long running query or to cancel a query that is not yet complete. However, unlike SQLConnection.cancel() this method only cancels the single statement. If the statement is not currently executing, calling this method does nothing.

No events are dispatched in direct response to the completion of the cancel() operation. However, once the cancel() operation completes and statement execution is cancelled, the SQLStatement instance dispatches an error event indicating that the statement execution (the execute() or next() call) did not complete. Alternatively, if a value was specified for the responder parameter of the execute() or next() call, the specified fault handler method is called. In either case, the SQLError instance that's passed to the listeners has an errorID property with a value of 3118 (Operation aborted).

clearParameters()method 
public function clearParameters():void

Language version: ActionScript 3.0
Runtime version: AIR 1.0

Clears all current parameter settings.

See also

execute()method 
public function execute(prefetch:int = -1, responder:Responder = null):void

Language version: ActionScript 3.0
Runtime version: AIR 1.0

Executes the SQL in the text property against the database that is connected to the SQLConnection object in the sqlConnection property.

If the responder argument is not null the specified Responder object designates methods that are called to handle the results of the operation. If the responder argument is null, in asynchronous execution mode a result event is dispatched if the operation is successful, or an error event is dispatched if the operation fails.

To access the results of a statement, such as the result rows of a SELECT statement or the database generated primary key of an INSERT statement, call the getResult() method. The results are available immediately after the statement executes in synchronous mode, and when the result event is dispatched in asynchronous mode.

Every statement must be prepared (compiled) before it can be executed. The first time a SQLStatement instance's execute() method is called, the statement is prepared by the runtime. Once a statement is prepared it does not need to be prepared again unless the text property changes. Setting one or more parameter values does not require the statement to be prepared again.

Parameters
prefetch:int (default = -1) — When the statement's text property is a SELECT statement, this value indicates how many rows are returned at one time by the statement. The default value is -1, indicating that all the result rows are returned at one time. This parameter is used in conjunction with the next() method to divide large result sets into smaller sets of data. This can improve a user's perception of application performance by returning initial results more quickly and dividing result-processing operations.

When the SQL statement is a SELECT query and a prefetch argument greater than zero is specified, the statement is considered to be executing until the entire result set is returned or either the SQLStatement.cancel() or SQLConnection.cancel() method is called. Note that because the number of rows in a result set is unknown at execution time, the database cursor must move beyond the last row in the result set before the statement is considered complete. When a prefetch argument is specified in an execute() call, at least one row more than the total number of rows in the result set must be requested (either through a prefetch value that's larger than the number of rows in the result set, or through subsequent calls to the next() method) before the resulting SQLResult instance's complete property is true.

 
responder:Responder (default = null) — An object that designates methods to be called when the operation succeeds or fails. In asynchronous execution mode, if the responder argument is null a result or error event is dispatched when execution completes.

Events
result:SQLEvent — Dispatched when the statement execution completes successfully, or when a prefetch argument value is specified and a SELECT statement returns one or more rows of data.
 
error:SQLErrorEvent — Dispatched when the operation fails in asynchronous execution mode.

Throws
Error — If the text property is null or contains an empty string (""); if the sqlConnection property is not set; if the SQLConnection instance assigned to the sqlConnection property is not connected; or if the statement is currently executing.
 
SQLError — If the operation fails in synchronous execution mode.

See also


Example
The following example demonstrates executing a SQLStatement, using event listeners to determine when the statement execution completes or fails.
var conn:SQLConnection;
var dbStatement:SQLStatement;

function init():void
{
    conn = new SQLConnection();
    conn.addEventListener(SQLEvent.OPEN, connOpenHandler);
	
	dbStatement = new SQLStatement();
    dbStatement.sqlConnection = conn;
    dbStatement.text = "SELECT id, name, ssn FROM employees";
	
    var dbFile:File = new File(File.separator + "employee.db");
    conn.open(dbFile);
}

function connOpenHandler(event:SQLEvent):void
{
    dbStatement.addEventListener(SQLEvent.RESULT, resultHandler);
    dbStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
    dbStatement.execute();
}

function resultHandler(event:SQLEvent):void
{
    var result:SQLResult = dbStatement.getResult();
    if (result != null)
    {
		var numRows:int = result.data.length;
        for (var i:int = 0; i < numRows; i++)
        {
            var row:Object = result.data[i];
            trace("id:", row.id, ", name:", row.name, ", ssn:", row.ssn);
        }
    }
}

function errorHandler(event:SQLErrorEvent):void
{
    trace("An error occured while executing the statement.");
}

The following example demonstrates executing a SQLStatement, using a Responder object to indicate which functions are called when the statement execution completes or fails.
var conn:SQLConnection;
var dbStatement:SQLStatement;
var employeeResponder:Responder;

function init():void
{
    conn = new SQLConnection();
    conn.addEventListener(SQLEvent.OPEN, connOpenHandler);
	
	dbStatement = new SQLStatement();
    dbStatement.sqlConnection = conn;
    dbStatement.text = "SELECT id, name, ssn FROM employees";
	
    var dbFile:File = new File(File.separator + "employee.db");
    conn.open(dbFile);
}

function connOpenHandler(event:SQLEvent):void
{
	employeeResponder = new Responder(resultHandler, errorHandler);
    dbStatement.execute(-1, employeeResponder);
}

function resultHandler(result:SQLResult):void
{
    if (result != null)
    {
		var numRows:int = result.data.length;
        for (var i:int = 0; i < numRows; i++)
        {
            var row:Object = result.data[i];
            trace("id:", row.id, ", name:", row.name, ", ssn:", row.ssn);
        }
    }
}

function errorHandler(error:SQLError):void
{
    trace("An error occured while executing the statement.");
}

getResult()method 
public function getResult():SQLResult

Language version: ActionScript 3.0
Runtime version: AIR 1.0

Provides access to a SQLResult object containing the results of the statement execution, including any result rows from a SELECT statement, and other information about the statement execution for all executed statements. In asynchronous execution mode, the result information is not available until the result event is dispatched.

When a SELECT statement is executed, if the execute() method is called with the default prefetch argument of -1, the returned SQLResult object contains the entire result set of the query.

When a prefetch argument is specified for an execute() or next() method call, the getResult() method behaves as a first-in, first-out queue of results. Each time the result event is dispatched, a new SQLResult object is added to the queue. Each time the getResult() method is called, the earliest SQLResult object (the one that was added to the queue first) is returned and removed from the queue. When there are no more SQLResult objects left in the queue, getResult() returns null.

Note that unless they are removed by calling getResult(), SQLResult objects remain in the queue. For example, if the execute() method is called multiple times without calling getResult(), the SQLResult objects associated with each execute() call remains in the queue.

Returns
SQLResult — A SQLResult object containing the result of a call to the execute() or next() method.

See also

next()method 
public function next(prefetch:int = -1, responder:Responder = null):void

Language version: ActionScript 3.0
Runtime version: AIR 1.0

Retrieves the next portion of a SELECT statement's result set. If there are no more rows in the result set, a result event is dispatched but no additional SQLResult object is added to the getResult() queue.

In asynchronous execution mode, if the responder argument is not null the specified Responder object indicates the methods that are called to handle the results of the operation. If the responder argument is null, a result event is dispatched if the operation is successful, or an error event is dispatched if the operation fails.

This method can only be called when the statement is still executing. When the statement is a SELECT query and a prefetch argument greater than zero is specified, the statement is considered to be executing until the entire result set is returned or either the SQLStatement.cancel() or SQLConnection.cancel() method is called.

Parameters
prefetch:int (default = -1) — When the statement's text property is a SELECT statement, this value indicates how many rows are returned at one time by the statement. The default value is -1, indicating that all the result rows are returned at one time. This can improve a user's perception of application performance by returning initial results more quickly and dividing result-processing operations.
 
responder:Responder (default = null) — An object that designates methods to be called when the operation succeeds or fails. If the responder argument is null a result or error event is dispatched when execution completes.

Events
result:SQLEvent — Dispatched when the statement execution completes successfully, or when a prefetch argument value is specified and the next() call returns one or more rows of data.
 
error:SQLErrorEvent — Dispatched when the operation fails in asynchronous execution mode.

Throws
Error — When the method is called while the statement is not currently executing (the executing property is false).
 
SQLError — if the operation fails in synchronous execution mode.

See also


Example
The following example demonstrates executing a SQLStatement, explicitly indicating that only the first 10 rows of the result set are to be returned the first time the result returns. The code checks the complete property of the SQLResult and, if not all the rows have been retrieved, calls the next() method.
var conn:SQLConnection;
var dbStatement:SQLStatement;

function init():void
{
    conn = new SQLConnection();
    conn.addEventListener(SQLEvent.OPEN, connOpenHandler);
	
	dbStatement = new SQLStatement();
    dbStatement.sqlConnection = conn;
    dbStatement.text = "SELECT id, name, ssn FROM employees";
	
    var dbFile:File = new File(File.separator + "employee.db");
    conn.open(dbFile);
}

function connOpenHandler(event:SQLEvent):void
{
    dbStatement.addEventListener(SQLEvent.RESULT, resultHandler);
    dbStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
    dbStatement.execute(10);
}

function resultHandler(event:SQLEvent):void
{
    var result:SQLResult = dbStatement.getResult();
    if (result != null)
    {
		var numRows:int = result.data.length;
        for (var i:int = 0; i < numRows; i++)
        {
            var row:Object = result.data[i];
            trace("id:", row.id, ", name:", row.name, ", ssn:", row.ssn);
        }
        if (!result.complete)
        {
            dbStatement.next(10);
        }
    }
}

function errorHandler(event:SQLErrorEvent):void
{
    trace("An error occured while executing the statement.");
}

Event detail
errorevent 
Event object type: flash.events.SQLErrorEvent
SQLErrorEvent.type property = flash.events.SQLErrorEvent.ERROR

Language version: ActionScript 3.0
Runtime version: AIR 1.0

Dispatched when an error occurs during an operation.

The SQLErrorEvent.ERROR constant defines the value of the type property of an error event dispatched when a call to a method of a SQLConnection or SQLStatement instance completes with an error. The error event has the following properties:

PropertyValue
bubblesfalse
cancelablefalse; there is no default behavior to cancel.
errorA SQLError object containing information about the type of error that occurred and the operation that caused the error.
currentTargetThe object that is actively processing the event object with an event listener.
targetThe SQLConnection or SQLStatement object reporting the error.

See also

resultevent  
Event object type: flash.events.SQLEvent
SQLEvent.type property = flash.events.SQLEvent.RESULT

Language version: ActionScript 3.0
Runtime version: AIR 1.0

Dispatched when an execute() or next() method call's operation completes successfully. Once the result event is dispatched the getResult() method can be called to retrieve statement results.

The SQLEvent.RESULT constant defines the value of the type property of a result event object. Dispatched when either the SQLStatement.execute() method or SQLStatement.next() method completes successfully. Once the SQLEvent.RESULT event is dispatched the SQLStatement.getResult() method can be called to access the result data. The result event has the following properties:

PropertyValue
bubblesfalse
cancelablefalse; there is no default behavior to cancel.
currentTargetThe object that is actively processing the event object with an event listener.
targetThe SQLStatement object that performed the operation.

See also