lamfete
19 May 2014, 18:39

I made a several changes and debugging.

It seemly something wrong with the connection.

using System;
using cAlgo.API;
using cAlgo.API.Indicators;
using cAlgo.API.Collections;
using System.Linq;
using MySql.Data.MySqlClient;

namespace cAlgo.Indicators
{
    [Indicator(ScalePrecision = 5, IsOverlay = false, AccessRights = AccessRights.None)]

    public class TickChart : Indicator
    {
        MySqlConnection conServer1;

        #region Open/Close Connection
        private bool Open()
        {
            //This opens temporary connection
            try
            {
                //conn.Open();
                string mysql_port = "3306";
                string mysql_host = "localhost";
                string mysql_database = "test";
                string mysql_user = "root";
                string mysql_password = "123";
                //MySqlConnection conn = null;
                conServer1 = new MySqlConnection("Max Pool Size='1000'; Port='" + mysql_port + "';Network Address='" + mysql_host + "';" + "Initial Catalog='" + mysql_database + "';" + "Persist Security Info=no;" + "User Name='" + mysql_user + "';" + "Password='" + mysql_password + "'; convert zero datetime=True");
                conServer1.Open();
                return true;
            } catch
            {
                //Here you could add a message box or something like that so you know if there were an error.
                return false;
            }
        }

        private bool Close()
        {
            //This method closes the open connection
            try
            {
                //conn.Close();
                conServer1.Close();
                return true;
            } catch
            {
                return false;
            }
        }
        #endregion

        [Output("Ask", Color = Colors.Blue)]
        public IndicatorDataSeries Ask { get; set; }

        [Output("Bid", Color = Colors.Red)]
        public IndicatorDataSeries Bid { get; set; }

        //private MarketDepth _marketDepth;

        private static void ShiftDataSeries(IndicatorDataSeries dataSeries)
        {
            for (var i = 0; i < dataSeries.Count - 1; i++)
            {
                dataSeries[i] = dataSeries[i + 1];
            }
        }

        private static void FillDataSeries(IndicatorDataSeries dataSeries, double value, int startIndex, int count)
        {
            for (var i = startIndex; i < startIndex + count; i++)
                dataSeries[i] = value;
        }

        public override void Calculate(int index)
        {
            if (!IsRealTime)
                return;

            if (!double.IsNaN(Ask[index]))
            {
                ShiftDataSeries(Ask);
                ShiftDataSeries(Bid);
            }

            FillDataSeries(Ask, Symbol.Ask, index, 50);
            FillDataSeries(Bid, Symbol.Bid, index, 50);

            var spread = Math.Round((Symbol.Ask - Symbol.Bid) / Symbol.PipSize, 1);
            ChartObjects.DrawText("Spread label", "Spread:\t" + spread + " pips", StaticPosition.BottomRight);

            //menampilkan query
            string query = "INSERT INTO test2 (ask, bid) VALUES ('" + Symbol.Ask + "', '" + Symbol.Bid + "')";
            ChartObjects.DrawText("echo_query", "Query:\t" + query, StaticPosition.BottomLeft);

            try
            {
                if (this.Open())
                {
                    //Opens a connection, if successful; run the query and then close the connection.
                    MySqlCommand cmd = new MySqlCommand(query, conServer1);

                    string query1 = "INSERT INTO test2 (ask, bid) VALUES ('" + Symbol.Ask + "', '" + Symbol.Bid + "')";
                    ChartObjects.DrawText("echo_query", "Query:\t" + query1, StaticPosition.TopRight);
                    cmd.ExecuteNonQuery();
                    this.Close();
                }
            } catch
            {
            }
            return;
        }

        public void Insert(string table, string column, decimal value)
        {
            //Insert values into the database.
            //table = "test2";
            //column = "ask, bid";
            //value = Ask, Bid;

            //Example: INSERT INTO names (name, age) VALUES('John Smith', '33')
            //Code: MySQLClient.Insert("names", "name, age", "'John Smith, '33'");
            //string query = "INSERT INTO " + table + " (" + column + ") VALUES (" + value + ")";
            string query = "INSERT INTO test2 (ask, bid) VALUES ('" + Symbol.Ask + "', '" + Symbol.Bid + "')";

            try
            {
                if (this.Open())
                {
                    //Opens a connection, if successful; run the query and then close the connection.

                    MySqlCommand cmd = new MySqlCommand(query, conServer1);

                    cmd.ExecuteNonQuery();
                    this.Close();
                }
            } catch
            {
            }
            return;
        }
    }
}

lamfete
19 May 2014, 15:51

Need help again.

I want to insert every tick of the price into MySQL.

There is no error when I build my code. But the tick price is not entered in the database.

using System;
using cAlgo.API;
using cAlgo.API.Indicators;
using cAlgo.API.Collections;
using System.Linq;
using MySql.Data.MySqlClient;

namespace cAlgo.Indicators
{
    [Indicator(ScalePrecision = 5, IsOverlay = false, AccessRights = AccessRights.None)]

    public class TickChart : Indicator
    {
        MySqlConnection conn = null;


        #region Constructors
        public TickChart(string hostname, string database, string username, string password)
        {
            hostname = "localhost";
            database = "test";
            username = "root";
            password = "";

            conn = new MySqlConnection("host=" + hostname + ";database=" + database + ";username=" + username + ";password=" + password + ";");
        }

        public TickChart(string hostname, string database, string username, string password, int portNumber)
        {
            hostname = "localhost";
            database = "test";
            username = "root";
            password = "";

            conn = new MySqlConnection("host=" + hostname + ";database=" + database + ";username=" + username + ";password=" + password + ";port=" + portNumber.ToString() + ";");
        }

        public TickChart(string hostname, string database, string username, string password, int portNumber, int connectionTimeout)
        {
            hostname = "localhost";
            database = "test";
            username = "root";
            password = "";

            conn = new MySqlConnection("host=" + hostname + ";database=" + database + ";username=" + username + ";password=" + password + ";port=" + portNumber.ToString() + ";Connection Timeout=" + connectionTimeout.ToString() + ";");
        }
        #endregion

        #region Open/Close Connection
        private bool Open()
        {
            //This opens temporary connection
            try
            {
                conn.Open();
                return true;
            } catch
            {
                //Here you could add a message box or something like that so you know if there were an error.
                return false;
            }
        }

        private bool Close()
        {
            //This method closes the open connection
            try
            {
                conn.Close();
                return true;
            } catch
            {
                return false;
            }
        }
        #endregion

        public void Insert(string table, string column, string value)
        {
            //Insert values into the database.
            table = "test1";
            column = "ask, bid";
            value = Ask + ", " + Bid;

            //Example: INSERT INTO names (name, age) VALUES('John Smith', '33')
            //Code: MySQLClient.Insert("names", "name, age", "'John Smith, '33'");
            string query = "INSERT INTO " + table + " (" + column + ") VALUES (" + value + ")";

            try
            {
                if (this.Open())
                {
                    //Opens a connection, if successful; run the query and then close the connection.

                    MySqlCommand cmd = new MySqlCommand(query, conn);

                    cmd.ExecuteNonQuery();
                    this.Close();
                }
            } catch
            {
            }
            return;
        }

        [Output("Ask", Color = Colors.Blue)]
        public IndicatorDataSeries Ask { get; set; }

        [Output("Bid", Color = Colors.Red)]
        public IndicatorDataSeries Bid { get; set; }

        //private MarketDepth _marketDepth;

        private static void ShiftDataSeries(IndicatorDataSeries dataSeries)
        {
            for (var i = 0; i < dataSeries.Count - 1; i++)
            {
                dataSeries[i] = dataSeries[i + 1];
            }
        }

        private static void FillDataSeries(IndicatorDataSeries dataSeries, double value, int startIndex, int count)
        {
            for (var i = startIndex; i < startIndex + count; i++)
                dataSeries[i] = value;
        }

        public override void Calculate(int index)
        {
            if (!IsRealTime)
                return;

            if (!double.IsNaN(Ask[index]))
            {
                ShiftDataSeries(Ask);
                ShiftDataSeries(Bid);
            }

            FillDataSeries(Ask, Symbol.Ask, index, 50);
            FillDataSeries(Bid, Symbol.Bid, index, 50);

            var spread = Math.Round((Symbol.Ask - Symbol.Bid) / Symbol.PipSize, 1);
            ChartObjects.DrawText("Spread label", "Spread:\t" + spread + " pips", StaticPosition.BottomRight);
        }
    }
}

lamfete
17 May 2014, 06:47

RE:

Spotware said:

Dear lamfete,

You specified reference in the wrong format. Instead of 

reference: MySql.Data.dll

you need to write

//#reference: MySql.Data.dll

However such approach is already obsolete. We can recommend you to read an article Legacy References

thank you for your help :)

lamfete
15 May 2014, 09:20

Hi,

I tried to connect cAlgo and mysql.

when I add reference: MySql.Data.dll and build it, I got this error:

a namespace cannot directly contain members such as fields or methods

this is my source code :

reference: MySql.Data.dll

using System;
using cAlgo.API;
using cAlgo.API.Indicators;
using cAlgo.API.Collections;
using System.Linq;

namespace cAlgo.Indicators
{
    [Indicator(ScalePrecision = 5, IsOverlay = false, AccessRights = AccessRights.None)]

    public class TickChart : Indicator
    {
        [Output("Ask", Color = Colors.Blue)]
        public IndicatorDataSeries Ask { get; set; }

        [Output("Bid", Color = Colors.Red)]
        public IndicatorDataSeries Bid { get; set; }

        //private MarketDepth _marketDepth;

        private static void ShiftDataSeries(IndicatorDataSeries dataSeries)
        {
            for (var i = 0; i < dataSeries.Count - 1; i++)
            {
                dataSeries[i] = dataSeries[i + 1];
            }
        }

        private static void FillDataSeries(IndicatorDataSeries dataSeries, double value, int startIndex, int count)
        {
            for (var i = startIndex; i < startIndex + count; i++)
                dataSeries[i] = value;
        }

        public override void Calculate(int index)
        {
            if (!IsRealTime)
                return;

            if (!double.IsNaN(Ask[index]))
            {
                ShiftDataSeries(Ask);
                ShiftDataSeries(Bid);
            }

            FillDataSeries(Ask, Symbol.Ask, index, 50);
            FillDataSeries(Bid, Symbol.Bid, index, 50);

            var spread = Math.Round((Symbol.Ask - Symbol.Bid) / Symbol.PipSize, 1);
            ChartObjects.DrawText("Spread label", "Spread:\t" + spread + " pips", StaticPosition.BottomRight);
        }
    }
}