SQL command Attach

angriff · Jun 22nd 2018

Does Attach work for datbases in plugin routines. Can I attach a second database? Can I have a database open and still query the World Datbase is the requirement.

red51 · Jun 23rd 2018

Yes, at least when using SQLite, you can use the ATTACH and DETACH statements, for example:

Java

Database db = getSQLiteConnection(getPath() + "/database1.db");


//Attach another database
db.execute("ATTACH '" + getPath() + "/database2.db" + "' AS database2;");


//Do something...
try(ResultSet result = db.executeQuery("SELECT * FROM database2.tablename")){
    while(result.next()){
        //...
    }
}
catch(SQLException e){
    e.printStackTrace();
}


//If you don't need the database connection anymore, close it
db.close();

Display More

However, usually it's fine to just establish an new connection to the 2nd database It's safe to have multiple connections to different database simultaneously, e.g.:

Java

Database db1 = getSQLiteConnection(getPath() + "/database1.db");
Database db2 = getSQLiteConnection(getPath() + "/database2.db");
...

Just remember to close() the database connections if you don't need them anymore.

If you want to access the world database btw, it's highly recommended to use the built-in WorldDatabase (since there are issues with thread-safety in SQLite). When using the WorldDatabase, all commits are synchronized.
Example to use the WorldDatabase:

Java

//Establish connection to world database
WorldDatabase worldDb = getWorldDatabase();


//Query all players, for example
try(ResultSet result = worldDb.executeQuery("SELECT * FROM Player")){
    while(result.next()){
        //In our case, we query the id, name and uid
        int dbID = result.getInt("ID");
        long uid = result.getLong("UID");
        String playername = result.getString("Name");
        System.out.println(dbID + ": " + name + " - " + uid);
        //example output: "1: angriff - 123456789123456789"
    }
}
catch(SQLException e){
    e.printStackTrace();
}

Display More

angriff · Jun 23rd 2018

Thanks .. just read about the attach in an SQL tutorial and could not find anything directly about that but was going to try to see if that was needed I need a third party UID from the database while using another database to keep track with an event.

this kind of support makes it fun.. thanks

Participate now!