Adobe Air- Using SQLite
Here I will provide all necessary files for creating, and executing statements from Air to your newly created SQLite database, I have commented all of my code, so you will be able to get the idea of how things are working. If you need some more help, feel free to leave me a comment and I will help.
These are all part of my DatabaseManager.as class.
Connecting to SQLite
For connecting to and creating a db, use the code as follows:
1: /**
2: * Blogging Air database manager for caching our tags and posts.
3: * Also for offline editablitiy.
4: * @author Jonnie
5: *
6: */
7: public class DatabaseManager
8: {
9: //DB Connection
10: private var dbconn:SQLConnection;
11: //Query Statement
12: private var sqlQuery:SQLStatement;
13: //Create Table Statement
14: private var sqlCreateTable:SQLStatement;
15: //Insert Statement
16: private var sqlInsert:SQLStatement;
17: //Import Statement
18: private var sqlImport:SQLStatement;
19:
20: [Bindable] private var model:ModelLocator = ModelLocator.getInstance();
21:
22: //Returned Data
23: [Bindable] private var sqlData:ArrayCollection;
24:
25: /**
26: * Creates a database connection, registers event listeners, specifies the database filename
27: * checks to see if the database exsists, if it does then we connection to it.
28: * If it doesnt, then we create a new database file, and create our tables.
29: *
30: * @constructor
31: */
32: public function DatabaseManager()
33: {
34: //Connect to the db
35: dbconn = new SQLConnection();
36:
37: //Add event listener
38: dbconn.addEventListener( SQLErrorEvent.ERROR, onSQLError );
39:
40: //Set the location of the db file
41: var dbFile:File = File.applicationStorageDirectory.resolvePath( "BlogDB.db" );
42:
43: //Check if the db file exsists
44: if ( dbFile.exists )
45: {
46: //Connect to the db then
47: dbconn.addEventListener( SQLEvent.OPEN, onSQLOpen );
48: } else {
49: //Create the db
50: dbconn.addEventListener( SQLEvent.OPEN, onSQLCreate );
51: }
52: //Execute
53: dbconn.openAsync( dbFile );
54: }
55:
Creating Tables
For creating tables, use the code as follows:
1: /**********************************************************
2: * ALL FUNCTIONS TO CREATE THE TABLES
3: * users/tags/posts
4: **********************************************************/
5:
6: /**
7: * Handles creating the tags table
8: *
9: */
10: public function createTagTable():void
11: {
12: //The query to execute
13: var query:String = "CREATE TABLE tags(" +
14: "[tag_id] INTEGER PRIMARY KEY," +
15: "[tag_name] VARCHAR(255)," +
16: "[tag_count] VARCHAR(255)," +
17: "[tag_user] VARCHAR(255))";
18:
19: //Make a new sql statement
20: sqlCreateTable = new SQLStatement();
21:
22: //Connect to the db
23: sqlCreateTable.sqlConnection = dbconn;
24:
25: //Set the sql to be executed
26: sqlCreateTable.text = query;
27:
28: //Execute it
29: sqlCreateTable.execute();
30:
31: trace( "Creating Tag Table query: " + query );
32: }
33:
34: /**
35: * Handles creating the posts table
36: *
37: */
38: public function createPostTable():void
39: {
40: //The query to execute
41: var query:String = "CREATE TABLE posts(" +
42: "[post_id] INTEGER PRIMARY KEY," +
43: "[post_url] VARCHAR(255) NOT NULL," +
44: "[post_hash] VARCHAR(255)," +
45: "[post_desc] VARCHAR(255) NOT NULL," +
46: "[post_tags] VARCHAR(255)," +
47: "[post_time] VARCHAR(255)," +
48: "[post_extended] VARCHAR(255)," +
49: "[post_shared] VARCHAR(255) DEFAULT yes," +
50: "[post_replace] VARCHAR(255) DEFAULT no," +
51: "[post_user] VARCHAR(255) NOT NULL);";
52:
53: //Make a new sql statement
54: sqlCreateTable = new SQLStatement();
55:
56: //Connect to the db
57: sqlCreateTable.sqlConnection = dbconn;
58:
59: //Set the sql to be executed
60: sqlCreateTable.text = query;
61:
62: //Execute it
63: sqlCreateTable.execute();
64:
65: trace( "Creating Post Table query: " + query );
66: }
67:
68: /**
69: * Handles creating the users table
70: *
71: */
72: public function createUserTable():void
73: {
74: //The query to execute
75: var query:String = "CREATE TABLE users(" +
76: "[user_id] INTEGER PRIMARY KEY," +
77: "[user_name] VARCHAR(255) NOT NULL," +
78: "[user_pass] VARCHAR(255)," +
79: "[user_feed] VARCHAR(255)," +
80: "[user_updated] VARCHAR(255));";
81:
82: //Make a new sql statement
83: sqlCreateTable = new SQLStatement();
84:
85: //Connect to the db
86: sqlCreateTable.sqlConnection = dbconn;
87:
88: //Set the sql to be executed
89: sqlCreateTable.text = query;
90:
91: //Execute it
92: sqlCreateTable.execute();
93:
94: trace( "Creating User Table query: " + query );
95:
96: }
97:
98: /** Creates all tables unless they are already there */
99: public function createAllTables():void
100: {
101: createPostTable();
102:
103:
104: trace( "Creating all tables" );
105: }
Inserting Data
For inserting data, use the code as follows:
1: /**
2: * Saves a post to the database by user and post
3: * @param post The post to save
4: * @param user The current user
5: */
6: public function savePost( post:Post, user:User ):void
7: {
8: var query:String = "INSERT INTO posts (" +
9: "post_url," +
10: "post_hash," +
11: "post_desc," +
12: "post_tags," +
13: "post_time," +
14: "post_extended," +
15: "post_shared," +
16: "post_replace," +
17: "post_user)" +
18: "VALUES ( " +
19: ":post_url," +
20: ":post_hash," +
21: ":post_desc," +
22: ":post_tags," +
23: ":post_time," +
24: ":post_extended," +
25: ":post_shared," +
26: ":post_replace," +
27: ":post_user)";
28:
29: sqlInsert = new SQLStatement();
30: sqlInsert.sqlConnection = dbconn;
31: sqlInsert.addEventListener( SQLEvent.RESULT, onSQLSave );
32: sqlInsert.addEventListener( SQLErrorEvent.ERROR, onSQLError );
33:
34: sqlInsert.text = query;
35: sqlInsert.parameters[":post_url"] = post.post_url;
36: sqlInsert.parameters[":post_hash"] = post.post_hash;
37: sqlInsert.parameters[":post_desc"] = post.post_description;
38: sqlInsert.parameters[":post_tags"] = post.post_tag;
39: sqlInsert.parameters[":post_time"] = post.post_time;
40: sqlInsert.parameters[":post_extended"] = post.post_extended;
41: sqlInsert.parameters[":post_shared"] = post.post_shared;
42: sqlInsert.parameters[":post_replace"] = post.post_replace;
43: sqlInsert.parameters[":post_user"] = user.user_name;
44:
45: sqlInsert.execute();
46:
47: trace( "Save post query: " + query );
48: }
49:
50: /**
51: * Saves a tag to the database
52: * @param tag The tag to save
53: * @param user The current user
54: *
55: */
56: public function saveTag( tag:Tag, user:User ):void
57: {
58: var query:String = "INSERT INTO tags ( tag , tag_count , tag_user )" +
59: "VALUES ( :tag , :tag_count, :tag_user )";
60:
61: sqlInsert = new SQLStatement();
62: sqlInsert.sqlConnection = dbconn;
63: sqlInsert.addEventListener( SQLEvent.RESULT, onSQLSave );
64: sqlInsert.addEventListener( SQLErrorEvent.ERROR, onSQLError );
65:
66: sqlInsert.text = query;
67: sqlInsert.parameters[ ":tag" ] = tag.tag_name;
68: sqlInsert.parameters[ ":tag_count" ] = tag.tag_count;
69: sqlInsert.parameters[ ":tag_user" ] = user.user_name;
70:
71: sqlInsert.execute();
72:
73: trace( "Save tag query: " + query );
74: }
75:
76: /**
77: * Saves a user to the database
78: * @param user The current user
79: *
80: */
81: public function saveUser( user:User ):void
82: {
83: var query:String = "INSERT INTO users ( user_name , user_pass , user_url ) " +
84: "VALUES ( :username , :password , :url )";
85:
86: sqlInsert = new SQLStatement();
87: sqlInsert.sqlConnection = dbconn;
88: sqlInsert.addEventListener( SQLEvent.RESULT, onSQLSave );
89: sqlInsert.addEventListener( SQLErrorEvent.ERROR, onSQLError );
90:
91: sqlInsert.text = query;
92: sqlInsert.parameters[ ":username" ] = user.user_name
93: sqlInsert.parameters[ ":password" ] = user.user_pass
94: sqlInsert.parameters[ ":url" ] = user.user_feed;
95:
96: sqlInsert.execute();
97:
98: trace( "Save user query: " + query );
99: }
Reading Data
For reading data, use the code as follows:
1: /**********************************************************
2: * ALL SELECT CALLS TO OUR 3 DATABASES
3: **********************************************************/
4:
5: /**
6: * Selects the user by argument
7: * @param args
8: *
9: */
10: public function selectUser( args:String = "*" ):void
11: {
12: var query:String = "SELECT " + args + " FROM users";
13:
14: sqlQuery = new SQLStatement();
15: sqlQuery.sqlConnection = dbconn;
16: sqlQuery.addEventListener( SQLErrorEvent.ERROR, onSQLError );
17: sqlQuery.addEventListener( SQLEvent.RESULT, onSQLResult_data );
18:
19: sqlQuery.text = query;
20:
21: sqlQuery.execute();
22:
23: trace( "Select user query: " + query );
24: }
25:
26: /**
27: * Selects all tags
28: * @param args by the argument
29: *
30: */
31: public function selectTag( args:String = "*" ):void
32: {
33: var query:String = "SELECT " + args + " FROM tags";
34:
35: sqlQuery = new SQLStatement();
36: sqlQuery.sqlConnection = dbconn;
37: sqlQuery.addEventListener( SQLErrorEvent.ERROR, onSQLError );
38: sqlQuery.addEventListener( SQLEvent.RESULT, onSQLResult_data );
39:
40: sqlQuery.text = query;
41:
42: sqlQuery.execute();
43:
44: trace( "Select tags query: " + query );
45: }
46:
47: /**
48: * Selects all posts
49: * @param args by the argument
50: *
51: */
52: public function getPosts():void
53: {
54: var query:String = "SELECT * FROM posts ORDER BY post_time DESC";
55:
56: sqlQuery = new SQLStatement();
57: sqlQuery.sqlConnection = dbconn;
58: sqlQuery.addEventListener( SQLErrorEvent.ERROR, onSQLError );
59: sqlQuery.addEventListener( SQLEvent.RESULT, onSQLResult_data );
60:
61: sqlQuery.text = query;
62:
63: sqlQuery.execute();
64:
65: trace( "Select posts query: " + query );
66: }
67:
68: /**
69: * Retrieves all post for current user
70: * @param user The current user that is logged in
71: *
72: */
73: public function getPostsForUser( user:User ):void
74: {
75: var query:String = "SELECT * FROM posts WHERE post_user = '" + user.user_name + "'";
76:
77: sqlQuery = new SQLStatement();
78: sqlQuery.sqlConnection = dbconn;
79: sqlQuery.addEventListener( SQLErrorEvent.ERROR, onSQLError );
80: sqlQuery.addEventListener( SQLEvent.RESULT, onSQLResult_data );
81:
82: sqlQuery.text = query;
83:
84: sqlQuery.execute();
85:
86: trace( "Posts for User query: " + query );
87: }
88:
89: /**********************************************************
90: * ALL FUNCTIONS TO DELETE SELECTED DATA
91: * users/tags/posts
92: **********************************************************/
93:
94: /**
95: * Deletes a post by the ID
96: * @param id
97: *
98: */
99: public function deletePost( id:Number ):void
100: {
101: var query:String = "DELETE FROM posts" +
102: "WHERE post_id = :post_id";
103:
104: sqlInsert = new SQLStatement();
105: sqlInsert.sqlConnection = dbconn;
106: sqlInsert.addEventListener( SQLEvent.RESULT, onSQLSave );
107: sqlInsert.addEventListener( SQLErrorEvent.ERROR, onSQLError );
108:
109: sqlInsert.text = query;
110: sqlInsert.parameters[ ":post_id" ] = id;
111:
112: sqlInsert.execute();
113:
114: trace( "Deleted post: " + id );
115: }
Importing Data
For importing data, use the code as follows:
1: /**********************************************************
2: * IMPORTS ALL DATA PASSES AS XMLNODE TO SQL DATABASE
3: **********************************************************/
4:
5: public function importPostXML( node:XMLNode, user:User ):void
6: {
7: var query:String = "INSERT INTO posts (" +
8: "post_url," +
9: "post_hash," +
10: "post_desc," +
11: "post_tags," +
12: "post_time," +
13: "post_extended," +
14: "post_shared," +
15: "post_replace," +
16: "post_user)" +
17: "VALUES ( " +
18: ":post_url," +
19: ":post_hash," +
20: ":post_desc," +
21: ":post_tags," +
22: ":post_time," +
23: ":post_extended," +
24: ":post_shared," +
25: ":post_replace," +
26: ":post_user)";
27: sqlImport = new SQLStatement();
28: sqlImport.sqlConnection = dbconn;
29: sqlImport.addEventListener( SQLEvent.RESULT, onSQLSave );
30: sqlImport.addEventListener( SQLErrorEvent.ERROR, onSQLError );
31: sqlImport.text = query;
32:
33: sqlImport.parameters[":post_url"] = node.attributes.href;
34: sqlImport.parameters[":post_hash"] = node.attributes.hash;
35: sqlImport.parameters[":post_desc"] = node.attributes.description;
36: sqlImport.parameters[":post_tags"] = node.attributes.tag;
37: sqlImport.parameters[":post_time"] = node.attributes.time;
38: sqlImport.parameters[":post_extended"] = node.attributes.extended;
39: sqlImport.parameters[":post_shared"] = node.attributes.shared;
40: sqlImport.parameters[":post_replace"] = node.attributes.replace;
41: sqlImport.parameters[":post_user"] = user.user_name;
42:
43: sqlImport.execute();
44:
45: trace( "Importing XML to SQLite Database" );
46: }
47:
And Now the Result and Fault Handlers
1: /**********************************************************
2: * ALL SQL RESULT AND EVENT HANDLERS
3: **********************************************************/
4:
5: /**
6: * Handles the DB Query result
7: * @param event
8: *
9: */
10: private function onSQLResult_data( event:SQLEvent ):void
11: {
12: var result:SQLResult = sqlQuery.getResult();
13: sqlData = new ArrayCollection( result.data );
14: model.postCollection = sqlData;
15:
16: }
17:
18:
19: /**
20: * Handles the Insert result
21: * @param event
22: *
23: */
24: private function onSQLSave( event:SQLEvent ):void
25: {
26: trace("New Record was inserted" + event.target.getResult().lastInsertRowID + "n" );
27: }
28:
29:
30: /**
31: * Handles the DB Open result
32: * @param event
33: *
34: */
35: private function onSQLOpen( event:SQLEvent ):void
36: {
37: trace( "Database was opened successfully." );
38:
39: }
40:
41: /**
42: * Hanles the DB Create result
43: * @param event
44: *
45: */
46: private function onSQLCreate( event:SQLEvent ):void
47: {
48: trace( "Database was created successfully." );
49: createPostTable();
50: createTagTable();
51: createUserTable();
52: }
53:
54: /**
55: * Handles the DB Error result
56: * @param event
57: *
58: */
59: private function onSQLError( event:SQLErrorEvent ):void
60: {
61: var errorString:String = "SQL Error: " + event.error.errorID + "nDetails: " + event.error.details
62: + "nDetail Arguments: " +event.error.detailArguments;
63: trace( errorString );
64: }
I hope that was useful.
November 14th, 2008 at 6:37 am
Hi,
i want to connect SQLite using Flex but i can’t do this. You are giving a DatabaseManagerseManager.as. This file is same as do copy in my project but i am not able to connect with SQLite so please tell me how to connect and what should i do for connecting with SQLite using Flex.
Thanx
November 15th, 2008 at 12:07 pm
Ok This is really simple, I will show you.
First you need to open or create the database, check if the database exists, and if it doesn’t create it, once you do that it should be open. Then when you want to insert a query into that database, you need to reference that connection as the connection you inserting to.
Look at this code:
private var db:DatabaseManager = new DatabaseManager();
private function checkUser():void
{
var u:UserVO = new UserVO( “test”, “test”, null, 0 );
db.checkUser( u );
}
This executes any function that you created inside of the database service, and as you see before in the DatabaseManager code, you create or open a database inside of the constructor, so you connection is going to be open for any queries.
July 29th, 2009 at 5:01 am
Hi
I am learning Flex and connection to SQLite.
In your reply to Smith, what is UserVO ?
I cannot see it referenced anywhere in your DB management class.
Also how do I reference the .as class file in my .mxml file ?
Sean
August 5th, 2009 at 3:24 am
The UserVO is a user object that is representing the user as a strong typed object. Here is the UserVO.as
/** *******************************************************************
* Del.icio.us Air Demo
* Copyright (c) 2008 Jonnie Spratley. All Rights Reserved.
* @author Jonnie Spratley
* @contact jonniespratley@gmail.com
* @internal
******************************************************************* */
package com.jonniespratley.itsdeliciousair.vo
{
/**
* VOs are used to create a layer of business objects that can be
* transferred between tiers, instead of using records, results sets, and datasets.
*/
[Bindable]
public class UserVO
{
public var username:String;
public var password:String;
public var inboxcount:Number;
public var lastupdated:Date;
public var feedurl:String;
public function UserVO( username:String,
password:String,
lastupdated:Date = null,
inboxcount:Number = 0 )
{
this.username = username;
this.password = password;
this.lastupdated = lastupdated;
this.feedurl = “http://del.icio.us/rss/” + username;
this.inboxcount = inboxcount;
}
}
}