Attach DB in SQLite from Java

Question

My code to attach a sqlitedsb to another worked fine until I upgraded to JAVA 7 ( my guess ) Howto create the syntax for attaching the database from java ? My syntax works fine using a SQL-Tool, but from JAVA I do not succeed.

My Code :

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TesteAttachDB {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        testattach();
    }

    public static void testattach(){

        Connection connection_historymapsdb = null;
        String sTargetDB="C://temp//historydb11_maps_en.db";
        try {
            Class.forName("org.sqlite.JDBC");
                connection_historymapsdb = DriverManager.getConnection("jdbc:sqlite:" + sTargetDB);
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        Statement statement;
        try {
            //String sDatabasetoattach="C://temp//test.db";
            //String sDatabasetoattach="C:/temp/test.db";
            String sDatabasetoattach="C:\temp\test.db";
            //String sDatabasetoattach="C:\temp\\userdb\test.db";
            statement = connection_historymapsdb.createStatement();
            String sSQL="Attach '" + sDatabasetoattach + "' as chronica_maps_tests";
            System.out.println(sSQL);
            statement.execute(sSQL);
            String sTestSQL="select count(*) from chronica_maps_tests.testtable";
            statement.execute(sTestSQL);
            System.out.println("worked.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Error Messages:

A:

Attach 'C://temp//test.db' as chronica_maps_tests
java.sql.SQLException: unable to open database: C://temp//test.db
    at org.sqlite.DB.execute(DB.java:275)
    at org.sqlite.Stmt.exec(Stmt.java:56)
    at org.sqlite.Stmt.execute(Stmt.java:83)
    at maps.TesteAttachDB.testattach(TesteAttachDB.java:48)
    at maps.TesteAttachDB.main(TesteAttachDB.java:15)

B:

Attach 'C:/temp/test.db' as chronica_maps_tests
java.sql.SQLException: unable to open database: C:/temp/test.db
    at org.sqlite.DB.execute(DB.java:275)
    at org.sqlite.Stmt.exec(Stmt.java:56)
    at org.sqlite.Stmt.execute(Stmt.java:83)
    at maps.TesteAttachDB.testattach(TesteAttachDB.java:48)
    at maps.TesteAttachDB.main(TesteAttachDB.java:15)

C:

Attach 'C:  emp est.db' as chronica_maps_tests
java.sql.SQLException: no such table: chronica_maps_tests.testtable
hat funktioniert !
    at org.sqlite.DB.throwex(DB.java:288)
    at org.sqlite.NestedDB.prepare(NestedDB.java:115)
    at org.sqlite.DB.prepare(DB.java:114)
    at org.sqlite.Stmt.execute(Stmt.java:82)
    at maps.TesteAttachDB.testattach(TesteAttachDB.java:52)
    at maps.TesteAttachDB.main(TesteAttachDB.java:15)

enter image description here

I tried different syntaxes (A,B,C) for sSQL, but nothing worked. How should the SQL-String be ?

You are using single quoates ('), try using double quotes (") around the file-name (also shown in [this](http://stackoverflow.com/a/9068549/3080094) answer). And maybe even "C:\\temp\\test.db" (escape the escape in Java code) will work? — vanOekel, Jan 18 '14 at 14:09
I just reloaded my old VirtualMachine where I started the same code with "C://temp//test.db" and it works ! In the meanwhile I updated to JAVA 7 and guess that is the reason why it doesn't work anymore. I didn't change the code. I copied the same code from my older virtual machine where it worked and in my new virtual machine it doesn't work ? — mcfly soft, Jan 18 '14 at 19:32

score 0 · Answer 1 · answered Jan 18 '14 at 19:18

0

In Java strings, \ is an escape character. \t is the horizontal tab control character, as you can see in the message:

Attach 'C:  emp est.db' as chronica_maps_tests

The correct syntax is like this:

String sDatabasetoattach = "C:\\temp\\test.db";

answered Jan 18 '14 at 19:18

CL.

173,858
17
217
259

Thanks, but I saw that too. This was only to show, that I tried every type of syntax. I just reloaded my old VirtualMachine where I started the same code with "C://temp//test.db" and it works ! In the meanwhile I updated to JAVA 7 and guess that is the reason why it doesn't work anymore. I didn't change the code. I copied the same code from my older virtual machine where it worked and in my new virtual machine it doesn't work ? – mcfly soft Jan 18 '14 at 19:32

score 0 · Answer 2 · answered Jan 19 '14 at 18:30

0

I added a newer JDBC Driver for SQLite and it worked. I guess the driver did not work correctly with JAVA 7. My old JDBC Driver was from 2011.

answered Jan 19 '14 at 18:30

mcfly soft

11,289
26
98
202

Attach DB in SQLite from Java

2 Answers2