Flash CS4 + SQLITE

sam picture sam · Feb 14, 2010 · Viewed 9.3k times · Source

I'm looking for some information regarding using SQLITE with AIR in Flash CS4, I cannot find any good examples, they're all built for Flex (which I don't want to use). Can anyone give me some basic examples of how to do this with Flash CS4 or direct me to some code examples / tutorials? I cannot find any anywhere...

Answer

Todd Moses picture Todd Moses · Feb 16, 2010

Here is an example from: http://www.flashmove.com/forum/showthread.php?t=34778

import flash.data.SQLResult;
import flash.filesystem.File;
import flash.data.SQLStatement;
import flash.data.SQLConnection;
import flash.data.SQLColumnSchema;
import flash.data.SQLTableSchema;
import flash.data.SQLSchemaResult;
import flash.events.SQLEvent;
import flash.events.SQLErrorEvent;
import flash.events.MouseEvent;


var sqlFile:File;
var sqlConn:SQLConnection;
var sqlStatement:SQLStatement;
init();
//- BUTTONS
go_mc.buttonMode = true;
go_mc.addEventListener(MouseEvent.MOUSE_DOWN, runQuery)
readDB_mc.buttonMode = true;
readDB_mc.addEventListener(MouseEvent.MOUSE_DOWN, readDB)

var exampleQuery:String = "INSERT INTO users (First_Name, Last_Name) VALUES ('Betty', 'Boil')";
query_txt.text = exampleQuery;


/**
 * Setup connection to db file
 */
function init():void {
    sqlConn = new SQLConnection();
    sqlConn.addEventListener(SQLEvent.OPEN, connOpenHandler);
    sqlConn.addEventListener(SQLErrorEvent.ERROR, errorHandler);

    sqlFile = new File(File.applicationDirectory.nativePath);
    sqlFile = sqlFile.resolvePath("users.db");
    feedback("Path to db file: "+sqlFile.nativePath);

    sqlConn.open(sqlFile);
}

function connOpenHandler(event:SQLEvent):void {
    feedback("DB Open");
    sqlStatement = new SQLStatement();
    sqlStatement.sqlConnection = sqlConn;
    sqlStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);

    dbScheme();
    readDB();
}

/**
 * Output the DB structure
 */
function dbScheme() {
    feedback("Schema...");
    sqlConn.loadSchema();
    var result:SQLSchemaResult = sqlConn.getSchemaResult();

    var table:SQLTableSchema = result.tables[0];
    //var column:SQLColumnSchema = table.columns[0]
    feedback("\tTable: "+table.name)
    for (var i=0; i<table.columns.length; i++) {
        feedback("\tColumn "+i+" - "+table.columns[i].name);
    }
}
/**
 * Output DB contents
 */
function readDB(e:Event = null) {
    sqlStatement.addEventListener(SQLEvent.RESULT, selectResultHandler);
    sqlStatement.text = "SELECT * FROM users";
    sqlStatement.execute();
}

/**
 * Run custom query
 */
function runQuery(e:MouseEvent) {
    var sqlQuery:String = query_txt.text;
    feedback("Query: "+sqlQuery);

    sqlStatement.addEventListener(SQLEvent.RESULT, queryResultHandler);
    sqlStatement.text = sqlQuery;
    sqlStatement.execute();
}
function queryResultHandler(e:SQLEvent){
    sqlStatement.removeEventListener(SQLEvent.RESULT, queryResultHandler);
    readDB();
}
/**
 * Handle readDB (SELECT) query
 */
function selectResultHandler(event:SQLEvent):void {
    feedback("Query Results...");
    sqlStatement.removeEventListener(SQLEvent.RESULT, selectResultHandler);

    var result:SQLResult = sqlStatement.getResult();

    if (result.data != null) {
        var numRows:int = result.data.length;
        for (var i:int = 0; i < numRows; i++) {
            var row:Object = result.data[i];
            feedback("\tid:"+ row.id+ ", name:"+ row.First_Name+" "+row.Last_Name);
        }
    }
}

function errorHandler(event:*):void {
    feedback("An error occured while executing the statement.");
}
function feedback(w:*) {
    output_txt.appendText(w+"\n");
    output_txt.verticalScrollPosition = output_txt.maxVerticalScrollPosition;
}