SQLStatement  - AS3

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.

Related API Elements

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

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.

More examples

Related API Elements

// 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());
        }
    }
}

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;
     

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;
     

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.

More examples

Related API Elements

// employees is a SQLStatement instance
employees.text = "SELECT FROM employees WHERE first = :firstName";
employees.parameters[":firstName"] = "Sam";
employees.execute();

// employees is a SQLStatement instance
employees.text = "SELECT FROM employees WHERE first = ?";
employees.parameters[0] = "Sam";
employees.execute();

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

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".

Creates a SQLStatement instance.

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).

Clears all current parameter settings.

Related API Elements

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

More examples

Related API Elements

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.");
}

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.");
}

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.

More examples

Related API Elements

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

More examples

Related API Elements

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.");
}

Dispatched when an error occurs during an operation.

Related API Elements

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.

Related API Elements