Accessing databases

By using ADO, SWFKit and SWFKit Pro can access almost all types of databases such as Access, MySql, dBase, Firebird, SQL Server, Oracle, etc, only if you have OLEDB drivers for the databases. ADO is the popular and high performance database accessing tool of Microsoft. Using ADO in SWFKit is very simple and convenient, and you can easily copy your database accessing code from ASP to SWFKit. Another advantage of using ADO in SWFKit to access databases is that you can immigrate from one type of database to another without rewriting all your code, just by changing the connection string. From SWFKit v3, you can also use ADO directly in action script in Flash 8. However, you would have to wrap the ADO objects in Action script classes by yourself. At here we will just discuss how to use ADO in FFish script.

Connect to the database Before a database can be accessed from a SWFKit application, a database connection has to be established. The steps to make a database connection are as follows.

First, creating an ADO.Connection object

    var conn = new ActiveXObject("ADODB.Connection");

Second, creating a connection string and opening the connection. The connection string contains the provider, db name, etc. For example, to connect to a MS Access DB "c:
mydata
sample.mdb", the connection string is

var connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mydata\sample.mdb";
conn.open(connStr);

If you want to change the type of your database, you just need to modify this connect string. To connect to a MS SQL SERVER DB "Northwind" on server "mysite", the connection string is

var connStr = "Provider=SQLOLEDB;Server=mysite;Database=Northwind;" +
    "User Id=MyId;Password=123aBc";
conn.open(connStr);

SWFKit can connect to any database like VB, Delphi, VC or ASP can do.

How to ensure the connection has been made successfully? You can handle the "ConnectComplete" event of the ADO.Connection object. The event fires when the connection is completed.

    var conn = new ActiveXObject("ADODB.Connection");
    conn.connectOK = false;
    conn.ConnectComplete = function (err, status, cnt)
    {
        if (typeof err != "undefined")
        {
            trace(err.Description);

            return;
        }
        else
        {
            trace("Connect complete!");
            conn.connectOK = true;
        }

        trace("status: ", status.value);
    }

    var connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mydata\sample.mdb";
    conn.open(connStr);

    if (!conn.connectOK)
    {
        trace("Failed to connect to the DB");
        return;
    }

Retrieving Data from the Databases After the connection has been made, you can retrive data from database using the ADO.Recordset object. The steps are listed as follows

First, creating the ADO.Recordset object

var record = new ActiveXObject("ADODB.Recordset");

Second, opening the ADO.Recordset object with a specified command string

record.Open("select id, name, age, sex, score from student",
    conn, /*adOpenKeyset*/1, /*adLockPessimistic*/2);

Third, moving to the start of the record set

record.moveFirst();

Finally, fetching Data

    while (!record.eof)
    {
        trace(record.Fields(0).Value.toString());
            trace(record.Fields(1).Value.toString());
        trace(record.Fields(2).Value.toString());
        trace(record.Fields(3).Value.toString());
        trace(record.Fields(4).Value.toString());

        record.moveNext();
    }

Tips:

1) Moving to the start of the record set

record.MoveFirst();

2) Moving to the end of the record set

record.MoveLast();

3) Moving to the previous record

    record.MovePrevious();
    if (record.bof()) record.MoveFirst();

4) Moving to the next record

    record.MoveNext();
    if (record.eof) record.MoveLast();

Links

ADO Reference

ADO Tutorial