Trying to send data from cTrader to PostgreSQL - is Npgsql.dll the best option?

Black_Hawk_Redemption
24 Mar 2025, 17:34

Here is what ChatGPT had to say :

Diagnosis

Access Rights in cTrader Your robot attribute is currently defined as:

csharp

[Robot(AccessRights = AccessRights.None, AddIndicators = true)]

With AccessRights.None, your cBot isn’t permitted to use external networking. Since your code attempts to open a connection to PostgreSQL (a network operation even if on localhost), it likely gets blocked by cTrader’s sandbox. Changing this to AccessRights.FullAccess is required to allow network communication.

Connection Hang at conn.Open() Since the code prints messages before the connection open but not after, it indicates that the connection request is either being silently blocked or hanging due to restrictions. That’s why nothing prints after conn.Open().

Consistency and Readability We can also benefit from:

Consistently using parameter names (e.g., using the "@" prefix in both the SQL statement and parameter assignments).

Including a connection timeout parameter in the connection string to avoid an indefinite hang.

Robust error handling which is already mostly in place, but adding meaningful logs can help in production.

Performance and Testing By ensuring that our code opens the connection only briefly (using using blocks) and catching any exceptions, we increase both the reliability and testability of the code. Always check that your PostgreSQL server is configured (with pg_hba.conf and postgresql.conf) to accept connections on the specified host, port, and user credentials.

Revised Code

Below is the updated code snippet incorporating the changes:

csharp

using System;
using System.Text;
using cAlgo.API;
using cAlgo.API.Collections;
using cAlgo.API.Indicators;
using cAlgo.API.Internals;
using System.Reflection;
using Npgsql;

namespace cAlgo.Robots {
    // Changed AccessRights to FullAccess to enable external networking.
    [Robot(AccessRights = AccessRights.FullAccess, AddIndicators = true)]
    public class QuantData : Robot
    {
        // Updated connection string with a timeout parameter.
        public string connString = "Host=127.0.0.1;Port=5432;Username=postgres;Password=(removed);Database=trading_data;Timeout=15;";

        protected override void OnStart()
        {
            // This message indicates that the cBot has started running
            Print(Message);
        }

        /// <summary>
        /// Inserts market data into the PostgreSQL database. Prevents duplicate entries on the same timestamp and symbol.
        /// </summary>
        public void InsertMarketData(DateTime timestamp, string symbol, double openPrice, double highPrice, double lowPrice, double closePrice, double volume)
        {
            try
            {
                Print("Starting InsertMarketData");

                // Use a using block to ensure the connection gets properly closed and disposed.
                using (var conn = new NpgsqlConnection(connString))
                {
                    // Attempt to open the connection. With FullAccess on, this should work.
                    conn.Open();
                    Print("Connection opened successfully.");

                    string sql = @"
                        INSERT INTO market_data (timestamp, symbol, open_price, high_price, low_price, close_price, volume)
                        VALUES (@timestamp, @symbol, @open_price, @high_price, @low_price, @close_price, @volume)
                        ON CONFLICT (timestamp, symbol) DO NOTHING;
                    ";

                    using (var cmd = new NpgsqlCommand(sql, conn))
                    {
                        // Adding parameters to prevent SQL injection and ensure type safety.
                        cmd.Parameters.AddWithValue("@timestamp", timestamp);
                        cmd.Parameters.AddWithValue("@symbol", symbol);
                        cmd.Parameters.AddWithValue("@open_price", openPrice);
                        cmd.Parameters.AddWithValue("@high_price", highPrice);
                        cmd.Parameters.AddWithValue("@low_price", lowPrice);
                        cmd.Parameters.AddWithValue("@close_price", closePrice);
                        cmd.Parameters.AddWithValue("@volume", volume);

                        // Execute the command and log the result.
                        int rowsAffected = cmd.ExecuteNonQuery();
                        Print($"Data insertion complete. Rows affected: {rowsAffected}.");
                    }
                }
            }
            catch (Exception ex)
            {
                // Log the error message; in production, consider additional logging or error recovery.
                Print("Error inserting data: " + ex.Message);
            }
        }

        protected override void OnBar()
        {
            // Retrieve market data from the current bar.
            DateTime timestamp = Server.Time;
            string symbol = Symbol.Name;
            double openPrice = Bars.OpenPrices.LastValue;
            double highPrice = Bars.HighPrices.LastValue;
            double lowPrice = Bars.LowPrices.LastValue;
            double closePrice = Bars.ClosePrices.LastValue;
            double volume = Bars.TickVolumes.LastValue;

            Print("All variables collected. Inserting market data...");

            // Insert market data into PostgreSQL.
            InsertMarketData(timestamp, symbol, openPrice, highPrice, lowPrice, closePrice, volume);
        }

        protected override void OnStop()
        {
            // Clean-up code if necessary when the cBot stops.
        }
    }
}

Explanation of the Logic

Access Rights The attribute [Robot(AccessRights = AccessRights.FullAccess, …)] is essential for networking. Without FullAccess, any code that attempts to use external connections (like conn.Open()) will be silently blocked or hang.

Database Connection In the InsertMarketData method:

We create an NpgsqlConnection using the connection string. The connection string now includes a timeout to prevent indefinite waiting.

conn.Open() initiates the connection to the PostgreSQL server.

Once connected, we prepare an SQL INSERT statement with parameters. The use of parameters ensures that our inputs are safe and prevents SQL injection.

The ON CONFLICT clause prevents duplicate entries for the same timestamp and symbol.

Data Insertion

We execute the command using ExecuteNonQuery() which returns the number of rows affected.

Throughout the process, we print status messages to help diagnose where the process might fail.

Handling Market Data In the OnBar method, we gather the current market data and delegate to InsertMarketData for insertion into the database.

Additional Considerations

Testing & Edge Cases:

Verify that your PostgreSQL configuration (pg_hba.conf and postgresql.conf) authorizes connections from cTrader.

If you're running cTrader and PostgreSQL on different machines, ensure that firewalls or network policies aren’t blocking the connection.

Consider what should happen if the network is down or the server becomes unreachable. Robust error recovery or reattempt mechanisms might be needed for a production system.

Optimizations:

For high-frequency data, consider batching writes instead of inserting on every bar. Batching can significantly improve performance.

Monitor the connection pool and adjust your connection string settings if needed.

By ensuring proper access rights and careful handling of your network operations, your cBot will reliably send data to PostgreSQL.

Black_Hawk_Redemption
24 Mar 2025, 17:39

I am trying to do something similar, but with SQLite, fetching tick historical prices and real-time prices as they come in. The code is perfect, but the cTrader embedded compiler doesn't seem to support SQLite. I'm still trying to get the official .NET compiler to work again… so I can get SQLite to work.