JDBC在存储过程调用时使用了空括号 “()”。

huangapple go评论78阅读模式
英文:

jdbc ads emtpy backets "()"on procedure call

问题

I am using a stored procedure from my Java app, using jdbc like so

String q="{call test.proc(?,?)}";
CallableStatement cal=conn.prepareCall(q);
cal.setInt(1, val1);
cal.setInt(2, val2);
ResultSet rs=cal.executeQuery();

for some reason this is returning an error

> Exception in thread "main"
> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an
> error in your SQL syntax; check the manual that corresponds to your
> MySQL server version for the right syntax to use near '()' at line 1

at debuging I noticed that for some reason an empty brackets set is added to the call

System.out.println(cal);

produces

com.mysql.jdbc.JDBC42CallableStatement@223191a6: CALL test.proc(1079,1);()

Just in case the jdbc connection has something to do with it, it is

Connection conn = DriverManager.getConnection("jdbc:mysql://"+host+"/"+DB+"?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&useSSL=false&noAccessToProcedureBodies=true"
            , user,password);

EDIT
Ok, this is just plain wierd. I dont get it. Ι changed the call and used the values directly on the string instead.

String q="{call test.proc(val1,val2)}";
CallableStatement cal=conn.prepareCall(q);
System.out.println(cal);
ResultSet rs=cal.executeQuery();

This for some reason prints as expected

> com.mysql.cj.jdbc.CallableStatement: CALL test.proc(1079,0)

But i am still getting the same error

> Exception in thread "main" java.sql.SQLSyntaxErrorException: You have
> an error in your SQL syntax; check the manual that corresponds to your
> MySQL server version for the right syntax to use near '()' at line 1

I really cant get this. This is not my first MySql procedure to use under jdbc. I even tried different jdbc drivers with no luck.

Edit - Solution
I am not sure how this happened, but the solution was as crazy as the problem. the problem was the curly brackets. For some reason (at least under jdbc 8.0.18) when i removed the curly brackets from the CallableStatement worked just fine.
Again, this makes no sense to me, but since it is working I will not question it much

so the lines

String q="{call test.proc(?,?)}";
CallableStatement cal=conn.prepareCall(q);

are wrong, and the correct statements are

String q="call test.proc(?,?)";
CallableStatement cal=conn.prepareCall(q);
英文:

I am using a stored procedure from my Java app, using jdbc like so

String q="{call test.proc(?,?)}";
CallableStatement cal=conn.prepareCall(q);
cal.setInt(1, val1);
cal.setInt(2, val2);
ResultSet rs=cal.executeQuery();

for some reason this is returning an error

> Exception in thread "main"
> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an
> error in your SQL syntax; check the manual that corresponds to your
> MySQL server version for the right syntax to use near '()' at line 1

at debuging I noticed that for some reason an empty brackets set is added to the call

System.out.println(cal);

produces

com.mysql.jdbc.JDBC42CallableStatement@223191a6: CALL test.proc(1079,1);()

Just in case the jdbc connection has something to do with it, it is

Connection conn = DriverManager.getConnection("jdbc:mysql://"+host+"/"+DB+"?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&useSSL=false&noAccessToProcedureBodies=true"
            , user,password);

EDIT
Ok, this is just plain wierd. I dont get it. Ι changed the call and used the values directly on the string instead.

String q="{call test.proc(val1,val2)}";
CallableStatement cal=conn.prepareCall(q);
System.out.println(cal);
ResultSet rs=cal.executeQuery();

This for some reason prints as expected

> com.mysql.cj.jdbc.CallableStatement: CALL test.proc(1079,0)

But i am still getting the same error

> Exception in thread "main" java.sql.SQLSyntaxErrorException: You have
> an error in your SQL syntax; check the manual that corresponds to your
> MySQL server version for the right syntax to use near '()' at line 1

I really cant get this. This is not my first MySql procedure to use under jdbc. I even tried different jdbc drivers with no luck.


Edit - Solution
I am not sure how this happened, but the solution was as crazy as the problem. the problem was the curly brackets. For some reason (at least under jdbc 8.0.18) when i removed the curly brackets from the CallableStatement worked just fine.
Again, this makes no sense to me, but since it is working I will not question it much

so the lines

String q="{call test.proc(?,?)}";
CallableStatement cal=conn.prepareCall(q);

are wrong, and the correct statements are

String q="call test.proc(?,?)";
CallableStatement cal=conn.prepareCall(q);

答案1

得分: 1

外部大括号在某些JDBC存储过程中是必需的,但我怀疑在这种情况下它们是不需要的。如果驱动程序能够自动删除这些大括号就好了。不幸的是,标准语法并不总是被接受的。我想这就是标准的好处:有那么多种。 JDBC在存储过程调用时使用了空括号 “()”。

英文:

While the outer braces are required for some JDBC stored procedures, I suspect they are not needed in this case. It would have been nice had the braces been silently removed by the driver. Unfortunately, a standard syntax is not always accepted. I suppose that's the good thing about standards: there are so many of them. JDBC在存储过程调用时使用了空括号 “()”。

答案2

得分: 0

你应该尝试使用nomosBirths.execute(),而且q不应该有分号。
如果你的存储过程是一个查询,使用execute方法返回的布尔值,并使用以下代码来获取结果集:ResultSet resultSet = statement.getResultSet();

英文:

You should try with
nomosBirths.execute() and q should not have semicolon
If your procedure is a select, use the boolean returned by execute method and use the following to get your result set ResultSet resultSet = statement.getResultSet();

huangapple
  • 本文由 发表于 2020年8月7日 19:16:21
  • 转载请务必保留本文链接:https://go.coder-hub.com/63300689.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定