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)?
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.
I'm trying to use your examples under MT5 but I'm getting this error:
2014.12.15 15:44:16.387MySQL-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.
"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)
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 ...
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 flagint DB;
DB = MySqlConnect(Host, User, Password, Database, Port, Socket, ClientFlag); // Connection to the database
Allow the use of cookies to log in to the MQL5.com website.
Please enable the necessary setting in your browser, otherwise you will not be able to log in.
You are signed in. Two-factor authentication has been disabled. Set it up again and scan a new QR code, or continue with two-factor authentication disabled.
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 :)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:
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.
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]
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]
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.
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 ...
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.