Package | flash.data |
Class | public class SQLStatement |
Inheritance | SQLStatement EventDispatcher Object |
Language Version: | ActionScript 3.0 |
Runtime Versions: | AIR 1.0 |
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.
More examples
Manipulating SQL database data
Working with SQL statements
Retrieving data from a database
Strategies for working with SQL databases
Related API Elements
Property | Defined By | ||
---|---|---|---|
constructor : 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 | ||
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 |
Method | Defined By | ||
---|---|---|---|
Creates a SQLStatement instance. | SQLStatement | ||
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 | ||
Dispatches an event into the event flow. | EventDispatcher | ||
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 | ||
Checks whether the EventDispatcher object has any listeners registered for a specific type
of event. | EventDispatcher | ||
Indicates whether an object has a specified property defined. | Object | ||
Indicates whether an instance of the Object class is in the prototype chain of the object specified
as the parameter. | Object | ||
Retrieves the next portion of a SELECT statement's result set. | SQLStatement | ||
Indicates whether the specified property exists and is enumerable. | Object | ||
Removes a listener from the EventDispatcher object. | EventDispatcher | ||
Sets the availability of a dynamic property for loop operations. | Object | ||
Returns the string representation of this object, formatted according to locale-specific conventions. | Object | ||
Returns the string representation of the specified object. | Object | ||
Returns the primitive value of the specified object. | Object | ||
Checks whether an event listener is registered with this EventDispatcher object or any of
its ancestors for the specified event type. | EventDispatcher |
Event | Summary | Defined By | ||
---|---|---|---|---|
[broadcast event] Dispatched when the Flash Player or AIR application gains operating system focus and becomes active. | EventDispatcher | |||
[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 |
executing | property |
executing:Boolean
[read-only] Language Version: | ActionScript 3.0 |
Runtime Versions: | 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
Related API Elements
itemClass | property |
itemClass:Class
Language Version: | ActionScript 3.0 |
Runtime Versions: | AIR 1.0 |
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.
Implementation
public function get itemClass():Class
public function set itemClass(value:Class):void
More examples
Related API Elements
Example ( How to use this example )
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()); } } }
parameters | property |
parameters:Object
[read-only] Language Version: | ActionScript 3.0 |
Runtime Versions: | 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;
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.
Implementation
public function get parameters():Object
More examples
Related API Elements
Example ( How to use this example )
:firstName
, in a SQL statement.
// 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();
sqlConnection | property |
sqlConnection:SQLConnection
Runtime Versions: | 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
IllegalOperationError — When an attempt is made to change the value
of this property while the statement is executing.
|
text | property |
text:String
Language Version: | ActionScript 3.0 |
Runtime Versions: | 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
IllegalOperationError — When an attempt is made to change the text
property while the statement is executing.
|
SQLStatement | () | Constructor |
public function SQLStatement()
Language Version: | ActionScript 3.0 |
Runtime Versions: | AIR 1.0 |
Creates a SQLStatement instance.
Throws
SecurityError — If the constructor is called from any sandbox outside
of the main application sandbox.
|
cancel | () | method |
public function cancel():void
Language Version: | ActionScript 3.0 |
Runtime Versions: | 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 Versions: | AIR 1.0 |
Clears all current parameter settings.
Related API Elements
execute | () | method |
public function execute(prefetch:int = -1, responder:Responder = null):void
Language Version: | ActionScript 3.0 |
Runtime Versions: | 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 | |
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: — 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: — Dispatched when the operation fails in asynchronous execution mode.
|
Throws
IllegalOperationError — 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.
|
More examples
Retrieving SELECT results in parts
Inserting data
Changing or deleting data
Related API Elements
Example ( How to use this example )
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."); }
getResult | () | method |
public function getResult():SQLResult
Language Version: | ActionScript 3.0 |
Runtime Versions: | 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.
SQLResult — A SQLResult object containing the result of a call to the execute()
or next() method.
|
More examples
Retrieving a database-generated primary key of an inserted row
Related API Elements
next | () | method |
public function next(prefetch:int = -1, responder:Responder = null):void
Language Version: | ActionScript 3.0 |
Runtime Versions: | 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: — 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: — Dispatched when the operation fails in asynchronous execution mode.
|
Throws
IllegalOperationError — 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.
|
More examples
Related API Elements
Example ( How to use this example )
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."); }
error | Event |
flash.events.SQLErrorEvent
property SQLErrorEvent.type =
flash.events.SQLErrorEvent.ERROR
Language Version: | ActionScript 3.0 |
Runtime Versions: | AIR 1.0 |
Dispatched when an error occurs during an operation.
TheSQLErrorEvent.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:
Property | Value |
---|---|
bubbles | false |
cancelable | false ; there is no default behavior to cancel. |
error | A SQLError object containing information about the type of error that occurred and the operation that caused the error. |
currentTarget | The object that is actively processing the event object with an event listener. |
target | The SQLConnection or SQLStatement object reporting the error. |
Related API Elements
result | Event |
flash.events.SQLEvent
property SQLEvent.type =
flash.events.SQLEvent.RESULT
Language Version: | ActionScript 3.0 |
Runtime Versions: | 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.
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:
Property | Value |
---|---|
bubbles | false |
cancelable | false ; there is no default behavior to cancel. |
currentTarget | The object that is actively processing the event object with an event listener. |
target | The SQLStatement object that performed the operation. |
Related API Elements
Thu Dec 6 2018, 01:12 PM -08:00