Jdbc stored procedures

Question

I created several stored procedures in the mysql database, but when I try to execute them, I get:

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. 

I added noAccessToProcedureBodies = true to my connection string, but I still get this message. I use the DriverManager.getConnection (url, user, password) method, and the URL that I use is

 jdbc:mysql://[server-url]/[database-name]?noAccessToProcedureBodies=true 

EDIT: I meant that I have a user called creautenti, which I use in this method to create new users:

 public static boolean creazioneUtente(String user, String password) throws EccezioneDaMostrare{ if(apriConnessione(CREA_UTENTI_USER, CREA_UTENTI_PW, false)){ try{ conn.setAutoCommit(false); String sqlCommand="CREATE USER ? @'%' IDENTIFIED BY PASSWORD ?"; String sqlCommandGrant="GRANT EXECUTE ON salvataggi.* TO ? REQUIRE SSL;"; String sqlCommandGrantEx="GRANT SELECT ON `mysql`.`proc` TO ? @'%';"; PreparedStatement s=conn.prepareStatement(sqlCommand); PreparedStatement sGrant=conn.prepareStatement(sqlCommandGrant); PreparedStatement sGrantEx=conn.prepareStatement(sqlCommandGrantEx); s.setString(1, user); sGrant.setString(1, user); sGrantEx.setString(1, user); s.setString(2, mySQLPASSWORD(password)); s.execute(); sGrant.executeUpdate(); sGrantEx.executeUpdate(); conn.commit(); conn.setAutoCommit(true); chiudiConnessione(); return true; } catch(SQLException e){ try { conn.rollback(); } catch (SQLException e1) { String msg=String.format("Errore durante la connessione al server MySQL:\n Messaggio:%s \n Stato SQL:%s \n Codice errore:%s", e.getMessage(), e.getSQLState(), e.getErrorCode()); throw new EccezioneDaMostrare(msg); } chiudiConnessione(); return false; } } else return false; } 

(the first line simply opens the connection to the server as creautenti, and conn is the Connection object). So I logged in as root on the server and named

 GRANT SELECT ON `mysql`.`proc` TO 'creautenti'@'%' WITH GRANT OPTION; 

who updated creautenti privileges accordingly, but when I call

 GRANT SELECT ON `mysql`.`proc` TO '<username>'@'%'; 

registered as creautenti for any other user, privileges are not changed. All routines are in salvataggi, on which I grant EXECUTE privileges to each user, so I also don’t think that the problem.

Answer 1

You might be better off providing access to the mysql.proc table for the user of your application. Therefore, connect to your MySQL database as root and follow these steps:

 GRANT SELECT ON `mysql`.`proc` TO '<username>'@'%'; 

Your Java application should be able to see the correct metadata without specifying noAccessToProcedureBodies=true

Also make sure that the user under whom you connect to the database has the rights to perform this procedure. Again, as a root user or a user with grant privileges:

 GRANT EXECUTE ON PROCEDURE db.storedproc TO '<username>'@'%'; 

Good luck

Answer 2

TL; DR; Change your Callable Statement to reference parameters by index instead of name.

After a similar problem, I updated the mysql-connector-java-5.1.26-bin.jar JDBC driver. Then the error changed from

The user does not have access to the metadata necessary to determine the stored types of procedure parameters. If rights cannot be granted, configure the connection with "noAccessToProcedureBodies = true" with the driver to generate parameters that represent INOUT strings regardless of the actual parameter types.

to

There is no access to parameters by name when the connection is configured not to access procedure bodies

I changed my Callable Statement to reference parameters by index instead of name, and hey presto it works.

Updating the driver may not be necessary, just knowing to use indexes instead of names if you do not have access to metadata or access to the body of a regular organization.

Luck

Answer 3

The following steps worked for me in mysql

Step 1: add / edit the following

Connection con = DriverManager.getConnection ("jdbc: mysql: // ipaddress: 3306 / logparser? NoAccessToProcedureBodies = true", "root", "");

Step 2: Run the following query GRANT ALL ON logparser.proc Go to root @ '%';

where logparser is the name of the database and proc is the name of the procedure.