Adobe Air - Using multiple SQLite databases at once

Question

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?

Answer 1

SQLite databases exist independently, so there's not way to do this from the database level.

You will have to write your own code to do this.

Answer 2

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

Answer 3

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).

Answer 4

the code of floor2 can not use in flex3~~~ who can give me right code?

Answer 5

this code can be work,it is write of me: package lib.tools { import flash.utils.ByteArray;

public class getConn
{
 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 var Conn:SQLConnection;

/* 定义连接函数
wirten by vivid msn:[email protected] */

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

/* 加密码的选项
wirten by vivid msn:[email protected] */

           Conn.open(dbFile, SQLMode.CREATE, false, 1024, Key); 

 }

/* 出错返回信息函数
wirten by vivid msn:[email protected] */

    private function createError(event:SQLErrorEvent):void
                    {
                        trace("Error code:", event.error.details);
                        trace("Details:", event.error.message);
                    }

/* 定义执行sql函数

wirten by vivid msn:[email protected] */

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



}

}