A comment inside a CallableStatement messes with parameters

[Eric Lemaitre]

Hi all,


I have a plain stupid call of stored procedure inside a CallableStatement of a JDBC program:
CallableStatement statement = connection.prepareCall("{call pkgxcard.get_balance (?,?)}");

Works fine of course.

But if I try to insert a standard SQL comment:
CallableStatement statement = connection.prepareCall("/* comment */{call pkgxcard.get_balance (?,?)}");

Then it works with Oracle but FAILS with MySQL (V-5.1.18) with:

java.sql.SQLException: Parameter number 1 is not an OUT parameter
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.CallableStatement.checkIsOutputParam(CallableStatement.java:690)
at com.mysql.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:1881)
at org.apache.commons.dbcp.DelegatingCallableStatement.registerOutParameter(DelegatingCallableStatement.java:94)
at db.test.StoredProcedureTest.testBalance(StoredProcedureTest.java:36)

So obviously adding a correct comment /**/ messes up with the parameters of CallableStatements with MySQL. Does anyone has any idea about this?

TIA, best regards.

[Paul Clapham]

Well, if you're just looking for a solution, then I would just move the comment out of the string literal. I.e. not this:

CallableStatement statement = connection.prepareCall("/* comment */{call pkgxcard.get_balance (?,?)}");

but this:

CallableStatement statement = connection.prepareCall(/* comment */"{call pkgxcard.get_balance (?,?)}");

[Eric Lemaitre]

Hi Paul

Paul Clapham wrote:Well, if you're just looking for a solution, then I would just move the comment out of the string literal. I.e. not this:

CallableStatement statement = connection.prepareCall("/* comment */{call pkgxcard.get_balance (?,?)}");

but this:

CallableStatement statement = connection.prepareCall(/* comment */"{call pkgxcard.get_balance (?,?)}");

No, not a fit, because I need the comment to be sent as well, what you describe above is a Java comment which will make exactly the same as sending the query without any comment at all, which of course works.
If you prefer, I need:

CallableStatement statement = connection.prepareCall("/* mysql_hint_inside_comment */ {call pkgxcard.get_balance (?,?)}");

The "mysql_hint_inside_comment" will be interpreted on server side while the call "{call pkgxcard.get_balance (?,?)}" would be executed along as well.

Thanks, but I still need further help.

[Koen Aerts]

Why do you need the comment inside the call? Would you see it somewhere in the DB logs? If you want to use Oracle hints, should these not go inside the stored procedure instead?

[Eric Lemaitre]

Hi Koen,

Koen Aerts wrote:Why do you need the comment inside the call? Would you see it somewhere in the DB logs? If you want to use Oracle hints, should these not go inside the stored procedure instead?

I need it because I have an interceptor which parses the query and will use the comment, to select some specific DB in a cluster for example.
And as it is a standard SQL comment it MUST not mess with the query anyway, the content of a correctly formed SQL comment should be ignored by MySQL server, so it is totally abnormal IMHO that a comment inside a query messes anything. So unless I made a mistake with this comment so I would need the correct syntax, I need an explanation and a workaround.

Best regards.

[Koen Aerts]

I haven't tried it, but would something like connection.prepareCall("{call pkgxcard.get_balance (?,?)} -- comment") work?

[Eric Lemaitre]

Hi Koen,

Koen Aerts wrote:I haven't tried it, but would something like connection.prepareCall("{call pkgxcard.get_balance (?,?)} -- comment") work?

Yes, both of these work:

CallableStatement statement = connection.prepareCall("{call pkgxcard.get_balance (?,?)} # comment");
CallableStatement statement = connection.prepareCall("{call pkgxcard.get_balance (?,?)} -- comment");

But I really need this kind of syntax:

CallableStatement statement = connection.prepareCall("/* comment */{call pkgxcard.get_balance (?,?)}");

Best regards.

[Paul Clapham]

Well, seems to me you should stop needing that and start using something which does work.

I don't think that's a radical proposition -- if you had some SQL which worked with Oracle and then you switched to MySQL and found it didn't work, then you would modify it so that it did work in both databases, wouldn't you? I don't believe you would say "This SQL syntax doesn't work with MySQL but I need it to work with MySQL", I believe you would fix your syntax.

[Martin Vashko]

I'd say the problem could be that due to the comment, the JDBC driver didn't recognize the escape sequence. If you need the comment before the procedure name, try this:

CallableStatement statement = connection.prepareCall("{call /* comment */ pkgxcard.get_balance (?,?)}");

If that does not work, use your database native syntax (you'll lose portability of course). Cannot help with MySQL though. Just as an example, on Oracle it could be:

CallableStatement statement = connection.prepareCall("/* comment */ begin pkgxcard.get_balance (?,?); end;");

Generally you simply use the text you'd run from your SQL client. Try there first to see what is acceptable.

Edit: after rereading, I'd say you'll need to use the native SQL syntax. My first proposition, even if it works, probably won't place the comment at the beginning.

[Eric Lemaitre]

Hi Martin,

Martin Vajsar wrote:Generally you simply use the text you'd run from your SQL client. Try there first to see what is acceptable.

Edit: after rereading, I'd say you'll need to use the native SQL syntax. My first proposition, even if it works, probably won't place the comment at the beginning.

Sadly no, since what I send from the native client may have to be very different from what I can send from JDBC.
In MySqlWorkbench, my stored procedure uses named parameters stated like "@parameter1", which exist and name is correct, but in JDBC as soon as I try to use them inside CallableStatement instead of ? I get some "missing placeholder exception". I must use a ? as place holder and then the parameters settings by name like "setType(name, value)" are still possible, but if I use the parameter names inside the CallableStatement no way.

I have 2 possible workarounds:

CallableStatement statement = connection.prepareCall("{call /* comment */ pkgxcard.get_balance (?,?)}");

CallableStatement statement = connection.prepareCall("{call pkgxcard.get_balance (?,?)} /* comment */");

It doesn't explain the issue, but it allows me to move forward.

[Martin Vashko]

Eric Lemaitre wrote:Sadly no, since what I send from the native client may have to be very different from what I can send from JDBC.
In MySqlWorkbench, my stored procedure uses named parameters stated like "@parameter1", which exist and name is correct, but in JDBC as soon as I try to use them inside CallableStatement instead of ? I get some "missing placeholder exception". I must use a ? as place holder and then the parameters settings by name like "setType(name, value)" are still possible, but if I use the parameter names inside the CallableStatement no way.

Sorry, Eric, I was sloppy regarding the bind variables. Furthermore, my experience is based mostly on Oracle and it might be different with another driver. In my experience, you can use any valid SQL command or snippet with JDBC, but you need to use JDBC specific syntax for bind (parameters) and specifically you cannot use named parameters outside of the {call ... } block. It is even possible to execute anonymous PL/SQL block in Oracle via JDBC. (I use this all the time to utilize the Oracle specific returning clause, which is neither recognized, nor supported by JDBC.)

So my suggestion should have been: start with the command in the form which works with SQL client, and only change bind (parameters) to conform to the JDBC convention. This also means to not use the {call ... } syntax, as this is JDBC specific extension. I don't know whether you did try this variant, so I'm restating it again. If this does not work for you, I'm out of ideas.