mysql database code sample

[auspiv]

so, i've been trying to implement a tick database for quite some time now, and alot of it came together last night. here is an indicator that writes L1 data to a table that also receives the tick quotes. it is quite cpu intensive. i haven't done any testing, optimization, or anything really other than get a "working" solution. maybe some of you guys can give some advice for optimization. well here is the structure for the mysql table:

CREATE TABLE `cl` (
  `id_tick` int(16) NOT NULL auto_increment,
  `time` varchar(17) NOT NULL,
  `bidvol` int(8) NOT NULL,
  `bidprice` float NOT NULL,
  `askprice` float NOT NULL,
  `askvol` int(8) NOT NULL,
  `lastprice` float NOT NULL,
  `volume` int(8) NOT NULL,
  PRIMARY KEY  (`id_tick`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

and the indicator code is attached, where qqqqq = database name, cl is table name, zzzzz is the server, xxxxx is user, and yyyyy is password.

if you have any ideas to improve this code, or any ideas at all for that matter, just say it.

edit: changed attachment to more current, better version

[auspiv]

oh, and i know there are bound to be errors hidden in the code. if you find any, let me know.

basically, i know alot of this community feels as if the ninjatrader developers don't share enough code. i'm posting this indicator just to give every a kick in the right direction.

[NinjaTrader_JoshP]

Hi auspiv,

Unfortunately MySQL databases is outside of the scope we can offer support for. Perhaps some community members may have an expertise in this area and can lend you a hand though.

NinjaTrader provides you the capacity to use things like MySQL, but we cannot support it. You will need to work out the programming involved with such an endeavor yourself.

[auspiv]

Hi auspiv,

Unfortunately MySQL databases is outside of the scope we can offer support for. Perhaps some community members may have an expertise in this area and can lend you a hand though.

NinjaTrader provides you the capacity to use things like MySQL, but we cannot support it. You will need to work out the programming involved with such an endeavor yourself.

yes, i know you do not support it. this is why i posted. i wasn't asking for help, and wasn't expecting any. i just wanted to post up an example of something i coded up to try and help others get started. basically, i did the dirty work that i've been wanting somebody else to do, and now i'm hoping other people can benefit from my work.

[NinjaTrader_JoshP]

Thank you for your contribution. We do appreciate it.

[GaryAlbers]

Question: regarding knowing when to close connection

auspiv,

Thanks for the post. I was glad for your example.
I looked at the code and saw one thing that got my attention and raised a question which I hope NT support can answer.

I noticed that in the OnMarketData method, you are opening and closing a
connection to db every time. I am no authority on MySql, but I would think
this is expensive, since this method is called multiple times per second and db connections take time.
This leads me to the idea that the connection should be established once at first bar or in initialization. However, I can not figure out how to know when to close the connection.

So my question for NT support is this: is there any method to override (or something like that) which an indicator could use to determine when to clean up resources allocated during execution?

Thanks again for the sample.
-gary

[NinjaTrader_JoshP]

To clean up custom resources you can override the Dispose() method. http://www.ninjatrader.com/support/h...e.html?Dispose

[auspiv]

auspiv,

Thanks for the post. I was glad for your example.
I looked at the code and saw one thing that got my attention and raised a question which I hope NT support can answer.

I noticed that in the OnMarketData method, you are opening and closing a
connection to db every time. I am no authority on MySql, but I would think
this is expensive, since this method is called multiple times per second and db connections take time.
This leads me to the idea that the connection should be established once at first bar or in initialization. However, I can not figure out how to know when to close the connection.

So my question for NT support is this: is there any method to override (or something like that) which an indicator could use to determine when to clean up resources allocated during execution?

Thanks again for the sample.
-gary

yes, i'm pretty sure it is very cpu intensive. like i said, this is a very rough draft (alot like the paper i'm supposed to be writing now) but anyways, the mysql process was hovering around 2-3% cpu usage, with a process size around 15MB. ninjatrader though, was from 5-12% cpu, with process size from 250-450MB. i have a C2D [email protected] with 4gb ram.

and those usage stats were done during the middle of the day running on ES, and the average query rate on the DB was around 80 queries/second.

hopefully, this sample will become a polished db interface over time, but until then, i can only add so much at a time. anyways, i've updated the code sample to include L1 ask/bid data writes to the DB as well as time/sales. the table structure has changed a little bit as well.

table sql:

CREATE TABLE `es` (
  `id_tick` int(16) NOT NULL auto_increment,
  `date` varchar(8) NOT NULL,
  `time` varchar(17) NOT NULL,
  `bidvol` int(8) NOT NULL,
  `bidprice` float NOT NULL,
  `askprice` float NOT NULL,
  `askvol` int(8) NOT NULL,
  `lastprice` float NOT NULL,
  `volume` int(8) NOT NULL,
  PRIMARY KEY  (`id_tick`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

[Slaffko]

Hi auspiv, please clarify how to compile your indicator, I get error "The type or namespace name 'Data' does not exist in the namespace 'System' (are you missing an assembly reference?)"

[ctrlbrk]

auspiv,

Thank you very much for sharing this code.

[michaelangela]

which driver?

Hey there auspiv,

Thanks for sharing your code. I was wondering which mysql driver you use? It seems there are a couple of .Net friendly versions. Are you using the one provided by Mysql itself?

Thanks!

[auspiv]

Hi auspiv, please clarify how to compile your indicator, I get error "The type or namespace name 'Data' does not exist in the namespace 'System' (are you missing an assembly reference?)"

you need to add a reference to the mysql connector. i'm sorry i can't be more specific, i was really fooling around when i got it to work.


michaelangela: i'm using the mysql connector net 5.2.

[kuhasu]

Thank you very much for sharing this code.

[Phyre]

'MySql' Could not be found

you need to add a reference to the mysql connector. i'm sorry i can't be more specific, i was really fooling around when i got it to work.


michaelangela: i'm using the mysql connector net 5.2.

Thanks again for sharing this code.

I know support won't be able to answer this so I'm hoping the user community will be able to point me in the right direction.

I've installed the mysql connector 5.2.5 from the link above and I'm having trouble compiling.

Here is the code:

using MySql.Data.MySqlClient;
using System.Data;

I'm getting the error:

The type or namespace name 'MySql' could not be found...
The type or namespace name 'Data' does not exist in the namespace 'System'

What can I do to resolve this?

[decus]

You need to add the references to both mySQL and System.Data.

Right click on the code window of a strategy and select references, add both MySql.Data.dll (from your downloaded connector) and System.Data.dll (in the same directory as your other .NET dll's)