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 architecture?each 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 Principle
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 overridden?more 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 step?the 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:
127.0.0.1 sa admin_password user_database (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;
The message to process a SQL command has the format:
SELECT * FROM Table1 1 If the connection is successful, the Java server application returns the following message:
1 After a command is processed, a message similar to the following is returned:
120 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. Building the Front-end
Take a look at the SocketGUI.fla file from the code download that accompanies this article. In Frame0 I load the code-behind file (GlobalFrame0.as). Frame1 contains the GUI elements. All the button event handlers in Frame1 simply reference methods in GlobalFrame0.as.
The Connect function handles the click functionality for the ‘Connect’ button. I use XMLSocket’s connect function to attempt to open a connection to the Java application using port 1024 (this can be any 16-bit number larger than or equal to 1024).
Opening a connection using XMLSocket is a two-step process: first, the connect method is invoked; then Flash calls the onConnect event handler to notify the application of the success or failure of the connection attempt; in this handler you must put the code that deals with the outcome of the attempt to open the connection.
For the connection attempt to be completely successful, in our sample application the XMLSocket.connect call to the Java application must succeed, and also the database authentication must be performed successfully. If this happens, the Java application will return an XML-encoded connection number, stored in the HConnection variable. XMLSocket’s onData event will fire when Flash receives messages from the server. Of course there are different responses that can be received: a successful connection attempt will receive a connection number, while a database command will receive a message from the database. The ParseReturn function determines the kind of response received and proceeds depending on this response.
If the connection is successful, the user can now send a command to the database; this functionality is implemented in the SendCommand function; it is as simple as calling XMLSocket.send.
Both Connect and SendCommand need the messages to be formatted properly so that the Java application understands them (the XML format was described above). XMLCreateLogon and XMLCreateCommand do just that. You will notice that each message needs to be terminated by a newline character (‘
‘), otherwise the Java server socket will not receive the message.
The only other thing left in the Flash code is the Disconnect function; to disconnect, you can either press the ‘Disconnect’ button or send a ‘disconnect’ message in the command line. All the functionality (basically, removing the database authentication and handle from the collection) is implemented in the server application; in Flash I only reset the connection handle.
Building the Back-end
The Java backend application has two parts, a ‘listener’ class (called oSckSrv) that uses Java sockets to receive the XML commands from Flash and an ‘executor’ class (called dbConn), which implements the actual database communication functionality.
|
If a ‘logon’ message was received, the dbConn class’s getConnection function is called to attempt to open the connection to the database. If a ‘command’ message was received, then the code looks to see if the XML message embeds a valid (existing) connection handle. If the handle is valid, dbConn is used to pass through the SQL message received from Flash.
The dbConn class does the actual database work. If you are familiar with JDBC, or have experience with another protocol such as ODBC or even ADO, the code will look very simple. The Connect function opens a connection to the database using the authentication parameters, but the really interesting function is Execute, which takes the SQL command string received from the Flash front end, and passes it to the database server’s engine to process it any way it can. Obviously, neither Flash nor Java has any knowledge of the message sent to the database; it is up to the database server to make sense of it!
The Execute function returns the XML-formatted output from the database server to the caller (oSckSrv), which in turn relays it back to the front-end. The message has to be terminated by a ‘ ’ so that Flash’s XMLSocket receives it correctly.
dbConn, as I wrote it, is coded to work with Microsoft SQL Server. However, it is quite easy to notice that the Java application does not have to intermediate connectivity to one database, one database server, or even one database platform only. If you want to connect to database servers other than SQL Server, you need to modify the code to load the appropriate JDBC drivers and supply the appropriate connection parameters through the front end. You would also probably need to add a ‘database type’ parameter on the Flash front end to help the server code decide which driver to load at runtime.
I will have to admit here that, for the sake of simplicity, I cheated a bit. You will notice in the ActionScript code that before calling the send method of XMLSocket, I open the connection again. I could have kept the connection open and ready to receive more requests via the ino buffered reader; however, the accept method of the ServerSocket class is blocking, meaning that it will wait for another connect request before allowing further processing; at the same time, if I waited for ino.readLine to receive more commands from an already-connected client, the Java application would not have been able to service connect requests from other clients. The way the code is currently written, as soon as a connection request is received, it is serviced and then the TCP/IP connection is closed by the server (this is the reason for the ‘Connection closed’ message displayed by the Flash application as soon as it connects or submits a command to the Java application); the application then goes back to waiting for another connection request.
A true ‘live’ connection would require the socket reads and writes in the Java application to be performed on a separate thread; as soon as a connect request was received and a connection to the client was opened, the processing of that connection should be performed independently of the main thread (which is now waiting for another connection to open). This would have raised the complexity of the Java code quite a bit and for the purposes of this article I preferred to ‘cheat’ and open/close the connections in ActionScript rather than dealing with Java threading issues. It is important to realize however that a full duplex, continuous dialog is possible and even desirable in a production-level application to reduce network performance issues associated with opening TCP/IP connections.
The biggest challenge overall, however, is dealing with the unlimited variety of the output that can result from the query submitted from the front end. A true query tool would use a very complex protocol to format the query output (for example, SQL Server uses an internal protocol named Tabular Data Stream that ‘describes’ to the client the response sent by the server), which is beyond of the scope of this article. For this reason I only implemented a very simple return message?the first columns of each record are enclosed in a
A real life Flash application, however, would hopefully not have to deal with an endless variety of database server responses. You may, for example, only need to get the high score for a user and some user preferences. Establishing a meaningful protocol in this case would be quite simple.
Limitations
The most significant challenge of this model is the need for the client application to know the metadata (the structure) of the information returned from the database by the server application. However, the typical scenario would involve a Flash application that only submits a limited number of queries to the databases and gets a limited number of responses, therefore keeping the ParseReturn function of a manageable size and complexity. In fact, some of the other database connectivity methods mentioned in the beginning of this article do require the programmer to define the XML schema of the messages to be exchanged, so there is no way to avoid describing the data to be received.
The communication volume is another limiting factor: a robust server application that needs to answer a lot of client calls may need to use multithreading, connection pooling, and other advanced techniques.
The security of the communications between the Flash application and the Java application is very important: my example sends unencrypted data that can be ‘sniffed’. A production-level application will need to encrypt data before sending it via sockets.
Setting up the Environment
You can install the Java application to run on the same machine as the database server or on a different one. To start the Java application, you will have to execute this command in DOS (see the launch.bat file in the source code download that accompanies this article):
set CLASSPATH=.;%CLASSPATH%;C:/Program Files/Microsoft SQL Server 2000 Driver for
JDBC/lib/mssqlserver.jar;C:/Program Files/Microsoft SQL Server 2000 Driver for JDBC
/lib/msutil.jar;C:/Program Files/Microsoft SQL Server 2000 Driver for JDBC/lib/msbase.jar;
java oSckSvrThe underlined portion of this command is the path to the JDBC driver for MS SQL Server.
For those developers with less Java expertise, setting up the development environment can be a bit of a hassle. I used the directory structure shown in Figure 2. I set up my Eclipse environment to copy the Java binaries to the in subdirectory, as shown in Figure 3. Figures 4 and 5 demonstrate the application making a connection to the database and receiving a reply.
 Figure 2. Java Application Directory Structure: The screen shot shows the author’s directory structure for the source. | Â |  Figure 3. Java Application Directory Structure: The screen shot shows the author’s directory structure for the binaries and launcher in Eclipse. |
| Author’s Note: You can download the JDBC driver for SQL Server from Microsoft’s web site (see “Related Resources,” left column). |
 Figure 4. Connecting to the Database: Here a connection is being made to SQL Server. | Â |  Figure 5. Query and Response: SQL Server receives the query from Figure 4 and sends its response. |
In conclusion, although typical communication with a database is not as straightforward in Flash as it is in other development environments, it can be implemented?with the help of a mediating application written in an environment that is capable of accessing databases using a protocol such as JDBC or ADO.
