small bug in mydac 4.30 "Commands out of sinc; you can´t run this command now."

[eduardosic]

Hi all,

i found a bug in myDAC 4.30 + mySQL 5.0 win32 + Delphi 7 build 4.453

in seconde execution of code raise the exception: "Commands out of sinc; you can´t run this command now."

if i call Unprepare before TmyQuery.Close , the exception don't is raised.
but in older version it's not necessary.

the code
if key = 13 then begin
with MyQuery1 do begin
Close;
ParamByName( 'find' ).AsString := edit1.text + '%';
prepare; Open;
end;
end;

[Ikar]

Thank you for information.
We reproduced your problem and fixed it. This fix will be included in the next MyDAC build.

[colinw]

I'm getting a very similar problem with D5 Ent (build 6.18) update pack 1 with MySQL 5.1 linux when calling stored procedures which return record sets.

The only thing is that I'm finding call unprepare before close DOES NOT reliably fix the problem.

Any ideas ?

MyDAC version was 5.0.16 (according to the version info of the installer file) and 5.00.1.7 (according to the download page)

[Antaeus]

Probably you run across a MySQL server bug. Please consult this topic of MyDAC FAQ for more information.

[colinw]

Well having played around with this for a bit I think I see what the problem is....
Firstly I note that there is NO problem making multiple calls to sps which return rowsets from the MySQL command line client. You can do something like this:
call mysp(p1,p2,@ResultCode); select @ResultCode; [one batch]
call mysp(p1,p2,@ResultCode); select @ResultCode; [another batch]
and each time you do it you will just get the two rowsets back as expected

If you try two calls with the MyDAC sp component you will get the command out of sync error

Investigating what is going on with EtherReal reveals that the the cmd line client waits for the first result set to come back before issuing the second statement in the batch. Looking at the trace for MyDAC the behaviour is quite different. Both statements are sent at the same time. This means that the client is not in a state to receive the second dataset when it returns.

For the moment I have been able to work around this by using MyQuery instead of MyStoredProc and issuing the two queries seperately. By doing this you can then make as many call as you like without problems.

This is not a bug in the MySQL server, but rather a bug in the MyStoredProc component. There is no problem making these sorts of calls from the MySQL C api, so it would be nice to see this fixed

Now, it's argueable that the MySQL design which seems to require two requests to the server to call an sp which has output params is a bit crazy, but there we are... I guess we just have to work with it

[Antaeus]

As I understand, you just setup a TStoredProc component, call the Execute method of this component twice in a row in the same thread, and get the "Command out of sync" error. Is it correct? If not, please explain this issue in more detail, or send me (evgeniyd*crlab*com) a small test project that demonstrates the problem.

[colinwes]

Yes this is correct. The only proviso is that the stored procedure in question must return a rowset AND have an output parameter.

The presence of the output parameter is what causes the TMyStoredProc component to issue the select @ReturnCode (or whatever the param is called) along with the call to the sp. The only problem is that the ethereal traces show that the second call (select) is being made before the first call has returned its result set - this is in contrast to the behaviour of the MySQL command line client and to what one normally does with the C api. (see earlier post)

[Antaeus]

Thank you for your answer, but I still cannot reproduce the problem. Could you send me a complete small sample to reproduce it?
Please specify the exact version of MyDAC. You can get it from the About sheet of TMyConnection Editor, or checking the value of the MydacVersion variable at run time.