Reading data from MYSQL database through Lazarus DLL (stumped and desperate).
Putting it into the base folder worked! Thanks so much. I don't really understand why placing it in System32 and/or in experts/libraries didn't work but thank god I'm past that problem.
Now I have another issue.....
I am sending the following query through my DLL : "SELECT Slope, STD FROM `data`.`10` WHERE pTime>=1104537600 ORDER BY pTime limit 10;"
That query should return the following data:
+-----------+----------+ | Slope | STD | +-----------+----------+ | 3.454545 | 0.044211 | | 0.727273 | 0.822676 | | 7.454545 | 1.019421 | | 9.272727 | 0.491299 | | 12.909091 | 0.237204 | | 13.636364 | 0.187217 | | 16.181818 | 0.253185 | | 16.000000 | 0.988212 | | 29.636364 | 1.214384 | | 30.727273 | 0.052578 | +-----------+----------+
I am sending this query each time the start() function of my EA is executed (in backtest mode).
The proper data is returned 99.99% of the time. Here is what it looks like in MT4's log:
2011.04.22 00:09:08 2005.01.05 07:20 ~~SQL EURUSD,M5: --------------------------------------- 2011.04.22 00:09:08 2005.01.05 07:20 ~~SQL EURUSD,M5: Slope: 30.72727300 STD: 0.05257800 2011.04.22 00:09:08 2005.01.05 07:20 ~~SQL EURUSD,M5: Slope: 29.63636400 STD: 1.21438400 2011.04.22 00:09:08 2005.01.05 07:20 ~~SQL EURUSD,M5: Slope: 16.00000000 STD: 0.98821200 2011.04.22 00:09:08 2005.01.05 07:20 ~~SQL EURUSD,M5: Slope: 16.18181800 STD: 0.25318500 2011.04.22 00:09:08 2005.01.05 07:20 ~~SQL EURUSD,M5: Slope: 13.63636400 STD: 0.18721700 2011.04.22 00:09:08 2005.01.05 07:20 ~~SQL EURUSD,M5: Slope: 12.90909100 STD: 0.23720400 2011.04.22 00:09:08 2005.01.05 07:20 ~~SQL EURUSD,M5: Slope: 9.27272700 STD: 0.49129900 2011.04.22 00:09:08 2005.01.05 07:20 ~~SQL EURUSD,M5: Slope: 7.45454500 STD: 1.01942100 2011.04.22 00:09:08 2005.01.05 07:20 ~~SQL EURUSD,M5: Slope: 0.72727300 STD: 0.82267600 2011.04.22 00:09:08 2005.01.05 07:20 ~~SQL EURUSD,M5: Slope: 3.45454500 STD: 0.04421100 2011.04.22 00:09:08 2005.01.05 07:15 ~~SQL EURUSD,M5: ---------------------------------------
However a small percent of time the query seems to malfunction and returns a (seemingly randomly) truncated list such as this:
2011.04.22 00:09:08 2005.01.03 19:20 ~~SQL EURUSD,M5: --------------------------------------- 2011.04.22 00:09:08 2005.01.03 19:20 ~~SQL EURUSD,M5: Slope: 30.72727300 STD: 0.05257800 2011.04.22 00:09:08 2005.01.03 19:20 ~~SQL EURUSD,M5: Slope: 29.63636400 STD: 1.21438400 2011.04.22 00:09:04 ~~SQL: loaded successfully
Other times the list is too large and contains duplicate values:
2011.04.22 00:09:08 2005.01.05 07:15 ~~SQL EURUSD,M5: --------------------------------------- 2011.04.22 00:09:08 2005.01.05 07:15 ~~SQL EURUSD,M5: Slope: 30.72727300 STD: 0.05257800 2011.04.22 00:09:08 2005.01.05 07:15 ~~SQL EURUSD,M5: Slope: 29.63636400 STD: 1.21438400 2011.04.22 00:09:08 2005.01.05 07:15 ~~SQL EURUSD,M5: Slope: 16.00000000 STD: 0.98821200 2011.04.22 00:09:08 2005.01.05 07:15 ~~SQL EURUSD,M5: Slope: 16.18181800 STD: 0.25318500 2011.04.22 00:09:08 2005.01.05 07:15 ~~SQL EURUSD,M5: Slope: 13.63636400 STD: 0.18721700 2011.04.22 00:09:08 2005.01.05 07:15 ~~SQL EURUSD,M5: Slope: 12.90909100 STD: 0.23720400 2011.04.22 00:09:08 2005.01.05 07:15 ~~SQL EURUSD,M5: Slope: 9.27272700 STD: 0.49129900 2011.04.22 00:09:08 2005.01.03 19:25 ~~SQL EURUSD,M5: Slope: 12.90909100 STD: 0.23720400 2011.04.22 00:09:08 2005.01.03 19:25 ~~SQL EURUSD,M5: Slope: 9.27272700 STD: 0.49129900 2011.04.22 00:09:08 2005.01.03 19:25 ~~SQL EURUSD,M5: Slope: 7.45454500 STD: 1.01942100 2011.04.22 00:09:08 2005.01.03 19:25 ~~SQL EURUSD,M5: Slope: 0.72727300 STD: 0.82267600 2011.04.22 00:09:08 2005.01.03 19:25 ~~SQL EURUSD,M5: Slope: 3.45454500 STD: 0.04421100 2011.04.22 00:09:08 2005.01.03 19:20 ~~SQL EURUSD,M5: ---------------------------------------
What is even weirder is that this problem does not seem to occur in output to DebugView.exe via OutputDebugString:
Executing query : SELECT Slope, STD FROM `data`.`10` WHERE pTime>= 1104537600 ORDER BY pTime limit 10; Number of records returned : 10 Number of fields per record : 2 3.454545 0.044211 0.727273 0.822676 7.454545 1.019421 9.272727 0.491299 12.909091 0.237204 13.636364 0.187217 16.181818 0.253185 16 0.988212 29.636364 1.214384 30.727273 0.052578 Freeing memory occupied by result set...
I am pretty stuck with this one...
Here is my DLL code (different from the one in the first post):
{$CALLING STDCALL}
library S_query;
{$mode objfpc}{$H+}
uses
cmem,
Classes,
Windows,
SysUtils,
strutils,
process,
profs_win32exceptiontrap,
mysql50;
var
sock: PMYSQL;
qmysql: st_mysql;
type
VArray = array[0..MaxLongInt div SizeOf(double) - 1, 0..1] of double;
PArray = ^VArray;
procedure SQL_Query(QRY: PChar; output: PArray); stdcall;
var
rowbuf: MYSQL_ROW;
recbuf: PMYSQL_RES;
i: integer;
begin
OutputDebugString(PChar(('Executing query : ' + QRY + '...')));
if (mysql_query(sock, QRY) < 0) then
begin
OutputDebugString(PChar(' Query failed '));
OutputDebugString(PChar(' ' + mysql_error(sock)));
Halt(1);
end;
recbuf := mysql_store_result(sock);
if RecBuf = nil then
begin
OutputDebugString(PChar(('Query returned nil result.')));
mysql_close(sock);
halt(1);
end;
OutputDebugString(PChar(('Number of records returned : ' + IntToStr(
mysql_num_rows(recbuf)))));
OutputDebugString(PChar(('Number of fields per record : ' + IntToStr(
mysql_num_fields(recbuf)))));
i := 0;
rowbuf := mysql_fetch_row(recbuf);
while (rowbuf <> nil) do
begin
output^[i, 0] := StrToFloatDef(rowbuf[0], -666);
output^[i, 1] := StrToFloatDef(rowbuf[1], -666);
OutputDebugString(PChar(FloatToStr(output^[i, 0]) + ' ' +
FloatToStr(output^[i, 1])));
rowbuf := mysql_fetch_row(recbuf);
i := i + 1;
end;
OutputDebugString(PChar(('Freeing memory occupied by result set...')));
mysql_free_result(recbuf);
end;
procedure InitSQL; stdcall;
begin
OutputDebugString(PChar('Connecting to MySQL...'));
mysql_init(PMySQL(@qmysql));
sock :=
mysql_real_connect(PMysql(@qmysql), '.', 'root', 'pass8900',
'data', 3306, 'mysql', 0);
if sock = nil then
begin
OutputDebugString(PChar(' Couldn''t connect to MySQL.'));
OutputDebugString(PChar(mysql_error(@qmysql)));
halt(1);
end;
end;
procedure DeInitSQL; stdcall;
begin
OutputDebugString(PChar(('Closing connection with MySQL.')));
mysql_close(sock);
end;
exports
SQL_Query,
InitSQL,
DeInitSQL;
begin
end.
And my MQL4 code (also different from the first post):
#import "S_query.dll" void SQL_Query(string query, double& mem[][2]); void InitSQL(); void DeInitSQL(); #import extern int limit = 10; string basequery = "SELECT Slope, STD FROM `data`.`10` WHERE pTime>= "; string query = ""; double mem[][2]; bool go = true; int init() { ArrayResize(mem,limit); ArrayInitialize(mem,-1); InitSQL(); return(0); } //+------------------------------------------------------------------+ //| expert start function | //+------------------------------------------------------------------+ int start() { query=StringConcatenate(basequery,"1104537600"," ORDER BY pTime limit ", limit); SQL_Query(query, mem); for (int i = 0; i<limit; i++) { Print("Slope: "+mem[i][0]+" STD: "+mem[i][1]); } Print ("---------------------------------------"); return(0); } //+------------------------------------------------------------------+ int deinit() { DeInitSQL(); return(0); }
Additionally I am having a problem where the DLL is not unloaded when the backtest completes. I am forced to exit MetaTrader whenever I recompile the DLL because otherwise the compiler claims that the file is in use. Could this somehow be the cause? Or maybe some sort of threading problem occurs within MySQL? Perhaps MT4's log can't keep up with all of the output and gets jumbled?... Seems unlikely but I am stumped!
A bit more help would be extremely appreciated!
Edit: I think this really might be a problem with the log buffer in MT4. I made a separate post about this: https://www.mql5.com/en/forum/133123
I don't really understand why placing it in System32 and/or in experts/libraries didn't work but thank god I'm past that problem.
This is a nasty old bug in MT4, I don't know why MetaQuotes refuse to fix it, it would be easy for them. It only happens in the backtester, each run in the backtester leaves the dll reference count incremented once too often. Loading and unloading it from a normal chart it will work.
You could try to forcefully unload it in your deinit()
if (IsTesting()) {
dll_handle = GetModuleHandle("yourdll.dll");
FreeLibrary(dll_handle);
}
This would decrement the reference count by one (and unload if it reaches zero). I'm not sure what happens in the optimizer, maybe the code has to be a bit more sophisticated using also IsOptimization() and UninitializeReason(). Or you could make a separate script for it that you run manually if needed. Don't try to do this from inside the dll, this would crash it.
I will think about the other problem later, I don't have much time at the moment.
I've got a new MT4/MYSQL related problem..
I can't seem to execute CALL statements (for stored procedures). I tried to do CALL SELECT(1) and I get an error message from MYSQL: " PROCEDURE data.TEST can't return a result set in the given context."
By googling the problem I found this website:
http://blog.ulf-wendel.de/?p=229
It seems to say that I need to use the mysql_i* functions that became available in mysql 5.5.
The problem is that lazarus seems to only support mysql 5.0.
Is there a way around this problem other than sending a bunch of plain-text queries?
p.s thanks for the dll unload code :)
- Free trading apps
- Over 8,000 signals for copying
- Economic news for exploring financial markets
You agree to website policy and terms of use
7bit solved my initial problem. Please scroll down to my second post for my current issue.
I am attempting to query my MySQL database with a DLL that I made in Lazarus, and return the result to MQL4. I made a standalone 'program' in lazarus that queries the database without any problems; but when I converted it to a MQL4-compatible library it ceased to work.
In fact MT4 tells me that it "cannot load library" (error 126). And yes, I put it in the MT4/experts/libraries folder.
From what I understand, this error could be caused by a missing dependency from this DLL. It uses the unit "mysql50", so I guess it is dependent on libmysql.dll (which is in the experts/libraries folder as well). I used the version of libmysql.dll that the lazarus website recommends: the 1484Kb version (I am using it to access a MYSQL 5.5 database).
The following is my Lazarus code:
library S_query; {$mode objfpc}{$H+} uses cmem, Classes, Windows, SysUtils, strutils, process, profs_win32exceptiontrap, mysql50; function SQL_Query(): PChar; stdcall; var sock: PMYSQL; qmysql: st_mysql; rowbuf: MYSQL_ROW; dummy: string; recbuf: PMYSQL_RES; QRY: PChar; DataBase: PChar; begin DataBase := 'data'; QRY := 'select Slope from `data`.`10` where pTime > 1291079000 limit 1000'; if paramcount = 1 then begin Dummy := ParamStr(1) + #0; DataBase := @Dummy[1]; end; OutputDebugString(PChar('Connecting to MySQL...')); mysql_init(PMySQL(@qmysql)); sock := mysql_real_connect(PMysql(@qmysql), '.', 'root', 'pass8900', 'data', 3306, 'mysql', 0); if sock = nil then begin OutputDebugString(PChar(' Couldn''t connect to MySQL.')); OutputDebugString(PChar(mysql_error(@qmysql))); // halt(1); end; OutputDebugString(PChar(('Done.'))); OutputDebugString(PChar(('Executing query : ' + QRY + '...'))); if (mysql_query(sock, QRY) < 0) then begin OutputDebugString(PChar(' Query failed ')); OutputDebugString(PChar(' ' + mysql_error(sock))); Halt(1); end; recbuf := mysql_store_result(sock); if RecBuf = nil then begin OutputDebugString(PChar(('Query returned nil result.'))); mysql_close(sock); halt(1); end; OutputDebugString(PChar(('Number of records returned : ' + IntToStr( mysql_num_rows(recbuf))))); OutputDebugString(PChar(('Number of fields per record : ' + IntToStr( mysql_num_fields(recbuf))))); rowbuf := mysql_fetch_row(recbuf); while (rowbuf <> nil) do begin OutputDebugString(PChar(('Slope: ' + rowbuf[0]))); rowbuf := mysql_fetch_row(recbuf); end; OutputDebugString(PChar(('Freeing memory occupied by result set...'))); mysql_free_result(recbuf); OutputDebugString(PChar(('Closing connection with MySQL.'))); mysql_close(sock); Result := PChar(rowbuf[0]); //halt(0); end; exports SQL_Query; begin end.And here is my MQL4 code: