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

It says *SQL error or missing database* , and I guess it's not table issue — Volatil3, Feb 21 '13 at 09:32
I thing that your are confusing the database name with table name. Your database name is apartments and table's name also the same? — Festus Tamakloe, Feb 21 '13 at 09:35
I think it is a path issue. Try giving the full path in `getConnection()` — Rahul, Feb 21 '13 at 09:35
@FestusTamakloe my db name is *main*, I am not finding the way to mention DB name. It's SQLIte — Volatil3, Feb 21 '13 at 10:05

Festus Tamakloe · Accepted Answer · 2013-02-21T13:15:40.677

8

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

edited Feb 21 '13 at 13:15

answered Feb 21 '13 at 10:16

Festus Tamakloe

11,231
9
53
65

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. – Volatil3 Feb 21 '13 at 10:24
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")); – Festus Tamakloe Feb 21 '13 at 10:44
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* – Volatil3 Feb 21 '13 at 10:50
I have put file in every subfolder and root folder, may it pick from somewhere but none :) – Volatil3 Feb 21 '13 at 10:53
if you put it in root then try to call this getConnection("jdbc:sqlite:"+getClass().getResource("apartments.db")); exactly without nothing else – Festus Tamakloe Feb 21 '13 at 10:54
Saying: */aaa/fff/demo/file:' does not exist* - May I send you Netbeans Project? – Volatil3 Feb 21 '13 at 11:01
you may get it from here: https://dl.dropbox.com/u/35231160/demo.zip It's a NETBeans Project – Volatil3 Feb 21 '13 at 11:05
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 – Volatil3 Feb 21 '13 at 11:11
getClass().getResource is still not reaching to exact Path. – Volatil3 Feb 21 '13 at 11:15
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. – Volatil3 Feb 21 '13 at 11:19
what you need is file:///path/to/build/classes/apartments.db this will me it work file:/// – Festus Tamakloe Feb 21 '13 at 11:21
String currentDir = new java.io.File("apartments.db").getAbsolutePath(); //This thing worked – Volatil3 Feb 21 '13 at 11:28
/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 – Volatil3 Feb 21 '13 at 11:31
Execution of JAR gives error again though file is in same folder where db file is. http://pastie.org/6280645 – Volatil3 Feb 21 '13 at 11:39
@Volatil3 ok. it works now just add String path=this.getClass().getResource("apartments.db").getPath(); connection = DriverManager.getConnection("jdbc:sqlite:"+path); i get A & 23 – Festus Tamakloe Feb 21 '13 at 13:13
Does it work in form of JAR too? I had to copy db file in same folder where I put JAR file. – Volatil3 Feb 21 '13 at 13:40
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 – Festus Tamakloe Feb 21 '13 at 14:16
Hello sir, i'm using derby embedded and .app couldn't connect my APPDATA, Can you give me some idea to solve this problem. http://stackoverflow.com/questions/36639175/derby-appdata-not-found-by-desktopos-jar-when-im-try-to-create-a-mac-os-x-appl Thanks. – Apr 15 '16 at 19:03

score 1 · Answer 2 · answered Feb 21 '13 at 10:16

1

Change The Extension .db to .sqlite

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

answered Feb 21 '13 at 10:16

Azad

5,047
20
38

I have explicitly give .db to filename. Changing did not make difference. – Volatil3 Feb 21 '13 at 10:58
Thank you! Finally fixed the issue I have been attempting to resolve for hours – CrazedCoder Oct 20 '17 at 14:39

score 0 · Answer 3 · answered May 30 '14 at 11:53

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

Java: Missing Database error

3 Answers3

Linked