Support Board
Support Board
Search Board
Control Panel
View My Posts / Threads
Direct MessagesDate/Time: Tue, 07 May 2024 02:05:26 +0000
[Programming Help] - Connecting to external MySQL database server
View Count: 6949
| [2013-11-02 12:46:56] |
| ChrisR - Posts: 7 |
|
I appreciate that this may be beyond you levels of support, however I would be grateful if you could provide some hints about how to go about this. Whenever a trigger event occurs (such as an MA crossover, for example), I want SC to connect to a MYSQL database on a remote server and write a record into a table. Is it possible to include and compile a MySQL connector such as SQLAPI in an SC script? If so, how is this done? Any help would be appreciated. Thanks Chris |
| [2013-11-02 22:23:31] |
| |
|
This should be doable using ACSIL: https://www.sierrachart.com/index.php?l=doc/doc_CreatingDLLs.html You just need to write C++ code that interacts with the MySQL database. So the API that you need to use will have to be callable from C++. Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing |
| [2013-11-04 11:14:14] |
| ChrisR - Posts: 7 |
|
Thanks I tried to use headers from the MYSQL connector, but I am not experienced enough with C++ to make it work with that approach. However I have compiled an external executable that connects to the remote MYSQL database and is then called and passed parameters from within SC using the system command i.e. if (sc.Index == 0) { SCString exeFile = "c:\\dbconnector\\remote_database.exe"; SCString symbol = sc.Symbol.GetChars(); SCString runExe = exeFile + " \"" + symbol + "\""; system ( runExe ); } This seems to work well. Date Time Of Last Edit: 2013-11-04 11:14:43
|
| [2013-11-04 17:29:29] |
| |
|
This is actually a good reliable approach.
Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing |
| [2013-11-05 12:01:55] |
| ChrisR - Posts: 7 |
|
I thought I'd update this thread in case anybody else wants to write to an external database. I have now created a very stable and powerful solution that works really well. SC is awesome, once you get your head around using ACSIL. This is my solution: ~~~~~~~~ SC is running on a Windows VPS. My SC indicator looks for events and then connects to an external exe file, also running on the VPS. SC sends parameters to the exe file. The exe fie connects to a remote MYSQL database running on a Linux server and writes the data from SC into the database. The exe file does error checking to ensure that the db connection is OK and that the db insert was successful. It returns a success/fail value back to the SC indicator. If the exe file detects a db problem, it sends me an email stating the error. If the exe file returns a fail to SC, the SC indicator does not send further requests to the exe file. ~~~~~~~~~~ Using one minute update intervals, there are no performance issues at all, and the system could easily cope with much shorter update intervals. The Windows VPS only has 2GB RAM and will easily cope with multiple instances of SC and charts I made sure there is a fast ping time (3ms) between the Windows VPS and Linux server. Chris |
| [2014-09-26 21:39:30] |
| enemyspy - Posts: 304 |
|
I would potentially be interested in this tool accompanied by the source code. I have attached a text file containing my contact info. If you wish to discuss this with me/consult/ect. I will leave the text file public for one week. Thanks. Date Time Of Last Edit: 2015-04-30 17:38:58
|
| [2014-09-29 03:16:41] |
| User56304 - Posts: 33 |
|
I'd be interested in the ability to write trades to a MySQL db as well. Currently doing it manually.
|
| [2014-09-29 08:55:17] |
| ChrisR - Posts: 7 |
|
Hi Guys I am around, but have not visited SC for ages as I have been working on another large trading project. The solution that I mentioned above works very well. It is ideal for connectivity to a web site and I have been using a fantastic charting tool to generate some excellent HTML based charts and TA studies. I would be happy to supply consultancy services. If required. I will contact Andrew (enemySpy) using his details later today. Can User56304 contact me too, to discuss please. Many Thanks Chris Date Time Of Last Edit: 2014-09-29 09:17:15
|
| [2014-09-29 11:20:13] |
| User56304 - Posts: 33 |
|
Email info for ChrisR: |
 email address.txt - Attached On 2014-09-29 11:19:31 UTC - Size: 27 B - 406 views |
| [2014-09-29 11:49:01] |
| ChrisR - Posts: 7 |
|
Thank you. I've got your email address and will contact you shortly. I have also sent an email to enemyspy.
Date Time Of Last Edit: 2014-09-29 11:58:05
|
| [2016-07-27 15:09:44] |
| jivetrader - Posts: 388 |
|
This is an old post but I am also very interested in a solution like this. i'm tradingjive at the big G email company. Thanks |
| [2016-07-28 06:50:05] |
| enemyspy - Posts: 304 |
|
I ended up developing my own solution/class for this using the otlv4 library let me know if you still need help on this, I might be able to help you out depending on the scope of what you are trying to do.
|
| [2016-07-28 15:21:17] |
| jivetrader - Posts: 388 |
|
Hi enemyspy, yes I am trying to decide on an approach to this. Would love a quick chat with you, my email is above. Thanks
|
| [2016-07-28 19:23:09] |
| enemyspy - Posts: 304 |
|
I contacted you @ the .com email address you indicated above.
|
| [2020-02-05 16:13:08] |
| Chad - Posts: 231 |
|
Hi enemyspy, I sent you a request for DM - I'm interested in any help you might be able to provide for interfacing an ACSIL script with an SQL db.
|
| [2021-03-15 19:50:53] |
| silvereagle - Posts: 23 |
|
Hello friends, I see this is a fairly old thread but I was interested in this as well. :) I requested permission to chat if you'd be able to kindly share some pointers or source code with me. Or if email works better I can also be pinged at sierrachart.trader [at] the big G email company [dot com] Thanks for any help it is much appreciated. |
| [2021-03-15 20:24:58] |
| enemyspy - Posts: 304 |
|
See here: http://otl.sourceforge.net/otl3_intro.htm Open Source c++ library for interfacing sql. I use it with postgres in custom studies, support for mysql is better. You can probably re-purpose the subgraph to text file study to work with it. What I found to be fastest (a little less mem efficient): -Open a connection pool on the heap (inside a study collection dll but outside the studies). -Create a small module that is not part of a custom study but uses sierrachart.h along with otlv4.h and whatever else you need. This will interface your SC data to the data base. -Pass rows to this module in batches use an upsert method if you want implemtation to be less complicated. -You can keep track of the rows already submitted by using persistent variable row indices. - Works best if upserts are submitted via background threads so that they are non blocking. -Memory and cpu will limit the number of asycronous batches you can send. So You will want to want to use some kind of queue to hold upsert information. -You will want to make sure that you are not copying the sc data and keeping it referenced instead (at least until it absolutely has to be copied like when you format and buffer it right before commit). Date Time Of Last Edit: 2021-03-15 20:46:06
|
| [2021-03-15 20:41:16] |
| enemyspy - Posts: 304 |
|
Another way you can do it is wait until sc.Index = sc.ArraySize-1 and send all the data in 1 big batch or series of batches at the end of the chart loading. It depends on what you are trying to do. It is even less complicated, particularly for non real time submissions. You just have a persistent int that tracks if data was already submitted or not. Also avoids having to keep a connection open on the heap.
|
| [2022-04-13 18:03:15] |
| User119231 - Posts: 7 |
|
Hello everybody. I wanted to ask if any of you who have managed to connect your custom study with MySQL would be willing to share some of your knowledge. I need to pull some data from my study to a database so that I can improve my system and any help would be great. My main issue is how I am going to create an external exe file which will receive arguments from the custom study. The part of sending the data to the database seems a bit less complex for now, at least theoritically. Thank you in advance for any of your help.
Date Time Of Last Edit: 2022-04-13 18:04:01
|
| [2022-04-20 06:28:03] |
| enemyspy - Posts: 304 |
|
You don't have to create an external .exe file. I mean you can if you want but you still have to push the data to that process. You could just make a connection in your custom study .dll and run the queries directly from a study. You might just want to run it on a separate worker thread so that the main study doesn't hang while it's inserting/updating. But even that is not really necessary. If you just need to push the data once at the end of the the load you can just open a connection once when the SC.index is on the final iteration and run your transactions. You mainly want to make sure that you don't keep instantiating a new connection and or run redundant transactions on every update. So you just need to make some logic that handles that as well. I would suggest making g a custom study that takes subgraphs as inputs and upserts the data from Those subgraph references to your database. One more thing is you probably want to ne separating it completely from any live trading instance you are running. Like in a separate instance of SC. You don't want that shit bogging down your trading especially if your code sucks like mine does. Date Time Of Last Edit: 2022-04-20 07:01:21
|
| [2022-04-20 07:30:22] |
| User119231 - Posts: 7 |
|
Thank you very much for your help enemyspy. My main issue here is how I am going to make the connection in my custom study .dll. As you said in your answer I just need to push the data once at the end of the load so I probably need to open a connection once but still I have no idea how to do this with a sierra chart custom study and I cannot find any sample code in the documentation or what kind of methods should I use to connect with the database. I was just thinking the external .exe solution because I saw what ChrisR was doing in the beginning of the thread using the system() function to push data from the custom study to the executable which talked with the database. Would you be willing to share with me some code or functions you have used to make this work so that I can understand how to proceed further with opening the connection part and how to push values to the database? Even if you could just point me to specific parts of the documentation that refer to this subject would be tremendously helpful (in case they exist). |
| [2022-04-20 15:58:17] |
| enemyspy - Posts: 304 |
|
I don't have the code accessible any more as I moved all my development to Linux and not that active with sc software any more. Sc is amazing though still like it. So first go into the source code files that sc provides. The use a study that is named something like "subgraph to file". I don't remember exactly what it is called but dig for it and study it. That will give you a good structure for how to interact with and output the subgraphs. Next you will modify the study to output to an sql database instead of a text file. I used the open source c++ otl library to make the sql connection and commit the data. You can find code examples of how to do this on their website: http://otl.sourceforge.net/ |
| [2022-04-20 19:07:00] |
| User119231 - Posts: 7 |
|
I did find two studies in the source code file studies6.cpp that seem to be similar to what you described. The one is called "WriteBarDataToFile" and the other "WriteBarAndStudyDataToFile". I will try to work with these two for start. I am really greateful for your help mate. Thank you very much!
|
| [2022-04-21 02:15:03] |
| enemyspy - Posts: 304 |
|
Np happy to do what I can. You should focus your attention on this one: WriteBarAndStudyDataToFile Hope you are able to get it all working wish I still had time for all this stuff it's super fun once you get that connected. I don't mind answering questions/helping as time allows for if you run into any issues. Just pm me. Cheers. Date Time Of Last Edit: 2022-04-21 02:26:46
|
To post a message in this thread, you need to log in with your Sierra Chart account: