Friday, January 23, 2009

[FLEX] Adobe AIR – Local SQL DB – Flex Certification Topics #4.2 & 4.3

#4)  Interacting with data sources and servers:
                # 4.2: Create, connect to and define a local DB.
                # 4.3: Add, Update and Remove records from local database.

Salient Features:

  • Adobe AIR Local SQL DB is stored as a single file on user’s computer. Each DB is stored completely within a single file.
  • AIR Local DB Engine does NOT support foreign key constraints.
  • Classes used to work with Local SQL DB:
    • flash.data.SQLConnection – Corresponding to JDBC Connection
    • flash.data.SQLStatement – Corresponding to JDBC Statement
    • flash.data.SQLResult – Corresponding to JDBC ResultSet
  • Classes related to schema information describing the structure of the database:
    • flash.data.SQLSchemaResult
    • flash.data.SQLTableSchema
    • flash.data.SQLViewSchema
    • flash.data.SQLIndexSchema
    • flash.data.SQLTriggerSchema
  • Classes with constants
    • flash.data.SQLMode
    • flash.data.SQLColumnNameStyle
    • flash.data.SQLTransactionLockType
    • flash.data.SQLCollationType
  • Classes related to events/errors
    • flash.events.SQLEvent
    • flash.events.SQLErrorEvent
    • flash.events.SQLUpdateEvent
    • flash.events.SQLErrorEvent
    • flash.errors.SQLError
  • SQLConnection.open() – Connection is opened in synchronous mode.
  • SQLConnection.openAsync() – Connection is opened in asynchronous mode.
  • If open / openAsync method is called without a parameter, an in memory database is created rather than a file on disk.
  • If open / openAsync method is called with a non-existent file name, that database file is created.
  • Example: 


    import flash.data.SQLConnection; 
    import flash.events.SQLErrorEvent; 
    import flash.events.SQLEvent; 
    import flash.filesystem.File; 
    var conn:SQLConnection = new SQLConnection(); 
    conn.addEventListener(SQLEvent.OPEN, openHandler); 
    conn.addEventListener(SQLErrorEvent.ERROR, errorHandler); 
    var dbFile:File = File.applicationStorageDirectory.resolvePath("DBSample.db"); 
    conn.openAsync(dbFile); 
    function openHandler(event:SQLEvent):void 
    { 
        trace("the database was created successfully"); 
    } 
    function errorHandler(event:SQLErrorEvent):void 
    { 
        trace("Error message:", event.error.message); 
        trace("Details:", event.error.details); 
    }




  • SQLMode.UPDATE: Open the file for update purpose. Throws an error, if the file does not exist.


  • SQLMode.CREATE: Default. Creates the file if it does not exist.


  • SQLMode.READ: DB is opened for Read-Only operations. No Add, Update or Delete operations can be performed.


  • SQLStatement and named parameters:

    The SQLStatement supports names parameters. The parameters can be used using : or @ syntax.


  • SQLStatement and UNnamed parameters: In this case, the parameters are expressed using ? and assigned using position.


  • SQLStatement:

    • .sqlConnection = conn;


    • .text = “SQL Statement”;


    • .parameters[] = ….


    • .addEventListener(.., ..);


    • .execute()';


    • .getResult()




  • SQLResult

    • .data



at