0

I am trying to insert some data from MT4 via libmysql.dll using an mysql wrapper. I have successfully managed to create and select a database, create a table, and even retrieve entries from a table (which were entered into a table from mysql workbench). I am now having trouble populating a table with data from mt4.

The problem: running the below insert query returns error 1064 at mt4 terminal. 

query = StringConcatenate ("insert into ",tablename," (`MQLTime`, `RTime`, `Open`, `High`, `Low`, `Close`, `Volume`) values ​​('" ,time, "','" ,RTIME, "'," ,open, "," ,high, "," ,low, "," ,close, "," ,volume, ");" );

The table ( and column datatypes) are generated as below:

query = StringConcatenate("CREATE TABLE ",dbName,".",tablename," 
    (MQLTime CHAR NOT NULL, RTime CHAR NULL, Open DOUBLE NULL, 
    High DOUBLE NULL, Low DOUBLE NULL, Close DOUBLE NULL, Volume BIGINT NULL, 
    PRIMARY KEY (MQLTime));");

The insert query as printed to the mt4 terminal indicates the error lies in VALUES part of the query ("??")

insert into EURUSD_M1 (`MQLTime`, `RTime`, `Open`, `High`, 
`Low`, `Close`, `Volume`) values ??('2014.07.30 13:00:00','2014.07.30 13:00:00',1.34018,1.34027,1.34015,1.34027,79);

When this print statement is entered into workbench (without "??"), the values are successfully entered into the appropriate columns.

I guess there is an issue with datatypes somewhere but I can't seem to figure out what it is. Any suggestions would be great. Thank you

user3180258
  • 97
  • 13
  • Check for some non printing characters between values and (. Or just remove everything between and put one space. – LHristov Jul 30 '14 at 10:43
  • Well I placed the cursor where the 'space' was between VALUES and ( and lo-and-behold there were 2 more presses of delete to bring them together. I now have another insert error with the same query but I'll see how I go with that first. What are non-printing caharacters exactly and how on earth do they get there :/ – user3180258 Jul 30 '14 at 10:48
  • Your time columns seems not appropriate: '2014.07.30 13:00:00' is a bit more than a single `CHAR`. – VMai Jul 30 '14 at 10:48
  • that's definitely what the problem is now. It doesn't seem to work as datetime either. Nor VARCHAR – user3180258 Jul 30 '14 at 10:57
  • Dates are restricted to a limited range of formats in MySQL. I don't think 'yyyy.mm.dd h:i:s' is one of them. Try 'yyyy-mm-dd h:i:s' – Strawberry Jul 30 '14 at 11:03
  • `STR_TO_DATE(your_date_string, '%Y.%m.%d %H:%i:%s')` should do it. It's highly recommended to use the appropriate data types instead of strings. By the way: CHAR and VARCHAR want a length, i.e. VARCHAR(30). – VMai Jul 30 '14 at 11:07
  • @VMai, is STR_TO_DATE able to run within a query or is it for preprocessing? If it won't work within the query then I don't think I can use it – user3180258 Jul 30 '14 at 11:17
  • @Strawberry, I now have the following being output as VARCHAR(20) though it seems unimpressed with the space between the date and time; "2014-07-30 14:11:00" ^^this also fails as datetime – user3180258 Jul 30 '14 at 11:21
  • Varchar? Store dates/datetimes as dates/datetimes!! :-( – Strawberry Jul 30 '14 at 11:22
  • You can include the STR_TO_DATE function call into your INSERT statement. I don't know MT4, but I would recommend using prepared statements, if those exist in MT4. – VMai Jul 30 '14 at 11:23
  • @Strawberry, in order to convert the datetime from the yyyy.mm.dd hh:mm:ss format of MT4 I have to convert to a string. To make things that bit more annoying, calling iTime in mql4 returns the number of seconds since their server went online in 1970 :/ – user3180258 Jul 30 '14 at 11:26
  • @VMai, MT4and its MQL4 language are a bit dicey at the best of times. That said they are the best tools openly available for this typw of data. I'll take a break then try out STR_TO_DATE. The screen is vibrating :S – user3180258 Jul 30 '14 at 11:28
  • Thank you everyone for your help. I will post the final script and the output as an answer. – user3180258 Jul 30 '14 at 11:51
  • Its seems I'll post the solution in 8hrs time due to lack of street cred :/ – user3180258 Jul 30 '14 at 11:57

1 Answers1

0

As a painting company in Australia say: "Problem Solvered"

MT4 side Query:

query = StringConcatenate ("insert into ",tablename," (MQLTime,RTime,Open,High,Low,Close,Volume) values (STR_TO_DATE('",time,"','%Y.%m.%d %H:%i:%s'),CONCAT('",RDATE," ','",RTIME,"'),",open,",",high,",",low,",",close,",",volume,");");

MT4 Output:

insert into EURUSD_M1 (MQLTime,RTime,Open,High,Low,Close,Volume) values (STR_TO_DATE('2014.05.20 01:40:00','%Y.%m.%d %H:%i:%s'),CONCAT('20140520 ','01:40:00'),1.37093,1.37093,1.37092,1.37093,3);

MySQL output:

2014-05-19 17:14:00, 20140519 17:14:00, 1.37202, 1.37209, 1.37201, 1.37202, 59

Again, thank you.

user3180258
  • 97
  • 13