Recipe 20.22 Querying a Database with Server-Side ActionScript

20.22.1 Problem

You want to use Server-Side ActionScript (SSAS) to query a database.

20.22.2 Solution

Make sure the database is registered as a datasource with ColdFusion or JRun, and then use the query( ) method of the SSAS CF object.

20.22.3 Discussion

Server-Side ActionScript allows you to query any registered datasource by way of the CF.query( ) method. The first step is to make sure that your database has been properly registered with ColdFusion or JRun. If you are not sure how to do this, then you should consult your ColdFusion or JRun documentation.

The CF.query( ) method allows you to pass it the following arguments as either positional or named parameters:

datasource

The name of the datasource as registered with ColdFusion or JRun.

sql

The SQL command.

username

If the datasource requires a username, you can specify it with this parameter.

password

If the datasource requires a password, you can specify it with this parameter.

maxrows

You can optionally specify the maximum number of records to return.

timeout

An optional number of milliseconds to wait before timing out.

When you use the CF.query( ) method with positional parameters, you can specify one or more optional parameters in the following orders:

CF.query(datasource, sql);
CF.query(datasource, sql, maxrows);
CF.query(datasource, sql, username, password);
CF.query(datasource, sql, username, password, maxrows);

You can use the timeout parameter only if you used named parameters. Otherwise, you can use named parameters by passing the method an object in which the properties match the parameter names. For example:

CF.query({datasource: "myDatabase", sql: "SELECT * FROM MyTable"});

The CF.query( ) method returns a recordset that you can return to the Flash movie. For example, here is an SSAS function that accepts a parameter and performs a database query to find records containing that value. The function then returns the recordset to the Flash movie.

function getUsers (city) {
  return CF.query("MyDatabase", "SELECT * FROM UsersTable WHERE city = " + city);
}

However, the recordset that the CF.query( ) method returns is not an ActionScript RecordSet object in SSAS (although it is converted into a RecordSet object in the Flash movie). This is because the ActionScript RecordSet class is available only in client-side ActionScript, not in SSAS. The object that CF.query( ) returns is actually a coldfusion.sql.QueryTable object. The coldfusion.sql.QueryTable class subclasses javax.sql.RowSet, so if you absolutely want to work with the recordset on the server before returning the data to Flash, you can use the methods of the RowSet class.

20.22.4 See Also

Recipe 20.21. Also, Chapter 21 covers recordsets in detail. For more information on RowSet objects, see the Java API at http://java.sun.com/j2ee/sdk_1.3/techdocs/api/javax/sql/RowSet.html.