parameter substitution inside single quotes

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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!

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/1a79a222-daa7-47c5-9976-3b8e7d77cf04%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: parameter substitution inside single quotes

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


On Tue, Jan 21, 2020 at 3:48 PM Yogeesh Kapila <[hidden email]> 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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/1a79a222-daa7-47c5-9976-3b8e7d77cf04%40googlegroups.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAKpaNj5O%3DH-ix9FC5rWb2YB%2B-9Rq41E2hbivmNNDH57M1qUOeQ%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: parameter substitution inside single quotes

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 <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="meJgVoSSDAAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">yogeesh...@...> 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 <a href="javascript:" target="_blank" gdf-obfuscated-mailto="meJgVoSSDAAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybati...@googlegroups.com.
To view this discussion on the web visit <a href="https://groups.google.com/d/msgid/mybatis-user/1a79a222-daa7-47c5-9976-3b8e7d77cf04%40googlegroups.com?utm_medium=email&amp;utm_source=footer" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/msgid/mybatis-user/1a79a222-daa7-47c5-9976-3b8e7d77cf04%40googlegroups.com?utm_medium\x3demail\x26utm_source\x3dfooter&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/msgid/mybatis-user/1a79a222-daa7-47c5-9976-3b8e7d77cf04%40googlegroups.com?utm_medium\x3demail\x26utm_source\x3dfooter&#39;;return true;">https://groups.google.com/d/msgid/mybatis-user/1a79a222-daa7-47c5-9976-3b8e7d77cf04%40googlegroups.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/92504e3c-7351-41ea-aa10-61a8652b2032%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: parameter substitution inside single quotes

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


On Tue, Jan 21, 2020 at 6:31 PM Yogeesh Kapila <[hidden email]> wrote:
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 <[hidden email]> 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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/1a79a222-daa7-47c5-9976-3b8e7d77cf04%40googlegroups.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/92504e3c-7351-41ea-aa10-61a8652b2032%40googlegroups.com.

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAKpaNj7ioCG3Sd%2BuJhQ030UdVzpxoXDAxpj0xY4MqDiCPN6RXQ%40mail.gmail.com.
Reply | Threaded
Open this post in threaded view
|

Re: parameter substitution inside single quotes

Yogeesh Kapila
Got it,
Thanks again!

--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/228d4813-ac9b-4f2b-ae2d-e6045b99a85f%40googlegroups.com.