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 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:
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.
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 ‘