1

I want to use SQLite in memory mode, and load a DB file that is stored inside the jar. Seems like SQLite can't find/load the file. Here's my code:

String dbpath = Resources.getResource("my-db.dat").getPath();
connection = DriverManager.getConnection("jdbc:sqlite::memory:");
File dbfile = new File(dbPath);
Statement statement = connection.createStatement();
try {
    statement.executeUpdate("restore from " + dbfile.getPath());
} catch (Exception e) {
    System.out.println("Error re-constructing in memory data base from Db file.");
}

The code isn't failing during restore, but when I try to query the DB I'm getting an error (org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such table: my_table)).

Things do work in regular mode (not in-memory).

danieln
  • 4,795
  • 10
  • 42
  • 64
  • Please post complete LogCat. – Sufian Mar 21 '17 at 09:21
  • It's not an android app – danieln Mar 21 '17 at 09:24
  • 1
    A embedded resource can't be referenced as a File, because it's not, it's a entry in a Zip file, you'll probably need to extract it to temp file. You can use Class#getResourceAsStream to get an InputStream to the resource – MadProgrammer Mar 21 '17 at 09:24
  • @danieln alright, post the full error message. – Sufian Mar 21 '17 at 09:26
  • @danieln and it is possible that the table isn't there. Try checking what the names are - http://stackoverflow.com/questions/82875/how-to-list-the-tables-in-an-sqlite-database-file-that-was-opened-with-attach – Sufian Mar 21 '17 at 09:27
  • @Sufian, it is there as it is working in non in-memory mode. – danieln Mar 21 '17 at 09:28
  • @MadProgrammer, things do work in non in-memory mode, without extracting the file. – danieln Mar 21 '17 at 09:29
  • 1
    I'm sure they do, I wasn't talking about the db, I was talking about the restore file, which I assume the by its nature, the restore command wants a reference to an actual file – MadProgrammer Mar 21 '17 at 09:32
  • thanks @MadProgrammer, that's the pose I used as a reference for *non* in-memory mode, and things were working great. – danieln Mar 21 '17 at 09:36
  • 1
    In the disk case, the driver has specific support for hooking up sqlite with the data from the jar. The driver takes the resource as a parameter. In the memory case, you're asking sqlite itself to somehow find a file that isn't actually a file. That isn't going to work. The code you've posted doesn't work as is for an on-disk db either does it? If give it a restore statement with a path to something that's actually inside the jar, does that work? I'm guessing no. – pvg Mar 21 '17 at 09:42
  • 1
    Unless you use some kind of script instead, i guess you're stuck with a temp file – MadProgrammer Mar 21 '17 at 09:46
  • 1
    Also, is the performance difference really going to matter that much to you? For a small file that will effectively end up in memory anyway, it's probably going to be roughly the same, minus the hassle of extracting anything. – pvg Mar 21 '17 at 09:54
  • @pvg that's assuming the OP only wants a read-only connection, rather than being able to write to a temporary database that's initialized from the resource – Klitos Kyriacou Mar 21 '17 at 10:13
  • I just need to read from the DB. – danieln Mar 21 '17 at 10:14
  • One convoluted option is to keep a sql statement level db dump in your jar. You can then read it to populate an in-memory sqlite db. This gives you an in-memory db at the cost of: more complicated code, longer startup/init time and a more annoying process whenever you need to update the embedded data. – pvg Mar 21 '17 at 10:38
  • 1
    @danieln if you just want to read it, what's the advantage of restoring from the resource into an in-memory database, over simply connecting directly to the read-only database inside the jar using `DriverManager.getConnection("jdbc:sqlite::" + dbPath)`? – Klitos Kyriacou Mar 21 '17 at 12:05

0 Answers0