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

New comment
 
What about caching_sha2_password support ? Is there any way to deal with this or should I just install mysql_native_password ?
 
Dmitri Custurov:
What about caching_sha2_password support ? Is there any way to deal with it or just install mysql_native_password ?

Hy so far native is only supported. does anyone really need sha2 support in this solution?

So your database server is not locally located or even under vpn?

 

Hi @Eugeniy Lugovoy and thanks for your hard work!

In the current version of your implementation, is there a way to retrieve the "LAST_INSERT_ID" after an INSERT operation (in a table with an AUTO_INCREMENT column)?

Thanks in advance and have a nice day :)
 
arsenico42:

Hi @Eugeniy Lugovoy and thanks for your hard work!

In the current version of your implementation, is there a way to retrieve the "LAST_INSERT_ID" after an INSERT operation (in a table with an AUTO_INCREMENT column)?

Thanks in advance and have a nice day :)


Hi arsenico42, you can try to make insert in one query and then select LAST_INSERT_ID in another one, but I cannot recommend this way, but I cannot recommend this way because LAST_INSERT_ID is function which returning global database variable, and another one insert (from another user, running concurrently) between your original insert and selection of LAST_INSERT_ID can affect LAST_INSERT_ID value.

This is not depended on library, it's how mysql/mariadb works.

But, I can advice you to create a function on database side to insert required values, select LAST_INSERT_ID and return this ID as function value. it might looks like:

CREATE FUNCTION `import_funds_add`(
        `p_file_name` varchar(64),
        `p_full_path` varchar(2000),
        `p_status` VARCHAR(10),
        `p_error` VARCHAR(2000)
)
RETURNS bigint(20)
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
     declare id bigint;
     INSERT INTO import_funds(`file_name`, `full_path`, `status`, `error`, `creation_date`) 
     VALUES (p_file_name, p_full_path, p_status, p_error, now());
     SELECT LAST_INSERT_ID() INTO id;
     RETURN(id);
END;;

And in MQL you have to open cursor for query:

qry="SELECT import_funds_add('bonuses.csv','c:\\broker imports\\XXX-FX','NEW','No errors') as last_id";

then get value as from usual cursor and close cursor.

So, instead of using those commands separately in MQL you can just combine them on DB side into function.

it's just an example from live system.

 
Carmine Marrone:

Hello elugovoy

 Awesome article and great libraries.

 I'm trying to use your examples under MT5 but I'm getting this error: 

2014.12.15 15:44:16.387 MySQL-001 'C:\Users\....\AppData\Roaming\MetaQuotes\Terminal\D0E8209F77C8CF37AD8BF550E51FF075\MQL5\libraries\MQLMySQL.dll' is not 64-bit version

Are you  running MT5 under 32-bit env?

 Could you pleas help me with it? 

 Thanks very much!

 Carmine Marrone. 

Hello Carmine, you can download x64 version from sources on github.

https://github.com/elugovoy/MQLMySQL-Project/tree/master/MQLMySQL/x64/Release

 

Hey @Eugeniy Lugovoy, first of all thanks for all the contribution to the community


I'm stucked here:

"Cannot load 'C:\Users\Admin\AppData\Roaming\MetaQuotes\Tester\D0E8209F77C8CF37AD8BF550E51FF075\Agent-127.0.0.1-3000\MQL5\libraries\MQLMySQL.dll'" [126]

"Cannot call 'ReadIni', '..\libraries\MQLMySQL.dll' is not loaded"
 
MARCOS DALCIN ALVES DINIZ:

Hey @Eugeniy Lugovoy, first of all thanks for all the contribution to the community


I'm stucked here:

"Cannot load 'C:\Users\Admin\AppData\Roaming\MetaQuotes\Tester\D0E8209F77C8CF37AD8BF550E51FF075\Agent-127.0.0.1-3000\MQL5\libraries\MQLMySQL.dll'" [126]

"Cannot call 'ReadIni', '..\libraries\MQLMySQL.dll' is not loaded"

Make sure you have MQLMySQL.dll & MQLMySQL.def files located in your folder "C:\Users\Admin\AppData\Roaming\MetaQuotes\Tester\D0E8209F77C8CF37AD8BF550E51FF075\Agent-127.0.0.1-3000\MQL5\libraries\"

As you can see if you are running MQL programs in Tester mode, the path to libs would be different (because of UAC)

 

multi-insert get error.

Query =         "INSERT INTO `test_table` (id, code, start_date) VALUES (1,\'EURUSD\',\'2014.01.01 00:00:01\');";
Query = Query + "INSERT INTO `test_table` (id, code, start_date) VALUES (2,\'EURJPY\',\'2014.01.02 00:02:00\');";
Query = Query + "INSERT INTO `test_table` (id, code, start_date) VALUES (3,\'USDJPY\',\'2014.01.03 03:00:00\');";
      

Error of multiple statements: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ...



 
does it support modify sql command?
 
Yu Zhang:

multi-insert get error.

Error of multiple statements: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ...



Make sure you have set multi statement client flag on database connection opening.

int ClientFlag = CLIENT_MULTI_STATEMENTS; // Setting the multi-statements flag
int DB; 

DB = MySqlConnect(Host, User, Password, Database, Port, Socket, ClientFlag); // Connection to the database
1...1718192021222324252627282930
New comment