SQLSyntax (Adobe AIR only)

Package:
Class:
Inheritance:
biz.flashscript.adobeAir
public class SQLSyntax
EventDispatcher
The SQLSyntax class provides syntax for many SQL commands to manage a database. Use the constructor new SQLSyntax().
Public Properties
Property Defined By
resultLength : int
To get the number of results in finding matches.
SQLSyntax
Public Methods
Method Defined By
SQLSyntax()
Creates a new instance of the SQLSyntax class.
SQLSyntax
createDefaultTable(tabName:String, cellName:String, _id:String="Id"):String
Creates a new table with integer autoincremented.
SQLSyntax
createTable(tabName:String, cellName:String):String
Creates a new table without integer autoincremented.
SQLSyntax
dropTable(tabName:String):String
Statement drops table.
SQLSyntax
alterTable(tabName:String, newName:String, newColumn:String=null):String
Will alter the name of an existing table and allow adding a column.
SQLSyntax
insIntoTable(tabName:String, fieldName:String, valueField:String):String
Adds values to the database.
SQLSyntax
insMultipIntoTable(tabName:String, fieldName:Array, valueField:Array):String
Adds multiple values to the database.
SQLSyntax
createIndex(tabName:String, dataName:String, valueField:String):String
Function creates an index.
SQLSyntax
castData(tabName:String, dataName:String, colName:String):String
Function casts data as ByteArray or as int.
SQLSyntax
updateData(tabName:String, fieldName:String, newValue:String, oldValue:String):String
Function updates values in a table.
SQLSyntax
updateNullData(tabName:String, fieldName:String, newValue:String, primKey:String, idNum:String):String
Function updates values using the id.
SQLSyntax
deleteData(tabName:String, fieldName:String, myValue:String):String
Function deletes values from the table.
SQLSyntax
createView(tabName:String, viewName:String, colName:String, viewFeature:String=null):String
Function creates a new view of a table.
SQLSyntax
dropView(viewName:String):String
Function to drop a view of a table.
SQLSyntax
findString(tabName:String, _column:String, viewName:String):String
Finding a value in the table.
SQLSyntax
findMatch(_mySQL:SQLDatabase, tabName:String, _column:String, viewName:String):void
Function to find a specific column or columns, which have a certain value.
SQLSyntax
Events
Event Defined By

dataMatch

This event is dispatched when the "findMatch" function returns results.

SQLSyntax
Property Detail
resultLength property
resultLength:int [read-write]

To get the number of results in finding matches using the "findMatch" function. Use the dataMatch event.

Implementation

public function get resultLength():int
public function set resultLength(value:int):void
Constructor Detail
SQLSyntax ()  Constructor
public function SQLSyntax()
Intializes a new SQLSyntax instance.
Method Detail

createDefaultTable()  method
public function createDefaultTable(tabName:String, cellName:Array, _id:String="Id"):String

This method creates a table with automatic Id column as primary key and autoincrement.

Parameters

  • tabName:String ____ Specifies the name of the table.
  • cellName:Array ____ Specifies an Array, which should not only contain the column name but also the datatype (e.g user Text).
  • _id:String="Id" ____ Specifies the name of the integer primary key. Default is "Id".

createTable()  method
public function createTable(tabName:String, cellName:Array):String

This method creates a table without Id column as primary key.

Parameters

  • tabName:String ____ Specifies the name of the table.
  • cellName:Array ____ Specifies an Array, which should not only contain the column name but also the datatype (e.g user Text).

dropTable()  method
public function dropTable(tabName:String):String

This method drops a table.

Parameters

  • tabName:String ____ Specifies the name of the table.

alterTable()  method
public function alterTable(tabName:String, newName:String, newColumn:String=null):String

This method will alter the name of an existing table and allow adding a column.

Parameters

  • tabName:String ____ Specifies the name of the table.
  • newName:Array ____ Specifies the new name for the table.
  • colName:String=null ____ Optional: specifies the name of a new column. Default is null.

insIntoTable()  method
public function insIntoTable(tabName:String, fieldName:String, valueField:String):String

This method allows adding data into a database with a table with single field name.

Parameters

  • tabName:String ____ Specifies the name of the table.
  • fieldName:String ____ Specifies the field name.
  • valueField:String ____ Specifies the value to enter.

insMultipIntoTable()  method
public function insMultipIntoTable(tabName:String, fieldName:Array, valueField:Array):String

This method allows adding multiple values into a database with a table with multiple field names.

Parameters

  • tabName:String ____ Specifies the name of the table.
  • fieldName:Array ____ Specifies the field names.
  • valueField:Array ____ Specifies the values to enter.

createIndex()  method
public function createIndex(tabName:String, indexName:String, colName:String):String

This method creates a table with automatic Id column as primary key and autoincrement.

Parameters

  • tabName:String ____ Specifies the name of the table.
  • indexName:Array ____ Specifies the index name.
  • colName:String ____ Specifies the column name.

castData()  method
public function castData(tabName:String, dataName:String, valueField:String):String

This method casts data as ByteArray or as int.

Parameters

  • tabName:String ____ Specifies the name of the table.
  • dataName:Array ____ Specifies the data to be cast.
  • valueField:String ____ Specifies the type, Bytearray or int data will be cast.

updateData()  method
public function updateData(tabName:String, fieldName:String, newValue:String, oldValue:String):String

This method updates a table replacing a value with a new value.

Parameters

  • tabName:String ____ Specifies the name of the table.
  • fieldName:String ____ Specifies the field name.
  • newValue:String ____ Specifies the new value.
  • oldValue:String ____ Specifies the old value to be replaced.

updateNullData()  method
public function updateNullData(tabName:String, fieldName:String, newValue:String, primKey:String, idNum:String):String

This method updates a table using the primary key and looking for the id.

Parameters

  • tabName:String ____ Specifies the name of the table.
  • fieldName:String ____ Specifies the field name.
  • newValue:String ____ Specifies the new value.
  • primKey:String ____ Specifies the primary key.
  • idNum:String ____ Specifies the id.

deleteData()  method
public function deleteData(tabName:String, fieldName:String, myValue:String):String

This method deletes data from a table.

Parameters

  • tabName:String ____ Specifies the name of the table.
  • fieldName:String ____ Specifies the field name.
  • myValue:String ____ Specifies the value (row) to be deleted.

createView()  method
public function createView(tabName:String, viewName:String, colName:String, viewFeature:String=null):String

This method creates a new view of a table.

Parameters

  • tabName:String ____ Specifies the name of the table.
  • viewName:String ____ Specifies the name of the view.
  • colName:String=null ____ Specifies column name.
  • viewFeature:String ____ Optional: specifies the location followed after WHERE. When 'viewFeature' is not null, the values of the indicated column is shown. Default is null.

dropView()  method
public function dropView(viewName:String):String

This method drops an existing view.

Parameters

  • viewName:String ____ Specifies the name of the view.

findString()  method
public function findString(tabName:String, columnName:String, valueName:String):String

This method allows finding a string in a column.

Parameters

  • tabName:String ____ Specifies the name of the table.
  • columnName:String ____ Specifies the name of the column.
  • valueName:String ____ Specifies a value.

findMatch()  method
public function findMatch(_mySQL:SQLDatabase, tabName:String, columnName:String, valueName:String):void

This method allows finding a string match in a given database and works in conjunction with the resultLength property and the dataMatch event.

Parameters

  • _mySQL:SQLDatabase ____ Specifies the SQLDatabase object.
  • tabName:String ____ Specifies the name of the table.
  • columnName:String ____ Specifies the name of the column.
  • valueName:String ____ Specifies a value.
Event Detail

dataMatch event

Event Object Type: biz.flashscript.adobeAir.SQLSyntax
Event.type property = biz.flashscript.adobeAir.SQLSyntax.DATA_MATCH

Dispatched when in a "findMatch" search the result is obtained.

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");
			}
		}
	}
}