commands out of sync; stored procedure
Posted by: Bob Tewilliger
Date: March 01, 2015 02:27AM

I wasn't too sure where to post this but seeing as it involves a call through a wrapper to libmysql.dll I figured this may be the best place.
I have a stored procedure which is as follows:

drop procedure if exists baXRC_fill2;
delimiter $$
create procedure baXRC_fill2(in DB varchar(7), in from_table varchar(12), in baXRCpos int, in factor int)
begin

-- Declare variable to store table names --
declare table_names varchar(50);
-- Declare cursor control variables --
declare table_finish boolean;
declare num_tables int default 0;
declare table_cntr int default 0;
-- Declare dynamic cursor to get table names --
declare table_cursor cursor for select * from vw_baXRC_tables;
-- Declare continue/break handler of cursor --
declare continue handler for not found set table_finish = true;

-- Drop objects evading deletion at last run of SP --
drop temporary table if exists vw_baXRC_tables;
-- Generate temporary table of cursor statement --
set @table_select = concat('create temporary table vw_baXRC_tables as select table_name from information_schema.tables where table_schema = \'', DB, '\' and
table_name not in (\'', DB, '_m1\',\'', DB, '_m1_OC\',\'', DB, '_m1_rowcounts\') and table_name not like \'%outcome\'');
select @table_select;
prepare s1 from @table_select;
execute s1;
deallocate prepare s1;

-- Open cursor and get number of tables returned --
open table_cursor;
select found_rows() into num_tables;
-- Open cursor loop and load cursor result into table_names --
loopy: loop
fetch table_cursor into table_names;
select table_names;
-- Ensure there are cursor 'rows' to work with before continuing operations --
if table_finish then close table_cursor;
leave loopy;
close table_cursor;
end if;

set @cleantables = concat('delete from ', DB, '.', table_names, ' where MQLTime = \'0000-00-00 00:00:00\'');
prepare s2 from @cleantables;
execute s2;
deallocate prepare s2;

-- Start block for filling baXRC values in tables returned by table_cursor --
block2: begin
-- Declare variables to store values for calculation --
declare Opn_pat1a1 decimal(8,5);
declare Cls_baX decimal(8,5);
-- Declare variable to hold pip change --
declare pip_change decimal(8,5) default 0;
-- Declare variable to store baXRC dates for update --
declare baXRC_dates varchar(100);
-- Declare cursor control varialbes --
declare dates_finish boolean;
declare num_rows int default 0;
declare date_cntr int default 0;
-- Declare dynamic cursor for getting baXRC dates --
declare date_cursor cursor for select * from vw_baXRC_dates;
-- Declare continue/break handler of cursor --
declare continue handler for not found set dates_finish = true;

-- drop objects that evaded drop last run --
drop temporary table if exists vw_baXRC_dates;

-- Generate temporary table of cursor statement --
set @dates_select = concat('create temporary table vw_baXRC_dates as select MQLTime from ', DB, '.', table_names, ' where baXRC is null order by MQLTime asc;');
-- select @dates_select;
prepare s3 from @dates_select;
execute s3;
deallocate prepare s3;

-- Open cursor and get number of 'rows' returned --
open date_cursor;
select found_rows() into num_rows;

-- Open cursor loop and load cursor into baXRC_dates
looper: loop
fetch date_cursor into baXRC_dates;
-- select baXRC_dates;
-- Ensure there are rows to work with before continuing operations --
if dates_finish then close date_cursor;
leave looper;
close date_cursor;
end if;

-- Generate prepared statement to retireve Open price of pat1 from table_names --
set @tempOpn = concat('select Opn from ', DB, '.', from_table, ' where MQLTime > \'', baXRC_dates, '\' order by MQLTime asc limit 1 into @Opn_pat1a1;');
-- select @tempOpn;
prepare s4 from @tempOpn;
execute s4;
-- select @Opn_pat1a1;
deallocate prepare s4;

-- Generate and execute prepared sstatement to retrieve Close price of baXRCpos --
set @tempCls = concat(' select Cls from ', DB, '.', from_table, ' where MQLTime >= \'', baXRC_dates, '\' order by MQLTime asc limit ', baXRCpos, ',1 into @Cls_baX;');
-- select tempCls;
prepare s5 from @tempCls;
execute s5;
-- select @Cls_baX;
deallocate prepare s5;

-- Set values of Opne/Close variables and calculate baXRC value --
set Opn_pat1a1 = @Opn_pat1a1;
set Cls_baX = @Cls_baX;

-- Calculate points changed from pat1a1 to baXRCpos --
set @baXRCval = (Cls_baX - Opn_pat1a1) * factor;
-- select @baXRCval;

-- Set string value to be used for baXRC --
if @baXRCval >= 10 then set @baXRCstring = 'Up';
elseif @baXRCval <= -10 then set @baXRCstring = 'Down';
else set @baXRCstring = 'Flat';
end if;

-- Update baXRC in table_names --
set @update_baXRC = concat('update ', DB, '.', table_names, ' set baXRC = \'', @baXRCstring, '\' where MQLTime = \'', baXRC_dates, '\';');
-- select @update_baXRC;
prepare s6 from @update_baXRC;
execute s6;
deallocate prepare s6;

-- End dates loop --
end loop looper;
end block2;
-- Drop dates temporary table --
drop temporary table if exists vw_baXRC_dates;

-- End table names loop --
end loop loopy;
-- Drop temporary table --
drop temporary table if exists vw_baXRC_tables;
-- End SP --
end $$
delimiter ;

This procedure is called from an outside program (MT4) using a wrapper (MQLMySQL.mqh) to libmysql.dll.

When called from the MySQL workbench the procedure operates as expected and if the SP is called from MT4 is operates fine too.

The problem arises when any subsequent calls are made to MySQL from MT4 which throw the commands out of sunc error.

From what I understand, this is thrown when results are not freed with mysql_free_result() or are not fetched before they are used (with mysql_fetch_result()). However, as I'm not actually returning any results to MT4, nor using the results generated from the SP in MT4, I'm a bit lost as to why this error is thrown.

Furthermore, I was under the impression that all SP buffers are cleared once the SP has finished its operations.

I have tried disconnecting/reconnecting after the SP has been called though this did not seem to fix the issue.

Any ideas anyone?

Options: ReplyQuote


Subject
Views
Written By
Posted
commands out of sync; stored procedure
4099
March 01, 2015 02:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.