Insert variables into an SQLite table in MetaEditor

External question — Stack Overflow Stack Exchange Author: jjo mmax Original post: https://stackoverflow.com/questions/79891186 License: CC BY-SA 4.0 — https://creativecommons.org/licenses/by-sa/4.0/ Adaptation: HTML converted to plain text; contact email addresses removed. If I want to insert into table COMPANY I write: "INSERT INTO COMPANY (AGE) VALUES (32);" . But if I want to insert a variable x=32 instead of 32, how can I do that? There are some ways in SQLite and Python ( 1 , 2 , and 3 ) but in MQL5 these do not work: "INSERT INTO COMPANY (AGE) VALUES (x);" "INSERT INTO COMPANY (AGE) VALUES (%x);" "INSERT INTO COMPANY (AGE) VALUES (?)", (32) "INSERT INTO COMPANY (AGE) VALUES (?) , (32)" "INSERT INTO COMPANY (AGE) VALUES (?) , (32);" "INSERT INTO COMPANY (AGE) VALUES (%s)", (32) "INSERT INTO COMPANY (AGE) VALUES (%s) , (32)" "INSERT INTO COMPANY (AGE) VALUES (*)" , (32) ... The source code: void OnInit() { string filename = "company.sqlite"; // create or open a database int db = DatabaseOpen(filename, DATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE); if(db == INVALID_HANDLE) { Print("DB: ", filename, " open failed with code ", _LastError); return; } if(DatabaseTableExists(db, "COMPANY")) { if(!DatabaseExecute(db, "DROP TABLE COMPANY")) { Print("Failed to drop table COMPANY with code ", _LastError); DatabaseClose(db); return; } } // creating table COMPANY if(!DatabaseExecute(db, "CREATE TABLE COMPANY(" "ID INT PRIMARY KEY NOT NULL," "NAME TEXT NOT NULL," "AGE INT NOT NULL," "ADDRESS CHAR(50)," "SALARY REAL );")) { Print("DB: ", filename, " create table failed with code ", _LastError); DatabaseClose(db); return; } // insert data into table if(!DatabaseExecute(db, "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1,'Paul',32,'California',25000.00);" "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2,'Allen',25,'Texas',15000.00); " "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3,'Teddy',23,'Norway',20000.00);" "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4,'Mark',25,'Rich-Mond',65000.00);")) { Print("DB: ", filename, " insert failed with code ", _LastError); DatabaseClose(db); return; } // prepare a request with a descriptor int request = DatabasePrepare(db, "SELECT * FROM COMPANY WHERE SALARY>15000"); if(request == INVALID_HANDLE) { Print("DB: ", filename, " request failed with code ", _LastError); DatabaseClose(db); return; } // printing all records with salary over 15000 int id, age; string name, address; double salary; Print("Persons with salary > 15000:"); for(int i = 0; DatabaseRead(request); i++) { // read the values of each field from the received record by its number if(DatabaseColumnInteger(request, 0, id) && DatabaseColumnText(request, 1, name) && DatabaseColumnInteger(request, 2, age) && DatabaseColumnText(request, 3, address) && DatabaseColumnDouble(request, 4, salary)) Print(i, ": ", id, " ", name, " ", age, " ", address, " ", salary); else { Print(i, ": DatabaseRead() failed with code ", _LastError); DatabaseFinalize(request); DatabaseClose(db); return; } } // deleting handle after use DatabaseFinalize(request); DatabaseClose(db); }

External answer — Stack Overflow Stack Exchange Author: Charlieface Original post: https://stackoverflow.com/a/79891197 License: CC BY-SA 4.0 — https://creativecommons.org/licenses/by-sa/4.0/ Adaptation: HTML converted to plain text; contact email addresses removed. According to the docs you can use DatabasePrepare to prepare a parameterized statement, and pass the parameters to that function. Something like this: int request = DatabasePrepare(db, "SELECT * FROM COMPANY WHERE SALARY > ?1", minSalary); You can do the same for an INSERT , just follow it with a call to DatabaseRead .

External answer — Stack Overflow Stack Exchange Author: Shivangi Prasad Original post: https://stackoverflow.com/a/79891742 License: CC BY-SA 4.0 — https://creativecommons.org/licenses/by-sa/4.0/ Adaptation: HTML converted to plain text; contact email addresses removed. CREATE TABLE COMPANY ( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00); INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00); INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00); INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) VALUES (4, 'Mark', 25, 'Richmond', 65000.00); INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) VALUES (5, 'David', 27, 'Texas', 85000.00);

External answer — Stack Overflow Stack Exchange Author: MatBailie Original post: https://stackoverflow.com/a/79891771 License: CC BY-SA 4.0 — https://creativecommons.org/licenses/by-sa/4.0/ Adaptation: HTML converted to plain text; contact email addresses removed. Commentary on the help page example for databasebind() /+------------------------------------------------------------------+ //| Script program start function | //+------------------------------------------------------------------+ void OnStart() { MqlTick ticks[]; //--- remember the start time before receiving the ticks uint start=GetTickCount(); //--- request the tick history per day ulong to=TimeCurrent()*1000; ulong from=to-PeriodSeconds(PERIOD_D1)*1000; if(CopyTicksRange(_Symbol, ticks, COPY_TICKS_ALL, from, to)==-1) { PrintFormat("%s: CopyTicksRange(%s - %s) failed, error=%d", _Symbol, TimeToString(datetime(from/1000)), TimeToString(datetime(to/1000)), _LastError); return; } else { //--- how many ticks were received and how much time it took to receive them PrintFormat("%s: CopyTicksRange received %d ticks in %d ms (from %s to %s)", _Symbol, ArraySize(ticks), GetTickCount()-start, TimeToString(datetime(from/1000)), TimeToString(datetime(to/1000))); } CREATE / CONNECT to the database... //--- set the file name for storing the database string filename=_Symbol+" "+TimeToString(datetime(from/1000))+" - "+TimeToString(datetime(to/1000))+".sqlite"; StringReplace(filename, ":", "."); // ":" character is not allowed in file names //--- open/create the database in the common terminal folder int db=DatabaseOpen(filename, DATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE | DATABASE_OPEN_COMMON); if(db==INVALID_HANDLE) { Print("Database: ", filename, " open failed with code ", GetLastError()); return; } else Print("Database: ", filename, " opened successfully"); //--- create the TICKS table if(!DatabaseExecute(db, "CREATE TABLE TICKS(" "SYMBOL CHAR(10)," "TIME INT NOT NULL," "BID REAL," "ASK REAL," "LAST REAL," "VOLUME INT," "TIME_MSC INT," "VOLUME_REAL REAL);")) { Print("DB: ", filename, " create table TICKS failed with code ", GetLastError()); DatabaseClose(db); return; } //--- display the list of all fields in the TICKS table if(DatabasePrint(db, "PRAGMA TABLE_INFO(TICKS)", 0)<0) { PrintFormat("DatabasePrint(\"PRAGMA TABLE_INFO(TICKS)\") failed, error code=%d at line %d", GetLastError(), __LINE__); DatabaseClose(db); return; } Create and prepare a parameterised INSERT statement. //--- create a parametrized request to add ticks to the TICKS table string sql="INSERT INTO TICKS (SYMBOL,TIME,BID,ASK,LAST,VOLUME,TIME_MSC,VOLUME_REAL)" " VALUES (?1,?2,?3,?4,?5,?6,?7,?8)"; // request parameters int request=DatabasePrepare(db, sql); if(request==INVALID_HANDLE) { PrintFormat("DatabasePrepare() failed with code=%d", GetLastError()); Print("SQL request: ", sql); DatabaseClose(db); return; } Begin a transaction and start binding variables to the prepared statement, in a loop so it can be executed for multiple rows of data. //--- set the value of the first request parameter DatabaseBind(request, 0, _Symbol); //--- remember the start time before adding ticks to the TICKS table start=GetTickCount(); DatabaseTransactionBegin(db); int total=ArraySize(ticks); bool request_error=false; for(int i=0; i<total; i++) { //--- set the values of the remaining parameters before adding the entry ResetLastError(); if(!DatabaseBind(request, 1, ticks[i].time)) { PrintFormat("DatabaseBind() failed with code=%d", GetLastError()); PrintFormat("Tick #%d line=%d", i+1, __LINE__); request_error=true; break; } //--- if the previous DatabaseBind() call was successful, set the next parameter if(!request_error && !DatabaseBind(request, 2, ticks[i].bid)) { PrintFormat("DatabaseBind() failed with code=%d", GetLastError()); PrintFormat("Tick #%d line=%d", i+1, __LINE__); request_error=true; break; } if(!request_error && !DatabaseBind(request, 3, ticks[i].ask)) { PrintFormat("DatabaseBind() failed with code=%d", GetLastError()); PrintFormat("Tick #%d line=%d", i+1, __LINE__); request_error=true; break; } if(!request_error && !DatabaseBind(request, 4, ticks[i].last)) { PrintFormat("DatabaseBind() failed with code=%d", GetLastError()); PrintFormat("Tick #%d line=%d", i+1, __LINE__); request_error=true; break; } if(!request_error && !DatabaseBind(request, 5, ticks[i].volume)) { PrintFormat("DatabaseBind() failed with code=%d", GetLastError()); PrintFormat("Tick #%d line=%d", i+1, __LINE__); request_error=true; break; } if(!request_error && !DatabaseBind(request, 6, ticks[i].time_msc)) { PrintFormat("DatabaseBind() failed with code=%d", GetLastError()); PrintFormat("Tick #%d line=%d", i+1, __LINE__); request_error=true; break; } if(!request_error && !DatabaseBind(request, 7, ticks[i].volume_real)) { PrintFormat("DatabaseBind() failed with code=%d", GetLastError()); PrintFormat("Tick #%d line=%d", i+1, __LINE__); request_error=true; break; } Run the statement, still within the loop. //--- execute a request for inserting the entry and check for an error if(!request_error && !DatabaseRead(request) && (GetLastError()!=ERR_DATABASE_NO_MORE_DATA)) { PrintFormat("DatabaseRead() failed with code=%d", GetLastError()); DatabaseFinalize(request); request_error=true; break; } Reset the prepared INSERT statement, ready to bind new parameters and run it again, then start the next loop. //--- reset the request before the next parameter update if(!request_error && !DatabaseReset(request)) { PrintFormat("DatabaseReset() failed with code=%d", GetLastError()); DatabaseFinalize(request); request_error=true; break; } } //--- done going through all the ticks With the loop of INSERTs finished, commit the transaction and close the connection. //--- transactions status if(request_error) { PrintFormat("Table TICKS: failed to add %d ticks ", ArraySize(ticks)); DatabaseTransactionRollback(db); DatabaseClose(db); return; } else { DatabaseTransactionCommit(db); PrintFormat("Table TICKS: added %d ticks in %d ms", ArraySize(ticks), GetTickCount()-start); } //--- close the database file and inform of that DatabaseClose(db); PrintFormat("Database: %s created and closed", filename); } /* Result: EURUSD: CopyTicksRange received 268061 ticks in 47 ms (from 2020.03.18 12:40 to 2020.03.19 12:40) Database: EURUSD 2020.03.18 12.40 - 2020.03.19 12.40.sqlite opened successfully #| cid name type notnull dflt_value pk -+----------------------------------------------- 1| 0 SYMBOL CHAR(10) 0 0 2| 1 TIME INT 1 0 3| 2 BID REAL 0 0 4| 3 ASK REAL 0 0 5| 4 LAST REAL 0 0 6| 5 VOLUME INT 0 0 7| 6 TIME_MSC INT 0 0 8| 7 VOLUME_REAL REAL 0 0 Table TICKS: added 268061 ticks in 797 ms Database: EURUSD 2020.03.18 12.40 - 2020.03.19 12.40.sqlite created and closed OnCalculateCorrelation=0.87 2020.03.19 13:00: EURUSD vs GBPUSD PERIOD_M30 */