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:

var myXMLURL:URLRequest = new URLRequest(aspURL);

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.

Page 3 of 4

Previous Page

Next Page