Discussion of article "How to Access the MySQL Database from MQL5 (MQL4)" - page 5

New comment
 
Eugeniy Lugovoy:

Hello James, 

I'm working at Upwork only on one big project currently. So, have no much free time nowadays. What's the problem you have with libs? How can I help you?

Hello,

I figured it out in the end, but thanks for the library anyway.... Very helpful!!

James
 

Hello,

I am trying to connect Metatrader to Mysql via your files. 
After the download and the installation I changed the file "MyConnection" by setting the info of my DB.

Here goes the problem:

At first, everything worked just fine; the scripts were creating tables inside the DB. Then I made a little change in the code (just changed the table's name) and deleted the table from the database so that the script would create a new one from scratch without droping the previous one. And all I got was the creation of the columns but without any data. 
So I decided to use the old scripts again. Then I got nothing; not even a table was created. Since then I have a DB that stays empty. 

Any ideas of what could possibly have gone wrong?

Thanks in advance!

 
peg:

Hello,

I am trying to connect Metatrader to Mysql via your files. 
After the download and the installation I changed the file "MyConnection" by setting the info of my DB.

Here goes the problem:

At first, everything worked just fine; the scripts were creating tables inside the DB. Then I made a little change in the code (just changed the table's name) and deleted the table from the database so that the script would create a new one from scratch without droping the previous one. And all I got was the creation of the columns but without any data. 
So I decided to use the old scripts again. Then I got nothing; not even a table was created. Since then I have a DB that stays empty. 

Any ideas of what could possibly have gone wrong?

Thanks in advance!

Hello Peg,

To create table use SQL command CREATE TABLE  http://dev.mysql.com/doc/refman/5.7/en/create-table.html

For inserting data use SQL command INSERT INTO https://dev.mysql.com/doc/refman/5.6/en/insert.html 

To execute such commands use the function MySqlExecute.

Also you can provide your code and I would be able to figure out the problem. 

MySQL :: MySQL 5.7 Reference Manual :: 13.1.14 CREATE TABLE Syntax
  • dev.mysql.com
creates a table with the given name. You must have the privilege for the table. Rules for permissible table names are given in Section 9.2, “Schema Object Names”. By default, the table is created in the default database, using the storage engine. An error occurs if the table exists, if there is no default database, or if the database does not...
 
Eugeniy Lugovoy:

Hello Peg,

To create table use SQL command CREATE TABLE  http://dev.mysql.com/doc/refman/5.7/en/create-table.html

For inserting data use SQL command INSERT INTO https://dev.mysql.com/doc/refman/5.6/en/insert.html 

To execute such commands use the function MySqlExecute.

Also you can provide your code and I would be able to figure out the problem. 

Hello Eugeniy,

Thank you for the links.

At first I used your code to connect to my database (I was using mySql Server in case it is useful). And I could see the table. Then I changed the table's name at your MySQL-002 Script (as you can see below) and thought it seemed to work it wouldn't create my table. Now I'm using WampServer and  I get:

0 11:09:37.579 Script MySQL-002 EURUSD,H1: loaded successfully

0 11:09:37.730 MySQL-002 EURUSD,H1: initialized

0 11:09:37.730 MySQL-002 EURUSD,H1: MQLMySQL v2.0 Demo Copyright © 2014, FxCodex Laboratory

0 11:09:37.730 MySQL-002 EURUSD,H1: Host: , User: , Database: 

0 11:09:37.730 MySQL-002 EURUSD,H1: Connecting...

0 11:09:37.741 MySQL-002 EURUSD,H1: Connected! DBID#0

0 11:09:37.742 MySQL-002 EURUSD,H1: Table `table` cannot be created. Error: Aucune base n'a ?t? s?lectionn?e

0 11:09:37.742 MySQL-002 EURUSD,H1: Disconnected. Script done!

0 11:09:37.742 MySQL-002 EURUSD,H1: uninit reason 0

0 11:09:37.743 Script MySQL-002 EURUSD,H1: removed

//+------------------------------------------------------------------+
//|                                                    MySQL-002.mq4 |
//|                                   Copyright 2014, Eugene Lugovoy |
//|                                        http://www.fxcodexlab.com |
//| Table creation (DEMO)                                            |
//+------------------------------------------------------------------+
#property copyright "Copyright 2014, Eugene Lugovoy."
#property link      "http://www.fxcodexlab.com"
#property version   "1.00"
#property strict

#include <MQLMySQL.mqh>

string INI;
//+------------------------------------------------------------------+
//| Script program start function                                    |
//+------------------------------------------------------------------+
void OnStart()
{
 string Host, User, Password, Database, Socket; // database credentials
 int Port,ClientFlag;
 int DB; // database identifier
 
 Print (MySqlVersion());

 INI = TerminalPath()+"\\MQL4\\Scripts\\MyConnection.ini";
 
 // reading database credentials from INI file
 Host = ReadIni(INI, "MYSQL", "Host");
 User = ReadIni(INI, "MYSQL", "User");
 Password = ReadIni(INI, "MYSQL", "Password");
 Database = ReadIni(INI, "MYSQL", "Database");
 Port     = StrToInteger(ReadIni(INI, "MYSQL", "Port"));
 Socket   = ReadIni(INI, "MYSQL", "Socket");
 ClientFlag = StrToInteger(ReadIni(INI, "MYSQL", "ClientFlag"));  

 Print ("Host: ",Host, ", User: ", User, ", Database: ",Database);
 
 // open database connection
 Print ("Connecting...");
 
 DB = MySqlConnect(Host, User, Password, Database, Port, Socket, ClientFlag);
 
 if (DB == -1) { Print ("Connection failed! Error: "+MySqlErrorDescription); } else { Print ("Connected! DBID#",DB);}
 
 string Query;
 Query = "DROP TABLE IF EXISTS `test`";
 MySqlExecute(DB, Query);
 
 Query = "CREATE TABLE `test` (id int, code varchar(50), start_date datetime)";
 if (MySqlExecute(DB, Query))
    {
     Print ("Table `test` created.");
    }
 else
    {
     Print ("Table `test` cannot be created. Error: ", MySqlErrorDescription);
    }
 
 MySqlDisconnect(DB);
 Print ("Disconnected. Script done!");
}
//+------------------------------------------------------------------+
 
peg:

Hello Eugeniy,

Thank you for the links.

At first I used your code to connect to my database (I was using mySql Server in case it is useful). And I could see the table. Then I changed the table's name at your MySQL-002 Script (as you can see below) and thought it seemed to work it wouldn't create my table. Now I'm using WampServer and  I get:

0 11:09:37.579 Script MySQL-002 EURUSD,H1: loaded successfully

0 11:09:37.730 MySQL-002 EURUSD,H1: initialized

0 11:09:37.730 MySQL-002 EURUSD,H1: MQLMySQL v2.0 Demo Copyright © 2014, FxCodex Laboratory

0 11:09:37.730 MySQL-002 EURUSD,H1: Host: , User: , Database: 

0 11:09:37.730 MySQL-002 EURUSD,H1: Connecting...

0 11:09:37.741 MySQL-002 EURUSD,H1: Connected! DBID#0

0 11:09:37.742 MySQL-002 EURUSD,H1: Table `table` cannot be created. Error: Aucune base n'a ?t? s?lectionn?e

0 11:09:37.742 MySQL-002 EURUSD,H1: Disconnected. Script done!

0 11:09:37.742 MySQL-002 EURUSD,H1: uninit reason 0

0 11:09:37.743 Script MySQL-002 EURUSD,H1: removed

Based on error I see from your log:  0 11:09:37.742 MySQL-002 EURUSD,H1: Table `table` cannot be created.

you are uses name "table" which is keyword of mysql database and cannot be used as table name, try to change to for example to "table1" just to check is it works. 

or possible the database was not selected or was not set. you can execute command "USE database_name" right after connection, where the database_name is the name of your database.

for example if my database is "mysql":

DB = MySqlConnect(Host, User, Password, Database, Port, Socket, ClientFlag);

if (DB == -1) { Print ("Connection failed! Error: "+MySqlErrorDescription); } else { Print ("Connected! DBID#",DB);} 

 MySqlExecute(DB, "USE mysql");

 
Eugeniy Lugovoy:

Based on error I see from your log:  0 11:09:37.742 MySQL-002 EURUSD,H1: Table `table` cannot be created.

you are uses name "table" which is keyword of mysql database and cannot be used as table name, try to change to for example to "table1" just to check is it works. 

or possible the database was not selected or was not set. you can execute command "USE database_name" right after connection, where the database_name is the name of your database.

for example if my database is "mysql":

DB = MySqlConnect(Host, User, Password, Database, Port, Socket, ClientFlag);

if (DB == -1) { Print ("Connection failed! Error: "+MySqlErrorDescription); } else { Print ("Connected! DBID#",DB);} 

 MySqlExecute(DB, "USE mysql");

Well, I followed your suggestions for the tables's name and the MySqlExecute(DB, "USE mt4") command but I still get the error message: 

0 12:51:40.534 Script MySQL-002new EURUSD,M1: loaded successfully

0 12:51:40.549 MySQL-002new EURUSD,M1: initialized

0 12:51:40.549 MySQL-002new EURUSD,M1: MQLMySQL v2.0 Demo Copyright © 2014, FxCodex Laboratory

0 12:51:40.549 MySQL-002new EURUSD,M1: Host: , User: , Database: 

0 12:51:40.549 MySQL-002new EURUSD,M1: Connecting...

0 12:51:40.565 MySQL-002new EURUSD,M1: Connected! DBID#0

0 12:51:40.565 MySQL-002new EURUSD,M1: Table `test_table` cannot be created. Error: Aucune base n'a ?t? s?lectionn?e

0 12:51:40.565 MySQL-002new EURUSD,M1: Disconnected. Script done!

0 12:51:40.565 MySQL-002new EURUSD,M1: uninit reason 0

0 12:51:40.565 Script MySQL-002new EURUSD,M1: removed 

 

Hello Eugeniy Lugovoy,

Big Thanks for your useful project.

I'm trying to read a string field from a table that it is about 3,000 characters, I used "MySqlGetFieldAsString" like this:

 


 if (Cursor >= 0)

 {

     Rows = MySqlCursorRows(Cursor);

         for (int i=0; i<Rows; i++)

         if (MySqlCursorFetchRow(Cursor))

            {

               Get_Setting = MySqlGetFieldAsString(Cursor,  4);

            }

..... 

 

But the problem is Get_String returns me only 1025 characters,

I need to read that field completely.

Can you help me to solve this?

 

Regards 

 
FX. MDA7:

Hello Eugeniy Lugovoy,

Big Thanks for your useful project.

I'm trying to read a string field from a table that it is about 3,000 characters, I used "MySqlGetFieldAsString" like this:

 


 if (Cursor >= 0)

 {

     Rows = MySqlCursorRows(Cursor);

         for (int i=0; i<Rows; i++)

         if (MySqlCursorFetchRow(Cursor))

            {

               Get_Setting = MySqlGetFieldAsString(Cursor,  4);

            }

..... 

 

But the problem is Get_String returns me only 1025 characters,

I need to read that field completely.

Can you help me to solve this?

 

Regards 

Yes, the max lenght of character data I've set to 1024 chars. I can change this limit and rebuild DLL. By the way, what datatype you are uses for that column on db side?
 
peg:

Well, I followed your suggestions for the tables's name and the MySqlExecute(DB, "USE mt4") command but I still get the error message: 

0 12:51:40.534 Script MySQL-002new EURUSD,M1: loaded successfully

0 12:51:40.549 MySQL-002new EURUSD,M1: initialized

0 12:51:40.549 MySQL-002new EURUSD,M1: MQLMySQL v2.0 Demo Copyright © 2014, FxCodex Laboratory

0 12:51:40.549 MySQL-002new EURUSD,M1: Host: , User: , Database: 

0 12:51:40.549 MySQL-002new EURUSD,M1: Connecting...

0 12:51:40.565 MySQL-002new EURUSD,M1: Connected! DBID#0

0 12:51:40.565 MySQL-002new EURUSD,M1: Table `test_table` cannot be created. Error: Aucune base n'a ?t? s?lectionn?e

0 12:51:40.565 MySQL-002new EURUSD,M1: Disconnected. Script done!

0 12:51:40.565 MySQL-002new EURUSD,M1: uninit reason 0

0 12:51:40.565 Script MySQL-002new EURUSD,M1: removed 

why version "MQLMySQL v2.0" ? it should be 2.2
 

Excellent!

Using MQL5 to generate data for MQL4.

Everything worked beautifully except that I had to remove the single quotes around tablename on the query command 

Query = "SELECT Symbol,Composite,Pattern FROM summarytable";

works ok

12345678910111213
New comment
Reason: