aspose file tools*
The moose likes JDBC and the fly likes A comment inside a CallableStatement messes with parameters Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "A comment inside a CallableStatement messes with parameters" Watch "A comment inside a CallableStatement messes with parameters" New topic
Author

A comment inside a CallableStatement messes with parameters

Eric Lemaitre
Ranch Hand

Joined: Jul 03, 2004
Posts: 538

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.


Eric LEMAITRE
CNAM IT Engineer, MS/CS (RHCE, RHCX, SCJA, SCJP, SCJD, SCWCD, SCBCD, SCEA, Net+)
Free Online Tutorials: http://www.free-tutorials-online.net/
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18127
    
    8

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:

but this:

Eric Lemaitre
Ranch Hand

Joined: Jul 03, 2004
Posts: 538

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:

but this:



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:

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
Ranch Hand

Joined: Feb 07, 2012
Posts: 344

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
Ranch Hand

Joined: Jul 03, 2004
Posts: 538

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
Ranch Hand

Joined: Feb 07, 2012
Posts: 344

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

Joined: Jul 03, 2004
Posts: 538

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:


But I really need this kind of syntax:


Best regards.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18127
    
    8

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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3435
    
  47

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:

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:

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
Ranch Hand

Joined: Jul 03, 2004
Posts: 538

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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3435
    
  47

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: A comment inside a CallableStatement messes with parameters
 
Similar Threads
Function does not exist
Function does not Exist
Callable statement without binding
No operations allowed after statement closed.
CallableStatement problem