SQLDatabase (Adobe AIR only)

Package:
Class:
Inheritance:
biz.flashscript.adobeAir
public class SQLDatabase
EventDispatcher
The SQLDatabase class processes a local Database for Adobe Air. The Database can be moved to different locations and placed in folders. Using this class a table can be created for the Database and data can be processed to and from the database. Use the constructor new SQLDatabase().
Public Properties
Property Defined By
sqlArray : Array
To create the table with parameters and values.
SQLDatabase
tableName : String
To give the table a name.
SQLDatabase
sqlTable : String
The table contents.
SQLDatabase
sqlResult : SQLResult
To get the table contents.
SQLDatabase
Public Methods
Method Defined By
SQLDatabase()
Creates a new instance of the SQLDatabase class.
SQLDatabase
makeSQLConnection(file:File, fileName:String="Test.db"):void
Creates a new Database.
SQLDatabase
makeFolder(dir:String, folder:String="sqldatabase"):File
Creates the folder for the database at a location.
SQLDatabase
processTable(sqlData:String):void
Function to add data to the table.
SQLDatabase
Events
Event Defined By

tableCreated

This event is dispatched when the table is created.

SQLDatabase

dataProcessed

This event is dispatched when the data are successfully added to the table.

SQLDatabase
All inherited events for the EventDispatcher class. N/A
Property Detail
sqlArray property
sqlArray:Array [read-write]

This is the array with values and their properties to create the table.

Implementation

public function get sqlArray():Array
public function set sqlArray(value:Array):void
tableName property
tableName:String [read-write]

This will hold the name for the table.

Implementation

public function get tableName():String
public function set tableName(value:String):void
sqlTable property
sqlTable:String [read-write]

This will create a new table.

Implementation

public function get sqlTable():String
public function set sqlTable(value:String):void
sqlResult property
sqlResult:SQLResult [read]

This function allows to get the table contents.

Implementation

public function get sqlResult():SQLResult
public function set sqlResult(value:SQLResult):void
Constructor Detail
SQLDatabase ()  Constructor
public function SQLDatabase()
Intializes a new SQLDatabase instance.
Method Detail

makeSQLConnection()  method
public function makeSQLConnection(file:File, fileName:String="Test.db"):void

This method will initiate the database creation.

Parameters

  • file:File ____ Specifies the file and its location for the database.
  • fileName:String ____ Optional: the name of the database. Default is "Test.db".

makeFolder()  method
public function makeFolder(dir:String, folder:String="sqldatabase"):File

Function will create the folder at a certain location and returns it.

Parameters

  • dir:String ____ Specifies the database location ("storage","desktop","documents","user"). Default is applicationStorageDirectory.
  • folder:String ____ Optional: Specifies the folder name. Default is "sqldatabase".

processTable()  method
public function processTable(sqlData:String):void

Function allows adding data to the table using SQL syntax.

Parameters

  • sqlData:String ____ data for the table.
Event Detail

tableCreated event

Event Object Type: biz.flashscript.adobeAir.SQLDatabase
Event.type property = biz.flashscript.adobeAir.SQLDatabase.TABLE_CREATED

Dispatched when the table is created.

dataProcessed event

Event Object Type: biz.flashscript.adobeAir.SQLDatabase
Event.type property = biz.flashscript.adobeAir.SQLDatabase.DATA_PROCESSED

Dispatched when the data are successfully added to the table.

Examples
Create a new fla file and name it SQLSyntaxExample.fla. Set the Publish settings to Adobe Air. Place the fla in the same folder as the biz folder. Put a DataGrid component (myGrid), three TextInput components (nameField, salaryField, ageField) and three Button components (submitBut, searchBut, deleteBut) and one label component (resultField) on the timeline. Then create an Actionscript file, name it SQLSyntaxExample.as and place this script.
package 
{
	import flash.display.Sprite;
	import biz.flashscript.adobeAir.SQLDatabase;
	import flash.events.*;
	import flash.data.SQLConnection;
	import fl.controls.Button;
	import fl.controls.TextInput;
	import flash.data.SQLResult;
	import fl.controls.DataGrid;
	import fl.controls.dataGridClasses.DataGridColumn;
	import biz.flashscript.adobeAir.SQLSyntax;
	import flash.filesystem.File;
	import flash.net.FileFilter;

	public class SQLDatabaseExample extends Sprite
	{
		public var submitBut:Button;
		public var searchBut:Button;
		public var nameField:TextInput;
		public var salaryField:TextInput;
		public var ageField:TextInput;
		public var myGrid:DataGrid;
		private var conn:SQLConnection;
		private var mySQL:SQLDatabase;
		private var sqlsyn:SQLSyntax;
		private var sqlArray:Array;
		private static var tableIsok:Boolean = false;

		public function SQLDatabaseExample ():void
		{
			init();
		}
		private function init ():void
		{
			// creating a new SQLDatabase object.
			//
			mySQL = new SQLDatabase();
			//
			// creating the location for the database
			//
			var file:File = mySQL.makeFolder("desktop");
			//
			// creating the database itself
			//
			mySQL.makeSQLConnection (file);
			//
			// writng the table
			//
			sqlsyn = new SQLSyntax();
			
			sqlArray = new Array ({name:"user", value:"TEXT"},{name:"salary", value:"TEXT"},{name:"age", value:"TEXT"});
			mySQL.sqlArray = sqlArray;
			mySQL.sqlTable = sqlsyn.createDefaultTable("employee",sqlArray);
			mySQL.tableName = "employee";
			mySQL.addEventListener (SQLDatabase.DATA_PROCESSED, openedDatabase);
			mySQL.addEventListener (SQLDatabase.TABLE_CREATED, resultFunction);
			submitBut.label = "Add data";
			submitBut.addEventListener (MouseEvent.CLICK, insertData);
			searchBut.label = "Show Data";
			searchBut.addEventListener (MouseEvent.CLICK, searchData);
			deleteBut.label = "Delete";
			deleteBut.addEventListener (MouseEvent.CLICK, deleteData);
			/*
			/ We define the columns for the DataGrid.
			*/
			var no:DataGridColumn=new DataGridColumn();
			var item:DataGridColumn=new DataGridColumn();
			var price:DataGridColumn=new DataGridColumn();
			myGrid.columns = ["name","salary","age"];
			myGrid.getColumnAt(0).width = 175;
			myGrid.getColumnAt(1).width = 75;
			myGrid.getColumnAt(2).width = 50;
			myGrid.getColumnAt(0).headerText = "NAME";
			myGrid.getColumnAt(1).headerText = "Salary";
			myGrid.getColumnAt(2).headerText = "Age";
			mySQL.addEventListener (SQLDatabase.DATA_PROCESSED, resultFunction);
		}
		private function openedDatabase (event:Event):void
		{
			trace("DATABASE OPEN!");
		}
		private function insertData (event:Event):void
		{
			if (tableIsok && nameField.text != "" && salaryField.text != "" && ageField.text != "")
			{
				var sqlValues:Array = new Array(nameField.text,salaryField.text,ageField.text);
				var sql:String = sqlsyn.insMultipIntoTable ("employee", sqlArray, sqlValues);
				mySQL.processTable (sql);
				mySQL.addEventListener (SQLDatabase.DATA_PROCESSED, resultFunction);
			}
			else
			{
				trace ("Fill out all fields");
			}
		}
		private function deleteData (event:Event):void
		{
			var sql:String = "";
			sql +=  "SELECT * FROM employee";
			mySQL.processTable (sql);
			sql = sqlsyn.deleteData("employee","user",nameField.text);
			mySQL.processTable (sql);
			mySQL.addEventListener (SQLDatabase.DATA_PROCESSED, resultFunction);
			sql = "SELECT * FROM employee";
			mySQL.processTable (sql);
			mySQL.addEventListener (SQLDatabase.DATA_PROCESSED, searchFunction);
		}
		private function searchData (event:Event):void
		{
			var sql:String = "";
			sql +=  "SELECT * FROM employee";
			mySQL.processTable (sql);
			mySQL.addEventListener ("dataProcessed", searchFunction);
		}
		private function searchFunction (event:Event):void
		{
			myGrid.removeAll ();
			event.currentTarget.removeEventListener (SQLDatabase.DATA_PROCESSED, searchFunction);
			var result:SQLResult = mySQL.sqlResult as SQLResult;
			if (result!=null)
			{
				if (result.data != null)
				{
					var numRows:int = result.data.length;
					for (var i:int = 0; i < numRows; i++)
					{
						var row:Object = result.data[i];

						if (row.user != null)
						{
							myGrid.addItem ({name:row.user,salary:row.salary,age:row.age});
						}
					}
				}
			}
		}
		private function resultFunction (event:Event):void
		{
			event.currentTarget.removeEventListener (SQLDatabase.DATA_PROCESSED, resultFunction);
			if (event.type == "tableCreated")
			{
				trace ("Table created");
				tableIsok = true;
			}
			if (event.type == "dataProcessed")
			{
				trace ("Data processed");
			}
		}
	}
}