4

I have a local MS Access database, and I'm trying to connect to it via Java. I'm using UCanAccess as the JDBC driver, and while the database is linked to a password protected database on a network drive, the one I'm connecting to has already been authenticated, and is not password protected itself. I have all of the dependencies in my classpath. When connecting, I keep getting this error:

java.lang.OutOfMemoryError: Java heap

I fired up Java VisualVM, and indeed, the main thread is eating up all 2GB of allocated memory. This has only ever happened to me when I tried to select a massive number of records from a large MySQL Database. I can't find any results on Google that relate to this happening upon connection, as I'm not even attempting to query the DB.

The code is simply:

Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
System.out.println("Initiating connection...");
conn = DriverManager.getConnection("jdbc:ucanaccess://C:/Databases/StoreSalesCurrent.accdb");
System.out.println("Connection established");
conn.close();

Any idea why just trying to establish a connection would cause such large memory consumption?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
TheDankOG
  • 77
  • 1
  • 6
  • My girlfriend attempted this same thing, and while she never got this particular error she could never get the JDBC drivers to work with MS Access. The drivers she attempted were just meant to be a proof of concept that it was possible and were not meant to be seriously used. Also, in as of Java 8, the driver she used no longer worked as a particular method that the bridge used was removed. I'm not sure if this is the case with your code however. My girlfriend eventually gave up on MS Access and started using a postgresql database. – Taelsin Nov 27 '15 at 17:29
  • 1
    @Taelsin - UCanAccess is a pure-Java solution that doesn't use the JDBC-ODBC Bridge so it works fine under Java 8. – Gord Thompson Nov 27 '15 at 19:46
  • 1
    @TheDankOG - Try appending `;memory=false` to your connection URL and see if that helps. – Gord Thompson Nov 27 '15 at 19:50

1 Answers1

3

By default UCanAccess loads the entire database into memory at the very first connection in the life of the JVM. This was chosen as the default behavior because the typical usage case is for smaller personal databases, not ones that are gigabytes in size.

It will work with the proper connection parameters, e.g., setting memory=false and perhaps other related options, but the startup time (the time of the very first connection in the JVM life) may become high. See the UCanAccess website for more details.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
jamadei
  • 1,700
  • 9
  • 8
  • The database isn't 2gb, it's about 1.1gb, which is why i allocated 2gb to the JVM, to allow it to load the entire DB and provide some overhead room for query execution. I'll try that memory=false flag when I get the chance. Edit: It's taking forever to connect, but at least the heap isn't exploding in JVisualVM. – TheDankOG Nov 27 '15 at 21:15
  • You may want to minimize memory occupation using Skipindexes=true(it won't have effect on referential integrity constraints) before memory=false. In fact an unexpected memory occupation may be due to indexes, yet if you can use memory=true you'll experiment good performences in query executions. – jamadei Nov 29 '15 at 09:56