Well, I was working on this new project where I needed to execute a stored procedure from my JAVA code over MySQL database. But, the problem was that the statement,
CallableStatement cs = con.prepareCall("CALL myproc(?)");
gave following error i.e.
User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types
Even on using the parameter "noAccessToProcedureBodies=true" in the connection string didnot work. It was like a nightmare. Googled like hell but to no resolution. The code seemed all correct and used to work fine on local development machine but when deployed on the web server it just failed. To my surprise, I was even able to execute the procedure through PHP code but was only the JAVA code that had issues.
I read somewhere that my database user did not have proper rights but even that could not be the problem as I had given all the privileges to my user.
Then I came upon this article (http://bugs.mysql.com/bug.php?id=61203) which points to a bug in MySQL JDBC connector which cause "noAccessToProcedureBodies" not to work.
I was now interested. A bug !! well, that was the least I could expect that gave me such horrible time. Anyways, the discussion at the article suggested using the parameter "useInformationSchema=true" in the URL string to over come the problem or to use a more latest version of the MySQL JDBC conenctor as this issue was fixed in version 5.1.19
So, a connection string like this works like charm till your web server host provider upgrades his MySQL JDBC driver i.e.
DBURL=jdbc:mysql://localhost:3306/DatabaseName?user=DBUser&password=DBPassword&useInformationSchema=true
Hope, this information comes handy to some one in need. Cheers!! :-)
CallableStatement cs = con.prepareCall("CALL myproc(?)");
gave following error i.e.
User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types
Even on using the parameter "noAccessToProcedureBodies=true" in the connection string didnot work. It was like a nightmare. Googled like hell but to no resolution. The code seemed all correct and used to work fine on local development machine but when deployed on the web server it just failed. To my surprise, I was even able to execute the procedure through PHP code but was only the JAVA code that had issues.
I read somewhere that my database user did not have proper rights but even that could not be the problem as I had given all the privileges to my user.
Then I came upon this article (http://bugs.mysql.com/bug.php?id=61203) which points to a bug in MySQL JDBC connector which cause "noAccessToProcedureBodies" not to work.
I was now interested. A bug !! well, that was the least I could expect that gave me such horrible time. Anyways, the discussion at the article suggested using the parameter "useInformationSchema=true" in the URL string to over come the problem or to use a more latest version of the MySQL JDBC conenctor as this issue was fixed in version 5.1.19
So, a connection string like this works like charm till your web server host provider upgrades his MySQL JDBC driver i.e.
DBURL=jdbc:mysql://localhost:3306/DatabaseName?user=DBUser&password=DBPassword&useInformationSchema=true
Hope, this information comes handy to some one in need. Cheers!! :-)
3 comments:
Excellent, it works!
Thank you...it works.
Following steps worked for me in mysql
Step 1 : add/edit following
Connection con = DriverManager.getConnection("jdbc:mysql://ipaddress:3306/logparser?noAccessToProcedureBodies = true ", "root", "");
Step 2 : Execute following query
GRANT ALL ON logparser.proc TO root@'%';
where logparser is DB name and proc is procedure name.
Post a Comment