MySQL & Ninja - futures io
futures io futures trading



MySQL & Ninja


Discussion in NinjaTrader

Updated
      Top Posters
    1. looks_one dsraider with 8 posts (8 thanks)
    2. looks_two ejtrader with 5 posts (5 thanks)
    3. looks_3 Big Mike with 4 posts (1 thanks)
    4. looks_4 shodson with 1 posts (0 thanks)
    1. trending_up 6,762 views
    2. thumb_up 14 thanks given
    3. group 5 followers
    1. forum 18 posts
    2. attach_file 1 attachments




Welcome to futures io: the largest futures trading community on the planet, with well over 125,000 members
  • Genuine reviews from real traders, not fake reviews from stealth vendors
  • Quality education from leading professional traders
  • We are a friendly, helpful, and positive community
  • We do not tolerate rude behavior, trolling, or vendors advertising in posts
  • We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community.  It's free and simple.

-- Big Mike, Site Administrator

(If you already have an account, login at the top of the page)

 
Search this Thread
 

MySQL & Ninja

(login for full post details)
  #1 (permalink)
New York, NY
 
 
Posts: 142 since Dec 2009
Thanks: 41 given, 87 received

Hey All,

I've written code so that one of my indicators can write directly to MySQL. The following works fine with a simple Windows Form I created but prints

"Error on calling 'OnBarUpdate' method for indicator '[indicator]' on bar 0: AssemblyResolveEvent handlers cannot return Assemblies loaded for reflection only."

in my output window when attempting to use with Ninja. I've been through this, and other, forums and can't find the answer.

 
Code
                            
//create a MySQL connection with a query string 
                    
MySqlConnection connection = new MySqlConnection("Server=server;Database=database;Port=port;Uid=userid;Password=password"); 

                    
//create a MySQL insert command with a query string 
                    
string myInsertQuery String.Format ("INSERT INTO table (column1, column2) values ('value1', 'value2')"); 
                    
MySqlCommand command = new MySqlCommand(myInsertQuery); 

                    
command.Connection connection
                    
connection.Open(); 

                    
//insert info 
                    
command.ExecuteNonQuery(); 
                 
                    
//close the connection 
                    
connection.Close(); 
Thanks,
DS

Reply With Quote

Journal Challenge February 2021 results:
Competing for $1500 in prizes from Topstep
looks_oneSBtrader82 's Trading Journalby SBtrader82
(172 thanks from 31 posts)
looks_twoJust BEING a Trader: Letting Go!!by iqgod
(120 thanks from 33 posts)
looks_3Wisdom is Emptinessby Mtype
(68 thanks from 25 posts)
looks_4Deetees DAX Trading Journal (time based)by Deetee
(31 thanks from 17 posts)
looks_5Journal for peanuts1956by peanuts1956
(23 thanks from 13 posts)
 
 
(login for full post details)
  #3 (permalink)
Site Administrator
Swing Trader
Data Scientist & DevOps
Manta, Ecuador
 
Experience: Advanced
Platform: My own custom solution
Trading: Emini Futures
 
Big Mike's Avatar
 
Posts: 49,758 since Jun 2009
Thanks: 32,301 given, 97,526 received


Sorry I don't have specific advise on the error. But I played with mysql db collection from Ninja a few years ago, here are some tips based on what I remember:

1) Don't create/destroy the connection OnBarUpdate. Instead just open it once, and close it once.

2) Don't write (INSERT) on every OnBarUpdate or OnMarketData. Instead use some sort of array or collection and write every 'x' ticks or every 'x' seconds. If you are timestamping, you can generate the timestamp within the collection or array so that when written it will retain the original timestamp.

I was able to record a dozen or more instruments at the tick level doing this, using a remote mysql server on my local network.

I have since deleted the code (by accident) so can't post, sorry.

Mike

We're here to help -- just ask

For the best trading education, watch our webinars
Searching for trading reviews? Review this list

Follow us on Twitter, YouTube, and Facebook

Support our community as an Elite Member:
https://futures.io/elite/
Follow me on Twitter Visit my futures io Trade Journal Reply With Quote
The following user says Thank You to Big Mike for this post:
 
(login for full post details)
  #4 (permalink)
New York, NY
 
 
Posts: 142 since Dec 2009
Thanks: 41 given, 87 received

Interesting, Mike. Will give these tips a whirl.

Thanks.

Reply With Quote
 
(login for full post details)
  #5 (permalink)
New York, NY
 
 
Posts: 142 since Dec 2009
Thanks: 41 given, 87 received

Afraid moving things around resulted in:

Error on calling 'OnStartUp' method for indicator '[indicator]': AssemblyResolveEvent handlers cannot return Assemblies loaded for reflection only. and

Error on calling 'OnTermination' method for indicator '[indicator]': AssemblyResolveEvent handlers cannot return Assemblies loaded for reflection only.

Oh Ninja. Why must you be so cruel...?

Reply With Quote
 
(login for full post details)
  #6 (permalink)
Portland, OR
 
Experience: Intermediate
Platform: Sierra Chart
Trading: ES
 
Posts: 269 since Jan 2011
Thanks: 327 given, 180 received

This thread should help in this space.



I am also looking for very similar but trying to use sqlserver ( or sql CE) with an intention of using certain other plug-ins in the future. Have to see how it goes.

dsraider - if you can find a solution using mySQL - can you post the result as well?

Thanks.

Reply With Quote
The following 3 users say Thank You to ejtrader for this post:
 
(login for full post details)
  #7 (permalink)
New York, NY
 
 
Posts: 142 since Dec 2009
Thanks: 41 given, 87 received

FIXED!!

I have absolutely no idea whatsoever why this worked but all I had to do was remove the System.Data.dll reference to .NET 4.0 and replace with with the same one from 2.0.

I am now happily writing to MySQL. Hope this helps anyone who's going through the same thing.

Mike, will still gladly take your advice on where to put what. I hadn't even thought of that so thanks again.

DS

Reply With Quote
The following 2 users say Thank You to dsraider for this post:
 
(login for full post details)
  #8 (permalink)
Site Administrator
Swing Trader
Data Scientist & DevOps
Manta, Ecuador
 
Experience: Advanced
Platform: My own custom solution
Trading: Emini Futures
 
Big Mike's Avatar
 
Posts: 49,758 since Jun 2009
Thanks: 32,301 given, 97,526 received

Glad to hear it. I was using NT 6.5 and it was about 3 years ago, using 5 year old hardware, so I am sure you can record a 100+ tick level instruments using an efficient method.

Mike

We're here to help -- just ask

For the best trading education, watch our webinars
Searching for trading reviews? Review this list

Follow us on Twitter, YouTube, and Facebook

Support our community as an Elite Member:
https://futures.io/elite/
Follow me on Twitter Visit my futures io Trade Journal Reply With Quote
 
(login for full post details)
  #9 (permalink)
Portland, OR
 
Experience: Intermediate
Platform: Sierra Chart
Trading: ES
 
Posts: 269 since Jan 2011
Thanks: 327 given, 180 received

dsraider - Very Nice. Would you please post the full sample code if possible?

I tried the other thread but some reason the data is not getting populated in mySQL database - don't see any errors but no data gets populated.

Wanted to replicate and see what you did.

thanks.

Reply With Quote
 
(login for full post details)
  #10 (permalink)
New York, NY
 
 
Posts: 142 since Dec 2009
Thanks: 41 given, 87 received


Hey EJ,

As you've probably read, I'm going to be moving things around but this got me writing to MySQL through OnBarUpdate():

 
Code
                            
//create a MySQL connection with a query string

            
MySqlConnection connection = new MySqlConnection("Server=server;Database=database;Port=port;Uid=userid;Password=password");

            
//create a MySQL insert command with a query string
            
string myInsertQuery String.Format ("INSERT INTO tablename (column1, column2) values ({0}, {1})"value1value2);
            
MySqlCommand command = new MySqlCommand(myInsertQuery);

            
command.Connection connection;
            
connection.Open();

            
//insert info
            
command.ExecuteNonQuery();
           
            
//close the connection
            
connection.Close(); 
One thing worth noting is that my table has an auto-increment column, column 1, but I did NOT use that in this code. "column1" up above is the first variable I'm looking to save. I ignored AI completely and it worked perfectly.

I'm shutting down for the night but if you have any issues, please note any error messages or output window statements. I came across many during this journey and they might ring a bell for me.

Good luck!
DS

Reply With Quote
The following 6 users say Thank You to dsraider for this post:
 
(login for full post details)
  #11 (permalink)
Portland, OR
 
Experience: Intermediate
Platform: Sierra Chart
Trading: ES
 
Posts: 269 since Jan 2011
Thanks: 327 given, 180 received

Thanks dsraider. Appreciate it. Checking out.

Mike - FYI:

your code is located in this thread:

NinjaTrader Support Forum - View Single Post - mysql database code sample

full thread:
mysql database code sample - Page 3 - NinjaTrader Support Forum

futures.io (formerly BMT) link:

Reply With Quote
The following user says Thank You to ejtrader for this post:
 
(login for full post details)
  #12 (permalink)
Site Administrator
Swing Trader
Data Scientist & DevOps
Manta, Ecuador
 
Experience: Advanced
Platform: My own custom solution
Trading: Emini Futures
 
Big Mike's Avatar
 
Posts: 49,758 since Jun 2009
Thanks: 32,301 given, 97,526 received


ejtrader View Post
Thanks dsraider. Appreciate it. Checking out.

Mike - FYI:

your code is located in this thread:

NinjaTrader Support Forum - View Single Post - mysql database code sample

full thread:
mysql database code sample - Page 3 - NinjaTrader Support Forum

futures.io (formerly BMT) link:

Thanks for digging. Unfortunately that was my first round of code, not with the enhancements I made later and then lost somehow.

Mike

We're here to help -- just ask

For the best trading education, watch our webinars
Searching for trading reviews? Review this list

Follow us on Twitter, YouTube, and Facebook

Support our community as an Elite Member:
https://futures.io/elite/
Follow me on Twitter Visit my futures io Trade Journal Reply With Quote
 
(login for full post details)
  #13 (permalink)
Portland, OR
 
Experience: Intermediate
Platform: Sierra Chart
Trading: ES
 
Posts: 269 since Jan 2011
Thanks: 327 given, 180 received

dsraider - Just wanted to let you know - I am able to write the data as well.

In the end it turned out to be the System.Data.dll - reference ( it was pointed to net 4.0 version and later changed to 2.0).

Should be able to experiment further going forward as this was the initial hurdle

PS: I think this should work fine with net 3.5 version of this dll as well - but would have to experiment further.

Thanks.

Attached Thumbnails
Click image for larger version

Name:	NT_References.jpg
Views:	60
Size:	23.6 KB
ID:	60971  
Reply With Quote
 
(login for full post details)
  #14 (permalink)
New York, NY
 
 
Posts: 142 since Dec 2009
Thanks: 41 given, 87 received

EJ - Awesome!

Thanks for letting me know. My 3.5 dll wasn't readily available (read I was tired and frustrated and couldn't find it at first glance) so I went with 2.0, wrote to MySQL and promptly ran away. Would definitely be interested to hear if 3.5 works out better for you.

P.S. Mike, I didn't realize you'd written the my_sql_db code from which I based mine so thanks yet again.

Reply With Quote
 
(login for full post details)
  #15 (permalink)
New York, NY
 
 
Posts: 142 since Dec 2009
Thanks: 41 given, 87 received

EJ, Mike et al,

Wondering if anyone has successfully separated code in the following way:

OnStartUp() - open connection

OnBarUpdate() - execute insert command (though will change later per Mike's suggestion)

OnTermination() close connection

While Print statements are telling me each of these are being called, and everything compiles properly, it's simply not working.

Placing connection.Open() in OnStartUp() gives me: Error on calling 'OnBarUpdate' method for indicator '[indicator]' on bar 0: Connection must be valid and open.

Placing connection.Close() in OnTermination gives me a message about pooling, which I've found out means I've opened too many connections WITHOUT closing any (this occurs when moving connection.Open() back to OnBarUpdate() with insert code).

Reply With Quote
 
(login for full post details)
  #16 (permalink)
Site Administrator
Swing Trader
Data Scientist & DevOps
Manta, Ecuador
 
Experience: Advanced
Platform: My own custom solution
Trading: Emini Futures
 
Big Mike's Avatar
 
Posts: 49,758 since Jun 2009
Thanks: 32,301 given, 97,526 received


dsraider View Post
EJ, Mike et al,

Wondering if anyone has successfully separated code in the following way:

NT7 was before my time with this So I can't comment much, except to say just add more debug print statements to see what is going on and track down the bad code.

Also check your MySQL settings, make sure the timeout is sufficiently high to not force close the connection on the server side between inserts if you choose to keep the connection open on the client (which would be my choice).

Mike

We're here to help -- just ask

For the best trading education, watch our webinars
Searching for trading reviews? Review this list

Follow us on Twitter, YouTube, and Facebook

Support our community as an Elite Member:
https://futures.io/elite/
Follow me on Twitter Visit my futures io Trade Journal Reply With Quote
 
(login for full post details)
  #17 (permalink)
Portland, OR
 
Experience: Intermediate
Platform: Sierra Chart
Trading: ES
 
Posts: 269 since Jan 2011
Thanks: 327 given, 180 received

There is an excellent thread started by GOMI ( Kudos to him!! ) - which has everything that is needed to get this done correct.

Here is the thread.


Reply With Quote
The following user says Thank You to ejtrader for this post:
 
(login for full post details)
  #18 (permalink)
OC, California, USA
 
Experience: Advanced
Platform: IB/TWS, NinjaTrader, ToS
Broker: IB, ToS, Kinetick
Trading: stocks, options, futures, VIX
 
shodson's Avatar
 
Posts: 1,958 since Jun 2009
Thanks: 528 given, 3,648 received


dsraider View Post
EJ, Mike et al,

Wondering if anyone has successfully separated code in the following way:

OnStartUp() - open connection

OnBarUpdate() - execute insert command (though will change later per Mike's suggestion)

OnTermination() close connection

While Print statements are telling me each of these are being called, and everything compiles properly, it's simply not working.

Placing connection.Open() in OnStartUp() gives me: Error on calling 'OnBarUpdate' method for indicator '[indicator]' on bar 0: Connection must be valid and open.

Placing connection.Close() in OnTermination gives me a message about pooling, which I've found out means I've opened too many connections WITHOUT closing any (this occurs when moving connection.Open() back to OnBarUpdate() with insert code).

Since you haven't included any code I can only offer a couple of thoughts

1) make sure your connection object has class scope, and is not a variable declared within your OnStartUp() method.

2) I would initialize and open the connection in OnBarUpdate() but only if the connection object is null. This helps prevent issues assuming that OnStartUp() will only be called once and only once at the beginning of the strategy's execution.

I have not done this with MySql, but I have done it with MS SQL, which you can download for free, and .NET is much more friendly to SQL Server out of the box. It's possible you need a .NET 4-friendly MySql driver, or use the older driver as some have done.

Follow me on Twitter Visit my futures io Trade Journal Reply With Quote
 
(login for full post details)
  #19 (permalink)
New York, NY
 
 
Posts: 142 since Dec 2009
Thanks: 41 given, 87 received

Apologies for the delayed response. They're trying to kill me in the real world...

Thank you to everyone for your suggestions. Unfortunately, nothing I've tried has worked. shodson, I've actually including my current working code early on in this thread. If you have time to check it out, I'd definitely appreciate it. I'm starting to think it might be an NT issue but this is "Unsupported."

DS

Reply With Quote


futures io Trading Community Platforms and Indicators NinjaTrader > MySQL & Ninja


Last Updated on February 2, 2012


Upcoming Webinars and Events
 

NinjaTrader Indicator Challenge!

Ongoing
 

Journal Challenge w/$1500 prizes from Topstep!

February
 

Battlestations! Show us your trading desk - $1,500 in prizes!

March
 

Call Option Buying: The New Pain Trade? w/Carley Garner

Elite only
     



Copyright © 2021 by futures io, s.a., Av Ricardo J. Alfaro, Century Tower, Panama, +507 833-9432, info@futures.io
All information is for educational use only and is not investment advice.
There is a substantial risk of loss in trading commodity futures, stocks, options and foreign exchange products. Past performance is not indicative of future results.
no new posts