Using socket mechanism to realize flash and database connection

Data | database | Database connections Creating a database connection in Flash is no easy task for Java developers or. NET developers. Because it takes a lot of trouble to solve this problem, in this article we build a Xmlsocket class to help implement this technology.

　　 First, Introduction

For a developer with a traditional client-server background, it would be a lot of trouble to go into ActionScript and connect Flash to a database. In the. NET or Java world, by invoking a database directly from an application, you only need to open a connection to the database and perform database operations, and in Flash it is not so simple. In this article, I will introduce you to the solution to this problem.

Even in a non-traditional development environment such as flash, database connectivity is essential. For example, you might want to store a high score in a game, or any user information that needs to be stored continuously between sessions. Also, the need for flexible database connections is growing, as more people are now building rich Internet applications based on flash solutions.

To sum up, there are three ways to connect a flash application to a database:

· Use Xmlconnector components;

· Use XML in conjunction with CGI;

· Use the Xmlsocket class.

You must be aware that there is no direct way to connect flash to a database; You must have an application to act as an intermediary between Flash and the database. Each of the methods described above uses this mechanism-each one slightly different in implementation and contains different flash features.

The first method has a good description in Flash Help and is no longer spoken here.

The second approach uses a combination of XML classes at one end of the flash with a CGI application on the server side, which is more suitable for network applications where there is no problem. This method uses a post HTTP request to connect to the application server. For a better explanation, refer to the Flash Help material (ActionScript Reference Guide, work with external data, send and load variables from a remote source, using an XML Class).

The third method, which is discussed in this article, is rarely documented for some reason. However, from a programmer's point of view, it is the most control-providing method. For a non-flash programmer, This is also the most familiar metaphor for database interactions. According to flash help information, "Xmlsocket ... Maintains an open connection to the server, which allows the server to send incoming messages immediately without a request from the client side. "

To show how Xmlsocket was used, I built a simple application: a flash database front-end (see Figure 1). This is very familiar to users who use the query tool because it implements a common design: The screen provides the authentication information field, and a field is used to enter the SQL command to be passed to the database. Although it is not possible for anyone to use flash to build a fully functional database query tool, the methods used in this example can be tailored to meet any specific needs.


Figure 1. Connecting GUI: This image shows a front-end of the database built in Flash.

　　 Ii. Basic Principles

The actual database operation is implemented through a back-end Java application (called "daemon" in Flash Help), which is connected to the database via JDBC (Java database connection). I chose Java because it is cross-platform and because its syntax is easy to understand for ActionScript developers. One based. NET has a conceptually similar look to the backend.
Even if you don't know Java, if you have some experience using ActionScript, then you should understand this article with no problem. If you are a Java or. NET programmer without deep flash experience, I would also like this article to provide some information to help you build your database connection framework using familiar concepts.

Note For testing this program you need to:

· Flash MX 2004 or Flash 8 (Professional Edition)

· Java IDE (I'm using Eclipse 3.0)

· Microsoft SQL Server driver for JDBC

· SQL Server 2000

The Java back end and Flash front-end will communicate through sockets. In Java, sockets are divided into server sockets (hearing and responding to incoming requests) and customer sockets (sending requests to server sockets). A complete discussion of Java sockets is beyond the scope of this article, so if you are unfamiliar with them, refer to other resources on the web; for the purposes of this article, it is sufficient to use sockets as a digital connection over the network between two applications.

Note that flash only supports "customer sockets"-It can connect to a server, but does not serve as a server to other client sockets and is implemented in the Xmlsocket class.

Xmlsocket has some limitations:

· It is only possible to connect to a TCP port that is greater than or equal to 1024;

· By default, security constraints prohibit connecting to a server application running on a different domain rather than a SWF file. This default behavior can be resolved by overloading, as detailed in the Flash reference documentation.

To make communication meaningful, both applications must use a protocol that understands each other (in this case, we are xml-based): a 0-non-terminal XML file is sent to the back-end application by flash and performs certain operations (database access in our case).

In the case of our query tool, executing a command on a database is a two-step process: first, connect to the database using the provided credentials. Then, if the connection succeeds, the command is sent and executed (as an SQL statement). I chose this two-step process because of the characteristics of the query application that I built (one session could include many transactions) and I wanted to build a more complex communication protocol between Java and Flash. However, it is possible to combine authentication and execution into one step-messages sent from Flash to a Java application simply include authentication information and commands; the Java backend then returns the output of the SQL command (or, in the case of a connection or execution failure, an error message).

To achieve authentication to a SQL Server database, the following information is required:

· The IP address of the database server;

· Database login and password;

· database name;

· The IP address or DNS name of the Java application (it can be the same as the database IP name, or it can be different).

Once the Java application detects a connection attempt, it tries to parse the XML message it receives. If it's an ' open connection ' message, the application extracts the IP address, login, password, and database name from the database server, attempts to open a JDBC connection to the database, and, if successful, returns an XML message and a unique connection handle to the Flash front-end. It will also add authentication information and connection numbers (a "handle") to an internal set of connections.

Following a successful connection, the front-end sends an SQL command, which also includes a connection handle in the message. Once you receive an SQL command, the backend extracts the authentication information that belongs to the connection handle and uses it to send the command to the database.

The XML message that the front-end sends to open a connection has the following format:

<flashLogon>
<IPAddr> 127.0.0.1 </IPAddr>
<Login> SA </Login>
<Password> Admin_password </Password>
<Database> User_database </Database>
</flashLogon>

Note that there are two IP address domains on the front end: A DNS name for a server that holds an IP address or a Java application running (port number 1024), and another that holds the database server's IP address; the above <IPAddr> references the database server.

The message that handles an SQL command has the following format:

<flashCommand>
<Command> SELECT * from Table1 </Command>
<Connection> 1 </Connection>
</flashCommand>

If the connection is successful, the Java server application returns the following message:

<Connection>
<HConnection> 1 </HConnection>
</Return>

After processing a command, returns a message similar to the following:

<Return>
120
</Return>

However, this message may also be more complex, for example:

<Return>
michaels,rich,1200,10962 Acacia St
king,mary,98012,11128 Maryland Ave
</Return>

It may also contain an error message, such as:

<Return>
SQL Error Table does not exist.
</Return>

　　 third, the construction front-end

Now, let's analyze the Socketgui.fla file in the source code of this article. In FRAME0, I loaded the background code file (globalframe0.as). Where the Frame1 contains GUI elements. All the button event handlers in the FRAME1 simply refer to the methods in the globalframe0.as.

The Connect function handles the Click function for the ' Connect ' button. I used the Xmlsocket connect function and tried to open a connection to the Java application using port 1024, which could be any 16 digits greater than or equal to 1024.

Using Xmlsocket to open a connection is a two-step process: first, call the Connect method, and Flash calls the OnConnect event handler to notify the application of the success or failure of an attempt to connect; In this processor, you must add code to handle the result of trying to open the connection.

In order for the connection to be completely successful, in our sample application, you must ensure that the Xmlsocket.connect call to the Java application executes successfully, and that the database authentication is executed successfully. If both conditions are met, the Java application returns an XML-encoded connection number and stores it in the hconnection variable. When Flash receives a message from the server, the Xmlsocket ondata event fires. Of course, you may receive a different response message: A successful connection will receive a connection number, and a database command will receive a message from the database. The Parsereturn function determines the kind of response that is received and continues execution according to this response.

If the connection succeeds, the user can now send a command to the database, which is implemented in the SendCommand function, as simple as calling Xmlsocket.send.

Both connect and SendCommand need to format the messages appropriately so that the Java application understands them (the XML format described above). Xmlcreatelogon and Xmlcreatecommand just finished the task. You will notice that each message needs to be terminated by a newline character (' \ n '), otherwise the Java server socket will not receive this message.

The only thing left about the flash encoding is the disconnect feature; In order to disconnect, you can press the ' Disconnect ' button or send a ' Disconnect ' message on the command line. All functions (essentially removing database authentication and handles from the collection) are implemented in the server application; in Flash, I only reset the connection handle.

　　 Iv. Building back-end

The Java back-end application has two parts: a ' listener ' class (called a oscksrv) that uses Java sockets to receive XML commands from flash and a ' executor ' class (called dbconn) that implements the actual database communication capabilities.

The core of the Java application is an infinite loop (refer to the main loop section in the Oscksrv.java file). It simply waits to receive a connection request on port 1024 and, once the connection request is received, uses a stream reader to read the XML message immediately. After experimenting with a variety of readers, I found that the BufferedReader Java class is best for receiving text (XML) strings. The Parsereceivedxml function is then tuned to interpret the message. This function uses the Document Object model API to analyze XML documents received from Flash.

If you receive a ' logon ' message, the getconnection function of the Dbconn class is called to attempt to open a connection to the database. If you receive a ' command ' message, the program code will parse whether the XML message embeds a valid (existing) connection handle. If the handle is valid, dbconn is used to transmit the SQL message received from Flash.

The Dbconn class is responsible for implementing the actual database work. This part of the code is easy to understand if you are familiar with JDBC, or if you have experience with other protocols, such as ODBC or even ADO. The Connect function uses authentication parameters to open a connection to a database, but the really interesting function is execute, which uses the SQL command string received from the front end of the flash and passes it to the engine of the database server to handle it in any way. Obviously, Flash and Java do not know the message sent to the database, only by the database server to understand it!

The Execute function returns the output from the XML format of the database server to the caller (OSCKSRV), which then transmits the output back to the front end. The message must be terminated with a ' xmlsocket ' so that Flash's receiver can receive it correctly.

In my dbconn programming, I used a Microsoft SQL Server database. However, you can easily see that the Java application does not have to be directly connected to a database, a database server or a database platform. If you want to connect to a database server other than SQL Server, you need to modify the code to load the appropriate JDBC driver and provide the appropriate connection parameters via the front end. You may also need to add a ' database type ' parameter to the Flash front-end to help the server code decide which driver to load at run time.

I must confess, for simplicity, I made a little "small gestures". You'll notice in your ActionScript code that I open the connection again before calling the Xmlsocket send method. I could have let the connection open and ready to receive more requests (via the INO buffer reader); However, the accept method of the ServerSocket class is being blocked, meaning that it will wait for another connection request before allowing further processing; If I wait for ino.readline to receive more commands from an already connected customer, the Java application will not service connection requests from other clients. The way I use it now is that once a connection request is received, the request is serviced and the TCP/IP connection is closed by the server (this is exactly the case when the Flash application connects or submits a command to the Java application and displays ' Connection Closed ' cause of the message); The application then returns to wait for another connection request.

In real-world development in Java applications, socket reads and writes are typically performed in a separate thread; Once a connection request is received and a connection to the client is opened, the processing of the connection should be performed independently of the main thread (now it is waiting for another connection to open). This may increase the complexity of Java coding, so in this article I used the "cheat" method in ActionScript and turned on/off the connection, bypassing the problem of handling Java multithreading. However, it is important to note that in a real-world application, it is possible to implement a Full-duplex connected session in order to reduce network performance problems associated with opening a TCP/IP connection.

The biggest challenge, however, is to handle a variety of unrestricted output (which may come from the results of queries submitted from the front end). A real query tool will use a very complex protocol to format the query output (for example, SQL Server uses an internal protocol called the tabular Data flow that describes to the customer the response sent by the server), which is beyond the scope of this article. To do this, I only implement a simple return message-the first column of each record is closed with the <Return> label. The response may be a database recordset, a return code, or a message, depending on the SQL command sent. A real application would need to embed the response with a complex set of XML tags and implement the same complex logic on the front end to decode the message.

However, a practice flash application may not want to handle a variety of database server responses. For example, you might just need to get a user's highest score and some user choices. The establishment of a meaningful agreement in such a situation would be fairly straightforward.

　　 v. Limitations

The biggest challenge in implementing the sample model for this article is the need for client applications to understand the metadata (structure) information returned from the database by the server application. Typically, however, a flash application will be included-it submits only a limited number of queries to the database and receives a limited number of responses, which can make the Parsereturn function moderate in size and complexity. In fact, other database connection methods mentioned in this article do require the programmer to define the XML schema of the message to be exchanged, so it is not possible to avoid describing the data to be received.

Communication capacity is another limiting factor: a strong server application that needs to answer many client calls may need to use multithreading, connection pooling, and other advanced technologies.
In addition, communication security between flash applications and Java applications is important: My sample program sends unencrypted data that might be "peeping". An actual application needs to encrypt the data before it is sent through the socket.

　　 Vi. Establishment of an environment

You can install the Java application on a machine that is the same as or different from the database server. In order to start the Java application, you must execute this command under DOS (see the Launch.bat file in the source code attached to this article):

Set classpath=.; %classpath%; C:/Program Files/microsoft SQL Server Driver for
Jdbc/lib/mssqlserver.jar; C:/Program Files/microsoft SQL Server Driver for JDBC
/lib/msutil.jar; C:/Program Files/microsoft SQL Server Driver for Jdbc/lib/msbase.jar;
Java oscksvr

Note that the key part of this command is the path of MS SQL Server to the JDBC driver.

For developers with little Java experience, setting up a development environment can be a little difficult. I used the directory structure shown in Figure 2. I built my eclipse environment to copy the Java binaries to the \ Bin subdirectory, as shown in Figure 3. Figures 4 and 5 show that the application establishes a connection to the database and receives a response.


Figure 2. Java application directory structure: This screenshot shows the author's source directory structure.

Figure 3. Java application directory structure: This screenshot shows the directory structure of the author's binaries and Launcher files (launcher) in Eclipse.
Note: You can download the JDBC driver corresponding to SQL Server from Microsoft's site.


Figure 4. Connecting to a database: This snapshot shows a connection to SQL Server being established.

Figure 5. Query and Response: SQL Server receives a query from Figure 4 and sends its response.
To allow Flash customers to download responses from Java Applications (if the programs are not on the same domain), a cross-domain XML policy file is required on the Web server. For more details, please refer to the Flash help.

In summary, although typical communication with a database is not as straightforward in flash as it is in other development environments, it can be implemented by means of a "broker" (for example, an application that accesses a database using a JDBC or ADO protocol).