Java: Missing Database error

Question

I am getting the following error:

java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such table: apartments)

In reality, the table does exist. The following is my code:

try {
    // load the sqlite-JDBC driver using the current class loader
    Class.forName("org.sqlite.JDBC");
    Connection connection = null;
    // create a database connection
    connection = DriverManager.getConnection("jdbc:sqlite:/Path/To/apartments.db");
    System.out.println(connection.getMetaData());
    Statement statement = connection.createStatement();
    statement.setQueryTimeout(30);

    ResultSet rs = statement.executeQuery("select * from apartments");
    while(rs.next()) {
        // read the result set
        System.out.println("TEXT = " + rs.getString("display_text"));
        System.out.println("X1 = " + rs.getInt("x1"));
    }
} catch (Exception ex) {
    Logger.getLogger(MouseEventDemo.class.getName()).log(Level.SEVERE, null, ex);
}

Padauk

It says SQL error or missing database , and I guess it's not table issue – Padauk

How to check it will not be a path issue? – Padauk

I thing that your are confusing the database name with table name. Your database name is apartments and table's name also the same? – Rankle

I think it is a path issue. Try giving the full path in getConnection() – Greenlee

@FestusTamakloe my db name is main, I am not finding the way to mention DB name. It's SQLIte – Padauk

Have a look on my post to see the way to get the right – Rankle

Answer 1

May be this can help you to get right path to you database

How to Specify Database Files

Here is an example to select a file C:\work\mydatabase.db (in Windows)

Connection connection = DriverManager.getConnection("jdbc:sqlite:C:/work/mydatabase.db");

A UNIX (Linux, Mac OS X, etc) file /home/leo/work/mydatabase.db

Connection connection = DriverManager.getConnection("jdbc:sqlite:/home/leo/work/mydatabase.db");

How to Use Memory Databases SQLite supports on-memory database management, which does not create any database files. To use a memory database in your Java code, get the database connection as follows:

Connection connection = DriverManager.getConnection("jdbc:sqlite::memory:");

This also can help

String path=this.getClass().getResource("apartments.db").getPath();
            connection = DriverManager.getConnection("jdbc:sqlite:"+path);

assumes that apartments.db is put in project root directory

Rankle

I tried this: getConnection("jdbc:sqlite:"+getClass().getResource("apartments.db")); and now it gives error: path to 'file:/path/to/apartments.db': '/paht/to/file:' does not exist I don't get why it's adding file string in the end. – Padauk

Connection connection = DriverManager.getConnection("jdbc:sqlite:/home/leo/work/mydatabase.db"); doesn't work for you? then try to copy & paste your database into your project root and get it with getConnection("jdbc:sqlite:"+getClass().getResource("apartments.db")); – Rankle

It's looling in build/class. see this: java.sql.SQLException: path to 'file:/xxxx/xx/demo/build/classes/events/apartments.db': '/xxxx/xx/demo/file:' does not exist – Padauk

I have put file in every subfolder and root folder, may it pick from somewhere but none :) – Padauk

if you put it in root then try to call this getConnection("jdbc:sqlite:"+getClass().getResource("apartments.db")); exactly without nothing else – Rankle

Saying: /aaa/fff/demo/file:' does not exist - May I send you Netbeans Project? – Padauk

you may get it from here: dl.dropbox.com/u/35231160/demo.zip It's a NETBeans Project – Padauk

Finally it did work, I put file in root folder of my system and then mention as /Myfolder... but this is not desired. I will need relative Path anyway so that I could put it in Jar – Padauk

getClass().getResource is still not reaching to exact Path. – Padauk

Yes I am giving filename, the output of method returns this path: file:/path/to/build/classes/apartments.db- It's adding file:/ in the beginning. – Padauk

what you need is file:///path/to/build/classes/apartments.db this will me it work file:/// – Rankle

String currentDir = new java.io.File("apartments.db").getAbsolutePath(); //This thing worked – Padauk

/Path/to/apartments.db // giving it hard-coded was not working... Even it is not feasible.. I made the distro file in JAR and ran as java -jar MouseEvent..it gives same path error. JAR Is not finding file – Padauk

Execution of JAR gives error again though file is in same folder where db file is. pastie.org/6280645 – Padauk

@Padauk ok. it works now just add String path=this.getClass().getResource("apartments.db").getPath(); connection = DriverManager.getConnection("jdbc:sqlite:"+path); i get A & 23 – Rankle

Does it work in form of JAR too? I had to copy db file in same folder where I put JAR file. – Padauk

If you want to run it as jar file i recommande you to put the db information and others attributes in a properties file so that you can place later the db file in the same folder as the jar file – Rankle

Hello sir, i'm using derby embedded and .app couldn't connect my APPDATA, Can you give me some idea to solve this problem. #36639675 Thanks. – Punctate

Answer 2

Change The Extension .db to .sqlite

connection = DriverManager.getConnection("jdbc:sqlite:Path\\To\\apartments.sqlite");

Vizier

I have explicitly give .db to filename. Changing did not make difference. – Padauk

Thank you! Finally fixed the issue I have been attempting to resolve for hours – Selfannihilation

Answer 3

I had the same problem when running my unit tests from Robolectric on Android platform. The problem turned out to be related to something to do with using a singleton pattern on my DatabaseHelper class which was being reused by all my unit tests as it was a static object created by a base class for all my unit tests.

In my case the fix was to set the static variable caching the singleton instance inside the DatabaseHelper to null after each test. SO the base class looks something like this for me:

import com.foo.app.HomeActivity;
import com.foo.contentProvider.DatabaseHelper;

import org.junit.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.runner.RunWith;
import org.robolectric.Robolectric;
import org.robolectric.RobolectricTestRunner;
import org.robolectric.annotation.Config;

import static org.junit.Assert.assertNotNull;

@Config(shadows = {ShadowCaseSensitiveSQLiteCursor.class})
@RunWith(RobolectricTestRunner.class)  // <== REQUIRED for Robolectric!
public class RobolectricTestBase {

    protected HomeActivity activity;
    protected Context context;
    protected DatabaseHelper databaseHelper;

    @BeforeClass
    public static void setupClass() {
        // To redirect Robolectric to stdout
        System.setProperty("robolectric.logging", "stdout");
    }

    @Before
    public void setup() {
        activity = (HomeActivity) Robolectric.buildActivity(HomeActivity.class).create().get();
        assertNotNull(activity);
        context = activity.getApplicationContext();
        assertNotNull(context);
        databaseHelper = DatabaseHelper.getInstance(context);
        assertNotNull(databaseHelper);
    }

    @After
    public void tearDown() {
        databaseHelper.close();  //This is where singleton INSTANCE var is set to null
    }
}

Faust