You are Here:Home>>Old Posts>>Using ActionScript 3.0 to retrieve MySQL data using a server-side ASP script

Using ActionScript 3.0 to retrieve MySQL data using a server-side ASP script

Carlos Pinho
By | 2008-02-13T13:40:08+00:00 Feb 13, 2008|Old Posts|

The impetus for this article came about one day when I was tasked with porting over an MTBF (Mean Time Between Failure) application that I had written many years back from dynamic ASP pages to Flash. The original ASP code talked to a Microsoft SQL database and, of course, used server-side ASP scripts to access the data in the database. Not wanting to reinvent the wheel, I set about rewriting the interface in Flash CS3 Professional and writing ActionScript 3.0 code to talk with my team’s database, which is a MySQL database rather than Microsoft SQL. By the way, that team is the Flash authoring team at Adobe. After resolving a few slight protocol differences between Microsoft SQL and MySQL, I created the code discussed in this article. I now use this code not only for the MTBF application but also for one of our automation testing applications, as well as for some internal tools that help the Flash team track and confirm different testing metrics. My hope is that this sample application will help you jump past the boring details of hooking up to a data source and instead focus on the fun part of writing a useful rich Internet application using Flash.

This article presents a complete solution for connecting a Flash application to a back-end MySQL database using XML and a server-side ASP script. I first show you how I used Flash to create MySQL-friendly SQL calls and then wrap them up into XML that is sent to that server-side ASP script. Then I break down the ASP and show you what it does with the XML sent from Flash. Finally, I reveal how Flash parses the XML that is returned. Along the way, I introduce some valuable functions as well as an interesting technique for building up multiple MySQL calls so that the server does not have to be called multiple times.

Requirements

In order to make the most of this article, you need the following software and files:

Flash CS3 Professional

MySQL database

Microsoft IIS server to run the server-side ASP script

Sample files:

Prerequisite knowledge

You should have at least some basic knowledge of Flash CS3 Professional and ActionScript 3.0, an understanding of how to set up and use a MySQL database, as well as an understanding of how to set up an IIS server for running the server-side ASP script. I assume that you will already have a MySQL database set up, but if you want more information on how to get and configure MySQL, go to the MySQL website. I also assume that you will have an IIS server set up, either locally or remotely, where you will run the server-side ASP script. For more information on setting up an IIS website, go to Microsoft Support.

Preparing to talk to the ASP script

I’ll start out by showing you how to prepare user input from your Flash application to be used to query the database. Before this data is sent to the ASP script, it needs to be converted to XML and formatted so that it is stored correctly in the MySQL database. First you’ll need to make sure that date objects and user input data can be translated into something MySQL will understand. Then you’ll need to wrap up the SQL calls into properly formatted XML for the server-side ASP script.

Making a date MySQL-friendly

When sending any data to a database, it needs to be formatted it in a way that the database expects to receive it. One of the things I discovered when working with MySQL is that it is very particular about how it wants dates formatted. It turns out that MySQL expects the date in the format of year, month, day (yyyymmdd). One catch to this is that MySQL expects leading zeros on the single-digit months and days. So if you were trying to submit the 9th of August, 2007, “200789” would not be valid but “20070809” would.

To make it easier, I created a function that takes a Flash Date object and converts it into a MySQL-friendly string. Look at the makeMySQLDate ActionScript function below to see how it works:

 
public function MakeMySQLDate(tmpDate:Date):String
{
   var iYear:Number = tmpDate.getFullYear();
   var iMonth:Number = tmpDate.getMonth()+1;
   var iDay:Number = tmpDate.getDate();
   var tmpStrMonth:String = iMonth.toString();
   if (iMonth <= 9) {
      tmpStrMonth = "0" + tmpStrMonth;
   }
   var tmpStrDay:String = iDay.toString();
   if (iDay <= 9) {
      tmpStrDay = "0" + tmpStrDay;
   }
   return iYear.toString() + tmpStrMonth + tmpStrDay;
}

As you can see from the code above, the makeMySQLDate function takes a Flash Date object as a parameter and returns a string. To create the string, it pulls the Date object apart into year, month, and day. The function then looks at the month and day and, if necessary, adds a leading “0.” Finally, it concatenates the data into a string in the “yyyymmdd” format and returns it.

Now you need to make sure the data does not have any characters that could cause problems when creating the SQL statement.

Creating MySQL-friendly data

One common problem with submitting data to a database is that the raw data may contain characters that are not themselves valid when put into a SQL statement. One example of this is the single quote. In a SQL statement, a single quote indicates a closing of variable data; for example, Booga='Data Here'. The single quote becomes a problem only when you take data from user input and don’t take this into account.

For example, a user types in a comment in a text input that says, “Can’t get the server data.” You then take this text and place it directly into a variable in a SQL statement like Comment='Can't get the server data.'. As soon as you submit it, you get an error because you have more than two single quotes around the data. To solve this problem, I created the fixStringDataForMySQL function:

 
public function fixStringDataForMySQL(strTemp:String):String
{
   var i:Number = 0;
   var iOld:Number = 0;
   var firstQuote:Boolean = false;
   var strNew:String = "";
   strTemp = trim(strTemp);
   while (i != -1){
      i = strTemp.indexOf("'", i);
      if (i != -1){
         if ((strNew != "") || (firstQuote)){
            strNew += "\" + strTemp.substring(iOld, i);
         }
         else if (i != 0) {
            strNew = strTemp.substring(iOld, i);
         }
         else {
            firstQuote = true;
         }
         iOld = i;
         i++;
      }
   }
   if (iOld <= strTemp.length){
      if (strNew != ""){
         strNew += "\" + strTemp.substring(iOld, strTemp.length);
      }
      else {
         strNew = strTemp.substring(iOld, strTemp.length);
      }
   }
   return (strNew);
}

This function traverses the string parameter supplied and adds the “\” characters in front of the single quotes to escape them so that the database does not interpret them incorrectly. Once it is done traversing the string, escaping the single quotes, it returns the modified string.

Now you’re ready to see how to wrap this data up in a SQL statement.

Creating the SQL statement

Once you fix all the dates and string data so that it is MySQL-friendly, you need to put together the SQL statement that will be stored in XML. This article assumes that you have a basic knowledge of SQL statements relating to MySQL, so I will not go into too much depth about the exact syntax. Instead, here’s a basic SQL statement that uses the functions discussed above to fix the data:

 
SQL = "SELECT * FROM myTable WHERE Comment=' fixStringDataForMySQL(strComment)'
AND RecDate='makeMySQLDate(RepDate)'"

This SQL statement is saying, “Get all of the records where the Comment field matches strComment and the RecDate field equals RepDate.” By using the two functions that make the data MySQL-friendly, you can be sure that you will minimize the conflicts that can occur.

Now you can package the SQL statement into XML that the server-side ASP script understands.

What about SQL injection using this technique?

One common problem that can occur when users are allowed to enter data that is sent to a database is called SQL injection. This is a technique where a user put a SQL statement into an input control in the hopes of gaining access to the database. To protect against this, make sure users cannot corrupt your SQL statements, which could confuse the database into executing a malicious statement.

One technique to prevent this situation is to escape characters just like the fixStringDataForMySQL function does. This solution is not completely bulletproof, however, because it is escaping only the single quote—but it works for most situations.

So this takes care of string problems but users can also try to hack their way in by using numeric or date controls. To combat this, you should make sure that the input from these types of controls are truly numeric and dates. Don’t just blindly take user input and stick it into your SQL statement without making sure it is the proper type or escaping out offending characters.

This article was not designed to cover all the ways you can prevent SQL injection from occuring. If you are concerned about the potential threat to your database from this type of attack, I recommend that you read up on all the methods to prevent SQL injection. A good place to start is Wikipedia’s entry for SQL injection. You can also do a Google search for SQL injection.

Understanding XML schema for the data sent to the server-side ASP script

Once you create a properly formatted SQL statement, it needs to be wrapped up into XML so that it can be sent to the ASP script. Here’s the schema of the XML that will be sent to the ASP script:

 
<MySQLRequest>
   <SQL SQL='<SQL DATA>' Name='<DATA LABEL>'></SQL>
   <SQL SQL='<SQL DATA>' Name='<DATA LABEL>'></SQL>
</MySQLRequest>

where:

  • SQL holds the SQL call that will sent to the MySQL database.
  • Name holds the name for the data that is returned by the SQL call from the MySQL database. The importance of this becomes clearer later in this article; it enables you to know which SQL statement returned that data so you can parse it correctly.

Now that you understand the schema, you are ready to begin creating the XML.

Making data XML-friendly

It would be great to simply create a string and then add that to the XML and have it work. Unfortunately, that is not the case. Like SQL statements, XML does not like single quotes very much either. To remedy the problem, I created the fixSingleQuotes function, which takes a string and converts it so that single quotes are properly formatted for XML:

 
public function fixSingleQuotes(strTemp:String):String
{
   var pattern:RegExp = /x27/gi;
   return (strTemp.replace(pattern, "'"));
}

This function simply finds all the single quotes and replaces them with the XML-friendly character entity, '. To do this, the function creates a regular expression object:

 
var pattern:RegExp = /x27/gi;

The object has the single quote in it (/x27/; this is the ASCII Hex value for the single quote). Then the code specifies to replace all instances (g means “replace all matches”) and ignore case (i means “evaluate without case sensitivity”). The function then calls the replace method of the string with the regular expression pattern and specifies that, when it finds a single quote, to replace it with the “‘” string and return resulting modified string:

 
return (strTemp.replace(pattern, "'"));

Now that you’ve fixed the single quotes, you are ready to create the SQL XML.

Packaging the SQL statements into XML

To make the creation of the XML easier, I created the createSQLXML function:

 
public function createSQLXML(SQL:String,
DataLabel:String):String
{
   return ("<SQL SQL='" + fixSingleQuotes(SQL) + "' Name='" + DataLabel + "'></SQL>");
}

This function uses the fixSingleQuotes function to fix any single-quote issues that may exist in the SQL string, and then package the string into XML that the ASP script will undertand. This example shows how to bring all this together to format the SQL and package it up into XML:

 
var SQLXML:String = "";
SQLXML = createSQLXML("SELECT * FROM MyTable WHERE Name='Craig'", "Product");

This code calls the createSQLXML function and then assigns the resulting XML to the SQLXML variable. Here is what is returned from the createSQLXML call:

 
<SQL SQL='SELECT * FROM myTable WHERE Name='Craig'' Name='Customer''></SQL>
 

Notice how the single quotes have been modified so that the XML does not confuse them for other attribute names or data.

You do not have to just send one SQL statement to the database. If you want to send more than one, just concatenate the results of the createSQLXML calls into one variable. Here is one example:

 
var SQLXML:String = "";
SQLXML += createSQLXML("SELECT * FROM MyTable WHERE Name='Craig'", "C_Product");
SQLXML += createSQLXML("SELECT * FROM MyTable WHERE Name='Susan'", "S_Product");
SQLXML += createSQLXML("SELECT * FROM MyTable WHERE Name='Giada'", "G_Product");

This batches up the SQL statements so that the database processes all three and then sends back the results of all three in one XML package.

Now that you have seen how to package up the SQL calls into XML, find out what’s involved in sending them to the server-side ASP script.

Communicating with the ASP script

Once you create the XML, the next step is to send it to the server-side ASP script and let it work its magic. When the SQL statement is finished gathering up all of its data, it returns an XML packet back to Flash, where the application parses through the returned data. First, I’ll show you how the data is sent from Flash to the server-side ASP script.

Sending the XML to the ASP script

Once you know how to set up a connection and send data from Flash to the world, sending the XML to the ASP script is pretty easy. This section covers sending the data to a server-side ASP script using a simple HTTP POST. To make sending XML data easier, I wrote the sendSQLXML function:

 
public function sendSQLXML(aspURL:String, SQLXML:String,
returnSQLXMLCallback:Function):void
{
   var myXMLURL:URLRequest = new URLRequest(aspURL);
   var variables:URLVariables = new URLVariables();
   variables.xmlSQL = "<MySQLRequest>" + SQLXML + "</MySQLRequest>";
   myXMLURL.data = variables;
   myXMLURL.method = URLRequestMethod.POST;
   var myLoader:URLLoader = new URLLoader();
   myLoader.addEventListener("complete", returnSQLXMLCallback);
   myLoader.load(myXMLURL);
}

This function accepts a URL string, SQLXML, and a callback function as parameters. The URL string is the fully qualified path to the ASP file. SQLXML is the XML that was created from the SQL calls, and the callback function is the function that is called when the data is returned from the ASP script.

Dig a little deeper into the function. The first thing it does is create a new URLRequest object using the URL string that points to the ASP file:

 
<span style="font-size: 11pt">var myXMLURL:URLRequest = new URLRequest(aspURL);</span>

The function then creates a URLVariables object to store the SQLXML. Notice that it wraps the SQL in MySQLRequest and then assigns this to the xmlSQL object within the URLVariable. This xmlSQL object does not exist in the URLVariable by default. Any variable object desired in variables can be created by using this method:

 
var variables:URLVariables = new URLVariables();
variables.xmlSQL = "<MySQLRequest>" + SQLXML + "</MySQLRequest>";

Next the URLVariable is loaded into the data of the URLRequest and the method of the request is set to POST:

 
myXMLURL.data = variables;
myXMLURL.method = URLRequestMethod.POST;

Finally, a URLLoader object is created, which initiates the POST of the XML data to the ASP script. An event listener is added that will be called when the loader returns from getting the data from the ASP script:

 
   var myLoader:URLLoader = new URLLoader();
   myLoader.addEventListener("complete", returnSQLXMLCallback);
   myLoader.load(myXMLURL);

That’s all it takes to post data to an ASP script. It is not really useful, though, if your ASP script does not know what to do with the XML data when it gets it. The next section details what the ASP script does with the XML when it gets it.

What happens in the ASP script

Here’s what the ASP does with all of the XML you send it. For the server-side scripting language, I’ve used VBScript in an ASP page. Below is the entire ASP script, which is called getSQLData.asp. This ASP script is available in the sample files provided with this article.

 
<%@ Language=VBScript %>
<%
Dim DBData
Dim SQL
Dim DataName
Dim xmlDoc
Dim Connection_String
Connection_String = "Driver={MySQL ODBC 3.51 Driver};Server=<YOUR
SERVER>;Port=<SERVER PORT>;Option=4;Database=<YOUR
DATABASE>;Uid=<YOUR ID>;Pwd=<YOUR PASSWORD>;"
set xmlDoc = Server.CreateObject("Msxml.DOMDocument")
xmlDoc.async = false
xmlDoc.loadXML(Request.Form("xmlSQL"))
Response.Write("<MySQLResponse>")
Set nodeList = xmlDoc.selectNodes("//SQL")
For Each TempNode In nodeList
   SQL = TempNode.Attributes.Item(0).nodeValue
   DataName = TempNode.Attributes.Item(1).nodeValue
   set DBData = Server.CreateObject("ADODB.Recordset")
   DBData.ActiveConnection = Connection_String
   DBData.Source = SQL
   DBData.CursorType = 0
   DBData.CursorLocation = 2
   DBData.LockType = 3
   DBData.Open()
   DBData_numRows = 0
   Response.Write("<" & DataName & ">")
   While (NOT (DBData.EOF))
      Response.Write ("<Data>")
      Dim i
      For i = 0 to DBData.Fields.Count - 1
         Response.Write ("<" & DBData.Fields.Item(i).Name & ">")
         Response.Write (DBData.Fields.Item(i).Value)
         Response.Write ("</" & DBData.Fields.Item(i).Name & ">")
      Next
      Response.Write ("</Data>")
      DBData.MoveNext()
   Wend
   Response.Write("</" & DataName & ">")
   DBData.Close()
   Set DBData = Nothing
Next
Response.Write("</MySQLResponse>")
set XMLDoc = Nothing
%>

Let me break it apart to show you what each section does. The top of the script includes the general variable declarations as well as the definition of the connection string that is used to talk to the database. A generic connection string is given in the sample code above; you will need to modify it with the connection information for your MySQL database.

The code then creates an XML document object to store the XML that was sent from Flash, and then sets the async property of the document to false:

 
set xmlDoc = Server.CreateObject("Msxml.DOMDocument")
xmlDoc.async = false

Next it loads the XML from Flash into the XML document:

 
xmlDoc.loadXML(Request.Form("xmlSQL"))

Notice that what it is loading is the xmlSQL variable that was loaded into the URLVariable in Flash. URLVariables are just form objects when they are posted.

The function then gets the data from the database and creates a response that will be sent back to Flash. This statement will write out the root tag of the XML response that is sent back to Flash:

 
Response.Write("<MySQLResponse>")

The function then creates a list of all the SQL nodes in the XML:

 
Set nodeList = xmlDoc.selectNodes("//SQL")

Now the function is ready to use the information in the XML to make the SQL call and create the response. This is done inside a loop so that all of the SQL calls will be processed. The first statement of this loop gets the SQL, which you will recall is the first attribute of the XML. It then gets the second attribute, which is the name:

 
SQL = TempNode.Attributes.Item(0).nodeValue
DataName = TempNode.Attributes.Item(1).nodeValue

Then the function creates and sets up the connection to the database:

 
set DBData = Server.CreateObject("ADODB.Recordset")
DBData.ActiveConnection = Connection_String
DBData.Source = SQL
DBData.CursorType = 0
DBData.CursorLocation = 2
DBData.LockType = 3
DBData.Open()
DBData_numRows = 0

Next it writes out the name of the data so that it is easier to parse when it is returned to Flash:

 
Response.Write("<" & DataName & ">")

It then loops through the fields in the row data returned from the SQL call and builds up the XML based on this data:

 
Response.Write ("<Data>")
Dim i
For i = 0 to DBData.Fields.Count - 1
   Response.Write ("<" & DBData.Fields.Item(i).Name & ">")
   Response.Write (DBData.Fields.Item(i).Value)
   Response.Write ("</" & DBData.Fields.Item(i).Name & ">")
Next
Response.Write ("</Data>")

Once all of the data in the fields of the row data has been written out, the function moves on to the next row, if one exists:

 
DBData.MoveNext()

If no other row data exists, it closes the DataName tag, closes the connection to the database, and frees up the database connection variable:

 
Response.Write("</" & DataName & ">")
DBData.Close()
Set DBData = Nothing

This process continues until all of the SQL calls contained in the XML are processed, at which point the closing MySQLResponse tag is written out and XML document variable is freed up:

 
Response.Write("</MySQLResponse>")
set XMLDoc = Nothing

As you can see, by using only a little code, you can gather up a ton of data from the database and return it to Flash.

After looking at the code, you may be wondering what kind of XML will Flash be getting back. To understand that, first look at the schema of the returned XML.

Returning XML schema

Examining the code, you might have a good idea of what the schema of the XML returned to Flash looks like. Just to make sure everyone is on the same page, I have created an example of the schema below:

 
<MySQLResponse>
   <DataName>
      <Data>
         <FirstFieldName>Value</FirstFieldName>
         <SecondFieldName>Value</SecondFieldName>
         <ThirdFieldName>Value</ThirdFieldName>
      </Data>
   </DataName>
</MySQLResponse>

where:

  • MySQLResponse is the root tag of the XML that is returned to Flash.
  • DataName is the name that was supplied with the XML to help identify the SQL statement data when it is parsed in Flash.
  • Data denotes a row of data from the database. There will be as many Data elements in each XML as there are rows in the data returned from the database.
  • FirstFieldName, etc. are the fields associated with a row returned from the database. The name of the tag corresponds with the name of the field in the database. There will be as many fields returned as there are columns in the row.

Now that you know what you will be getting back from Flash, take a look at how Flash parses the XML.

Parsing the XML returned from the ASP script

Once the data comes back from the ASP script, Flash needs to go through it and put it to use. Previously I supplied a callback function to the loader object when I made the POST to the ASP script. When the data comes back it will be this function that parses the XML data. Here’s an example of such a function:

 
public function handleXMLData(evtObj:Event):void
{
   // Put the returned XML in an XML object.
   var MySQLXML:XML = XML(evtObj.target.data);
   // This will get you to where the data begins.
   var MySQLDataXML:XMLDocument = new XMLDocument();
   MySQLDataXML.ignoreWhite = true;
   MySQLDataXML.parseXML(MySQLXML);
   // Dig deeper for the data.   
   var rsNode:Object = MySQLDataXML.firstChild.firstChild;
   // Walk the returned recordset.
   while (rsNode != null)
   {
      var DataNode:Object = rsNode.firstChild;
      while (DataNode != null)
      {
         var FieldNode:Object = DataNode.firstChild;
         // Handle data based on which SQL call this
         // data comes from.             
         if (rsNode.nodeName == "DataName")
         {
            // Go through the fields returned.
            while (FieldNode != null)
            {
               if (FieldNode.nodeName == "FirstFieldName")
               {
                  //Do something with first field data.
                  strFirstFieldName = FieldNode.firstChild.nodeValue;
               }
               else if (FieldNode.nodeName == "SecondFieldName")
               {
                  //Do something with second field data.
                  strSecondFieldName = FieldNode.firstChild.nodeValue;
               }
               else if (FieldNode.nodeName == "ThirdFieldName")
               {
                  //Do something with third field data.
                  strThirdFieldName = FieldNode.firstChild.nodeValue;
               }
               //Move to next field node.
               FieldNode = FieldNode.nextSibling;
            }
         }
         // Move to next data node.
         DataNode = DataNode.nextSibling;
      }
      // Move to next SQL call's recordset.
      rsNode = rsNode.nextSibling;
   }
}

When the data comes back to Flash from the ASP script, it is returned in the Event object of the Loader object. This Event object is passed into the callback function as the evtObj parameter. Take this object and get the XML out of it, and then put it into a Flash-friendly XMLDocument object. That is what the code below does: it extracts the XML from the Event object and puts into an XMLDocument object so that it easy to use in Flash:

 
// Put the returned XML in an XML object.
var MySQLXML:XML = XML(evtObj.target.data);
// This will get you to where the data begins.
var MySQLDataXML:XMLDocument = new XMLDocument();

A couple of other methods are called to clean up the XMLDocument:

 
MySQLDataXML.ignoreWhite = true;
MySQLDataXML.parseXML(MySQLXML);

You are now ready to dig into the XMLDocument and get out the data you want. The first thing to do is get to the root where the data is:

 
var rsNode:Object = MySQLDataXML.firstChild.firstChild;

In terms of the schema for the XML returned from the ASP, this statement places the function at the first <DataName> tag. I’ve set up a while loop to walk through all of the <DataName> packets. Within that loop, another while loop walks through the <Data> that represents the rows. Finally, another while loop walks through the <FieldNames> that represent the column data for each row.

Although this function looks only for one <DataName> when parsing the data, if you were to provide multiple SQL statements, you could give each one a different data name when submitting them and then parse the data differently depending on each data name.

You now have the tools to get data from a MySQL database using Flash, XML, and a server-side ASP script. I hope this solution encourages you to write RIAs with Flash or Flex. Even though this article focused on Flash, you could use this same code to write a Flex application or even an application on Adobe AIR.

Where to go from here

This article describes SQL calls that get data from the database. In a future article I will describe how to use what you learned here to put data back into the database.

I do not address error handling in this article. The next logical step for this code would be to add some kind of protection so that when errors do occur on the server, you can respond to them appropriately.

Also not mentioned in this article are the very useful functions, DateAdd, Trim, LTrim, and RTrim. LTrim removes the leading white space, RTrim removes the trailing white space, and Trim removes both the leading and trailing white space. The DateAdd function enables you to add second, minutes, hours, days, or weeks to a date to get a new date. This is really handy when you are trying to figure out what date is two weeks from now when it crosses over a month boundary.

You can find these functions, along with the others listed in this article, in the General_Functions.as file included with the sample files linked to at the beginning of this article.

Creative Commons License
This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License

About the author

Craig Simmons is a senior lead quality engineer on the Flash authoring team at Adobe Systems. He has been in the software industry for 17 years and worked at companies such as Macromedia and Micrografx before coming to work at Adobe. He is also a Microsoft Certified Professional in Visual Basic .NET, although he writes more ActionScript than VB these days.

About the Author:

Carlos Pinho
A father, a husband and a geek... Carlos was the founder of projects like The Tech Labs and Flash Enabled Blog. He is the founder of TekTuts He is passionate about technologies. Their main skills are in analytics, transport & logistics, business administration. He also writes about programming resources, trends, strategy and web development.