ADOBE AIR SQLLITE NOTEZ

____________________
ADOBE AIR SQLLITE NOTEZ
some more info
-------------------------INDEX
0. ABOUT / UM WAS GEHT ES?
1. working example
2.
3.
0. ABOUT / UM WAS GEHT ES?
store & read stuff from a local sqllite-database-file.
btw. Adobe AIR does NOT store the specified sqllite database inside the bin-debug, bin or src directory
of your project
… no no
it will be under: /Users/canoodle/Library/Preferences/SQLLITE-TEST2/Local Store/sampleDB.db
1. working example
<?xml version="1.0" encoding="utf-8"?>
<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="vertical" preinitialize="openDatabaseConnection()" fontSize="12" backgroundColor="#FFFFFF" width="600" height="700"
creationComplete="initApp();">
<mx:Script>
<![CDATA[
import flash.data.SQLConnection;
import flash.events.SQLErrorEvent;
import flash.events.SQLEvent;
import flash.filesystem.File;
import mx.controls.Alert;
private var conn:SQLConnection;
private var initComplete:Boolean = false;
private var sqlStatement:SQLStatement;
public function initApp():void
{
readTable();
}
public function openDatabaseConnection(event:Event = null):void
{
if(event == null)
{
// create new sqlConnection
conn = new SQLConnection();
conn.addEventListener(SQLEvent.OPEN, openDatabaseConnection);
conn.addEventListener(SQLErrorEvent.ERROR, openDatabaseConnection);
// get currently dir
var dbFile:File = File.applicationStorageDirectory.resolvePath("sampleDB.db");
// open database,If the file doesn't exist yet, it will be created
conn.openAsync(dbFile);
}
else if(event.type == SQLEvent.OPEN)
{
onDatabaseOpen();
}
else if(event.type == SQLErrorEvent.ERROR)
{
debug.text = event['error']['message']+" "+event['error']['details'];
trace(debug.text);
}
}
// connect and init database/table
private function onDatabaseOpen(event:Event = null):void
{
if(event == null)
{
// init sqlStatement object
sqlStatement = new SQLStatement();
sqlStatement.sqlConnection = conn;
var sql:String =
"CREATE TABLE IF NOT EXISTS user (" +
"
id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"
name TEXT, " +
"
password TEXT" +
")";
sqlStatement.text = sql;
sqlStatement.addEventListener(SQLEvent.RESULT, onDatabaseOpen); // -> HandleResult
sqlStatement.addEventListener(SQLErrorEvent.ERROR, onDatabaseOpen);
sqlStatement.execute();
}
else if(event.type == SQLEvent.RESULT)
{
HandleResult();
}
else if(event.type == SQLErrorEvent.ERROR)
{
debug.text = event['error']['message']+" "+event['error']['details'];
trace(debug.text);
}
}
// retrieve data from SQLlite Result and display it
private function HandleResult(event:SQLEvent = null):void
{
// refresh data
var sqlresult:SQLResult = sqlStatement.getResult();
datafiled.dataProvider = sqlresult.data;
}
// read table
private function readTable(event:Event = null):void
{
if(event == null)
{
var readQuery:String = "SELECT * FROM user"
sqlStatement = new SQLStatement();
sqlStatement.sqlConnection = conn;
sqlStatement.addEventListener(SQLEvent.RESULT, readTable);
sqlStatement.addEventListener(SQLErrorEvent.ERROR, readTable);
debug.text+=readQuery+"\n";
execute(readQuery);
}
else if(event.type == SQLEvent.RESULT)
{
HandleResult();
}
}
private function errorHandler(event:SQLErrorEvent):void
{
trace("Error code:", event.error['code']);
trace("Details:", event.error.message);
}
// execute query
private function execute(sql:String):void
{
if(sqlStatement.executing == false)
{
sqlStatement.text = sql;
sqlStatement.execute();
}
}
// insert
private function insert_user(event:Event = null):void
{
if(event == null)
{
var sqlupdate:String = "Insert into user(name,password) values('"+username.text+"','"+password.text+"')";
sqlStatement = new SQLStatement();
sqlStatement.sqlConnection = conn;
sqlStatement.addEventListener(SQLEvent.RESULT, insert_user);
sqlStatement.addEventListener(SQLErrorEvent.ERROR, insert_user);
debug.text+=sqlupdate+"\n";
execute(sqlupdate);
}
else if(event.type == SQLEvent.RESULT)
{
readTable();
}
}
// delete
private function delete_user(event:Event = null):void
{
if(event == null)
{
var sqldelete:String = "";
if(datafiled.selectedItem != null)
{
sqldelete = "delete from user where id='" +datafiled.selectedItem.id +"'";
sqlStatement = new SQLStatement();
sqlStatement.sqlConnection = conn;
sqlStatement.addEventListener(SQLEvent.RESULT, insert_user);
sqlStatement.addEventListener(SQLErrorEvent.ERROR, insert_user);
debug.text+=sqldelete+"\n"
execute(sqldelete);
}
else
{
Alert.show("please select a user from the list","and then press delete");
}
}
else if(event.type == SQLEvent.RESULT)
{
readTable();
}
}
// update user
private function update_user(event:Event = null):void
{
if(event == null)
{
var sqlUpdate:String = "";
if(datafiled.selectedItem != null)
{
sqlUpdate = "UPDATE user SET name='"+username.text+"',password='"+password.text+"' where id='" +datafiled.selectedItem.id +"'";
sqlStatement = new SQLStatement();
sqlStatement.sqlConnection = conn;
sqlStatement.addEventListener(SQLEvent.RESULT, insert_user);
sqlStatement.addEventListener(SQLErrorEvent.ERROR, insert_user);
debug.text+=sqlUpdate+"\n"
execute(sqlUpdate);
}
else
{
Alert.show("please select a user from the list","and then press update again.");
}
}
else if(event.type == SQLEvent.RESULT)
{
readTable();
}
}
]]>
</mx:Script>
<mx:TextArea x="21" y="10" width="402" height="179" id="debug"/>
<mx:DataGrid x="21" y="197" id="datafiled">
<mx:columns>
<mx:DataGridColumn headerText="ID" dataField="id"/>
<mx:DataGridColumn headerText="username" dataField="name"/>
<mx:DataGridColumn headerText="password" dataField="password"/>
</mx:columns>
</mx:DataGrid>
<mx:Form x="21" y="471">
<mx:FormItem label="username">
<mx:TextInput id="username"/>
</mx:FormItem>
<mx:FormItem label="password">
<mx:TextInput id="password"/>
</mx:FormItem>
</mx:Form>
<mx:Button x="300" y="503" label="add" click="insert_user()"/>
<mx:Button x="300" y="503" label="update" click="update_user()"/>
<mx:Button x="300" y="533" label="delete" click="delete_user()"/>
</mx:WindowedApplication>
2.
blabla
3.