In this tutorial we're going to show you how to connect to a Microsoft Access database file but everything you learn here will apply equally to other data sources (SQL Server, MySQL, Oracle, etc).

To follow this tutorial you'll need to be familiar with ActionScript 3. You can use Flash, Flex or your authoring tool of choice, as long as it supports ActionScript 3.

You don't need to have Access installed unless you want to open the example database file. An ADO provider for Access ships with all recent versions of Windows and that's all we need to work with these files in SWF Studio.



What is ADO?

ADO stands for ActiveX Data Objects and it's really just a set of COM objects designed to let you write programs that access data without knowing how the database is implemented. All you need to know is how to connect to the database you want to access. All the details about how to work with a particular database is handled by the ADO provider for that database.

Because the ADO provider hides the details of the underlying data object, database files aren't the only things you can connect to with ADO. There are ADO providers for database servers (e.g. SQL Server, MySQL, Oracle), Excel spreadsheets and even specially formatted text files.

The SWF Studio ADO plugin doesn't expose the real ADO objects and interfaces, instead it provides a wrapper that simplifies things and exposes just the core ADO functionality that you'll need for 99% of your data access projects.

Making A Connection

The only time you really have to care about what type of data source you're using is when you connect to it. ADO uses connection strings to figure out how to connect to different data sources. A good connection string reference can be found at connectionstrings.com. This will also give you a good idea of how many different data sources you can manipulate with ADO.

The setConnectString method takes one parameter, the connection string that tells ADO about the dats source you want to connect to. The example below shows the connection string you need to connect to an Access database file, called "Tutorial.mdb", that is in the same folder as your EXE.

ssCore.ADO.setConnectString({connectString:"Provider=Microsoft.Jet.OLEDB.4.0; Persist Security Info=False; Data Source="+ssGlobals.ssStartDir+"\\Tutorial.mdb;"});
The Data Source portion of the Access connection string requires a fully qualified path to the database file. We could hardcode the path, but instead we've used ssGlobals.ssStartDir to refer to the folder where our EXE is running. ADO.setConnectString doesn't actually make the connection That command just sets up the connection string that the ADO plugin will use when you attempt to open the data source.

A Simple Query

Let's look at how we can use SQL to query the data source and retrieve some information. One way to do that is with the setSQL method. This is how we ask for all rows and all columns in the Products table.

ssCore.ADO.setSQL({sql:"SELECT * FROM Products"});
The setSQL method doesn't actually execute the query, it just tells the ADO plugin what query we'd like to execute when we make the connection.

You can replace the SQL in that example with any valid SQL for the data source you're connected to. Most people play it safe and stick to SQL-92 syntax, which most data sources understand.

How Do You Like Your Data?

The ADO plugin can return the results of a query in a variety of formats. Your choices are: XML, CSV, URL, DLM, XML_Attribs, and XML_Nodes. Please see the help for ADO.setRowFormat for more details. For this example we'll use XML_NODES since AS3 has native support for XML using E4X.

ssCore.ADO.setRowFormat({format:"XML_NODES"});
Opening the Data Source

Finally, we'll put everything together and open the datasource to create a recordset.

ssCore.ADO.setConnectString({connectString:"Provider=Microsoft.Jet.OLEDB.4.0; Persist Security Info=False; Data Source="+ssGlobals.ssStartDir+"\\Tutorial.mdb;"});
ssCore.ADO.setSQL({sql:"SELECT * FROM Products"});
ssCore.ADO.setRowFormat({format:"XML_NODES"});
ssCore.ADO.open({}, {callback:openHandler});

It's a good idea to call the ADO.open method asynchronously to avoid problems where the data source is unavailable or unreachable. The openHandler will be called by SWF Studio when we've successfully connected to the data source, or when an error condition is detected, but it won't cause the rest of the application to stop responding while we wait.

Reading the Results

Once we have successfully opened the data soruce we need to retrieve the results. We have the choice of moving through the results one row at a time, asking for a particular row, or having all rows returned at once. We'll choose all at once since the recordset isn't very large in this example.

The following is the implementation of the openHandler callback function we specified in the previous example. It includes some error handling that will display a message in the SWF Studio Trace tab (make sure to build the EXE with the debug option enabled) if anything goes wrong.

function openHandler(return_obj:Object, callback_obj:Object, error_obj:Object):void
{
   if (return_obj.success)
   {
      ssDebug.trace("Database connection successfully opened.");

      // We've successfully connected to the database and a recordset is now available.
      // We now need to get the results from the database.
      ssCore.ADO.getRows({}, {callback:datasetHandler});
   }
   else
   {
      ssDebug.trace("ERROR: "+error_obj.description);
   }
}

The ADO.getRows method gets all the rows generated by our SQL query in one shot. We've called this function asynchronously so if a large amount of data is generated our EXE will remain responsive while the data is retrieved.

Once the getRows method completes, our datasetHandler callback function is called and we can create an XML object from the results.

function datasetHandler(return_obj:Object, callback_obj:Object, error_obj:Object):void
{
   if (return_obj.success)
   {
      ssDebug.trace("Results successfully retrieved");

      // We now have the results from the recordset in a string. We'll convert the string to XML.
      var products:XML = new XML(return_obj.result);
   }
   else
   {
      ssDebug.trace("ERROR: "+error_obj.description);
   }
}

Tiptoe Through the Data

Now that we have the data in an XML document we can use familiar XML object methods and properties to access that data.

ssDebug.trace("Name in first record: "+unescape(products.row[0].Name.text()));
Because we used XML_NODES as our data format, the ADO plugin escapes all the data it passes back to us to make sure it is safe to contain in an XML document. The unescape() call above undoes that and makes sure the data we get out of the XML object is exactly what was stored in the database file.

Closing the Connection

Any open ADO connections will be automatically closed when the application quits, but it's always a good idea to do it manually as soon as we're done.

ssCore.ADO.close();
The Complete Example

Below is the full example from this tutorial as a self-contained class, which you can also find in the SPX file. To use an SPX file you need to have SWF Studio installed. Just download the SPX file and double-click to open it in SWF Studio. A folder will be created with the same name as the SPX file. Inside the folder you 'll find an FLA, AS class file, SWF, and SPF (SWF Studio project file). SWF Studio will automatically open the SPF file so all you have to do is press the Build button to create an EXE.

NOTE: There is no user interface for this example. Instead we have used ssDebug,trace commands to display messages in the SWF Studio Trace Tab.

package
{
   import flash.display.*;
   import flash.events.*;

   public class ADOTutorial extends MovieClip
   {
      public function ADOTutorial()
      {
         if (stage) init();
         else addEventListener(Event.ADDED_TO_STAGE, init);
      }

      private function init(e:Event=null):void
      {
         ssCore.ADO.setConnectString({connectString:"Provider=Microsoft.Jet.OLEDB.4.0; Persist Security Info=False; Data Source="+ssGlobals.ssStartDir+"\\Tutorial.mdb;"});
         ssCore.ADO.setSQL({sql:"SELECT * FROM Products"});
         ssCore.ADO.setRowFormat({format:"XML_NODES"});
         ssCore.ADO.open({}, {callback:openHandler});
      }

      public function openHandler(return_obj:Object, callback_obj:Object, error_obj:Object):void
      {
         if (return_obj.success)
         {
            ssDebug.trace("Database connection successfully opened.");

            // We've successfully connected to the database and a results set is now available.
            // We now need to get the results from the database.
            ssCore.ADO.getRows({}, {callback:datasetHandler});
         }
         else
         {
            ssDebug.trace("ERROR: "+error_obj.description);
         }
      }

      public function datasetHandler(return_obj:Object, callback_obj:Object, error_obj:Object):void
      {
         if (return_obj.success)
         {
            ssDebug.trace("Results successfully retrieved");

            // We now have a results set in a string. We'll convert the string to XML.
            var products:XML = new XML(return_obj.result);

            // Trace the Name column value of the first row.
            // Notice the unescape call. Data is always escaped and will need to be unescaped.
            ssDebug.trace("Name in first record: "+unescape(products.row[0].Name.text()));

            // To view the data structure just uncomment the line below:
            //ssDebug.trace(products.toXMLString());

            // Optionally close the connection after use.
            ssCore.ADO.close();
         }
         else
         {
            ssDebug.trace("ERROR: "+error_obj.description);
         }
      }
   }
}

Download: ado_as3_tutorial.spx (85KB)