Using TMyStoredProc gives parameter not found error

[docH]

The situation
My database is accessed remotely from an ISP's server. The DB has only one user who has GRANT ALL PRIVILIGES (but is not a super user or root). My Delphi application logs into the database using that same single user (that I also use for development). I have many stored procedures in my database. All of them have definers with an IP address in of the form '[email protected]' as the default definer is the only type I have permissions to make.

The problem
Assume I have a stored procedure defined as

Code: Select all

CREATE PROCEDURE make_invoice(invoice_id INT);

If I use a TMyQuery to run it using sql such as

Code: Select all

MyQuery1.clear;
MyQuery1.sql.add('CALL make_invoice(1234);');
MyQuery1.open;

then it works fine running my application on my development machine (with the same IP address as in the definers) and also from a colleague running the same application from a different IP address.

It also works from both my IP and another IP if I use a TMyConnection eg

Code: Select all

MyConnection1.ExecSQL('CALL make_invoice(1234)'); 

However, if I choose to use a TMyStoredProc instead and use code such as

Code: Select all

MyStoredProc1.StoredProcName := 'make_invoice';    
MyStoredProc1.PrepareSQL;           
MyStoredProc1.ParamByName('(invoice_id ').AsInteger := 1234;    
MyStoredProc1.Execute;

then it still works fine when running from my IP address but when running from a different IP address I get an error

Code: Select all

Parameter 'invoice_id' not found

when it tries to assign a value to the parameter (and the execute fails)

What I have tried
I inserted a debug message

Code: Select all

Showmessage( MyStoredProc1.sql.text);

after PrepareSQL and before assigning the parameter value.
On my machine the message displays

Code: Select all

CALL make_invoice(:invoice_id);

but when running the application from a different IP address the message is just

Code: Select all

CALL make_invoice()

with no parameters shown

Why can I call stored procedures using TMyQuery or TMyConnection from my IP address or another IP address but I can only call them using the more appropriate TMyStoredProc when running from my IP address? - and what can I do about it?

[ViktorV]

When calling the TMyStoredProc.PrepareSQL method, a query is executed on MySQL system tables: for MySQL server versions lower than 5.0.4 - on the mysql.proc table, for newer versions - on INFORMATION_SCHEMA.ROUTINES. To accomplish your task, you should grant SELECT permission to the users.

[docH]

Thank you, that's useful information. However the database is on a server hosted by my ISP and I already have SELECT permissions to INFORMATION_SCHEMA.ROUTINES.

I am the only user and I can run SQL such as

Code: Select all

SELECT * FROM INFORMATION_SCHEMA.ROUTINES

when logged in in from my IP address, the same IP as in the DEFINERs. I expect that's why I can also run Stored Procedures using TMyStoredProc from my IP address.

So it would appear that when I am logged in from a different IP address I no longer have permission to SELECT from INFORMATION_SCHEMA.ROUTINES and that's why TMyStoredProc fails.

So the question is why do I lose SELECT permissions on INFORMATION_SCHEMA.ROUTINES when logged in from a different IP address and is there anything I can do about it to force TMyStoredProc to be able to execute the SELECT, and the code of the procedure?

(I do not have permission to change any of the definers and as far as I know my user name is not tied to a particular IP address as I can run everything else I want to from a different IP address.)

[ViktorV]

Your question is related to the operation of MySQL server rather than the functionality of MyDAC. Please contact the support team of your server or refer to the MySQL server documentation for information; you may also ask your question on specialized forums.

[docH]

I have already had long conversations with my ISP who insist the only way for INFORMATION_SCHEMA.ROUTINES to be accessed from a different IP is by making all the definers localhost (which prevents me editing the SP when logged in as me)

My post here is to fully understand how TMyStoredProc, in combination with TMyConnection, is attempting to query the database to see if I can work around the issue of TMyStoredProc being able to query INFORMATION_SCHEMA.ROUTINES from one IP address but not from another whilst TMyQuery, that does not use this table can run from either.

It appears that I might have to write my own version of TMyStoredProc that uses just TMyQuery to achieve the same effect.

[ViktorV]

To solve your task, you can use the next code:

Code: Select all

MyStoredProc1.SQL.Text := 'CALL make_invoice(:invoice_id)';
MyStoredProc1.ParamByName('invoice_id').AsInteger := 1234;
MyStoredProc1.Execute;