Tag Archives: MySQL

Demo MySQL C Insert Java Select with Positions Database Table

Demo MySQL C Insert Java Select with Positions Database Table

Demos of C and Java Interactive Brokers

Source files

Java: MySQLPosition.tar

C: SimulinkModelTest.tar

 

Here is the MYSQL Position table CREATE statement:

CREATE TABLE `Positions` (
`idPositions` int(11) NOT NULL DEFAULT ‘0’,
`shortSymbol` int(11) DEFAULT NULL,
`shortEntryTS` timestamp NULL DEFAULT NULL,
` shortEntryPrice` decimal(10,4) DEFAULT NULL,
`shortStopLossPer` decimal(3,2) DEFAULT NULL,
`shortSoftTargetPerc` decimal(3,2) DEFAULT NULL,
`longSymbol` int(11) DEFAULT NULL,
`Positionscol` varchar(45) DEFAULT NULL,
`longEntryTS` timestamp NULL DEFAULT NULL,
`longEntryPrice` decimal(10,4) DEFAULT NULL,
`longStopLossPer` decimal(3,2) DEFAULT NULL,
`longSoftTargetPerc` decimal(3,2) DEFAULT NULL,
`shortExitTS` timestamp NULL DEFAULT NULL,
`shortExitPrice` decimal(10,4) DEFAULT NULL,
`longExitTS` timestamp NULL DEFAULT NULL,
`longExitPrice` decimal(10,4) DEFAULT NULL,
`shortQty` int(11) DEFAULT NULL,
`longQty` int(11) DEFAULT NULL,
`shortBeta` decimal(4,4) DEFAULT NULL,
`shortLong` decimal(4,4) DEFAULT NULL,
PRIMARY KEY (`idPositions`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
select * from Positions

 

MySQL demo with Matlab C Java in Ubuntu Linux source files

Here are the instructions to

C: http://zetcode.com/db/mysqlc/

Java: http://quantlabs.net/blog/2015/09/java-instructions-for-mysql-and-ubuntu-linux/

Netbeans project file: TestIBTWS.tar

C files: mysql-connector-java-5.1.36.tar

Code for Matlab:

>> conn = database(‘testdb’,’root’,’Hello123_’,’/home/caustic/DB_Drivers/mysql-connector-java-5.1.36-bin.jar’,’jdbc:mysql://localhost/testdb’);

query=’select * from Cars’;

x=fetch(conn,query)

 

For R connectivity, using NOSQL options for clustering and parallelization using Redis, Cassandra, Couch, MongoDB, MYSQL, Hadoop with HBase

For R connectivity, using NOSQL options for clustering and parallelization using Redis, Cassandra, Couch, MongoDB, MYSQL, Hadoop with HBase

I have a completed my R source code walkthroughs of 14 popular forecasting models for my membership. Now I focus on my cluster to speed up the simulations of the algos. As a result, it always comes down to how R talks to the popular NOSQL options out there. It seems I have narrowed it down to MongoDB and Redis. There are really not decent client R code examples for Hadoop, Couch, or Cassandra. Here are some links that making me lean towards Redis.
http://stackoverflow.com/questions/10696463/mongodb-with-redis

Comparing MongoDB and Redis, Part 1

http://openmymind.net/2011/5/8/Practical-NoSQL-Solving-a-Real-Problem-w-Mongo-Red/

http://www.quora.com/What-are-the-advantages-and-disadvantages-of-using-MongoDB-vs-CouchDB-vs-Cassandra-vs-Redis

http://java.dzone.com/articles/should-i-use-mongodb-couchdb

http://stackoverflow.com/questions/5252577/how-much-faster-is-redis-than-mongodb

Plus the client coding examples for Redis is much more helpful.

Update: It looks like I am going with MongoDB as I have 3 32 bit Macs. There is a limitation of 2 gb with Mongo but at least they can be used. MYSQL does not support older versions of OSX as well Redis is really Linux only. Too bad on the Redis side because it looked awesome!

 

Here is a Youtube video on how to use R to access MYSQL through RMySQL R package

Here is a Youtube video on how to use R to access MYSQL through RMySQL R package

This might be one of those extra unneeded videos you can find on Youtube but surprise, there were none. After running my survey of which database (commercial, open source RDMS or NOSQL) R users run, it struck me that nearly half use MYSQL followed by PostgreSQL.

I always had concerns of MYSQL with scaling and redundancy for my large data I anticipate my models and simulations will need. I demoed everything from Cassandra, Hadoop, Redis, MongolDB, etc. I found many were good but I found Redis to be the best one that fit my needs. As a result, I though  I was set until…

I came across an open source trading platform done in C++ called Trading Shim. This was a rare find as it met all my needs including Interactive Brokers but used MYSQL out of box. The database schema was big as well. I tried debugging and configuring which is still not complete but I am getting closer.

After seeing the results of my survey, I through why not stick with MYSQL for now as it can be fast enough. Hey, Yahoo Finance uses it for their backend so it cannot be that bad right. I just understand you would go through some heavy configuring to do something like sharding. This would not be as easy as in something like Redis. Anyhow, also MYSQL has proven to be the most popular database as well so it might be wise to get more comfortable with something that is widely used.

As for this Trading Shim, it makes my life so much easier without needing to recode anything so I just need to have my R algorithms access the MYSQL data. This was a much more sensible option at least this point to minimize the coding and debugging cycles.

So …enough typing, I have this video posted to show how easy it to have R access MYSQL. As said, this appears to only work in a Linux environment due to the RMySQL requires RCpp which only works with GCC, not Visual Studio for Windows. Sorry, I don’t make the rules. You could install MINGW for Windows and GCC but why would you want to go through the pain of that?

[youtube_sc url=”http://www.youtube.com/watch?v=LvCFaTln_3g” playlist=”how to have R access MYSQL through RMySQL R package”]

Which RDMS or NOSQL database do you use for R? MySQL, Cassandra, HBase, MongoDB, Oracle, PostgreSQL, CouchDB, SQLite?

Which RDMS or NOSQL database do you use for R? MySQL, Cassandra,  HBase, MongoDB, Oracle, PostgreSQL, CouchDB, SQLite?

This R survey is kind of important. It will show a few things:

  1. Which R most users use regardless if they are commercial vs open source vs NOSQL .
  2. This will help us figure out which database is best for R using the scalability and speed depending on the requirements. This includes multiple writes for market tick data from C++ or a Java application and access by various R algorithms for analytics purposes.

Go here for the poll.

Here are some reasonable options with reasons:

MYSQL

I would assume this to be the number one choice since it is open source (or at least they say). It also contains sharding and other scalability needs with clustering. Is this something that people are using for their trading platform requirements? This includes using MYSQL as a tick data repository.

PostgreSQL

Is anyone actually using this open source database for their R needs?

Oracle

This is easily the most popular commercial RDMS for both Linux/Unix and Windows. As Oracle has open R into with a connector into their ecosystem, I wondered if people are actually using this.

SQL Server/DB2/Sybase

I am unsure if there are any R package connectors to any of these databases. I was just curious as I am really not interested in these as a real option.

Cassandra

There seems to be no R package support for this. I once posted something on R-Bloggers.com and it lit up the site, it made me wonder if this is actually more popular than people think. It seems to meet the needs of both quick write and read access.

Redis

Now the doRedis R package looked really hot. It even showcased how to use with a potential financial analytics system. I even saw Java sharding examples which left me excited on the capabilities of this database.

MongoDB

This seems to be strangely the most popular of all. I also found various R packages which seems to support it as well.

HBase which is part of Hadoop

Eh. No support even according to Revolutionary Analytics which their lacking install R package guides. I gave up pretty quickly on these R packages.

All others database options seem fine but the ones listed above seem the most viable for any R user as repository for scaling and clustering.

Go here for the poll.

http://quantlabs.net/surveys/2012/06/19/what-rdms-or-nosql-database-should-a-r-user-focus-on/