parameter substitution inside single quotes

[Yogeesh Kapila]

Hi,

I have a situation, any help would be appreciated.

My query is a json_exists() query where in the path expression is a literal value (Oracle db) -:

SELECT * FROM some_table WHERE (JSON_EXISTS(some_column,'$?(@ == #{blah})'));

I want Mybatis to evaluate this and correctly put the value in #{blah}.

Note: The first question mark (?) is part of the query and not a placeholder for prepared statement.

I dont want to use ${blah} as this field is an user input and I want to use #{blah} only.

How can I make myBatis do this.

Any help would be appreciated.

Thanks!

[Jeff Butler]

This isn't really a MyBatis problem. If Oracle is interpreting the '?' in a special way in the JSON_EXISTS query, there's not much we can do about it. I think ${blah} is probably your only option here - unless there is some way to tell Oracle the second '?' is a parameter marker and the first '?' is not.

Jeff Butler

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/1a79a222-daa7-47c5-9976-3b8e7d77cf04%40googlegroups.com.

[Yogeesh Kapila]

Thanks for the response Jeff.

I found this on the web, would this be causing the problem here ?

The problem is with parameters passing to the query.

When you use expression like #{parameterName} to specify a parameter mybatis transforms it to the jdbc parameter placeholder ? and then sets the parameter by index. For this query:

 select * from  a where col = #{param}

the query generated by mybatis would be:

 select * from a where col = ?

Because you quoted the parameter like this:

 select * from  a where col = '#{param}'

the generated query becomes:

 select * from  a where col = '?'

And this is treated by JDBC API as a query without any parameters so when mybatis tries to set parameters using JDBC PreparedStatement API the error is that parameter index is invalid.

Is my understanding correct that since my generated query is in the similar format as above, JDBC Prepared Statement is not able to place the value there from the parameter map.

So there isn't any way of fixing my situation, any pointers ?
Thanks

On Tuesday, 21 January 2020 13:53:42 UTC-8, Jeff Butler wrote:

This isn't really a MyBatis problem. If Oracle is interpreting the '?' in a special way in the JSON_EXISTS query, there's not much we can do about it. I think ${blah} is probably your only option here - unless there is some way to tell Oracle the second '?' is a parameter marker and the first '?' is not.

Jeff Butler

On Tue, Jan 21, 2020 at 3:48 PM Yogeesh Kapila <yogeesh...@gmail.com> wrote:

Hi,

I have a situation, any help would be appreciated.

My query is a json_exists() query where in the path expression is a literal value (Oracle db) -:

SELECT * FROM some_table WHERE (JSON_EXISTS(some_column,'$?(@ == #{blah})'));

I want Mybatis to evaluate this and correctly put the value in #{blah}.

Note: The first question mark (?) is part of the query and not a placeholder for prepared statement.

I dont want to use ${blah} as this field is an user input and I want to use #{blah} only.

How can I make myBatis do this.

Any help would be appreciated.

Thanks!

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mybati...@googlegroups.com.

[Jeff Butler]

That is the issue for sure. I think you will have to use ${blah} for string substitution so you don't end up with another '?' in the query - that will probably just work.

You might need to tell MyBatis that this is not a prepared statement - I'm not sure about that, you will just have to try it.  If you still get errors about parameters when using ${blah}, then tell MyBatis not to use prepared statements for this query.

You do this through XML or annotations depending on how you are using MyBatis.  For example...

<select id="foo" statementType="STATEMENT">...</select>

or 

@Select

@Options(statementType="STATEMENT")

Foo selectSomething();

Jeff Butler

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/92504e3c-7351-41ea-aa10-61a8652b2032%40googlegroups.com.

[Yogeesh Kapila]

Got it,
Thanks again!