无法从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的网址
选项包括。
有人能告诉我这里有什么问题吗?有什么我需要检查的吗?
Can someone tell me what is wrong here? Is there anything I need to check?
推荐答案
有两种方法可以解决这个问题:
There are two ways to solve this:
-
设置连接的
noAccessToProcedureBodies = true
property
set the connection's
noAccessToProcedureBodies=true
property
例如,作为连接字符串的一部分:
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
表,其中包含所有数据库中所有存储过程的信息(包括源代码)。
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屋!