• Choose a category
• All
• Classic-Fiction

statement.parameters[1] = object.city;

try {

statement.execute();

trace("item created");

} catch(error:SQLError) {

trace(error.message);

}

}

Figure 6-2. The geography table with some dynamic data added

As an alternative, you can use the following syntax. Here we assume named parameters that work much like an associate array. Figure 6-3 shows the result:

addItem({country:"United States", city:"New York"});

function addItem(object:Object):void {

var statement:SQLStatement = new SQLStatement();

statement.sqlConnection = connection;

var insert:String =

"INSERT INTO geography (country, city) VALUES (:co, :ci)";

statement.text = insert;

statement.parameters[":co"] = object.country;

statement.parameters[":ci"] = object.city;

try {

statement.execute();

trace("item created");

} catch(error:SQLError) {

trace(error.message);

}

}

Figure 6-3. The geography table with dynamic data and named parameters added

Using either of these two dynamic approaches facilitates re-use of the same SQL statement to add many items, but is also more secure because the parameters are not written in the SQL text. This prevents a possible SQL injection attack.

Requesting data

Data is requested by using the SELECT statement. The result is an SQLResult that you can get as a property of the SQLStatement: statement.getResult(). Each row item is received as an object with property names corresponding to the table column names. Note the use of the * in place of the columns’ name to get the entire table:

import flash.data.SQLResult;

var statement:SQLStatement = new SQLStatement();

statement.sqlConnection = connection;

statement.text = "SELECT * FROM geography";

statement.addEventListener(SQLEvent.RESULT, selectionReceived);

statement.execute();

function selectionReceived(event:SQLEvent):void {

statement.removeEventListener(SQLEvent.RESULT, selectionReceived);

var result:SQLResult = statement.getResult();

if (result != null) {

var rows:int = result.data.length;

for (var i:int = 0; i < rows; i++) {

var row:Object = result.data[i];

trace(row.id + "" + row.country + "" + row.city);

}

}

}

Instead of requesting the entire table, you may want to receive only one item in the table. Let’s request the country that has New York as a city. Execute(1) only returns the item stored under table ID 1:

var statement:SQLStatement = new SQLStatement();

statement.sqlConnection = connection;

statement.text = "SELECT country FROM geography WHERE city = 'New York'";

try {

statement.execute(1);

var result:SQLResult = statement.getResult();

if (result.data != null) {

trace(result.data[0].country);

}

} catch(error:Error) {

trace("item", error.message);

}

Let’s make the same request again, passing the city as dynamic data:

getCountry("New York");

function getCountry(myCity:String):void {

var statement:SQLStatement = new SQLStatement();

statement.sqlConnection = connection;

statement.text = "SELECT country FROM geography WHERE city = :ci";

statement.parameters[":ci"] = myCity;

try {

statement.execute(1);

var result:SQLResult = statement.getResult();

if (result.data != null) {

trace(result.data[0].country);

}

} catch(error:Error) {

trace("item", error.message);

}

}

Editing existing data

Existing data can be modified. In this example, we’re searching for the country United States and changing the city to Washington, DC. Figure 6-4 shows the result:

modifyItem("United States", "Washington DC");

function modifyItem(myCountry:String, myCity:String):void {

var statement:SQLStatement = new SQLStatement();

statement.sqlConnection = connection;

var updateMessage:String =

"UPDATE geography SET city = :ci where country = :co";

statement.text = updateMessage;

statement.parameters[":co"] = myCountry;

statement.parameters[":ci"] = myCity;

try {

statement.execute();

trace("all removed");

} catch(error:Error) {

trace("item", error.message);

}

}

Figure 6-4. The geography table with existing data modified

Now let’s look for the country France and delete the row that contains it (see