window.runtime property | window.runtime.flash.data.SQLStatement |
Inheritance | SQLStatement EventDispatcher Object |
Runtime Versions: | 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.
See also
Creating and modifying a database
Manipulating SQL database data
Working with SQL statements
Retrieving data from a database
Strategies for working with SQL databases
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 | ||
prototype : 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 |
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 | ||
cancel():void
Cancels execution of this statement. | SQLStatement | ||
clearParameters():void
Clears all current parameter settings. | SQLStatement | ||
dispatchEvent(event:Event):Boolean
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 | ||
hasEventListener(type:String):Boolean
Checks whether the EventDispatcher object has any listeners registered for a specific type
of event. | EventDispatcher | ||
hasOwnProperty(name:String):Boolean
Indicates whether an object has a specified property defined. | Object | ||
isPrototypeOf(theClass:Object):Boolean
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 | ||
propertyIsEnumerable(name:String):Boolean
Indicates whether the specified property exists and is enumerable. | Object | ||
removeEventListener(type:String, listener:Function, useCapture:Boolean = false):void
Removes a listener from the EventDispatcher object. | EventDispatcher | ||
setPropertyIsEnumerable(name:String, isEnum:Boolean = true):void
Sets the availability of a dynamic property for loop operations. | Object | ||
toLocaleString():String
Returns the string representation of this object, formatted according to locale-specific conventions. | Object | ||
toString():String
Returns the string representation of the specified object. | Object | ||
valueOf():Object
Returns the primitive value of the specified object. | Object | ||
willTrigger(type:String):Boolean
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] Runtime Versions: | 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.
See also
itemClass | property |
itemClass:Class
Runtime Versions: | 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.)
See also
parameters | property |
parameters:Object
[read-only] Runtime Versions: | 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 = "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 = "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.
See also
Example ( How to use this example )
, in a SQL statement. The employees
obejct in the code
is an air.SQLStatement instance.
employees.text = "SELECT FROM employees WHERE first = "; employees.parameters[""] = "Sam"; employees.execute();
employees
obejct in the code
is an air.SQLStatement instance
employees.text = "SELECT FROM employees WHERE first = ?"; employees.parameters[0] = "Sam"; employees.execute();
sqlConnection | property |
sqlConnection:SQLConnection
Runtime Versions: | 1.0 |
The SQLConnection object that manages the connection to the database or databases on which the statement is executed.
Throws
IllegalOperationError — When an attempt is made to change the value
of this property while the statement is executing.
|
text | property |
text:String
Runtime Versions: | 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".
Throws
IllegalOperationError — When an attempt is made to change the text
property while the statement is executing.
|
SQLStatement | () | Constructor |
public function SQLStatement()
Runtime Versions: | 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
Runtime Versions: | 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
Runtime Versions: | 1.0 |
Clears all current parameter settings.
See also
execute | () | method |
public function execute(prefetch:int = -1, responder:Responder = null):void
Runtime Versions: | 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.
|
See also
getResult()
Retrieving data from a database
Retrieving SELECT results in parts
Inserting data
Changing or deleting data
Example ( How to use this example )
var conn = new air.SQLConnection(); conn.addEventListener(air.SQLEvent.OPEN, connOpenHandler); var dbStatement = new air.SQLStatement(); dbStatement.sqlConnection = conn; dbStatement.text = "SELECT id, name, ssn FROM employees"; var dbFile = new air.File(air.File.separator + "employee.db"); conn.open(dbFile); function connOpenHandler(event) { dbStatement.addEventListener(air.SQLEvent.RESULT, resultHandler); dbStatement.addEventListener(air.SQLErrorEvent.ERROR, errorHandler); dbStatement.execute(); } function resultHandler(event) { var result = dbStatement.getResult(); if (result != null) { var numRows = result.data.length; for (i = 0; i < numRows; i++) { var row = result.data[i]; air.trace("id:", row.id, ", name:", row.name, ", ssn:", row.ssn); } } } function errorHandler(event) { air.trace("An error occured while executing the statement."); }
var conn = new air.SQLConnection(); conn.addEventListener(air.SQLEvent.OPEN, connOpenHandler); var dbStatement = new air.SQLStatement(); dbStatement.sqlConnection = conn; dbStatement.text = "SELECT id, name, ssn FROM employees"; var dbFile = new air.File(air.File.separator + "employee.db"); conn.open(dbFile); function connOpenHandler(event) { employeeResponder = new air.Responder(resultHandler, errorHandler); dbStatement.execute(-1, employeeResponder); } function resultHandler(result) { if (result != null) { var numRows = result.data.length; for (i = 0; i < numRows; i++) { var row = result.data[i]; air.trace("id:", row.id, ", name:", row.name, ", ssn:", row.ssn); } } } function errorHandler(error) { air.trace("An error occured while executing the statement."); }
getResult | () | method |
public function getResult():SQLResult
Runtime Versions: | 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.
|
See also
next | () | method |
public function next(prefetch:int = -1, responder:Responder = null):void
Runtime Versions: | 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.
|
See also
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 = new air.SQLConnection(); conn.addEventListener(air.SQLEvent.OPEN, connOpenHandler); var dbStatement = new air.SQLStatement(); dbStatement.sqlConnection = conn; dbStatement.text = "SELECT id, name, ssn FROM employees"; var dbFile = new air.File(air.File.separator + "employee.db"); conn.open(dbFile); function connOpenHandler(event) { dbStatement.addEventListener(air.SQLEvent.RESULT, resultHandler); dbStatement.addEventListener(air.SQLErrorEvent.ERROR, errorHandler); dbStatement.execute(10); } function resultHandler(event) { var result = dbStatement.getResult(); if (result != null) { var numRows = result.data.length; for (i = 0; i < numRows; i++) { var row = result.data[i]; air.trace("id:", row.id, ", name:", row.name, ", ssn:", row.ssn); } if (!result.complete) { dbStatement.next(10); } } } function errorHandler(event) { air.trace("An error occured while executing the statement."); }
error | Event |
flash.events.SQLErrorEvent
property SQLErrorEvent.type =
flash.events.SQLErrorEvent.ERROR
Runtime Versions: | 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. |
See also
result | Event |
flash.events.SQLEvent
property SQLEvent.type =
flash.events.SQLEvent.RESULT
Runtime Versions: | 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. |
See also
Thu Sep 29 2011, 02:34 AM -07:00