[solved] problem calling two different SP

[sean]

I have two different SPs:

Code: Select all

CREATE PROCEDURE test1(IN var1 INT, OUT var2 DOUBLE)
  READS SQL DATA
BEGIN
  SET var2=2.02;
END;
CREATE PROCEDURE test2(IN v1 INT, OUT v2 DOUBLE)
  READS SQL DATA
BEGIN
  SET v2= v1 + 10.02;
END;

These are called as follows:

Code: Select all

  with MyStoredProc1 do begin
    try
      Close;
      StoredProcName:='test1';
      ParamByName('var1').AsInteger:=10;
      Execute;

      ShowMessage('test1 var2=' +FieldByName('@var2').AsString);
    finally
      Close;
    end;
  end;

  with MyStoredProc1 do begin
    try
      Close;
      StoredProcName:='test2';
      ParamByName('v1').AsInteger:=10;
      Execute;

      ShowMessage('test2 v2=' +FieldByName('@v2').AsString);
    finally
      Close;
    end;
  end;

In the design environment, the two results are popped up fine.

BUT, in the production (no delphi), The first SP works [var2=2.02], the second says "Parameter var1 not found".
After that SP test1 no longer works either, until I restart the client program. Normal mysql queries queries work fine, it only affects SPs (which I would really like to be able to use..)

I first noticed this is much more complex queries, and created the very simple examples above as a proof of concept. Seem like a bug to me?

D7 / MyDAC 5.9.0.51

Thanks in advance

[Dimon]

I can not reproduce the problem.
Try to execute the following code:

Code: Select all

StoredProcName:='';

before the line:

Code: Select all

StoredProcName:='test1';

[sean]

That did not make any difference ("Parameter 'var1' not found" still happens at run time)

I also deleted and re-added the MyStoredProc1 visual object to be sure it has no special settings. This had no effect.

Running the DBmonitor on the runtime system does not show the SP being called either.

Could be be something in the MyConnection properties?
Have you any other suggestions?

[Dimon]

I still can not reproduce the problem.
Please, try to compose a sample to demonstrate the problem and send it to dmitryg*devart*com.
Also supply me the exact version of MySQL server and client. You can see it in the Info sheet of TMyConnection Editor.

[sean]

There were some out of band emails, but a solution has been found.

The users calling the queries did not have the (mysql) rights to list the SP details.

Its a problem with the way APIs such as mydac check SPs before calling them, see:
http://bugs.mysql.com/bug.php?id=10640

The workaround:
mysql> GRANT SELECT on mysql.proc to MYUSER@'%';

'MYSUSER' is the user who calls the SP, configured in the MyConnection.

Thanks to Dmitry Gerasimenko for the help.

This shoould go in a FAQ, it will surely bit anyone who tries to use SPs from Mydac?

Regards,

Sean