A list of products available on this site
Documentation for XtraMania's xtras
Prices and links to the online store
Xtras, PDFs, samples
Have a question? Ask us!
Contacts
Logo. www.xtramaina.com  
Home Search E-mail
Using ADODB in AIR

ActiveX2AIR - COM/ActiveX for AIR

ADODB Databasing in AIR/Flash AS3

ActiveX Data Objects (ADO) provides a universal programming way of handling databases. ActiveX2AIR allows using ADO right in Flash Builder AIR projects on Windows.
ADO documentation is probably already available at your Windows\Help folder. See ADO210.CHM file. Otherwise there is MSDN.

Below we will show how you can use ADODB right in Flash AIR project.

Create database connection

First of all, make sure you have added ActiveX2AIR ANE to your project and import types from:
import com.xtramania.*

To create a new COM object we use ActiveX.CreateObject method specifying the ProgId of the required object:


// Create connection object and setup events
var cnn:ActiveX;
cnn = ActiveX.CreateObject("ADODB.Connection");

If we are going to listen events fired by COM object, we should enable COM events and subscribe either to specific COM events or to generic ActiveXEvent.INCOMING event.


cnn.addEventListener(ActiveXEvent.INCOMING, onIncomingEvent );
cnn.EnableEvents( );
...

// Incoming events from COM objects are printed to log text box
private function onIncomingEvent(ev:ActiveXEvent ):void
{
  log.text += "\nEVT:" + ev.Name + "\n";

  if( ev.Args != null )
    for( var prop:String in ev.Args )
      log.text += " " + prop + " = " + ev.Args[prop] + "\n";

  log.scrollToRange( log.text.length );
}

Note: Make sure to correctly release COM objects when you use events. Failure to do so may cause memory and resource leaks. You should call something like code below wher you do need connection object anymore.

cnn.removeEventListener(ActiveXEvent.INCOMING, onIncomingEvent );
cnn.dispose();

Now we are ready to open a database connection. Usually all connection parameters are taken from connection string. Yo may see here to get one.


try
{
  cnn.call("Open", connectionString.text );
}
catch( err:ActiveXError )
{
  Alert.show( err.message, title );
}

Any COM method may fail, so we must catch exceptions of type ActiveXError to get error information about what is going wrong. We will get here if the specified connections string is wrong and ADODB.Connection object fails to actually connect to a database.

Query database with ADODB.Recordset object

To get some data we shoud create a new ADODB recordset object and bind it to the open connection to database. Then we can open it using SQL query and some options if needed. Once recordset is open we can read it and use to fill the data grid control.


try
{
  // Execute SQL query
  var rst:ActiveX = ActiveX.CreateObject("ADODB.Recordset");
  rst.setProp("ActiveConnection", cnn );

  rst.call("Open", sql.text, "#Missing", "#adOpenStatic" );

  // Fill data grid with data from recordset
  FillDataFromRecordset( rst );
}
catch( err:ActiveXError )
{
  Alert.show( err.message, title );
}

Populate datagrid from ADODB.Recordset

Below is the function that reads ADODB.Recordset, creates datagrid columns for returned data and fills datagrid with database records.


<mx:AdvancedDataGrid id="dataGrid" width="100%" height="100%" designViewDataType="flat" >
...

// Fill data grid with data from recordset
private function FillDataFromRecordset( rst:ActiveX ):void
{
  var fields:ActiveX = rst.getProp("Fields" ) as ActiveX;
  var fieldsCount:int = int(fields.getProp("Count"));

  var columns:Array = new Array();

  try
  {
    for( var i:int = 0; i < fieldsCount; i++ )
    {
      var field:ActiveX = fields.call("Item", i ) as ActiveX;
      var name:String = String(field.getProp("Name" ));

      var column:AdvancedDataGridColumn = new AdvancedDataGridColumn(name);
      column.headerText = name;
      column.dataField = name;
      column.resizable = true;

      columns.push(column);
    }
  }
  catch( err:ActiveXError )
  {
    Alert.show( err.message, title );
  }

  dataGrid.columns = columns;

  var data:Array = new Array();

  try
  {
    while( !Boolean(rst.getProp("EOF")))
    {
      var item:Object = new Object;

      for( i = 0; i < fieldsCount; i++ )
      {
        field = fields.call("Item", i ) as ActiveX;
        name = String(field.getProp("Name" ));
        var value:Object = field.getProp("Value" );

        if( value == null )
          value = "<NULL>";
        else if( value is ByteArray )
          value = hex( ByteArray(value) );

        item[name] = value;
      }

      data.push(item);

      rst.call("MoveNext");
    }
  }
  catch( err:ActiveXError )
  {
    Alert.show( err.message, title );
  }

  dataGrid.dataProvider = data;
  dataGrid.enabled = true;
  dataGrid.editable = "false";
}

Below is the screenshot of the sample AIR application that connects to the database and reads some data from SQL Server:

ADO sample screenshot

COM/ActiveX syntax in AS3

AIR Native Extension does not allow to integrate COM objects deep inside AS3, so the only way for extension is wrapping a COM object with call, getProp and setProp methods. In this way cascading property access does not look nice, but there is one usefull thing implemented in ActiveX2AIR to simplify that. It is Eval and EvalGet methods. They allow to get a cascaded property value in one call:

var recordset:ActiveX;
...

var value:Object = recordset.Eval("Fields('Data').Value");
or
var value:Object = recordset.Eval("Fields('Data')");

Use EvalGet if you need the real object instead of its default property value:
var field:ActiveX = recordset.EvalGet("Fields('Data')") as ActiveX;

COM/ActiveX objects often use named constants values, like adOpenStatic. ActiveX2AIR automatically checks string parameters starting with # to see whether it is a named constant. So "#adOpenStatic" will be replaced to appropriate numeric value.

Use special value "#Missing" to indicate missing parameter.

Download sources

Sample Flash Builder project is available to download.

Site homeSearchContact author © Eugene Shoustrov. www.xtramania.com