database connection in 2017b

Ok, I recently installed Matlab 2017b. They said something about changes in the database toolbox. I thought they affect the GUI mostly. However my code, which is working well in 2017a, won't work in 2017b.

if true

dbpath = 'D:\xxxxx.mdb';
url = [['jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='''';DBQ='] dbpath];
con = database('','','','sun.jdbc.odbc.JdbcOdbcDriver', url);
tic
qry = exec(con,['SELECT Mode,ID,Station,R,U,f,Valid,SectionNr FROM tblSectionData WHERE ID >= 53500 ORDER BY ID']);
qry = fetch(qry);
temp = qry.Data;
close(qry);
close(con);
toc

end

I checked in the GUI and the DB Driver is also listed there, like in 2017a, with the same name. I'm even able to open an connection with the GUI and import data. I don't want to do this through the GUI all the time though.

Has anyone got an idea what I need to change in order to make the code work in 2017b?

I can only guess that somehow this "translator" which works the SQL statements on the Access database is working differently in 2017b.

[Kojiro Saito]

What error message did you get?

[Steineklopfer]

Well the message I get from Matlab is

"Undefined function 'fetch' for input arguments of type 'struct'."

But that is the wrong one I guess and just comes up because I don't do any error handling before.

The message from the Con object is more resonable which states:

"No suitable driver found for jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=D:\xxxxx.mdb"

This one is definately there. I triple checked in the Database Explorer.

"Microsoft Access Driver (*.mdb, *.accdb)"

It probably somehow doesn't work together with the "jdbc:odbc:Driver" bit in 2017b anymore...

Answer 1

[Kojiro Saito]

MATLAB R2017a uses Java 7 but from MATLAB R2017b Java 8 is used. JDBC-ODBC bridge is no longer supported from Java 8, so you need to choose from two options.

(1) Use ODBC driver Database toolbox no longer supports 32 bit driver, so you need to use ODBC driver for 64 bit. It can be downloaded from Microsoft.

Please see Microsoft Access Database Engine 2010 Redistributable

Or, if you need to connect to 32-bit version of Access, please see this answer.

(2) Use other drivers.

Instead of JDBC-ODBC bridge, use other JDBC drivers,such as UCanAccess, which might enables you to connect Access without ODBC.

Hope this help.

[Steineklopfer]

Thanks for that info :) I've already got the 64bit driver, but since I really like the SQL syntax I guess I will give this UCanAccess a try.

[Silvia Dell'Acqua]

Hi, I've got the same connection problem with R2017b and Access: I am using a 64bit machine with 64bit drivers. I would like to try UCanAccess. What are the steps I have to follow and how can I change the connection string reported below?

I have downloaded the driver from http://ucanaccess.sourceforge.net/site.html, but I only see a bunch of files, no executables to install it.

% Matlab embedded JDC/ODBC bridge

folder = pwd;

folder = [folder(1:end-16) '\02. Outputs\'];

db_name = [DB_name_label '.accdb'];

db_path = [folder db_name];

url = ['jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=' db_path];

conn = database('','','','sun.jdbc.odbc.JdbcOdbcDriver',url);

[Steineklopfer]

Unfortunately I can't give you any help on that yet, because I didn't have the time to figure this out. I'm still using 2017a because of that.

I only started some search on that which ended on that page: https://de.mathworks.com/matlabcentral/answers/254287-set-java-class-parameter with its two onward links. I didn't go further than that yet :(

Answer 2

[Silvia Dell'Acqua]

Here are some instruction on how to move from the JDBC-ODBC bridge to the UCanAccess driver. The static connection is more reliable, but please note that in case you were deploying the code, you could only use the dynamic connection as the Matlab Runtime doesn’t work with the static one.

STATIC CONNECTION

[1] Download the UCanAccess drivers from http://ucanaccess.sourceforge.net/site.html[it’s a folder containing a bunch of files and subfolders]

[2] copy the whole folder somewhere on your local drive, e.g. ‘C:\Program Files\MATLAB\UCanAccess-4.0.2-bin’ (hereafter $driverfolder)

[3] create a .txt file ‘javaclasspath.txt’ and save it under ‘$prefdir’, that you can find launching the command >>prefdir

writhe inside the file the following

$driverfolder \ucanaccess-4.0.2.jar
$driverfolder \lib\commons-lang-2.6.jar
$driverfolder \lib\commons-logging-1.1.1.jar
$driverfolder \lib\hsqldb.jar
$driverfolder \lib\jackcess-2.1.6.jar
$driverfolder \loader\ucanload.jar

[4] create a .obs file ‘java.obs’ and save it under ‘$matlabroot\bin\$arch’, that you can find launching the commands >>matlabroot and >>computer(‘arch’)

writhe inside the file the following

-DUCANACCESS_HOME=$driverfolder

[5] change the sting connections inside the code from

url = ['jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=' db_path];
conn = database('','','','sun.jdbc.odbc.JdbcOdbcDriver',url);

to

db_path = strrep(db_path,'\','/');
url = ['jdbc:ucanaccess://' db_path];
conn = database('','','','net.ucanaccess.jdbc.UcanaccessDriver',url);

where ‘db_path’ is the path where your DB is saved

DYNAMIC CONNECTION

[1] as [1] above

[2] copy the whole folder somewhere in a predefined path you are releasing with the executable or let the users copy it in their local machines (note that the code needs this path as an input – in case you want to let the users change it, consider to add a field in your gui interface)

[3] add to the code the following lines

javaaddpath([folder_driver '\ucanaccess-4.0.2.jar'])
javaaddpath([folder_driver '\lib\commons-lang-2.6.jar'])
javaaddpath([folder_driver '\lib\commons-logging-1.1.1.jar'])
javaaddpath([folder_driver '\lib\hsqldb.jar'])
javaaddpath([folder_driver '\lib\jackcess-2.1.6.jar'])
javaaddpath([folder_driver '\loader\ucanload.jar'])

where ‘folder_driver’ is the folder mentioned in point [2] (e.g. folder_driver = [pwd '\UCanAccess-4.0.2-bin'];)

[4] as [6] above

[Steineklopfer]

Wow! Thanks a lot for sharing this. I will try that with my code as soon as I get the chance :)

[Graeme]

Thanks for the thorough walkthrough above! I am trying to get this set up exactly as you've described with MATLAB R2019b, and having an issue. I have a few questions:

1. Is the process above the same for recent versions of MATLAB, in particular for R2019b?
2. I updated the file names and versions inside of the "javaclasspath.txt" file to match the latest download of UCanAccess.
3. I'm not familiar with .obs files. I simply created a txt file in notepad and saved it with the extension .obs. Will this work?
4. I confirmed from the UCanAccess site examples here that the driver names to use in the MATLAB code are still the same.

Here is my code:

db_path = 'C:\Data\file1.mdb'

db_path = strrep(db_path,'\','/');

url = ['jdbc:ucanaccess://' db_path];

conn = database('','','','net.ucanaccess.jdbc.UcanaccessDriver',url);

But when I run this I get a conn.Message of:

'JDBC Driver Error: No suitable driver found for jdbc:ucanaccess://C:/Data/file1.mdb'

Do you have any suggestions for what to try next? Thank you!

[Graeme]

I've made two additional changes and am now getting a different error:

1. I had mistakenly put the javaclasspath.txt in the main MATLAB\R2019b folder, but I relocated it to the $prefdir folder exactly as returned by the command "prefdir"
2. Should the javaclasspath.txt file actually have a space in each line between the $driverfolder and the '\...' portion?

I relaunched MATLAB, and when I run the code above I get the following error. Thank you in advance for any help you can provide!

Error using database (line 59)

Java exception occurred:

java.lang.NoClassDefFoundError: Could not initialize class

net.ucanaccess.jdbc.UcanaccessDriver

at java.lang.Class.forName0(Native Method)

at java.lang.Class.forName(Class.java:348)

at java.sql.DriverManager.isDriverAllowed(DriverManager.java:556)

at java.sql.DriverManager.isDriverAllowed(DriverManager.java:548)

at java.sql.DriverManager.getDriver(DriverManager.java:297)

at

com.mathworks.toolbox.database.DatabaseConnection.<init>(DatabaseConnection.java:105)

[Graeme]

Ok, one more attempt and details to add!

I found that in my javaclasspath.txt file I had mistakenly typed "...hsqldb_2.5.0.jar" instead of the correct "...hsqldb-2.5.0.jar"

I corrected this, relaunched MATLAB, and reran the code.

I got both a warning and an error:

WARNING:You shouldn't use 'user' reserved word as column name in the table BatchHistory_Table (it refers to the database user).

Escape it in your SQL!

(e.g. SELECT [USER] FROM TABLE WHERE [USER]='Joe')

Does this warning just come up every time? Or does this mean something about my mdb file that I need to investigate/correct?

Then, I get a conn.Message of:

'JDBC Driver Error: UCAExc:::5.0.0-SNAPSHOT unexpected token: AND required: VALUE'

Any suggestions on this? Thank you!

Answer 3

[Steineklopfer]

Ok I tried this now. It seems that UcanAccess has problems with large databases or databases that contain problems in the structure.

I tried it two times now. First time I tried to load the same amount of rows like with the old driver and 2017a. I didn't pay much attention and just checked back a couple of minutes later.

I got a whole list of error messages (Error1.txt)

The second time I paid more attention to check if the memory messages have something to do with the RAM in my computer. I also tried to load only like 3000 rows at once (Error2.txt).

It seems, that the problem is not related to insufficient RAM however.

It is some other Java problem that I'm not familiar with. I guess I have to stick to 2017a until the source DBs might change from mdb to sqlite or something similar.

Thanks for all the help :)

Answer 4

[Silvia Dell'Acqua]

I've faced the same issue

          java.lang.OutOfMemoryError: GC overhead limit exceeded

          java.lang.OutOfMemoryError: Java heap space

when trying to insert a much larger dataset (275.000 x 3 records of doubles).

You can easily fix it with one of the following

[1] increase the Java Heap memory

 open Matlab, menu "Home", select "Preferences", then "General", then "Java Heap Memory"

[2] change the java.obs file adding this command

-XX:UseGCOverheadLimit

[3] split the query to insert subsets of data multiple times. Potentially you need to open/close the connection each time

[Steineklopfer]

Yeap that helped :) I changed the heap memory to max, which was around 4GB I think. For now I was able to load all the rows at once.

I will think about loading them in in a loop or something similar, should I encounter that problem again.

Thanks for sharing the solution :)

Answer 5

[Stanislav Ginzburg]

What about connection to Oracle 11/12 by driver jdbc java8 ?