| Bug #43576 | Unexpected exception when trying to register OUT parameters in CallableStatement | ||
|---|---|---|---|
| Submitted: | 12 Mar 2009 8:26 | Modified: | 18 Jun 2010 16:02 |
| Reporter: | sergio cruz | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 5.1.8 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | CallableStatement, connectorj, stored procedures | ||
[12 Mar 2009 9:38]
Tonci Grgin
Test case for our test framework.
Attachment: TestBug43576.java (text/x-java), 4.08 KiB.
[12 Mar 2009 9:42]
Tonci Grgin
Hi Sergio and thanks for your report. I am unable to repeat the problem described with test case attached. Client on W2K8x64 with latest c/J 5.1 sources. MySQL server 5.1.31GA on OpenSolaris 2008.11 x64 remote host. Test case output: Loading JDBC driver 'com.mysql.jdbc.Driver' Done. Done. Connected to 5.1.31-log java.vm.version : 1.5.0_17-b04 java.vm.vendor : Sun Microsystems Inc. java.runtime.version : 1.5.0_17-b04 os : Windows Server 2008, 6.0, x86 sun.management.compiler : HotSpot Client Compiler ------------------------------------------------- Please attach functioning test case next time to avoid numerous syntax errors when typing. Please try to repeat the problem with latest c/J build from snapshots or build it yourself.
[12 Mar 2009 10:16]
sergio cruz
Hi Toncy, many thanks for your help.
I have tested with your code and it works ok, but note that it is not the same case of my proof.
In my proof the "IN" parameters are passed with literals, not like dinamic parameters. If I change your code and do this, the test fails ...
Please, could you check this issue?
My properties system are:
java.vm.version : 1.5.0_16-b02
java.vm.vendor : Sun Microsystems Inc.
java.runtime.version : 1.5.0_16-b02
os : Linux, 2.6.24-19-generic, i386
sun.management.compiler : HotSpot Client Compiler
The connector/J version is the 5.1.7, I think that is the recently stable version.
I send you your test class modified with the exactly problem.
For example look this part of your modified code:
CallableStatement callSt = connection.prepareCall("{ call bug43576_1(?, 'xxx', ?, ?) }");
// callSt.setString(2, "xxx");
callSt.registerOutParameter(1, java.sql.Types.VARCHAR);
callSt.registerOutParameter(2, java.sql.Types.VARCHAR);
callSt.registerOutParameter(3, java.sql.Types.VARCHAR);
callSt.execute();
String out1 = callSt.getString(1);
String out2 = callSt.getString(3);
String out3 = callSt.getString(4);
System.out.println("out1_1 = " +out1);
System.out.println("out1_2 = " +out2);
System.out.println("out1_3 = " +out3);
Note the IN parameter with literal. The exception returned is:
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: -1
at com.mysql.jdbc.CallableStatement.setOutParams(CallableStatement.java:2207)
at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:876)
at TestBug43576Local.main(TestBug43576Local.java:35)
Many thanks in advance.
[12 Mar 2009 10:18]
sergio cruz
TestBug43576.java modified with exactly fail case.
Attachment: TestBug43576.java (text/x-java), 3.99 KiB.
[13 Mar 2009 7:42]
Tonci Grgin
Sergio, there is a typo in your test:
//1, 2 & 3
callSt.registerOutParameter(1, java.sql.Types.VARCHAR);
callSt.registerOutParameter(2, java.sql.Types.VARCHAR); //3 ?
callSt.registerOutParameter(3, java.sql.Types.VARCHAR); //4 ?
//1, 2 & 4
String out1 = callSt.getString(1);
String out2 = callSt.getString(3);
String out3 = callSt.getString(4);
This can't possibly work afais.
[13 Mar 2009 8:56]
sergio cruz
Hi Tonci,
you have reason, the indexes in "getter" methods are not ok, excuse me for the mistake. In fact, the class which I attached has the indexes ok.
But the problemn is in the call to "execute()" method, before the "getters", it is in this point when exception is throws.
Also, I think that indexes "1, 2 and 3" are ok, because represent the wild character "?", and only are three in this case.
The correct extract of code:
CallableStatement callSt = connection.prepareCall("{ call bug43576_1(?, 'xxx', ?, ?) }");
callSt.registerOutParameter(1, java.sql.Types.VARCHAR);
callSt.registerOutParameter(2, java.sql.Types.VARCHAR);
callSt.registerOutParameter(3, java.sql.Types.VARCHAR);
callSt.execute(); -----> THROWS EXCEPTION
String out1 = callSt.getString(1);
String out2 = callSt.getString(2);
String out3 = callSt.getString(3);
System.out.println("out1_1 = " +out1);
System.out.println("out1_2 = " +out2);
System.out.println("out1_3 = " +out3);
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: -1
at com.mysql.jdbc.CallableStatement.setOutParams(CallableStatement.java:2207)
at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:876)
at TestBug43576Local.main(TestBug43576Local.java:35)
I tested too with indexes 1, 3 and 4, I did not know if you think that it is the correctly, and result is another exception.
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 3
at com.mysql.jdbc.CallableStatement.checkIsOutputParam(CallableStatement.java:676)
at com.mysql.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:1844)
at TestBug43576Local.main(TestBug43576Local.java:34)
Well, I think that the problem appear when the query invoked has IN parameters like literals, thus, could you try to execute your original test with only OUT parameters with "?" ?
For example:
Probe this " CallableStatement callSt = conn1.prepareCall("{ call bug43576_1(?, 'xxx', ?, ?) }"); "
in place of: " CallableStatement callSt = conn1.prepareCall("{ call bug43576_1(?, ?, ?, ?) }");
callSt.setString(2, "xxx");"
(theses cases are in the class that I attached yesterday, I removed IN parameters and I put they into the query directly)
Thanks.
[20 Mar 2009 7:07]
Tonci Grgin
Sergio, I am not sure things can work the way you described, mixing values provided for parameters. > Also, I think that indexes "1, 2 and 3" are ok, because represent the wild character "?", and only are three in this case. I don't think this is true. Please make note of Bug#17898 as everything is explained there. If possible, can you test against MySQL server 6.0.9? I do not have anyone I can consult on this right now but will do so much later today. Can you please find urls quoting the behavior you're requesting is allowed either on Java or on c/J level? And a word on ruling. Even though I closed the report with CRp I am still monitoring and we'll find out what's wrong.
[23 Mar 2009 8:31]
sergio cruz
Hi Tonci, I have read the bug#17898 and it has similar features. The bug has this comment in Description section: " ... especially when a user wants to use a literal as the IN value of an INOUT parameter." My problem is like these, when I use literal values for IN parameters. I think that you only have to try the last class that I attached (12/03/2009). In this class, I have replaced the "setters" for IN parameters with literals in the query. Did you check this class? The class works fine in Oracle and SQLServer... Many thanks.
[23 Mar 2009 15:31]
Tonci Grgin
Extended test case for our test framework.
Attachment: TestBug43576.java (text/x-java), 4.41 KiB.
[23 Mar 2009 15:35]
Tonci Grgin
Sergio, I extended test case to cover for your case and it still works... Please review it and see how it differs from your code.
As for "?" magic, here's what I gathered so far:
o Thing with mixing literals and provided parameter values should work. In any case it should not throw NPE.
o When mixing parameter values this way, only thing that matters is count of place-holders ("?"). See attached test case.
You can also try latest c/J driver from snapshots and see if it works for you.
[23 Mar 2009 16:09]
sergio cruz
Hello Tonci, thanks for your answer.
Your new code works ok, but it is not the same case that I attached you.
I tested your code and my case, and the results are these.
CASE 1, works OK
----------------
CallableStatement callSt = conn1.prepareCall("{ call bug43576_1(?, ?, ?, ?) }");
callSt.setString(2, "xxx");
callSt.registerOutParameter(1, java.sql.Types.VARCHAR);
callSt.registerOutParameter(3, java.sql.Types.VARCHAR);
callSt.registerOutParameter(4, java.sql.Types.VARCHAR);
callSt.execute();
assertEquals("ncfact string", callSt.getString(1));
assertEquals("ffact string", callSt.getString(3));
assertEquals("fdoc string", callSt.getString(4));
CASE 2, works OK
----------------
CallableStatement callSt2 = conn1.prepareCall("{ call bug43576_2(?, ?, ?, ?, ?) }");
callSt2.setString(1, "xxx");
callSt2.setString(2, "yyy");
callSt2.registerOutParameter(3, java.sql.Types.VARCHAR);
callSt2.registerOutParameter(4, java.sql.Types.VARCHAR);
callSt2.registerOutParameter(5, java.sql.Types.VARCHAR);
callSt2.execute();
assertEquals("ncfact string", callSt2.getString(3));
assertEquals("ffact string", callSt2.getString(4));
assertEquals("fdoc string", callSt2.getString(5));
CASE 3, works OK
----------------
CallableStatement callSt3 = conn1.prepareCall("{ call bug43576_2(?, 'yyy', ?, ?, ?) }");
callSt3.setString(1, "xxx");
//callSt3.setString(2, "yyy");
callSt3.registerOutParameter(2, java.sql.Types.VARCHAR);
callSt3.registerOutParameter(3, java.sql.Types.VARCHAR);
callSt3.registerOutParameter(4, java.sql.Types.VARCHAR);
callSt3.execute();
assertEquals("ncfact string", callSt3.getString(2));
assertEquals("ffact string", callSt3.getString(3));
assertEquals("fdoc string", callSt3.getString(4));
CASE 4, FAIL! (my case)
-----------------------
CallableStatement callSt4 = conn1.prepareCall("{ call bug43576_2('xxx', 'yyy', ?, ?, ?) }");
// callSt4.setString(1, "xxx");
// callSt4.setString(2, "yyy");
callSt4.registerOutParameter(1, java.sql.Types.VARCHAR);
callSt4.registerOutParameter(2, java.sql.Types.VARCHAR);
callSt4.registerOutParameter(3, java.sql.Types.VARCHAR);
callSt4.execute();
assertEquals("ncfact string", callSt4.getString(1));
assertEquals("ffact string", callSt4.getString(2));
assertEquals("fdoc string", callSt4.getString(3));
Note that ALL IN parameters have literals,
could you check exactly this example?, the others ones work ok, but the case which I try to explain is this.
[23 Mar 2009 17:09]
Tonci Grgin
Sergio, I stand corrected...
Seemingly, offset into param array is wrong when both params are set as in this addition to test case:
CallableStatement callSt4 = conn1.prepareCall("{ call bug43576_2('xxx', 'yyy', ?, ?, ?) }");
//callSt4.setString(1, "xxx");
//callSt4.setString(2, "yyy");
callSt4.registerOutParameter(1, java.sql.Types.VARCHAR);
callSt4.registerOutParameter(2, java.sql.Types.VARCHAR);
callSt4.registerOutParameter(3, java.sql.Types.VARCHAR);
callSt4.execute();
assertEquals("ncfact string", callSt4.getString(1));
assertEquals("ffact string", callSt4.getString(2));
assertEquals("fdoc string", callSt4.getString(3));
Verified as described using latest c/J sources and test case attached + above addition.
[23 Mar 2009 17:20]
sergio cruz
Thank you very much for your time, Tonci.
[24 Mar 2009 8:39]
Tonci Grgin
Sergio, no problem. With such a good report and patient reporter it was a pleasure.
[16 Apr 2010 10:34]
Tonci Grgin
Hum ahh, we forgot about fixing this bug... Will try myself figure out why 5 suddenly becomes 3 during parameter registration.
[16 Apr 2010 11:50]
Tonci Grgin
There is a logical error in code flow...
We start in CallableStatement, ln. 2195 with this.paramInfo.numParameters ("this" is compiled callable statement, paramInfo.numParameters is 5, which is correct imo), proceed to this.setBytesNoEscapeNoQuotes(outParamIndex ... (which is 3 and, imo correct) to PreparedStatement, ln. 3695 checkBounds.
checkBounds (ln. 3705), in turn, uses this.parameterCount ("this" is the same compiled callable statement) which is 3 and bombs out on second parameter passed (whose actual index is 4).
} else if (paramIndex > this.parameterCount) {
Why do we mix this.paramInfo.numParameters and this.parameterCount in same code-path I do not now right now. Nor do I know whether we should "fix" this.parameterCount to be the same as this.paramInfo.numParameters or just change the IF condition in checkBounds...
[19 Apr 2010 16:07]
Tonci Grgin
Patch is ready but ugly. Will need some time to refine it.
[22 Apr 2010 10:38]
Tonci Grgin
Proposed patch
Attachment: diff.txt (text/plain), 3.17 KiB.
[15 Jun 2010 22:23]
Mark Matthews
Fixed for 5.1.13, see http://bazaar.launchpad.net/~mark-mysql/connectorj/5.1/revision/944
[18 Jun 2010 16:02]
Tony Bedford
An entry has been added to the 5.1.13 changelog: An unexpected exception when trying to register OUT parameters in CallableStatement. Sometimes Connector/J was not able to register OUT parameters for CallableStatements.
Description: When trying to register OUT parameters in a Callastatement an unexpected exception is returned in certain cases. I have found problems with the next kind of stored procedures: 1. Stored procedures whose signature is like this "name(IN, IN, OUT, OUT, OUT)" where 'IN' and 'OUT' indicates the kind of parameter. The next expception is reported when trying to execute the statement. "Exception in thread "main" java.sql.SQLException: Parameter index out of range (4 > number of parameters, which is 3)." 2. Stored procedures whose signature is like "name(OUT, IN, OUT, OUT)" The next expception is reported when trying to execute the statement. "Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: -1" How to repeat: - For case one: 1. Create a simple stored procedure like this. CREATE PROCEDURE foo1 (OUT nfact VARCHAR(100), IN ccuenta VARCHAR(100), OUT ffact VARCHAR(100), OUT fdoc VARCHAR(100)) BEGIN SET nfact = 'ncfact string'; SET ffact = 'ffact string'; SET fdoc = 'fdoc string'; END$$ 2. Invoke from JDBC with connector/J. ... ... CallableStatement callSt = connection.prepareCall("{CALL foo1(?, 'paramValue1', ?, ?)}"); callSt.registerOutParameter(1, Types.VARCHAR); callSt.registerOutParameter(2, Types.VARCHAR); callSt.registerOutParameter(3, Types.VARCHAR); callSt.execute(); nFact = callSt.getString(1); fFact = callSt.getString(2); fDoc = callSt.getString(3); callSt.close(); - For case two: 1. Create a simple stored procedure like this. CREATE PROCEDURE foo2 (IN ccuent1 VARCHAR(100), IN ccuent2 VARCHAR(100), OUT nfact VARCHAR(100), OUT ffact VARCHAR(100), OUT fdoc VARCHAR(100)) BEGIN SET nfact = 'ncfact string'; SET ffact = 'ffact string'; SET fdoc = 'fdoc string'; END$$ 2. Invoke from JDBC with connector/J. ... ... CallableStatement callSt = connection.prepareCall("{CALL foo2('param1', 'param2', ?, ?, ?)}"); callSt.registerOutParameter(1, Types.VARCHAR); callSt.registerOutParameter(2, Types.VARCHAR); callSt.registerOutParameter(3, Types.VARCHAR); callSt.execute(); nFact = callSt.getString(1); fFact = callSt.getString(2); fDoc = callSt.getString(3); callSt.close();