ForumSections

Trouble with SQL Database query results.

New comment

Hello,

I'm starting to work with Databases and I'm getting more records in a query in MQL than if I paste the exact same query in the database interface.

I literally paste the SELECT statement from my MQL code into the database query interface.  In the query interface I get six records.  In the MQL I get 28 records, and in the database interface a "SELECT * ...." returns 76 records.

The SELECT statements:

In the database interface:


And in the MQL code, I get 28 Records:

//+------------------------------------------------------------------+
//|                                                database test.mq5 |
//|                                             https://www.mql5.com |
//+------------------------------------------------------------------+
#property link      "https://www.mql5.com"
#property version   "1.00"


   struct restrictionData
      {
      string instrument;
      string dates;
      int restrictions;
      };

restrictionData restriction;


//+------------------------------------------------------------------+
//| Script program start function                                    |
//+------------------------------------------------------------------+
void OnStart()
  {

//---
   int eventDBHandle=DatabaseOpen("FTMO_Database.db",DATABASE_OPEN_READONLY);

   // doesn't matter if I have the last ";" inside the close quote or not -- same result
   // doesn't matter if I have =1 or ==1 , same result of 28 records.
   // SELECT DISTINCT ... also gives same result.
   // I'm getting duplicate records in the query result in the MQL, and only 6 records in the database query interface.
   // I do have some duplicate records in the database.  But when I use SELECT DISTINCT in the query interface, I get 4 unique records.
   string sqlSelect="SELECT nb.Instrument, nb.Dates, nb.Restrictions FROM NewsBlackout as nb WHERE Restrictions==1 ORDER BY dates ASC;";
   int dbReq=DatabasePrepare(eventDBHandle,sqlSelect);
   if(dbReq==INVALID_HANDLE)
      {
      Print("Invalid Database Handle");
      DatabaseClose(eventDBHandle);
      return;
      }
   
   bool sqlSuccess=DatabaseReadBind(dbReq, restriction);
   if(sqlSuccess==false)
      {
      Print("No records found: ",GetLastError());
      return;
      }
   int recCount=sizeof(restriction);
   Print("Records Selected: ",recCount);
   // returns 28 records
   
   // and this successfully loops through values, ALL of which have restriction.restrictions = 1.
   for(int i=0; i<recCount; i++)
      {
      DatabaseReadBind(dbReq, restriction);
      Print(restriction.instrument," - ",restriction.dates," - Restriction: ",restriction.restrictions);
      }
   DatabaseFinalize(dbReq);
   
  }
//+------------------------------------------------------------------+

I'm totally confused.  I feel like if I execute a SELECT in the query interface, and copy/paste it into my MQL, I should always get the same record count.

Capitalizing the field names in my struct doesn't change anything either.

Any help is GREATLY appreciated!!!

Thanks,

sizeof()returns the byte-size of your data structure, not the total rows of your SELECT result

int recCount=sizeof(restriction);
Soewono Effendi #:
sizeof()returns the byte-size of your data structure, not the total rows of your SELECT result

Thank you!


Dang, now I'll have to find out how to determine the record count.  I appreciate it very much!!!!

New comment