Browse DevX
Sign up for e-mail newsletters from DevX


Connect Flash to a Database Using Sockets : Page 2

Making a database connection in Flash is not the straightforward process enjoyed by Java and .NET developers. And while it may be a black art, it needn't be avoided, for the XMLSocket class is there to help.

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 ('\n'), 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.

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.
The core of the Java application is an endless loop (refer to the Main loop section of the constructor function in oSckSrv.java). It simply waits to receive a connection request on port 1024; once it is received, it uses a stream reader to read in the XML message; after experimenting with various readers, I have found that the BufferedReader Java class is the most appropriate for receiving text (XML) strings. The parseReceivedXML function is then called to interpret the message. This function uses the Document Object Model API to parse the XML document received from Flash.

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 '\0' 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 <Return> tag. This response can be a database recordset, a return code, or a message, depending on the SQL command that was sent. A production-level application would need to use a complex set of XML tags to embed the response and implement an equally complex logic in the front end to decode this message.

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.

Thanks for your registration, follow us on our social networks to keep up-to-date