How to read the result ofmysql Fetch_row function in MML4 - MQL4 and MetaTrader 4

Debugger 2011.05.15 14:16 #11

I did not work with MySql, but I did with MS SQL

--- 2011.05.15 15:21 #12

how to read the result of mysql_fetch_row function in MQL4 ?

Nefedov Kirill 2011.05.15 18:32 #13

sergeev:

how to read the result of mysql_fetch_row function in MQL4 ?

I am currently developing a script to work with a MySQL database to read, insert, delete and modify.

The script is a big one, but the essence of working with the database will be understandable I think from one function.

However, in this example ID of record from table is returned, but it will not be a problem to remake, I remember and text and numbers returned from database, everything worked.

//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
#import "libmysql.dll"
int mysql_init(int db);
int mysql_errno(int TMYSQL);
int mysql_real_connect(int TMYSQL, string host, string user, string password, string DB,int port,int socket,int clientflag);
int mysql_real_query(int TMSQL, string query, int length);
int mysql_store_result(int TMSQL); 
string mysql_fetch_row(int result); 
int mysql_num_rows(int result); 
void mysql_free_result(int result);
void mysql_close(int TMSQL);
int mysql_insert_id(int result);
#import

//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
int mysql; string query = ""; int myerr;

int CompanyID = 0;
int SymbolID = 0;
int LastRecordQuotesID = 0;

//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
bool connect() { 
   mysql = mysql_init(mysql); 
   if (mysql!=0) Print("allocated"); 
   string host="IP adress";          // меняем на своё
   string user="Login";              // меняем на своё
   string password="Password";       // меняем на своё
   string DB="db_base";              // меняем на своё
   int clientflag=0; 
   int port=3306; 
   string socket=""; 
   int res=mysql_real_connect(mysql,host,user,password,DB,port,socket,clientflag); 
   int err=GetLastError(); 
   if (res==mysql){
      Print("connected"); 
      return(true);
   } else {
      Print("error=",mysql," ",mysql_errno(mysql)," ");
      return(false);
   }
return(false);
} 

//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
int start(){
   if(!connect()) return(0);
//----
   CompanyID = CompanyID(AccountCompany(), AccountServer());
//----
   mysql_close(mysql);
   return(0);
  }

//+--------------------------------------------------------------------------------------------+
//| Функция поиска и вставки названия компании и сервера для связи.                            |
//+--------------------------------------------------------------------------------------------+
//| Переменные:                                                                                |
//|            string AccCompany  - AccountCompany()                                           |
//|            string AccServer   - AccountServer()                                            |
//+--------------------------------------------------------------------------------------------+
int CompanyID(string AccCompany = "", string AccServer = ""){
   int count_records, result;
   string row = "";
   
   query = StringConcatenate("SELECT id FROM Company WHERE Company = \'",AccCompany,"\' AND `Server` = \'",AccServer,"\'");
   mysql_real_query(mysql,query,CountLength(query));
   myerr = mysql_errno(mysql);
   if(myerr > 0) Print("error=",myerr);
   result = mysql_store_result(mysql);
   count_records = Number_Of_Rows(result);
   
   if (count_records > 0){
      row = mysql_fetch_row(result);
      mysql_free_result(result);
      return( StrToInteger(StringSubstr(row, 8, StringLen(row)-1) ) );
   } else {
      query = StringConcatenate( "INSERT INTO `Company` (`Company`, `Server`) VALUES (\'",AccCompany ,"\', \'",AccServer,"\')");
      mysql_real_query(mysql,query,CountLength(query));
      myerr = mysql_errno(mysql);
      if(myerr > 0) Print("CompanyID(): error=",myerr);
      mysql_free_result(result);
      return(mysql_insert_id(mysql));
   }
}

//+--------------------------------------------------------------------------------------------+
//| Вспомогательная Функция подсчёта колличества символов в передаваемом запросе.              |
//+--------------------------------------------------------------------------------------------+
//| Переменные:                                                                                |
//|            string query  - Текстовая страка                                                |
//+--------------------------------------------------------------------------------------------+
int CountLength(string query){
   return(StringLen(query));
}

//+--------------------------------------------------------------------------------------------+
//| Вспомогательная Функция подсчёта колличества результатов из базы данных                    |
//+--------------------------------------------------------------------------------------------+
//| Переменные:                                                                                |
//|            int result  - Результат запроса                                                 |
//+--------------------------------------------------------------------------------------------+
int Number_Of_Rows(int result){
   return(mysql_num_rows(result));
}

MySQL Read from Database Chatter about the MT5 Read from MySQL

--- 2011.05.15 19:36 #14

did a little investigative experiment

I have created a table with two fields
INT id; TEXT user

there are two rows in the table
id user
1 aaa
2 bbb

1. When querying "SELECT id FROM table", the fetch_row function returns (address)(address) 1 (address)(address) 2
2. The "SELECT user FROM table" query, the fetch_row function returns (address) aaa (address) bbb

Generally, this can be handled, the only pity is that it returns in string

If I query two fields "SELECT id, user FROM table", the function returns (address) 1 (address) (address) (address) 2
4. If we swap the "SELECT user, id FROM table" fields, the function returns (address) aaa (address) bbb

it turns out that only the first field from the string is clearly visible and another 4-byte address is added.

Printing tables and SQL Errors, bugs, questions Any questions from newcomers

Evgeny Potapov 2011.05.16 03:13 #15

sergeev:

did a little investigative experiment

created a table with two fields
INT id; TEXT user

the table has two rows
id user
1 aaa
2 bbb

1. When querying "SELECT id FROM table" function fetch_row returns (address)(address) 1 (address)(address) 2
2. When requesting "SELECT user FROM table" function fetch_row returns (address) aaa (address) bbb

Generally it can be handled, but return is in string

3. if the function returns two fields "SELECT id, user FROM table", it returns (address) 1 (address) (address) 2
4. If we swap the "SELECT user, id FROM table" fields, the function returns (address) aaa (address) bbb

it turns out that only the first field from the string is explicitly seen and another 4-byte address is added.

How do you get this into the programme afterwards?

I'm basically going to write floating point numbers and read them.

Evgeny Potapov 2011.05.16 03:24 #16

HIDDEN:

I am currently developing a script to work with a MySQL database to read, insert, delete and modify.

The script is a big one, but the essence of working with the database will be understandable I think from one function.

True, this example returns record ID from the table, but to remake no problem, I remember and text and numbers returned from the database, all worked.

This is an interesting example, but it doesn't show how to read numerical data.

Basically, I want to use a base with a table of three columns for just two actions:

1) Read the first two columns (index and the first numeric column)

Then choose the most relevant number from the numeric column within the MQ4 program

2) Read from the index of that number the number in the second column.

Which functions should I use for this? And do they work normally in the specified DLL?

Absolute courses Can I please get Import from an Excel/CSV-file

--- 2011.05.16 06:50 #17

Eugene1:

This is an interesting example, but it doesn't show how to read numerical data.

Have you written one line of code to see what is returned and how?

HIDDEN already gave a complete solution to read one field from the database. did you run it ?

Slava 2011.05.16 08:03 #18

Graff:

If you can help write ansi to unicode converter for MKL5, the code of which is given in the previous post. I will send you working code for MKL4, it will also help you when writing class for MKL5.

MQL5 has functions to convert unicode. See /en/docs/convert/chararraytostring and /en/docs/convert/stringtochararray

Declare uchar arrays instead of strings in function import.

--- 2011.05.16 08:11 #19

stringo:

In import functions, do not declare strings, but arrays of type uchar

The problem is, unfortunately, that these strings are returned as a result of a function, not as one of its & parameters.

mysql_fetch_row returns a pointer to an array of strings - char**

--------

does anyone know if there are any low-level kernel functions that can return a char ** string from a char array ?
(like strcpy, strcat...)

and similarly from an int* array - some lib function to get its specific element ?
even if the function itself isn't designed for this operation directly :)

Discussion on "How to Question on DLL ...... Any questions from newcomers

Nefedov Kirill 2011.05.16 09:18 #20

Full code implementation....

#import "libmysql.dll"
int mysql_init(int db);
int mysql_errno(int TMYSQL);
int mysql_real_connect(int TMYSQL, string host, string user, string password, string DB,int port,int socket,int clientflag);
int mysql_real_query(int TMSQL, string query, int length);
int mysql_store_result(int TMSQL); 
string mysql_fetch_row(int result); 
int mysql_num_rows(int result); 
void mysql_free_result(int result);
void mysql_close(int TMSQL);
int mysql_insert_id(int result);
#import

#include <WinUser32.mqh>

int mysql; string query = ""; int myerr;
//+------------------------------------------------------------------+
//| script program start function                                    |
//+------------------------------------------------------------------+
int start(){
   if(!connect()) return(0);
//----
   Print("Возвращаем ID из MySQL: ",Read_ID(10));
   Print("Возвращаем Integer из MySQL: ",Read_Integer(1));
   Print("Возвращаем Double из MySQL: ",DoubleToStr(Read_Double(1),8));
   Print("Возвращаем Datetime из MySQL: ",Read_Datetime(1)," (",StrToTime(Read_Datetime(1)),")");
   Print("Возвращаем Text из MySQL: ",Read_Text(1));
//----
   mysql_close(mysql);
   return(0);
  }
//+------------------------------------------------------------------+
bool connect() { 
   mysql = mysql_init(mysql); 
   if (mysql!=0) Print("allocated"); 
   string host="localhost"; 
   string user="root"; 
   string password=""; 
   string DB="mql4"; 
   int clientflag=0; 
   int port=3306; 
   string socket=""; 
   int res=mysql_real_connect(mysql,host,user,password,DB,port,socket,clientflag); 
   int err=GetLastError(); 
   if (res==mysql){
      Print("connected"); 
      return(true);
   } else {
      Print("error=",mysql," ",mysql_errno(mysql)," ");
      return(false);
   }
return(false);
} 

//+--------------------------------------------------------------------------------------------+
//| Читаем ID из MySQL                                                                         |
//+--------------------------------------------------------------------------------------------+
int Read_ID(int number = 0){
   int count_records, result;
   string row = "";
   
   query = StringConcatenate("SELECT id FROM MQL4_TEST WHERE `integer` = ", number);
   
   mysql_real_query(mysql,query,CountLength(query));
   myerr = mysql_errno(mysql);
   if(myerr > 0) Print("error=",myerr);
   result = mysql_store_result(mysql);
   count_records = Number_Of_Rows(result);
   
   if (count_records > 0){
      row = mysql_fetch_row(result);
      mysql_free_result(result);
      return( StrToInteger(StringSubstr(row, 8, StringLen(row)-1) ) );
   }
}

//+--------------------------------------------------------------------------------------------+
//| Читаем Integer из MySQL                                                                         |
//+--------------------------------------------------------------------------------------------+
int Read_Integer(int id = 0){
   int count_records, result;
   string row = "";
   
   query = StringConcatenate("SELECT `integer` FROM MQL4_TEST WHERE id = ", id);
   mysql_real_query(mysql,query,CountLength(query));
   myerr = mysql_errno(mysql);
   if(myerr > 0) Print("error=",myerr);
   result = mysql_store_result(mysql);
   count_records = Number_Of_Rows(result);
   
   if (count_records > 0){
      row = mysql_fetch_row(result);
      mysql_free_result(result);
      return( StrToInteger(StringSubstr(row, 8, StringLen(row)-1) ) );
   }
}

//+--------------------------------------------------------------------------------------------+
//| Читаем Double из MySQL                                                                         |
//+--------------------------------------------------------------------------------------------+
double Read_Double(int id = 0){
   int count_records, result;
   string row = "";
   
   query = StringConcatenate("SELECT `double` FROM MQL4_TEST WHERE id = ", id);
   mysql_real_query(mysql,query,CountLength(query));
   myerr = mysql_errno(mysql);
   if(myerr > 0) Print("error=",myerr);
   result = mysql_store_result(mysql);
   count_records = Number_Of_Rows(result);
   
   if (count_records > 0){
      row = mysql_fetch_row(result);
      mysql_free_result(result);
      return( StrToDouble(StringSubstr(row, 8, StringLen(row)-1) ) );
   }
}

//+--------------------------------------------------------------------------------------------+
//| Читаем Datetime из MySQL                                                                         |
//+--------------------------------------------------------------------------------------------+
string Read_Datetime(int id = 0){
   int count_records, result;
   string row = "";
   
   query = StringConcatenate("SELECT `datetame` FROM MQL4_TEST WHERE id = ", id);
   mysql_real_query(mysql,query,CountLength(query));
   myerr = mysql_errno(mysql);
   if(myerr > 0) Print("error=",myerr);
   result = mysql_store_result(mysql);
   count_records = Number_Of_Rows(result);
   
   if (count_records > 0){
      row = mysql_fetch_row(result);
      mysql_free_result(result);
      return( StringSubstr(row, 8, StringLen(row)-1) );
   }
}

//+--------------------------------------------------------------------------------------------+
//| Читаем Text из MySQL                                                                         |
//+--------------------------------------------------------------------------------------------+
string Read_Text(int id = 0){
   int count_records, result;
   string row = "";
   
   query = StringConcatenate("SELECT text FROM MQL4_TEST WHERE id = ", id);
   mysql_real_query(mysql,query,CountLength(query));
   myerr = mysql_errno(mysql);
   if(myerr > 0) Print("error=",myerr);
   result = mysql_store_result(mysql);
   count_records = Number_Of_Rows(result);
   
   if (count_records > 0){
      row = mysql_fetch_row(result);
      mysql_free_result(result);
      return( StringSubstr(row, 8, StringLen(row)-1) );
   }
}
//+--------------------------------------------------------------------------------------------+
//| Вспомогательная Функция подсчёта колличества результатов из базы данных                    |
//+--------------------------------------------------------------------------------------------+
//| Переменные:                                                                                |
//|            int result  - Результат запроса                                                 |
//+--------------------------------------------------------------------------------------------+
int Number_Of_Rows(int result){
   return(mysql_num_rows(result));
}

//+--------------------------------------------------------------------------------------------+
//| Вспомогательная Функция подсчёта колличества символов в передаваемом запросе.              |
//+--------------------------------------------------------------------------------------------+
//| Переменные:                                                                                |
//|            string query  - Текстовая страка                                                |
//+--------------------------------------------------------------------------------------------+
int CountLength(string query){
   return(StringLen(query));
}

SQL dump of the database

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `MQL4_TEST`
-- ----------------------------
DROP TABLE IF EXISTS `MQL4_TEST`;
CREATE TABLE `MQL4_TEST` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `integer` int(11) DEFAULT NULL,
  `double` double(11,8) DEFAULT NULL,
  `datetame` datetime DEFAULT NULL,
  `text` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=cp1251;

-- ----------------------------
-- Records of MQL4_TEST
-- ----------------------------
INSERT INTO `MQL4_TEST` VALUES ('1', '10', '20.12345678', '2011-05-16 10:24:43', 'Тестирование MySQL и MQL4');

Working result

Attach MySQL to MQ4 - page 2