Unable to embed SQLite in my Robot

driftingprogrammer
16 Feb 2020, 12:43

RE:

driftingprogrammer said:

Hi,

I am trying to embed a lightweight database in my robots so they can communicate through that database and log event in the database and also create a sort of audit log since at this point i have not found any way of saving the log outputs in cTrader Automate.

I have changed the .net Framework to 4.6.1 in Visual Studio and imported the System.Data.Sqllite package using Nuget Package.

Theoretically speaking this is all i need to do and now i can start invoking SQLite API's to create file based database and access them.

I can write a console application on Visual Studio and everything goes fine, i can see my dlls are being copied to the bin/Debug folder.

But when i run my robot (it has compiled fine since i have imported SQLite using Nuget Package in Visual Studio 2017)

I get the below error

"We have an exception Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

 

I have spent a lot of time but now i think i am going in circles none of the solutions mentioned here worked for me - 

Unable to load DLL 'SQLite.Interop.dll'

 

Any guidance would be appreciated, or is there some other recommend database that can be used by the robot, please let me know.

Thanks in advance,

Warm Regards,

Vipin,
 

I have now tried doing the same thing as mentioned above but without changing the .NetFramework to version 4.6.1 . For .NetFramework 4 Client Profile itself i downloaded the System.Data.Sqlite package but still when i try to create a connection from within the robot it throws the below exception.

Crashed in OnStart with DllNotFoundException: Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
 

In my experience with Postgres , I was getting an error when my .NetFramework was 4.6.1 so i had to downgrade it back to 4 Client Profile and rather choose a much older version of the postgres package on nuget, the one that would install with .NetFramework 4 Client Profle, and then things worked fine for me, i was able to create connections to the database.

So in a way i was hoping that the reason for the above failure might have been me upgrading the .Net Framework. But no luck here, here the robot fails to connect to the database although I have used the packages from nuget which are compatible with .NetFrameowork 4 Client Profile.

driftingprogrammer
25 Feb 2020, 08:00

Making development easy

The first time I open metatrader and the first item it shows me under the tab Article is - SQLITE: NATIVE HANDLING OF SQL DATABASES IN MQL5

Where they have given step by step instructions on how to achieve what i have been trying to achieve in cTrader without any success.

And so far cTrader does not even seem to bother to acknowledge weather they support SQLite, or they intend to to sometime in the future.

Is there a reason why one would not conclude from this example that MT5 ecosystem is way more mature for people interested in algorithmic trading.

ClickAlgo
25 Feb 2020, 10:29

Hi, Ben,

As the cTrader uses the full power of the .NET framework almost anything is possible, it just depends on your knowledge of software engineering, it is worth noting that Spotware simply provides an API to the platform, you can put very complex core logic into an architecture of satellite .NET assemblies. It is in these assemblies that you can connect to any database Oracle, SQL Server and even SQLite.

"We have an exception Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

Make sure you have added the reference to both your cBot & external assembly projects. You can set the .NET Framework version to anything you want on your .NET assemblies.

Add the Nuget Package to both your cBot project and the satellite .NET assembly.

Paul Hayes
Sales & Marketing
Email: contact@clickalgo.com
Phone: (44) 203 289 6573
Website: https://clickalgo.com

Twitter | Facebook | YouTube | Pinterest | LinkedIn

PS: Why not join our instant chat group on Telegram or visit our YouTube Channel

PanagiotisCharalampous
25 Feb 2020, 10:36

Hi Ben Floyd,

If you can share with us the exact steps you are following so that we can reproduce the problem, we can have a look at it and let you know if we have any suggestions.

Best Regards,

Panagiotis 

Join us on Telegram

noeyamn
05 Mar 2020, 12:55

Hi everyone

I am taking back my previous message saying that it I succeeded to make it work:

I have the same issue.

This is what I tried :

- (VS 2019 ) : nuget : install System.Data.SQLite package

- (ctrader / manage bot reference) : add reference to C:\Users\Me\Documents\cAlgo\Sources\Robots\MyBot\packages\System.Data.SQLite.Core.1.0.112.0\lib\net40\System.Data.SQLite.dll

Here is a bypass : I am currently running it by loading dll from code :

Here is how I connect :

_SqliteAssembly = Assembly.LoadFrom(@"C:\Users\Me\Documents\cAlgo\Sources\Robots\MyBot\packages\System.Data.SQLite.Core.1.0.112.0\lib\net40\System.Data.SQLite.dll");
            Type sqliteConnectionType = _SqliteAssembly.GetType("System.Data.SQLite.SQLiteConnection");

            _SqliteConnection = Activator.CreateInstance(sqliteConnectionType, new object[] { "Data Source=" + dataBasePath + "; Version=3; FailIfMissing=True;" });
            sqliteConnectionType.GetMethod("Open").Invoke(_SqliteConnection, null);

I do the rest by using reflection to invoke methods, this is quit tidious but it works

Cheers

noeyamn
05 Mar 2020, 21:44

@Panagiotis

Can you let me knowif you can reproduce the error?

PanagiotisCharalampous
06 Mar 2020, 08:35

Hi noeyamn,

I cannot reproduce any errors when installing from NuGet. Why do you add references manually? The Package Manager should take care of this automatically.

Best Regards,

Panagiotis 

Join us on Telegram

noeyamn
06 Mar 2020, 21:31

RE:

PanagiotisCharalampous said:

Hi noeyamn,

I cannot reproduce any errors when installing from NuGet. Why do you add references manually? The Package Manager should take care of this automatically.

Best Regards,

Panagiotis 

Join us on Telegram

Here is how I get the error :

- (VS 2019 ) : nuget : install System.Data.SQLite package

- (ctrader / manage bot reference) : add reference to C:\Users\Me\Documents\cAlgo\Sources\Robots\MyBot\packages\System.Data.SQLite.Core.1.0.112.0\lib\net40\System.Data.SQLite.dll

Then write some code to open a connection to db (the following is what I have tried)

String dataBasePath = @"D:\DB.sqlite";
SQLiteConnection _SqliteConnection = new SQLiteConnection("Data Source=" + dataBasePath + "; Version=3; FailIfMissing=True;");
SQLiteConnection _SqliteConnection.Open();

Then lunch bot

driftingprogrammer
08 Mar 2020, 23:36

More Details

Here is another post complaining that they could not connect to SQLite database - 

I have mentioned in this post - 

how i had to use old libraries for connection to postgresql but similar solution did not work for SQLite.

I am using Visual Studio 2017, are we suppose to use 2019 version?

Thanks.

PanagiotisCharalampous
09 Mar 2020, 12:11

Hi noeyamn,

You did not answer my question.  Why do you add references manually? I installed SQLite using Package Manager and I have no problems. See below

Everything seems fine and the cBot builds without a problem.

Best Regards,

Panagiotis 

Join us on Telegram

noeyamn
09 Mar 2020, 16:03 ( Updated at: 21 Dec 2023, 09:21 )

RE:

PanagiotisCharalampous said:

Hi noeyamn,

You did not answer my question.  Why do you add references manually? I installed SQLite using Package Manager and I have no problems. See below

Everything seems fine and the cBot builds without a problem.

Best Regards,

Panagiotis 

Join us on Telegram

Hi Panagiotis,

I manually load the assembly to avoid the exception "Unable to load DLL 'SQLite.Interop.dll'" when running the bot only (compilation was succesful) 

The exception happens to me when starting the bot.

Did you try running the bot you showed on the screenshot ?

___________________________

I have just restarted from scratch with a brand new bot and the error is different now :

=> Crashed in OnStart with TypeInitializationException: The type initializer for 'System.Data.SQLite.UnsafeNativeMethods' threw an exception.

The error happens when starting bot. Compilation is succesful

When placing a breakpoint, the error happens on

_SqliteConnection = new SQLiteConnection("Data Source=" + dataBasePath + "; Version=3; New=True;");

It is a security exception

driftingprogrammer
09 Mar 2020, 18:55 ( Updated at: 21 Dec 2023, 09:21 )

RE: RE:

noeyamn said:

PanagiotisCharalampous said:

Hi noeyamn,

You did not answer my question.  Why do you add references manually? I installed SQLite using Package Manager and I have no problems. See below

Everything seems fine and the cBot builds without a problem.

Best Regards,

Panagiotis 

Join us on Telegram

Hi Panagiotis,

I manually load the assembly to avoid the exception "Unable to load DLL 'SQLite.Interop.dll'" when running the bot only (compilation was succesful) 

The exception happens to me when starting the bot.

Did you try running the bot you showed on the screenshot ?

___________________________

I have just restarted from scratch with a brand new bot and the error is different now :

=> Crashed in OnStart with TypeInitializationException: The type initializer for 'System.Data.SQLite.UnsafeNativeMethods' threw an exception.

The error happens when starting bot. Compilation is succesful

When placing a breakpoint, the error happens on

_SqliteConnection = new SQLiteConnection("Data Source=" + dataBasePath + "; Version=3; New=True;");

It is a security exception

 

 

This security exception typically happens when you have not given AccessRights.FileSystem to your cbot.

driftingprogrammer
09 Mar 2020, 18:56 ( Updated at: 21 Dec 2023, 09:21 )

RE:

PanagiotisCharalampous said:

Hi noeyamn,

You did not answer my question.  Why do you add references manually? I installed SQLite using Package Manager and I have no problems. See below

Everything seems fine and the cBot builds without a problem.

Best Regards,

Panagiotis 

Join us on Telegram

Have you been able to run the program, can you please share screenshot of a running robot printing out the successful connection object to the database.

Thanks in advance.

noeyamn
09 Mar 2020, 21:56

Thansk Panagiotis for telling me about AccessRights.

After giving AccessRights.FullAccess to the bot, the exception is back again when starting bot:

Compilation is Ok

Crashed in OnStart with DllNotFoundException: Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

I have the minimum code :

using System;
using System.Data.SQLite;
using System.Linq;
using cAlgo.API;
using cAlgo.API.Indicators;
using cAlgo.API.Internals;
using cAlgo.Indicators;

namespace cAlgo.Robots
{
    [Robot(TimeZone = TimeZones.UTC, AccessRights = AccessRights.FullAccess)]
    public class NewcBot : Robot
    {
        [Parameter(DefaultValue = 0.0)]
        public double Parameter { get; set; }

        private SQLiteConnection _SqliteConnection;
        protected override void OnStart()
        {
            String dataBasePath = "D:\\DBTest.sqlite";
            _SqliteConnection = new SQLiteConnection("Data Source=" + dataBasePath + "; Version=3; FailIfMissing=True;");
            _SqliteConnection.Open();
        }

        protected override void OnTick()
        {
        }

        protected override void OnStop()
        {
        }
    }
}

with net40 System.data.sqlite.dll referenced in ctrader and package installed in VS

noeyamn
10 Mar 2020, 22:26

Hi Panagiotis,

Have you been able to reproduce the issue ?

Regards.

PanagiotisCharalampous
11 Mar 2020, 10:42

Hi noeyamn,

The problem seems to be that SQLite .Net dll is referencing other dll files which cannot be found when it is looking in the folder of cTrader application. The workaround is to add a folder in Documents e.g. "sqllite" and add SQLite.Interop.dll file there. The cBot can add this folder to Path environment variable so that this folder would be searched too. See below an example

using System;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using cAlgo.API;
using cAlgo.API.Indicators;
using cAlgo.API.Internals;
using cAlgo.Indicators;

namespace cAlgo.Robots
{
    [Robot(TimeZone = TimeZones.UTC, AccessRights = AccessRights.FullAccess)]
    public class NewcBot : Robot
    {
        [Parameter(DefaultValue = 0.0)]
        public double Parameter { get; set; }

        protected override void OnStart()
        {
            AddSqlightFolderToPath();

            var dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "mydatabase.db");
            var con = new SQLiteConnection("Data Source=" + dbPath);
            con.Open();
            Print("DB connection: ", con.State);
        }

        private void AddSqlightFolderToPath()
        {
            var libPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "sqllite");

            var path = Environment.GetEnvironmentVariable("PATH");
            var pathItems = path.Split(';');
            Print(libPath);
            if (!pathItems.Contains(libPath))
                Environment.SetEnvironmentVariable("PATH", path + ";" + libPath);
        }

        protected override void OnTick()
        {
            // Put your core logic here
        }

        protected override void OnStop()
        {
            // Put your deinitialization logic here
        }
    }
}

Best Regards,

Panagiotis 

Join us on Telegram

driftingprogrammer
11 Mar 2020, 13:16

RE:

PanagiotisCharalampous said:

Hi noeyamn,

The problem seems to be that SQLite .Net dll is referencing other dll files which cannot be found when it is looking in the folder of cTrader application. The workaround is to add a folder in Documents e.g. "sqllite" and add SQLite.Interop.dll file there. The cBot can add this folder to Path environment variable so that this folder would be searched too. See below an example

using System;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using cAlgo.API;
using cAlgo.API.Indicators;
using cAlgo.API.Internals;
using cAlgo.Indicators;

namespace cAlgo.Robots
{
    [Robot(TimeZone = TimeZones.UTC, AccessRights = AccessRights.FullAccess)]
    public class NewcBot : Robot
    {
        [Parameter(DefaultValue = 0.0)]
        public double Parameter { get; set; }

        protected override void OnStart()
        {
            AddSqlightFolderToPath();

            var dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "mydatabase.db");
            var con = new SQLiteConnection("Data Source=" + dbPath);
            con.Open();
            Print("DB connection: ", con.State);
        }

        private void AddSqlightFolderToPath()
        {
            var libPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "sqllite");

            var path = Environment.GetEnvironmentVariable("PATH");
            var pathItems = path.Split(';');
            Print(libPath);
            if (!pathItems.Contains(libPath))
                Environment.SetEnvironmentVariable("PATH", path + ";" + libPath);
        }

        protected override void OnTick()
        {
            // Put your core logic here
        }

        protected override void OnStop()
        {
            // Put your deinitialization logic here
        }
    }
}

Best Regards,

Panagiotis 

Join us on Telegram

Glad you were able to replicate the issue, thanks for the solution.

driftingprogrammer
11 Mar 2020, 13:19

RE: RE:

driftingprogrammer said:

PanagiotisCharalampous said:

Hi noeyamn,

The problem seems to be that SQLite .Net dll is referencing other dll files which cannot be found when it is looking in the folder of cTrader application. The workaround is to add a folder in Documents e.g. "sqllite" and add SQLite.Interop.dll file there. The cBot can add this folder to Path environment variable so that this folder would be searched too. See below an example

using System;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using cAlgo.API;
using cAlgo.API.Indicators;
using cAlgo.API.Internals;
using cAlgo.Indicators;

namespace cAlgo.Robots
{
    [Robot(TimeZone = TimeZones.UTC, AccessRights = AccessRights.FullAccess)]
    public class NewcBot : Robot
    {
        [Parameter(DefaultValue = 0.0)]
        public double Parameter { get; set; }

        protected override void OnStart()
        {
            AddSqlightFolderToPath();

            var dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "mydatabase.db");
            var con = new SQLiteConnection("Data Source=" + dbPath);
            con.Open();
            Print("DB connection: ", con.State);
        }

        private void AddSqlightFolderToPath()
        {
            var libPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "sqllite");

            var path = Environment.GetEnvironmentVariable("PATH");
            var pathItems = path.Split(';');
            Print(libPath);
            if (!pathItems.Contains(libPath))
                Environment.SetEnvironmentVariable("PATH", path + ";" + libPath);
        }

        protected override void OnTick()
        {
            // Put your core logic here
        }

        protected override void OnStop()
        {
            // Put your deinitialization logic here
        }
    }
}

Best Regards,

Panagiotis 

Join us on Telegram

Glad you were able to replicate the issue, thanks for the solution.

Can you please let us know what versuon of SQLite driver did you donwload from Nuget.

Thanks in advance.

PanagiotisCharalampous
11 Mar 2020, 14:59

Hi driftingprogrammer,

1.0.112

Best Regards,

Panagiotis 

Join us on Telegram

noeyamn
11 Mar 2020, 22:27

Thank you very much Panagiotis and the ctrader team for the workaround that you proposed.

Is it going to be a fix in a future release ?