无法从 Java 执行 MySQL 存储过程 [英] Can't execute a MySQL stored procedure from Java

问题描述

我正在从在 tomcat 中运行的 Web 应用程序连接到在 linux 机器上运行的 MySQL (5.08) 数据库.

I am connecting to a MySQL (5.08) database running on a linux machine from a web application running in tomcat.

当我尝试执行存储过程时出现以下异常:

I get the following exception when I try to execute a stored procedure:

com.hp.hpl.chaos.web.exception.DBException: getNextValue for operatorinstance[Additional Information from SQL Exception][SQLErrorCode: 0 SQLState: S1000
    at com.hp.hpl.chaos.web.util.SQLUtil.getNextValue(SQLUtil.java:207)
        ..............

Caused by: java.sql.SQLException: 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.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1619)
at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:4034)
at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:709)
at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:513)
at com.mysql.jdbc.Connection.parseCallableStatement(Connection.java:4583)
at com.mysql.jdbc.Connection.prepareCall(Connection.java:4657)
at com.mysql.jdbc.Connection.prepareCall(Connection.java:4631)
at com.hp.hpl.chaos.web.util.SQLUtil.getNextValue(SQLUtil.java:196)
... 17 more

在机器上安装mysql之后.我已将以下授予选项授予 root

After installing mysql on the machine. I have given the follwing grant options to root

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'pass';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

在java类中..

我按如下方式连接到数据库:

I am connecting to the db as follows:

String url = "jdbc:mysql://ipaddress:3306/test";
                con = DriverManager.getConnection(url,"root", "pass");

我还尝试了包含 noAccessToProcedureBodies=true 选项的网址.

I have also tried the url with the noAccessToProcedureBodies=true option included.

有人能告诉我这里有什么问题吗?有什么需要我检查的吗?

Can someone tell me what is wrong here? Is there anything I need to check?

推荐答案

有两种方法可以解决这个问题:

There are two ways to solve this:

1. 设置连接的noAccessToProcedureBodies=true 属性

例如作为连接字符串的一部分:

For example as part of the connection string:

jdbc:mysql://ipaddress:3306/test?noAccessToProcedureBodies=true

然后 JDBC 驱动程序将为参数创建INOUT"字符串，而不需要像异常所说的元数据.

The JDBC driver will then create "INOUT" strings for the arguments without requiring meta data like the exception says.

授予数据库用户对mysql.proc的SELECT权限

Grant SELECT privileges on mysql.proc to the database user

例如在mysql提示符下:

For example in the mysql prompt:

GRANT SELECT ON mysql.proc TO 'user'@'localhost';

当然，这将允许应用程序读取整个 mysql.proc 表，其中包含有关 all 数据库中的 all 存储过程的信息(包括源代码).

Of course this would allow the application to read the entire mysql.proc table that contains information about all stored procedures in all databases (including source code).

这篇关于无法从 Java 执行 MySQL 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！