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);
Code: Select all
MyQuery1.clear;
MyQuery1.sql.add('CALL make_invoice(1234);');
MyQuery1.open;
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)');
Code: Select all
MyStoredProc1.StoredProcName := 'make_invoice';
MyStoredProc1.PrepareSQL;
MyStoredProc1.ParamByName('(invoice_id ').AsInteger := 1234;
MyStoredProc1.Execute;
Code: Select all
Parameter 'invoice_id' not found
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);
Code: Select all
CALL make_invoice()
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?