Discussion of article "SQLite: Native handling of SQL databases in MQL5" - page 8

New comment
 

And question #2:

Trying to use the ATTACH/DETACH functions to transfer data between databases using SQLite's built-in mechanism, the result is a transaction error 5601...

When running the same transaction in SQLiteStudio, everything is fine.

Is this done on purpose or is something not working?

   m_db.Create("IndiTemp","indi",m_db_main.GetStruct(),true);
   string db_path=StringFormat("%s\\Files\\%s.sqlite",
                               TerminalInfoString(TERMINAL_COMMONDATA_PATH),
                               "Indi");
                               
   string query=StringFormat("ATTACH DATABASE '%s' AS X; ",db_path),
          tables[];
          
   int total=m_db.GetTables(tables);
   if(total>0)
     {
      for(int i=0; i<total; i++)
         StringAdd(query,StringFormat("INSERT OR IGNORE INTO %s SELECT * FROM X.%s; ",tables[i],tables[i]));
     }
     
   StringAdd(query,"DETACH X; ");
   m_db.TransactionExecute(query);
 
Daniil Kurmyshev program execution), probably error inside MQL function, part of the code from my library:


Unfortunately, the DatabaseExport function has not received the functionality to export the table (although this functionality was originally intended) and currently supports only queries.

On the error 4022, do you get it during testing, if yes, the tester has a limitation on the export file size - 1GB total size of data written to disc ?

 
Daniil Kurmyshev SQLite's inbuilt mechanism, the result is transaction error 5601...

When executing the same transaction in SQLiteStudio, everything is fine.

Is this done on purpose or is something not working?

The query should work, check if the paths are correct
 
Ilyas #:

Unfortunately, the DatabaseExport function never got the table export functionality (although this functionality was originally intended) and currently only supports queries.

On the error 4022, do you get it during testing, if yes, the tester has a limitation on the export file size - 1GB total size of data written to disc ?

Error 4022 appears without tester, if you specify query instead of table name in DatabaseExport function.

The size of the database is less than 10 Mb, I was planning to use this functionality to copy data from the database on disc to the database in memory.
 
Ilyas #:
The query should work, check if the paths are correct

Thanks, I will try to provide the path differently, but in fact in SQLiteStudio the query at the specified path works, I output the query to the terminal log and then copied and executed in SQLiteStudio without problems.

I will write about the result.

 
Is there a possibility to connect to the base in RAM by name and if to use the parameter of the general cache, I know that SQLite has such a possibility, but I would like to know if it is included in MT5, thank you.
 

Test for multi-threaded writing to the database, run multiple copies of the script, each copy can be labelled with the expert_id parameter.

#property copyright "Aliaksandr Hryshyn"
#property link      "https://www.mql5.com/en/users/greshnik1"
#property version   "1.00"
#property script_show_inputs

enum e_E
  {
   e_0,
   e_1,
   e_2,
   e_3,
  };
input e_E expert_id=0;
//+------------------------------------------------------------------+
//| Script programme start function|
//+------------------------------------------------------------------+
void OnStart()
  {
   int db_handle=DatabaseOpen("tmp//test.sqlite",DATABASE_OPEN_READWRITE|DATABASE_OPEN_CREATE|DATABASE_OPEN_COMMON);
   const string sql_create=
      "CREATE TABLE IF NOT EXISTS Demo("
      "Id INTEGER PRIMARY KEY AUTOINCREMENT,"
      "Expert_id INTEGER,"
      "demo_0 TEXT(2000),"
      "demo_1 TEXT(2000),"
      "demo_2 TEXT(2000),"
      "demo_3 TEXT(2000),"
      "demo_4 TEXT(2000),"
      "demo_5 TEXT(2000),"
      "demo_6 TEXT(2000),"
      "demo_7 TEXT(2000)"
      ");";
   const string sql_add=
      "INSERT INTO Demo(Expert_id,demo_%s) VALUES(%s,'%s')";
   if(!DatabaseExecute(db_handle,sql_create))//Create table if absent
     {
      DatabaseClose(db_handle);
      Print("Error:",GetLastError());
      Print("Query:",sql_create);
      return;
     }
   string s1;
   string sql;
   int err_counter=100;//Record addition error counter
   for(int i1=0; i1<1000; i1++)
     {
      s1="";
      for(int i2=0; i2<100; i2++)
        {
         s1+=string(MathRand());
        }
      sql=StringFormat(sql_add,string(MathRand()%8),string(expert_id),s1);
      if(DatabaseExecute(db_handle,sql))//Add a record to a random column from the demo_... set.
        {
         err_counter=100;
        }
      else
        {
         if(err_counter==0)
           {
            DatabaseClose(db_handle);
            Print(expert_id,": end");
            return;
           }
         else
           {
            Print(expert_id,": ",err_counter);
            err_counter--;
            i1--;
           }
        }
      if(IsStopped())
         break;
      Sleep(1);// A little bit of sleep
     }
   DatabaseClose(db_handle);
  }

Everything is written correctly:


But I get errors about blocking:

...
2021.09.18 13:08:05.252 Test_sql (EURUSD,M1)    2: 80
2021.09.18 13:08:05.267 Test_sql (EURUSD,M1)    database error, database is locked
2021.09.18 13:08:05.267 Test_sql (EURUSD,M1)    2: 79
2021.09.18 13:08:05.283 Test_sql (EURUSD,M1)    database error, database is locked
2021.09.18 13:08:05.283 Test_sql (EURUSD,M1)    2: 78
2021.09.18 13:08:05.299 Test_sql (EURUSD,M1)    database error, database is locked
2021.09.18 13:08:05.299 Test_sql (EURUSD,M1)    1: 100
2021.09.18 13:08:05.315 Test_sql (EURUSD,M1)    database error, database is locked
2021.09.18 13:08:05.315 Test_sql (EURUSD,M1)    1: 99
...

How to be? There should be a mechanism of conditional record insertion without returning an error.

 
Ilyas #:
The query should work, make sure the paths are correct

1. Checked ATTACH DATABASE does work!

The problem was in the following, I tried to attach to the base created in RAM, the physical base in this case ATTACH DATABASE does not work, but if we attach the RAM base to the physical base, it works!

This is quite enough for me to create a copy of the base in RAM, which speeds up calculations)))


2. I decided to go further... and check one more possibility, namely:

ATTACH DATABASE 'file:memdb1?mode=memory&cache=shared' AS M;

According to the idea, the database should be created in RAM and cease to exist after the thread is closed, but its behaviour is different...

In 1, I can join it from any MQL5 programme, Expert Advisor, indicator, script, service,

In 2 the data in it are saved forever, even after rebooting, I already thought ... that maybe the database is created physically, but a search by the name *memdb1*did not find anything

P.S. Perhaps because I created the base from the indicator, and it works in a common thread?


This is a cool feature, but I would like to know if it will remain in the terminal in the next updates and after my message about it:)))).

In this case, you can easily transfer large amounts of data between programs, without problems and delays, and do not use global variables limited by name length....


In this execution it is deleted after the MQL5 programme is finished:

ATTACH DATABASE ':memory:?cache=shared' AS M;

ATTACH DATABASE ':memory:' AS M;

 

Good afternoon!

Question to MT developers, is it possible to disable the Shared attribute of the SQLite library, by experience in other projects determined that this is the cause of the error: database error , database is locked.

If you are accessing the database from different threads, it is all solved at the level of the database itself, namely by executing these two queries immediately after opening the database:

1. PRAGMA journal_mode = WAL;

2. PRAGMA busy_timeout = 1000;

but the locking error will go away only after you make the base not Shared, you can 't do it with queries.

 
Daniil Kurmyshev projects determined that this is the cause of the error: database error, database is locked.

If access is from different threads to the database, it is all solved at the level of the database itself, namely by executing these two queries immediately after opening the database:

1. PRAGMA journal_mode = WAL;

2. PRAGMA busy_timeout = 1000;

but the locking error will go away only after you make the base not Shared, you can 't do it with queries.

In this case the speed of queries will drop and memory requirements will increase....

but everything is OK :-)

SQLite is designed for one thread or one writer and many readers. Many writers are not about it, about it other bases that are not "light".

it's not about MQL - it's just the way it is really and everywhere.
123456789101112
New comment