CODEWITHSUNDEEP
actionscript-3apache-flexsqliteairadobe
Shawn
Shawn

Reputation: 19793

Using multiple SQLite databases at once

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?

Upvotes: 11

Views: 22560

Answers (3)

Theo
Theo

Reputation: 132862

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

Upvotes: 6

Theo
Theo

Reputation: 132862

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

Upvotes: 9

morning
morning

Reputation:

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

Upvotes: 0

Related Questions