英文:
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存储过程中是必需的,但我怀疑在这种情况下它们是不需要的。如果驱动程序能够自动删除这些大括号就好了。不幸的是,标准语法并不总是被接受的。我想这就是标准的好处:有那么多种。
英文:
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.
答案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();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论