database connection in 2017b
5 次查看(过去 30 天)
显示 更早的评论
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
2017-11-17
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.
(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.
3 个评论
Silvia Dell'Acqua
2017-12-1
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);
更多回答(4 个)
Silvia Dell'Acqua
2017-12-14
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
4 个评论
Graeme
2020-6-17
I've made two additional changes and am now getting a different error:
- 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"
- 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
2020-6-17
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!
Silvia Dell'Acqua
2018-1-18
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
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Database Toolbox 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!