Attach MySQL to MQ4 - page 5

New comment
 
sergeev:

Yes, everything works perfectly, but so far I have only made it for MQL5

an example of how it works from above in my post


Can you send me a piece of code regarding mysql_fetch_row, it's not clear from your example how to fetch cells when the result consists of several rows and columns.
 
Graff:

Can you send a piece of code concerning mysql_fetch_row, it is not clear from your example how to get cells when the result consists of several rows and columns.

similarly.

1. take mysql_num_rows, mysql_num_fields

2. get a pointer to an array of pointers to the next string mysql_fetch_row, and a pointer to field lengths mysql_fetch_lengths

3. we pull these lengths from the lengths array into our own array (via memcpy).

4. From a pointer to an array of field pointers, pull this array of field pointers (since we know mysql_num_fields).

5. Knowing lengths of the fields (array of lengths obtained from fetch_lengths) and pointers to the fields themselves we are pulling data into uchar array of each field with memcpy

6. go back to clause 2.

 
HIDDEN:
I've twisted and turned it both ways, nothing worked for me on 4. Sometimes the terminal even crashes completely.

Kirill, everything works in MQL4. I tested it on the 409 build.

Here is an example to get a string

#import "libmysql.dll"
    int mysql_get_client_info(); // функция вернула char*
#import "msvcrt.dll"
    int strcpy(string strDestination, int strSource); // копируем NULL-строку из source в байтовый массив 
#import

void start()
{
    int ptr; string data="123456789"; 
    ptr=mysql_get_client_info(); // получили указатель на строку
    strcpy(data, ptr); // скопировали его в массив
    Print("client_info="+data); // вывели на печать
}

Result
!sql USDCHF,M30: client_info=6.0.0

the same for an integer array
replace by

    int strcpy(int &strDestination[], int strSource); // копируем NULL-строку из source в байтовый массив
 
sergeev:

Kirill, everything works in MQL4. I tested it on build 409 .

here is an example to get the string

result
!sql USDCHF,M30: client_info=6.0.0

the same if you do it for an array
replace by

I have the same build, but my terminal crashes.... I have to try it on different terminals from different brokerage companies.

Although it may depend on the system, I'm testing with win7 x64.

 

HIDDEN:


xp/32

Then dig around for calls and contact Service Desk directly about the bug.

Maybe they will advise what to do.

 
sergeev:

similarly.

1. take mysql_num_rows, mysql_num_fields

2. get a pointer to an array of pointers to the next string mysql_fetch_row, and a pointer to field lengths mysql_fetch_lengths

3. we pull these lengths from the lengths array into our own array (via memcpy).

4. From a pointer to an array of field pointers, pull this array of field pointers (since we know mysql_num_fields).

5. Knowing lengths of the fields (array of lengths obtained from fetch_lengths) and pointers to the fields themselves we are pulling data into uchar array of each field with memcpy

6. Back to step 2.



Almost there. At this stage, we can only get the first cell of each row. memcpy for some reason only copies the first element into my arrays. Killed an entire evening. What am I doing wrong?

Source, dump, log in appendix.

Files:
mysql_mql5.zip  4 kb
 
Graff:


Almost got it. At this stage, I can only get the first cell of each row. memcpy copies only the first element into my arrays for some reason. Killed an entire evening. What am I doing wrong?

Source code, dump, log in appendix.


there are comments

1. There is no need to use UNICODE2ANSI functions at all. You have CharArrayToStr and ShortArrayToStr for this purpose.

2. I haven't tried to use string in the strcpy(string strDestination, int strSource); function, everything was done through arrays. If you know what you're copying from UTF encoding, it's better to store data in a short array.

3. Here you have a technical error (which is why everything goes wrong)
memcpy(alens,lens,num_fields);

It's not a single-byte array like uchar. I need memcpy(alens,lens,num_fields*sizeof(int));

 
sergeev:

there are comments

1. There is no need to use the UNICODE2ANSI functions at all. You have CharArrayToStr and ShortArrayToStr for this purpose.

2. I haven't tried to use string in the strcpy(string strDestination, int strSource); function, everything is done through arrays. If you know what you're copying from UTF encoding, it's better to store data in a short array.

3. this is where you have a technical error (which is why everything goes wrong)
memcpy(alens,lens,num_fields);

It's not a single-byte array like uchar. you need memcpy(alens,lens,num_fields*sizeof(int));



Thank you! It's working. Are you planning to release a class or library to work with the muscle?
 
Graff:

Thank you! It's working. Any plans to release a class or library to work with musl?

If there's a need, I can. I just don't need to explain what's already clear...

There are only 50 functions in this libmysql...

and most of them are purely service functions. Of the dozens you need.

--------

How do you actually see this class or library? What functions should it have?

Just make duplicates of api functions, or put some sets of actions into one function?

 
sergeev:

If there's a need, I can. I just don't need to explain what's already clear...

There are only 50 functions in this libmysql...

most of them are purely service functions. ten of them are essential for work.

--------

In general, how do you see this class or library? What functions should it have?



I believe that simply describing the functions of libmysql.dll is not enough. The class must allow the user to work with the database simply and effortlessly.

Example 1: DB connection. To connect to the database via my feeble class, the class constructor must be called, although a whole series of actions, not always necessary or even required, takes place inside the class.

CMYSQL2::CMYSQL2(const string host="localhost",const string user="root",const string password="",const string database="database",const uint port=3306)
  {
   uchar _host[],_user[],_password[],_database[],_socket[];
   StringToCharArray(host,_host);
   StringToCharArray(user,_user);
   StringToCharArray(password,_password);
   StringToCharArray(database,_database);
// Connecting
   mysql=mysql_init(NULL);
   uint conn=mysql_real_connect(mysql,_host,_user,_password,_database,port,_socket,0);
   if(mysql==NULL || conn==NULL || mysql!=conn){ Print(__FUNCTION__,"-> MySQL connetion failure.");}
  }

Example 2: getting a multi-line and multi-column result. All the user needs is to feed the query and an array(structure) to write the result.

//+------------------------------------------------------------------+
//|  Returns string array as sql_results struct param and rows count
//+------------------------------------------------------------------+
uint CMYSQL2::GetArray(string query,sql_results &out[])
  {
   Query2(query);
   StoreResult();
   uint rows=GetNumRows();
   uint fields=GetNumFields();
   ArrayResize(out,rows);

   for(uint r=0;r<rows;r++)
     {
      ArrayResize(out[r].value,fields);
      string fr_res=mysql_fetch_row(result);
      
      for(uint f=0;f<fields;f++)
        {
         out[r].value[f]=get_cell_u(fr_res,f);//Print("3,",f,",",fields);
        }
     }
   FreeLastResult();
   return(rows);
  }

^ this is old code, just to get the idea.

There could also be many examples on how to add information to the database.

If you just use a set of functions with no checks, it's very easy to get an Access violation read to 0x00000000 in 'libmysql.dll' and crash the whole system.

Ready to consider the possibility of creating an open class to work with the muscle.

1234567
New comment