Today I wanted to talk about how to configure MySQL and Matlab Database Toolbox so you can access an SQL server directly from Matlab. I am posting these instructions because when I was doing this I had to look through about 40 web sites to find the information I needed to configure the Database Toolbox because Matlab didn't feel like writing decent documentation. Just to let everyone know, I mainly use Windows, so these instructions may not be very useful if you are interested in using another OS, but I hope they help.
1. Install Matlab, including the Database toolbox on your computer.
2. Install MySQL on your computer. The latest release can be downloaded from www.mysql.com/downloads/. If you are unfamiliar with MySQL, install using the default settngs. When you are prompted to set a passoword, this is for the root (administrator) account. You can use the root account as your primary login for MySQL, which will give you every possible privilige. However, it is generally considered good practice to only use the root account for administrative purposes, and to set up and use a separate login with limited privileges for everyday use.
3. Make sure Java is installed on your computer, as this is how Matlab will communicate with MySQL. If it is not installed, it can be downloaded from http://java.com/en/download/index.jsp.
4. Download the MySQL Connector/J from http://www.mysql.com/downloads/connector/j/. This is the MySQL Java driver. The driver can be stored anywhere you want, but I recommend navigating to the Matlab installation files, and placing the driver in the Java directory. On my computer, the file is located in the following location: C:\Program Files\MATLAB\R2009a\java\mysql-connector-java-5.1.12-bin.jar.
5. Associate the driver with Matlab. This is done by adding the location of the driver to the classpath file. To do this, in the Matlab command line type 'edit classpath.txt'. On the classpath file, type the following on a separate line: '$matlabroot/java/mysql-connector-java-5.1.12-bin.jar'. $matlabroot is Matlab's way of abbreviating the folder that matlab is installed in, so for example, on my computer, $matlabroot is the same as C:\Program Files\MATLAB\R2009a\. Using $matlabroot is better than typing the entire path because Windows allows spaces in path names, but sometimes they can cause problems in Matlab. If you are installing a different version of the driver than I am, remember to put the correct name of the driver in the classpath file.
6. Restart your computer.
7. If you do not have a database setup in MySQL, create a dummy database and table for testing your connection. The following commands can be typed into the MySQL command line to make the dummy database and variable:
create database testdb;
create table testtbl ( var1 varchar(8),var2 int);
insert into testtbl VALUES ( 'hello',7);
8. In the Matlab command line, type 'confds'. This command brings up a window that will allow you to configure a data source. Select the 'Create New File...' option and name the configuration file that will store the connection information, then click 'OK'. In the Name field, designate the name you want for the data source. In the driver field, type 'com.mysql.jdbc.Driver'. In the URL field, type 'jdbc:mysql://localhost/testdb'. Click on 'Add/Update'. If you have a database that is located on a different computer, replace 'localhost' in the URL field with the location of the database. I have only used a database located on the same machine as the Matlab installation, so If anyone can verify this last bit about connecting to a database located on a different computer I would appreciate the feed back.
9. Test the connection by clicking 'Test'. You will be prompted to type the username and password. If you are logging in using the root account, use 'root' as username, and whatever password you set.
10. Open the Visual Query Builder by typing 'querybuilder' into the Matlab command line. If the name of your database does not show up in your in the data sources, click Query -> Load, and load the configuration file. You may be asked to enter your username and password for the database again. From here you can play around with VQB and see how it works.
Now that the connection has been set up between Matlab and MySQL, you can either use the VQB, or use the database functions. I recommend either using the database function, or building your own GUI that does what you want it do do, as the VGB GUI has a number of limitations. I will probably post more later on how define tables in the database and exactly how to use the database toolbox to import/export data to/from the database. Good luck!
Thanks for the clear how-to steps! Up till now I've spent tons of time struggling with my own database format in matlab. I think that I've rewritten it at least four times and it is still not good enough. When you've got a lot of historical data, selecting and joining becomes very troublesome and SQL is a very neat solution for this.
ReplyDeleteBy the way, my way of working with IB data is first downloading it to csv format and then inserting it in some database with a paser script. This way the precious data that took so long to download (due to draconian restrictions by IB) can be kept in a general format.
I'll try to post more on how to set up the SQL tables and a few handy functions for inserting and retrieving data from the database using Matlab in the next weeks or so.
ReplyDeleteHi Riggster, I'm not sure if you've taken a look at SqLite, something that turned out to be an ideal solution for me. Much more convenient than MySql.
ReplyDeletehey sjev,
ReplyDeleteI'll take a look at SqLite. Thanks for the recommendation. Regarding the capabilities of SqLite, do you know if you can create a trigger that creates a new table when a row is inserted into a table? MySQL won't allow this and it really bothers me. Here is an example of code I would like to be able to write for a trigger.
CREATE TRIGGER TR_TOC AFTER INSERT ON TOC FOR EACH ROW
CREATE TABLE TBL_NM (DATE DATETIME PRIMARY KEY,
OPEN DECIMAL,
HIGH DECIMAL,
LOW DECIMAL,
CLOSE DECIMAL);
TOC would be a table of contents that keeps track of each stock I have in the database, the date range for historical data I have for that security, the sector it belongs to, whether it belongs to the S&P 500 or other major indices, and the name of the table that stores the historical data. The table that stores the historical data is what I want to create using a trigger whenever a new line is inserted into the TOC table.
I've seen that SQLite supports triggers, but I've never used them.
ReplyDeleteHi Riggster,
ReplyDeleteI have been reading your blog with great enthusiasm! On the topic of "HOWTO: Wrap Interactive Brokers TWS api in a Matlab class", I also downloaded your ready made code. After following instructions (both from you and from IB), your code throws an error when trying to establish the ActiveX com port. TWS is configured, VB 2008 is installed, and I am running TWS under Win 7 Ultimate, 64-bit, Matlab version 2009b. Do you think this issue is caused by lack of compatibility of IB's ActiveX plugin with 64-bit systems?
Also, at this point, I do not need HF data. In fact I only need end of day data for assets, and as such I have not subscribed to any IB data supplier (15 mins of delay are no problem for end of day...).
How would you recoomedn I go forward from this point on? I would be very grateful for any hint you could give me on the subject.
Thanks ahead for your time!
sir we r really grateful to u... we appreciate ur work... it helped us alot....
ReplyDeleteheartfelt thank you
Kavya & Deepthi
hey riggster,
ReplyDeletei wanna create a user in mysql other than "root" and login with it.. mysql supports creating new user, but i m not getting how to switch to new user and work with it.. till now i m working with "root" as user..
can you help me with this???
and i ve one more prob...
i ve to update some predefined data to 3tables in database and data once its stored, tables should not be getting updated anytime..so after finish storing data in that DB, i wanna make it non_updatable.
can you please suggest some solution for it??
Sorry for the slow reply Kavya. I'm glad I have been able to help someone. Regarding your SQL user name questions, here are some suggestions. I hope they answer what you are asking.
ReplyDeleteTo create a new user, log in as 'root', then create a new username and passoword using the syntax:
CREATE USER 'newusername' IDENTIFIED BY 'password';
This will create a username with no privileges. Then use the GRANT syntax to grant certain privileges to the username. Below are a couple examples. For more info on granting privileges, look up GRANT in the documentation.
GRANT ALL ON db1.* TO 'jeffrey';
GRANT SELECT ON db2.invoice TO 'jeffrey';
I would suggest that you only allow the 'root' user to have all privileges on all databases. Set up one username and grant select and insert privileges to it. Use this username to access the database when you are inserting data into the database. Set up a second username and only grant the select privilege to it. Use this username when you want to query the database, but not have the ability to modify it in any way.
Lastly, to use a different username, open the command line the way you normally would when opening mysql, then type:
mysql -u username -p
This will let you login and prompt you for your password.
Dear First,
ReplyDeleteI sincerely apologize for missing your comment. I have not been using the ActiveX code that is posted, and the person who originally wrote it is also no longer using it either. He has written a much more stable software package called IbMatlab that he is sharing publicly from his website (www.leptokurtosis.com). This new software is written in java, and has order submission functionality in addition to downloading end of day data. This will help you get around any 64 bit incompatibility issues.
I am not sure what exactly your goals are, but if you want software that can submit orders, use IbMatlab. If all you want is to download EOD data, forget this software, and write a simple matlab script that downloads EOD data from yahoo. I hope this helps.
Hi Riggster, I'm a complete newbie to ALL of this.
ReplyDeleteI'm using Ubuntu 11.04 and Matlab R2009a.
I'd never even heard of mysql until last week.
I tried to use your mysql commands:
create database testdb;
create table testtbl ( var1 varchar(8),var2 int);
insert into testtbl VALUES ( 'hello',7);
The first one worked but the second one failed like this:
mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)
mysql> create table testtbl ( var1 varchar(8),var2 int);
ERROR 1046 (3D000): No database selected
Anyone with any experience would probably be able to figure this out but I've never touched a database before and this tangle of mysql, matlab and java
is quite a 'maze of twisty passages' if you get my reference.
I'll look elsewhere for an answer and post it if I find one.
Thanks for any ideas and for your original post,
Cinth
Hi again,
ReplyDeleteI'm making progress and offer my experience to help others.
I had to add this line:
use testdb;
after the first line of your test database generation above to avoid the error I posted a few minutes ago.
Then I was ready to try 'confds' on the Matlab command line.
I got an error when I put something else besides 'testdb' in for the 'Name:' field. I thought your comment implied I could use any name but it didn't like my choice. I tried 'testdb' and it worked!!
As I mentioned, I don't really know what I'm doing in this database world yet. Most posts I see assume some knowledge or use of databases. I am just trying to access some scientific data that just happens to have been put into a database. Mostly for fast access. Everything will be local to my machine.
It has been quite a journey so far but thanks to you and other very helpful folks out there in the ether, I'm making progress!
If only the world could get along as well as the Open Source community! Sorry for the political comment.
Thanks again,
Cinth
Hi, Vriend
ReplyDeleteI get an error, how can I solve this problem.
??? Error using ==> feval
Input PROGID does not represent an Activex control.
If this PROGID used to work before, please check vendor's
documentation for equivalent activex control progid.
Error in ==> C:\Program Files\MATLAB\R2010b\toolbox\matlab\winfun\actxcontrol.p>actxcontrol/createControl at
208
Error in ==> C:\Program Files\MATLAB\R2010b\toolbox\matlab\winfun\actxcontrol.p>actxcontrol at 179
Error in ==> tws_Connect at 8
tws = actxcontrol('TWS.TwsCtrl.1',[0 0 0 0],f,'tws_Event');
Error in ==> tws_conn_hdata_demo at 5
[f tws] = tws_Connect();
Hello everyone, Are you into trading or just wish to give it a try, please becareful on the platform you choose to invest on and the manager you choose to manage your account because that’s where failure starts from be wise. After reading so much comment i had to give trading tips a try, I have to come to the conclusion that binary options pays massively but the masses has refused to show us the right way to earn That’s why I have to give trading tips the accolades because they have been so helpful to traders . For a free masterclass strategy kindly contact (bitcointrade965@gmail.com) for a free masterclass strategy. He'll give you a free tutors on how you can earn and recover your losses in trading for free.. WhatsApp: +2348032246310
ReplyDelete