or someone coming to ActionScript development from a traditional client-server background, connecting Flash to a database is a bit of a black art. In the .NET or Java world, you simply open a connection to a database and perform database operations by calling the database directly from the application. Life is not so simple in Flash; however, in this article I will show that it does not have to be overly complicated either and there are ways of achieving the same functionality in Flash.
Even in a less traditional development environment such as Flash, the need for database connectivity should be obvious: for example, you may want to store the high scores in a game, or any user information that needs to be persisted between sessions. And the need for versatile database connectivity will only increase, as more people adopt Flash for building rich Internet applications.
There are three ways in which a Flash application can connect to a database:
- using the XMLConnector component;
- using a combination of XML and CGI;
- using the XMLSocket class.
It is important to realize that there is no direct way of connecting Flash to a database; an application is needed to act as an intermediary between Flash and the database. Each of the methods described above uses this architectureeach in a slightly different way and involving different Flash features.
The first method is well documented in the Flash help (Using Flash > Data Integration > Data Connectivity).
|Figure 1. Connection GUI: The image shows a database front-end built in Flash. |
The second method uses a combination of the XML class on the Flash side with a CGI application on the server side, and is suitable in situations where network latency is not an issue. This method uses a POST HTTP request to connect to the application server. For a good explanation of this, see Flash help (ActionScript reference guide > Working with external data > Sending and loading variables to and from a remote source > Using the XML class
The third method, which this article will focus on, is, for some reason, the least well documented. It is however the one that offers the most control from a programmer's point of view. It is also the most familiar metaphor of database interaction for a non-Flash programmer. According to the Flash help, "XMLSocket … maintains an open connection to the server, which allows the server to immediately send incoming messages without a request from the client."
To illustrate it, I built a simple application: a Flash database front end (see Figure 1). It will look very familiar to users of query tools as it implements the common design: the screen has fields for the authentication information, and also a field to enter the SQL command to be passed to the database. While it is unlikely that anyone will build a fully-fledged database query tool in Flash, the methods used in this example can be easily customized for any particular needs.
The actual database operations are performed by a backend Java application (called "daemon" in the Flash help) that connects to the database via JDBC (Java Database Connectivity). I chose Java because it is cross-platform and also because its syntax will be easy to understand by ActionScript developers. A .NET-based backend will be similar in concept.
Even if you do not know Java, if you have some experience with ActionScript you should have no problem understanding the code in this article (save perhaps for some more advanced threading concepts that I discuss later). If you are a Java or .NET programmer without extensive Flash experience I hope this article will present some of the 'missing links' that will help you build your database connectivity framework using familiar concepts.
|What You Need
| - Flash MX 2004 or 8 (Professional)
- A Java IDE (Eclipse 3.0 used by the author)
- Microsoft SQL Server 2000 driver for JDBC
- SQL Server 2000
The Java back-end and the Flash front-end will communicate through sockets. In Java there are server sockets (listening to and responding to incoming requests) and client sockets (which can send requests to server sockets). A full discussion of Java sockets is beyond the purpose of this article, so if you are not familiar with them please read the articles I mention in the "References" section (see left column) for more details; for our purposes it suffices to consider sockets as a numbered connection between two applications over a network.
Flash only supports 'client sockets' that can connect to a server but not act as servers to other client sockets, and are implemented in the XMLSocket class.
XMLSocket has a few limitations:
- it is only possible to connect to TCP ports greater than or equal to 1024;
- security restrictions prevent connecting to a server application running in a different domain than the SWF file. This default behavior can be overriddenmore details at the end of the article.
For the communication to be meaningful, the two applications have to use a mutually understood protocol (XML-based, in this case): a zero-terminated XML file is sent by Flash to the back-end application, which has to be able to make sense of it and perform operations (in our case, database access) on its behalf.
In our query tool case, executing a command against the database is a two-step process: first, connect to the database using the supplied credentials. Then, send and execute the command (as SQL statements) if the connection is successful. I chose a two-step process due to the nature of the query application I built, where a session can involve many transactions and also because I wanted to build a somewhat more complex communications protocol between Java and Flash. It is possible however to combine the authentication and execution in one stepthe message sent to the Java application from Flash would simply include both the authentication information and the command; the Java back-end would then return the output of the SQL command (or an error message in case of connection or execution failure).
To authenticate to a SQL Server database, the following pieces of information are required:
- the IP address of the database server;
- the database login and the password;
- the database name;
- the IP address or DNS name of the Java application (which may or may not be the same as the database IP).
Once the Java application detects a connection attempt, it will try to parse the XML message it receives. If it is a 'open connection' message, the application will extract the IP address of the database server, the login, the password, and the database name; it will attempt to open a JDBC connection to the database; if successful, it will return an XML message with a unique connection handle back to the Flash front end. It will also add the authentication information and the connection number (a 'handle') to an internal connections collection.
When, following a successful connection, the front-end sends a SQL command, it will also include the connection handle in the message. Upon receipt of a SQL command, the back-end will extract the authentication information pertaining to the connection handle and use it to send the command to the database.
The XML message to open a connection sent by the front end has the following format:
(Please notice that the front end has two IP address fields: one holds the IP address or the DNS name of the server where the Java application is running, on port 1024, and the other one holds the IP address of the database server; <IPAddr>
above refers to the database server).
The message to process a SQL command has the format:
<Command>SELECT * FROM Table1</Command>
If the connection is successful, the Java server application returns the following message:
After a command is processed, a message similar to the following is returned:
However, this message can be more complex, such as:
Michaels,Rich,1200,10962 Acacia St
King,Mary,98012,11128 Maryland Ave
It can also contain an error message, such as:
SQL Error 101 Table does not exist.