actionscript 3 - Using multiple SQLite databases at once


Translate

I have 2 SQLite databases, one downloaded from a server (server.db), and one used as storage on the client (client.db). I need to perform various sync queries on the client database, using data from the server database.

For example, I want to delete all rows in the client.db tRole table, and repopulate with all rows in the server.db tRole table.

Another example, I want to delete all rows in the client.db tFile table where the fileID is not in the server.db tFile table.

In SQL Server you can just prefix the table with the name of the database. Is there anyway to do this in SQLite using Adobe Air?


All Answers
  • Translate

    I just looked at the AIR SQL API, and there's an attach method on SQLConnection it looks exactly what you need.

    I haven't tested this, but according to the documentation it should work:

    var connection : SQLConnection = new SQLConnection();
    
    connection.open(firstDbFile);
    connection.attach(secondDbFile, "otherDb");
    
    var statement : SQLStatement = new SQLStatement();
    
    statement.connection = connection;
    statement.text = "INSERT INTO main.myTable SELECT * FROM otherDb.myTable";
    statement.execute();
    

    There may be errors in that code snipplet, I haven't worked much with the AIR SQL API lately. Notice that the tables of the database opened with open are available using main.tableName, any attached database can be given any name at all (otherDb in the example above).


  • Translate

    It's possible to open multiple databases at once in Sqlite, but it's doubtful if can be done when working from Flex/AIR. In the command line client you run ATTACH DATABASE path/to/other.db AS otherDb and then you can refer to tables in that database as otherDb.tableName just as in MySQL or SQL Server.

    Tables in an attached database can be referred to using the syntax database-name.table-name.

    ATTACH DATABASE documentation at sqlite.org


  • James Lee
    Translate

    this code can be work,it is write of me:

    package lib.tools
    
    import flash.utils.ByteArray;
    import flash.data.SQLConnection;
    import flash.data.SQLStatement;
    import flash.data.SQLResult;
    import flash.data.SQLMode; 
    import flash.events.SQLErrorEvent;
    import flash.events.SQLEvent;
    import flash.filesystem.File;
    import mx.core.UIComponent;
    import flash.data.SQLConnection;
    
    public class getConn {
        public var Conn:SQLConnection;
    
        public function getConn(database:Array) {       
            Conn = new SQLConnection();
            var Key:ByteArray = new ByteArray();
            Key.writeUTFBytes("Some16ByteString"); 
            Conn.addEventListener(SQLErrorEvent.ERROR, createError);
            var dbFile:File = File.applicationDirectory.resolvePath(database[0]);
            Conn.open(dbFile);
            if(database.length > 1) {
                for(var i:Number = 1; i < database.length; i++) {
                    var DBname:String = database[i];
                    Conn.attach(DBname.split("\.")[0], File.applicationDirectory.resolvePath(DBname));
                }
            }
            Conn.open(dbFile, SQLMode.CREATE, false, 1024, Key); 
        }
    
        private function createError(event:SQLErrorEvent):void {
            trace("Error code:", event.error.details);
            trace("Details:", event.error.message);
        }
    
        public function Rs(sql:Array):Object {
            var stmt:SQLStatement = new SQLStatement();
            Conn.begin();
            stmt.sqlConnection = Conn;
            try {
                for(var i:String in sql) {          
                    stmt.text = sql[i]; 
                    stmt.execute();
                }
                Conn.commit();
            } catch(error:SQLErrorEvent) {
                createError(error);
                Conn.rollback();
            };
            var result:Object =stmt.getResult();
            return result;
        }
    }