You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ibatis.apache.org by "adamb (JIRA)" <ib...@incubator.apache.org> on 2006/05/25 23:32:29 UTC

[jira] Created: (IBATIS-297) iBATIS doesn't remap sql fully

iBATIS doesn't remap sql fully
------------------------------

         Key: IBATIS-297
         URL: http://issues.apache.org/jira/browse/IBATIS-297
     Project: iBatis for Java
        Type: Bug

  Components: SQL Maps  
    Versions: 2.1.5    
    Reporter: adamb


iBATIS doesn't remap parameters.

If I have statement with remapResults="true":

select * from mytable where $dynamicWhereClause$

and pass in the following two parameters: 
dynamicWhereClause                              #key#='abc'
key                                                                 java.lang.String="abc"

Then the remap puts the #key# in the sql directly, rather than rechecking the statement to determine what the parameters will be, so gives the error
message:
java.sql.SQLException: ORA-00911: invalid character 

because it hasn't recognised the new hash parameters.

Version tested on:
iBATIS 2.1.5

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Closed: (IBATIS-297) iBATIS doesn't remap sql fully

Posted by "Jeff Butler (JIRA)" <ib...@incubator.apache.org>.
     [ http://issues.apache.org/jira/browse/IBATIS-297?page=all ]
     
Jeff Butler closed IBATIS-297:
------------------------------

    Resolution: Won't Fix

Closing ... I'm assuming the workarounds I've outlined in the comments are sufficient.

> iBATIS doesn't remap sql fully
> ------------------------------
>
>          Key: IBATIS-297
>          URL: http://issues.apache.org/jira/browse/IBATIS-297
>      Project: iBatis for Java
>         Type: New Feature

>   Components: SQL Maps
>     Versions: 2.1.5
>     Reporter: adamb

>
> iBATIS doesn't remap parameters.
> If I have statement with remapResults="true":
> select * from mytable where $dynamicWhereClause$
> and pass in the following two parameters: 
> dynamicWhereClause                              #key#='abc'
> key                                                                 java.lang.String="abc"
> Then the remap puts the #key# in the sql directly, rather than rechecking the statement to determine what the parameters will be, so gives the error
> message:
> java.sql.SQLException: ORA-00911: invalid character 
> because it hasn't recognised the new hash parameters.
> Version tested on:
> iBATIS 2.1.5

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (IBATIS-297) iBATIS doesn't remap sql fully

Posted by "Jeff Butler (JIRA)" <ib...@incubator.apache.org>.
    [ http://issues.apache.org/jira/browse/IBATIS-297?page=comments#action_12413311 ] 

Jeff Butler commented on IBATIS-297:
------------------------------------

This is by design.  When you use the dollar sign delimiters, iBATIS passes exactly what you specify.  iBATIS has no provision for re-parsing the statement.  If we really wanted to support this (we don't), then you could easily invent situations where there were many levels of parsing before the statement was actually executed.  I think this would be a bad idea - could lead to very obfuscated code.

In your example, you could make this work the way you want by doing this:

select * from mytable where $condition$ #value#

Then specify map values:

condition: java.lang.String="someField ="
value: java.lang.String="abc"


> iBATIS doesn't remap sql fully
> ------------------------------
>
>          Key: IBATIS-297
>          URL: http://issues.apache.org/jira/browse/IBATIS-297
>      Project: iBatis for Java
>         Type: Bug

>   Components: SQL Maps
>     Versions: 2.1.5
>     Reporter: adamb

>
> iBATIS doesn't remap parameters.
> If I have statement with remapResults="true":
> select * from mytable where $dynamicWhereClause$
> and pass in the following two parameters: 
> dynamicWhereClause                              #key#='abc'
> key                                                                 java.lang.String="abc"
> Then the remap puts the #key# in the sql directly, rather than rechecking the statement to determine what the parameters will be, so gives the error
> message:
> java.sql.SQLException: ORA-00911: invalid character 
> because it hasn't recognised the new hash parameters.
> Version tested on:
> iBATIS 2.1.5

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (IBATIS-297) iBATIS doesn't remap sql fully

Posted by "adamb (JIRA)" <ib...@incubator.apache.org>.
    [ http://issues.apache.org/jira/browse/IBATIS-297?page=comments#action_12413318 ] 

adamb commented on IBATIS-297:
------------------------------

How can that be used to implement, for instance, a condition that is an existence test that contains multiple parameters

> iBATIS doesn't remap sql fully
> ------------------------------
>
>          Key: IBATIS-297
>          URL: http://issues.apache.org/jira/browse/IBATIS-297
>      Project: iBatis for Java
>         Type: Bug

>   Components: SQL Maps
>     Versions: 2.1.5
>     Reporter: adamb

>
> iBATIS doesn't remap parameters.
> If I have statement with remapResults="true":
> select * from mytable where $dynamicWhereClause$
> and pass in the following two parameters: 
> dynamicWhereClause                              #key#='abc'
> key                                                                 java.lang.String="abc"
> Then the remap puts the #key# in the sql directly, rather than rechecking the statement to determine what the parameters will be, so gives the error
> message:
> java.sql.SQLException: ORA-00911: invalid character 
> because it hasn't recognised the new hash parameters.
> Version tested on:
> iBATIS 2.1.5

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (IBATIS-297) iBATIS doesn't remap sql fully

Posted by "adamb (JIRA)" <ib...@incubator.apache.org>.
    [ http://issues.apache.org/jira/browse/IBATIS-297?page=comments#action_12413312 ] 

adamb commented on IBATIS-297:
------------------------------

Unfortunately, the number of clauses is dynamic, so the suggested solution won't work.  Our current solution is to insert the parameter string into the query ourselves, but this means that we get a large performance loss by not having parameterised queries, so the DB has to re-prepare each query.

As to the remapping being nested, I was expecting that the $$ remapping only happened once, but the ## remapping happened after the $$ remapping (i.e. no infinite recursion possible).

> iBATIS doesn't remap sql fully
> ------------------------------
>
>          Key: IBATIS-297
>          URL: http://issues.apache.org/jira/browse/IBATIS-297
>      Project: iBatis for Java
>         Type: Bug

>   Components: SQL Maps
>     Versions: 2.1.5
>     Reporter: adamb

>
> iBATIS doesn't remap parameters.
> If I have statement with remapResults="true":
> select * from mytable where $dynamicWhereClause$
> and pass in the following two parameters: 
> dynamicWhereClause                              #key#='abc'
> key                                                                 java.lang.String="abc"
> Then the remap puts the #key# in the sql directly, rather than rechecking the statement to determine what the parameters will be, so gives the error
> message:
> java.sql.SQLException: ORA-00911: invalid character 
> because it hasn't recognised the new hash parameters.
> Version tested on:
> iBATIS 2.1.5

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (IBATIS-297) iBATIS doesn't remap sql fully

Posted by "Jeff Butler (JIRA)" <ib...@incubator.apache.org>.
    [ http://issues.apache.org/jira/browse/IBATIS-297?page=comments#action_12413320 ] 

Jeff Butler commented on IBATIS-297:
------------------------------------

This can get complex depending on how you lay out your parameter object.  But I think with the new nested iterate support you should be able to achieve just about anything you need.  Here's one way to do it with a nested iterate (this would work now with a build of iBATIS from source in SVN, it will be released with iBATIS 2.2.0):

List values = new ArrayList();
values.add("1");
values.add("2");
values.add("3");

Map condition = new HashMap();
map.put("condition", "someField in");
map.put("value", values);

List conditions = new ArrayList();
conditions.add(condition);

Map parms = new HashMap();
parms.put("conditions", conditions);

queryForList("statementId", parms);


select *
from mytable
<iterate prepend="where" conjunction="and" property="conditions">
  $conditions[].condition$
  <iterate open="(" close=")" conjunction="," property="conditions[].value">
    #conditions[].value[]#
  <iterate>
</iterate>

This should generate:
select *
from myTable
where someField in("1", "2", "3")

Again, this won't work with an official iBATIS release until iBATIS 2.2.0 - but you could check the source out of SVN and build it now.

> iBATIS doesn't remap sql fully
> ------------------------------
>
>          Key: IBATIS-297
>          URL: http://issues.apache.org/jira/browse/IBATIS-297
>      Project: iBatis for Java
>         Type: Bug

>   Components: SQL Maps
>     Versions: 2.1.5
>     Reporter: adamb

>
> iBATIS doesn't remap parameters.
> If I have statement with remapResults="true":
> select * from mytable where $dynamicWhereClause$
> and pass in the following two parameters: 
> dynamicWhereClause                              #key#='abc'
> key                                                                 java.lang.String="abc"
> Then the remap puts the #key# in the sql directly, rather than rechecking the statement to determine what the parameters will be, so gives the error
> message:
> java.sql.SQLException: ORA-00911: invalid character 
> because it hasn't recognised the new hash parameters.
> Version tested on:
> iBATIS 2.1.5

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (IBATIS-297) iBATIS doesn't remap sql fully

Posted by "Jeff Butler (JIRA)" <ib...@incubator.apache.org>.
    [ http://issues.apache.org/jira/browse/IBATIS-297?page=comments#action_12413317 ] 

Jeff Butler commented on IBATIS-297:
------------------------------------

If the number of conditions is dynamic, you can also use the <iterate> tag like this:

select # from myTable
<iterate prepend="where" conjunction="and" property="conditions">
  $conditions[].condition$ #conditions[].value#
</iterate>

Where "conditions" is a List property of your parameter object, and the list contains Maps setup like my previous comment.

You can make more elaborate expressions than this using nested iterate tags (which is available in SVN now, but not released until iBATIS 2.2.0).  There is an example of this in SVN in the tests for the nested iterate tags.  Send me a note if you need a link.

BTW - The next version of Abator will use this idea to allow you to generate a virtually unlimited dynamic where clause.

> iBATIS doesn't remap sql fully
> ------------------------------
>
>          Key: IBATIS-297
>          URL: http://issues.apache.org/jira/browse/IBATIS-297
>      Project: iBatis for Java
>         Type: Bug

>   Components: SQL Maps
>     Versions: 2.1.5
>     Reporter: adamb

>
> iBATIS doesn't remap parameters.
> If I have statement with remapResults="true":
> select * from mytable where $dynamicWhereClause$
> and pass in the following two parameters: 
> dynamicWhereClause                              #key#='abc'
> key                                                                 java.lang.String="abc"
> Then the remap puts the #key# in the sql directly, rather than rechecking the statement to determine what the parameters will be, so gives the error
> message:
> java.sql.SQLException: ORA-00911: invalid character 
> because it hasn't recognised the new hash parameters.
> Version tested on:
> iBATIS 2.1.5

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira