mql4_mysql_ansi2uniqode stopped working?

[BobbyT]

Hello all,
Last year I was working on an EA that relies on calling data from MySQL using "mql4-mysql.mqh" include and "libmysql.dll" dll library.
Due to life and what not I had to put it on the back burner for a while. I fired it up again this week to find it crashing the terminal.
I have tracked it down to the mql4_mysql_ansi2unicode function which is called from the include file.
For the life of me I can't work out what has happened. There are several functions in my EA which depend on retrieving data from MySQL. I have included one of these below. If anyone could lend me some brain cells I would be most grateful

Code: Select all

void LastCandle()   
   {
     Print("Last candle");
           string query =StringConcatenate("Select concat ('->',concat_ws ('->',CAST(MQLTime as char))) from eurusd_m1 ORDER BY MQLTime DESC LIMIT 1;"); // take the first candle latest DB
     Print("query = ",query);
       
       int row="";
       string ultimacandela="";
       int length = 0;
       
       uchar queryChar[];
       StringToCharArray(query, queryChar);
       
       length = StringLen (query);
       mysql_real_query (dbConnectId, queryChar, length);
  
       int myerr = mysql_errno (dbConnectId);
       if (myerr> 0)
           Print ("error =", myerr);

       int result = mysql_store_result (dbConnectId);
       Print("result=",result);
       int numOfRows = mysql_num_rows (result);
       Print("numrows=",numOfRows);
       if (numOfRows> 0)
       {
             row = mysql_fetch_row (result);
             Print("row=",row);
             row = mql4_mysql_ansi2unicode(row);
             Print("ansiiRow=",row);
             row = StringTrimLeft(row);
             Print("TrimmedANSI=",row);
             row = StringSubstr(row,8,80);
             Print("row2 = ",row);
      } 
      mysql_free_result (result);
      return;
    }

Cheers,
BobbyT

Edit: I should probably say that there are no issues connecting to the database nor writing data to it. In fact, the above function has a block removed which adds all the available data when there is non present in the target table. What code is left is meant to retrieve the datatime stamp of the last bar entered to the database. This value is then used by other functions to back fill any missing data.

[renexxxx]

Did you get the latest MySQL library for build 600+ from https://www.mql5.com/en/code/11114?

[BobbyT]

Hi Rene,

Thanks for the reply.
Yeah this is the version I'm using. Even tried the version from github as it is kept more up to date.
trying to implement the stuff from here this morning: https://www.mql5.com/en/articles/932
I'll keep you posted.

if you (or anyone else) have any solutions that would be great. It will save me from having to do an extensive rewrite

Cheers,
BobbyT

[renexxxx]

Hi Rene,

Thanks for the reply.
Yeah this is the version I'm using. Even tried the version from github as it is kept more up to date.
trying to implement the stuff from here this morning: https://www.mql5.com/en/articles/932
I'll keep you posted.

if you (or anyone else) have any solutions that would be great. It will save me from having to do an extensive rewrite

Cheers,
BobbyT

Hi BobbyT,

I can't replicate your setup, as I don't have your data model and the rest of your code. What strikes me though is this:

Code: Select all

             row = mql4_mysql_ansi2unicode(row);

line.

mql4_mysql_ansi2unicode needs an integer memory address (which is 'row') and returns a string. You assign that string to 'row', which is an integer --> not good.

You may have to have a look at what they do with the memcpy stuff to get the results of the mysql_fetch_row into MQL4, like so:

Code: Select all

ArrayResize(data, num_rows);
    
    for ( int i = 0; i < num_rows; i++ ) {
    
      int row_ptr = mysql_fetch_row(resultStruct);
      int len_ptr = mysql_fetch_lengths(resultStruct);
      
      for ( int j = 0; j < num_fields; j++ ) {
         int leng;
         memcpy(leng, len_ptr + j*sizeof(int), sizeof(int));
         
         ArrayResize(byte,leng+1);
         ArrayInitialize(byte,0);
         
         int row_ptr_pos;
         memcpy(row_ptr_pos, row_ptr + j*sizeof(int), sizeof(int));
         memcpy(byte, row_ptr_pos, leng);
         
         string s = CharArrayToString(byte);
         data[i][j] = s;
         
         LocalFree(leng);
         LocalFree(row_ptr_pos);
      }
    }

[BobbyT]

I had previously tried setting row at string and that made difference to the problem. I'm a bit miffed as to whats happening as this had worked previously.

I'll have a play with your suggestion when I get my head around it

I also tried implementing this: https://www.mql5.com/en/articles/932

After swapping out the connection block with my existing connection block:

Code: Select all

void MySQLConnect(){
goodConnect = init_MySQL(dbConnectId, host, user, pass, "", port, socket, client);    
   if (goodConnect) 
    Print("Good connection; ID=",dbConnectId);
   else
    {Print("Shite Connection");
    return;} // bad connect
    
    
    string dbquery=StringConcatenate("use ",dbName,";");
    MySQL_Query(dbConnectId, dbquery);
        Print("DB " ,dbName," found and connected");
    mysql_select_db (dbConnectId, dbName);  //select the db for future operations
    int myerr = mysql_errno (dbConnectId); // note the presence of errors
    if (myerr> 0)
        Print ("errore =", myerr); // record errors in log
   }

I get connection but get an access violation

The code for the retrieval is as found below with the original (and defunct) connection code commented out:

Code: Select all

void GetData() {

/*   dbConnectId = MySqlConnect (host, user, pass, dbName, port, socket, client);
   
   if (dbConnectId <0) {
      Print("Connection failed; error = ",MySqlErrorDescription);
      return;}
   else {
      Print ("Connected as ", dbConnectId); }*/
////////////////////////////////////////////////////////////////////////////      
   string query;
   int i, Cursor, Rows;
   
   int vId;
   string vCode;
   datetime vStartTime;
   
   query = "select MQLTime, RTime, High from eurusd_m1 order by MQLTime desc limit 1";
      Print ("SQL > ", query);
   Cursor = MySqlCursorOpen(dbConnectId, query);
////////////////////////////////////////////////////////////////////////////
   if (Cursor >= 1) {
      Rows = MySqlCursorRows(Cursor);
         Print (Rows, " row(s) selected");
      for (i = 0; i < Rows; i++)
         if (MySqlCursorFetchRow(Cursor)) {           
            vStartTime = MySqlGetFieldAsDatetime(Cursor, 0);      //MQLTime
            vCode = MySqlGetFieldAsString(Cursor, 1);    //RTime
            vId = MySqlGetFieldAsInt(Cursor, 2);      //High
            Print ("Row[",i,"]: id = ",vId, ", code = ", vCode, ", start_time = ", TimeToString(vStartTime, TIME_DATE|TIME_SECONDS)); }
        
         MySqlCursorClose(Cursor); }
      else {
         Print("Cursor close failed; error = ",MySqlErrorDescription); }
////////////////////////////////////////////////////////////////////////////
   MySqlDisconnect(dbConnectId);
   Print("Disconnected biatch");
}

Once again I'll keep you posted

Edit: What would you need from me (code, data etc) to help you help me a bit easier

[BobbyT]

Yeah boi!!!
After doing this all morning: http://www.youtube.com/watch?v=WmPkqkqAxyc

I arrived at this:

Code: Select all

#property strict

#import "libmysql.dll"

#include<MQLMySQL.mqh>
#include<mql4-mysql.mqh>

extern string  Host     = "127.0.0.1";
extern string  User     = "root";
extern string  Password     = "password";
extern string  Database   = "mt76";       
extern string  Socket   = "";

extern int     Port     = 3306;
extern int     ClientFlag   = 0;

extern int    DB = 0;
extern bool   goodConnect=false;
//+------------------------------------------------------------------+
//| Expert initialization function                                   |
//+------------------------------------------------------------------+
int OnInit()
  {
//---
   mysqlconnect();
   GetData();
//---
   return(INIT_SUCCEEDED);
  }
//+------------------------------------------------------------------+
//| Expert deinitialization function                                 |
//+------------------------------------------------------------------+
void OnDeinit(const int reason)
  {
//---
   
  }
//+------------------------------------------------------------------+
//| Expert tick function                                             |
//+------------------------------------------------------------------+
void OnTick()
  {
//---
   
  }
//+------------------------------------------------------------------+
void mysqlconnect(){
   Print ("Connecting...");

   DB = MySqlConnect(Host, User, Password, Database, Port, Socket, ClientFlag);    
   
   if (DB == -1) { Print ("Connection failed! Error: "+MySqlErrorDescription); } 
   else { 
   Print ("Connected! DBID#",DB);}
}
////////////////////////////////////////////////////////////////////////////
void GetData() {

   string query;
   int i, Cursor, Rows;
   
   double high;
   string RTime;
   datetime MQLTime;
   
   query = "select * from `eurusd_m1` order by MQLTime desc limit 1;";
      Print ("SQL > ", query);
   Cursor = MySqlCursorOpen(DB, query);
////////////////////////////////////////////////////////////////////////////
   if (Cursor >= 0) {
      Rows = MySqlCursorRows(Cursor);
         Print (Rows, " row(s) selected");
      for (i = 0; i < Rows; i++)
         if (MySqlCursorFetchRow(Cursor)) {           
            MQLTime = MySqlGetFieldAsDatetime(Cursor, 0);      //MQLTime
            RTime = MySqlGetFieldAsString(Cursor, 1);    //RTime
            high = MySqlGetFieldAsDouble(Cursor, 2);      //High
            Print ("Row[",i,"]: High = ",high, ", R_time = ", RTime, ", MQL_time = ", TimeToString(MQLTime, TIME_DATE|TIME_SECONDS)); }
        
         MySqlCursorClose(Cursor); }
      else {
         Print("Cursor close failed; error = ",MySqlErrorDescription); }
////////////////////////////////////////////////////////////////////////////
   MySqlDisconnect(DB);
   Print("Disconnected biatch");
}

From this: https://www.mql5.com/en/articles/932

Thanks for your help Rene. Even though I still can't make heads or tails of that block you posted

Let the rebuilding commence

(After I get back from my parents for the weekend )