Matlab + MySQL Database (more driver loading problems)

[Ellarco]

Like far too many others, I am going insane trying to get Matlab and MySQL
to communicate using JDBC. Below Ill paste a short session which I think
will illustrate the problem. Essentially the driver I want to use will not
load (using Class.forName as one normally does in Java) despite being
available for instantiation! Matlab finds it for some operations but not for
others?!?

I was happily using the Database Toolbox until I discovered that it for some
reason or the other (perhaps to send me to an early grave) cant handle
BLOBs.

Details follow. Please assist as I am completely stumped.

El.

----- Heres a session that illustrates the problem -----

>> version

ans =

6.1.0.450 (R12.1)

>> version -java

ans =

Java 1.3.0 with Sun Microsystems Inc. Java HotSpot(TM) Client VM
(mixed mode)

>> % Make sure the classpath is correct - you can see the driver's jar is
there at the beginning
>> java.lang.System.getProperty('java.class.path')

ans =

.;C:\Program
Files\MySQL\APIs\mysql-connector-java-3.0.9-stable\mysql-connector-java-3.0.
9-stable-bin.jar;C:\Documents and
Settings\Alan\Desktop\forrest-srv-1.0\out\Forrest-srv-1.0.jar;C:\Progra~1\JP
robe\jre13\lib\rt.jar;C:\Progra~1\JProbe\jre13\lib\i18n.jar;C:\Progra~1\JPro
be\jre13\lib\swingall.jar;C:\Program Files\Matlab6\java\patch;C:\Program
Files\Matlab6\java\jar\util.jar;C:\Program
Files\Matlab6\java\jar\widgets.jar;C:\Program
Files\Matlab6\java\jar\beans.jar;C:\Program
Files\Matlab6\java\jar\hg.jar;C:\Program
Files\Matlab6\java\jar\icebrowserbean.jar;C:\Program
Files\Matlab6\java\jar\ide.jar;C:\Program
Files\Matlab6\java\jar\jmi.jar;C:\Program
Files\Matlab6\java\jar\mlwidgets.jar;C:\Program
Files\Matlab6\java\jar\mwt.jar;C:\Program
Files\Matlab6\java\jar\page.jar;C:\Program
Files\Matlab6\java\jar\services.jar;C:\Program
Files\Matlab6\java\jar\test.jar;C:\Program
Files\Matlab6\java\jar\toolbox\bdd.jar;C:\Program
Files\Matlab6\java\jar\toolbox\curvefit.jar;C:\Program
Files\Matlab6\java\jar\toolbox\database.jar;C:\Program
Files\Matlab6\java\jar\toolbox\dials.jar;C:\Program
Files\Matlab6\java\jar\toolbox\filterdesign.jar;C:\Program
Files\Matlab6\java\jar\toolbox\images.jar;C:\Program
Files\Matlab6\java\jar\toolbox\instrument.jar;C:\Program
Files\Matlab6\java\jar\toolbox\nnet.jar;C:\Program
Files\Matlab6\java\jar\toolbox\physmod.jar;C:\Program
Files\Matlab6\java\jar\toolbox\reqmgt.jar;C:\Program
Files\Matlab6\java\jar\toolbox\rptgen.jar;C:\Program
Files\Matlab6\java\jar\toolbox\simulink.jar;C:\Program
Files\Matlab6\java\jar\toolbox\vr.jar;C:\Program
Files\Matlab6\java\jar\verctrl.jar;C:\Program
Files\Matlab6\java\jarext\dcxjp.zip;C:\Program
Files\Matlab6\java\jarext\mwjava2specific.jar;C:\Program
Files\Matlab6\java\jarext\SilkTest_Java1.jar;C:\Program
Files\Matlab6\java\jarext\commapi\win32\comm.jar;C:\Program
Files\Matlab6\java\jarext\ib5core.jar;C:\Program
Files\Matlab6\java\jarext\ib5ref.jar;C:\Program
Files\Matlab6\java\jarext\ib5util.jar;C:\Program
Files\Matlab6\java\jarext\vb20.jar;

>> % Attempt to load the MySQL driver
>> java.lang.Class.forName('com.mysql.jdbc.Driver');
??? Java exception occurred:
java.lang.ClassNotFoundException: com/mysql/jdbc/Driver
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
.

>> % O-k-a-y ... we'll try instantiating the MySQL driver directly, and
registering it manually with the DriverManager
>> driver = com.mysql.jdbc.Driver

driver =

com.mysql.jdbc.Driver@5fc8a0

>> % Is the success of the last operation not very strange in light of the
error we got during the forName above?
>> % We'll just do a quick check first to see if any other drivers are
loaded
>> drivers = java.sql.DriverManager.getDrivers

drivers =

java.util.Vector$1@6b321b

>> drivers.hasMoreElements

ans =

0

>> % Nop, no drivers. Now lets try and register the one we created
>> java.sql.DriverManager.registerDriver(driver)
>> % There are no errors/exceptions. It must have worked, but ...
>> drivers = java.sql.DriverManager.getDrivers

drivers =

java.util.Vector$1@37b407

>> drivers.hasMoreElements

ans =

0

>> % Alas registerDriver did nothing! No driver loaded means that when we
try this
>> con =
java.sql.DriverManager.getConnection('jdbc:mysql://localhost:3306/db1')
??? Java exception occurred:
java.sql.SQLException: No suitable driver
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
.

>> % ... we get that.

Arrggghhh .....

[Georges Schutz]

Hello,
I also use the Matlab DB-Toolbox. Until know I always connect via ODBC
but wanted to switch to JDBC.
Using Java 1.3.1 with matlab 6p5 and
mysql-connector-java-3.0.10-stable-bin.jar.

After putting the MySQL Connector/J to the Matlab class path
(toolbox/local/classpath.txt) and several tests I was able to connect
to a test database using the matlab database() function:

------
conn = database( 'test', 'dumyuser', 'dumypassword', ...
'com.mysql.jdbc.Driver', 'jdbc:mysql://localhost:3306/' );
curs = exec(conn, 'show tables from test');
curs = fetch(curs);
close(curs); close(conn);
-------

This works the same way it worked via ODBC with the difference that no
ODBC DataSource has to be defined on the client site.

On thing I remarked is that for example the access to the database
meta data does not work.
------
>> dmd(conn)
??? No appropriate method getMetaData for class
com.mysql.jdbc.Connection.

Error in ==> C:\MATLAB6p5\toolbox\database\database\@dmd\dmd.m
On line 26 ==> o.DMDHandle = getMetaData(c.Handle);
--------
According to the MySQL Connector/J documentation this should work but
I didn't look at it closer until know.

Hope this can help

Georges Schutz

[barry van setten]

Hello,

I also insert data in to MYSQL database. I have one problem. Both
with ODBC and JDBC I have the problem that when I insert a datetime,
that there is a offset, like described in Matlab Solution 24107 for
Access, where you have to substract 693960 day from the serialdate.
However I have a offset of 693960 days and one month when I export
datetimes form Matlab to Mysql. I cannot correct for this because
when I correct for this one month I cannot export dates larger than
30 days. I.e. 31-12-2003 is exported as either as 30-12-2003 or as
1-1-2004. Do you have such experience and a solution.

Best regards,

Barry van Setten

[Barry van Setten]

Matlab Solution Number: 32641 contains a m file that solves the date
export problem. However, with that m file you will have problems
exporting \ since mysql uses \ as escape character. You have to
change every \ to \\.

Barry

[Jonathan Karr]

Six years later I got the same error that reported here. The solution is to write a java class (see below) which registers the MySQL driver and creates a database connection. After that you can use the returned database connection to create statements, execute statements, and fetch result sets. See matlab central post with author 'Jonathan Karr' for full solution

//package edu.stanford.covertlab.database;

import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;

public class MySQLLoader
{
public static Connection makeConnection(String hostName, String schema, String userName, String password)
throws Exception
{
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
return DriverManager.getConnection("jdbc:mysql://"+hostName+"/"+schema, userName, password);
}
}

[Jonathan Karr]

This file contains the full MATLAB/MySQL connector J solution: http://www.mathworks.com/matlabcentral/fileexchange/28237.