You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Da...@Equifax.com on 2007/10/26 15:25:28 UTC
Problem executing dynamic SQL statement
I'm having a problem with a dynamic statement. It is executed against
different tables that have different structures. But it seems that the
columns for the first execution are being cached somehow. The statement is
this:
<select id="getScoreCounts" parameterClass="map" resultClass=
"java.util.HashMap">
select * from
<dynamic>
<iterate open="(" close=")" conjunction="),(" property="scoreCodes">
select count($scoreCodes[]$) as $scoreCodes[]$
from $tableName$
where $scoreCodes[]$ is not null and record_type = #recordType#
</iterate>
</dynamic>
</select>
The result is the same without the <dynamic> tag. The logged statement
looks correct, but the SQL error references a column from a previous
execution.
For example, in the logs I'll see something like
select * from ( select count(COL_A) as COL_A from TABLE_X where
COL_A is not null and record_type = ? ),( select count(COL_B) as COL_B
from TABLE_A where COL_B is not null and record_type = ? )
this statemnet will succeed. then I'll see something like
select * from ( select count(COL_A) as COL_A from TABLE_YY where
COL_A is not null and record_type = ? )
and that will cause a SQL error:
check the result mapping for the 'COL_B' property.
Cause: java.sql.SQLException: Invalid column name
Since there is no 'COL_B' in that statement, this is causing me a lot of
grief; I would sure appreciate some insight on this.
BTW I'm using version 2.3.0.677, with Spring 2.0.4
Thanks,
Dave Derry
We must begin not just to act, but to think, for there is no better slave
than the one who believes his slavery to be freedom, and we are in
no greater peril than when we cannot see the chains on our minds
because there are yet no chains on our feet.
-- Michael Reid
This message contains information from Equifax Inc. which may be
confidential and privileged. If you are not an intended recipient, please
refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e-mail
postmaster@equifax.com.
Re: Problem executing dynamic SQL statement
Posted by Da...@Equifax.com.
Thank-you Larry!
That solved the problem. Guess I'll need to read the developer guide
completely, rather than just referring to it occasionally.
Dave
We must begin not just to act, but to think, for there is no better slave
than the one who believes his slavery to be freedom, and we are in
no greater peril than when we cannot see the chains on our minds
because there are yet no chains on our feet.
-- Michael Reid
"Larry Meadors"
<lmeadors@apache.
org> To
Sent by: user-java@ibatis.apache.org
larry.meadors@gma cc
il.com
Subject
Re: Problem executing dynamic SQL
10/26/2007 10:02 statement
AM
Please respond to
user-java@ibatis.
apache.org
Add remapResults="true" to your select element.
Larry
On 10/26/07, Dave.Derry@equifax.com <Da...@equifax.com> wrote:
>
> I'm having a problem with a dynamic statement. It is executed against
> different tables that have different structures. But it seems that the
> columns for the first execution are being cached somehow. The statement
is
> this:
>
> <select id="getScoreCounts" parameterClass="map" resultClass=
> "java.util.HashMap">
> select * from
> <dynamic>
> <iterate open="(" close=")" conjunction="),("
property="scoreCodes">
> select count($scoreCodes[]$) as $scoreCodes[]$
> from $tableName$
> where $scoreCodes[]$ is not null and record_type =
#recordType#
> </iterate>
> </dynamic>
> </select>
>
> The result is the same without the <dynamic> tag. The logged statement
> looks correct, but the SQL error references a column from a previous
> execution.
>
> For example, in the logs I'll see something like
>
> select * from ( select count(COL_A) as COL_A from TABLE_X where
> COL_A is not null and record_type = ? ),( select count(COL_B) as
COL_B
> from TABLE_A where COL_B is not null and record_type = ? )
>
> this statemnet will succeed. then I'll see something like
>
> select * from ( select count(COL_A) as COL_A from TABLE_YY where
> COL_A is not null and record_type = ? )
>
> and that will cause a SQL error:
>
> check the result mapping for the 'COL_B' property.
> Cause: java.sql.SQLException: Invalid column name
>
>
> Since there is no 'COL_B' in that statement, this is causing me a lot of
> grief; I would sure appreciate some insight on this.
>
> BTW I'm using version 2.3.0.677, with Spring 2.0.4
>
> Thanks,
> Dave Derry
>
> We must begin not just to act, but to think, for there is no better slave
> than the one who believes his slavery to be freedom, and we are in
> no greater peril than when we cannot see the chains on our minds
> because there are yet no chains on our feet.
> -- Michael Reid
>
>
> This message contains information from Equifax Inc. which may be
> confidential and privileged. If you are not an intended recipient,
please
> refrain from any disclosure, copying, distribution or use of this
> information and note that such actions are prohibited. If you have
> received this transmission in error, please notify by e-mail
> postmaster@equifax.com.
>
>
>
Re: Problem executing dynamic SQL statement
Posted by Larry Meadors <lm...@apache.org>.
Add remapResults="true" to your select element.
Larry
On 10/26/07, Dave.Derry@equifax.com <Da...@equifax.com> wrote:
>
> I'm having a problem with a dynamic statement. It is executed against
> different tables that have different structures. But it seems that the
> columns for the first execution are being cached somehow. The statement is
> this:
>
> <select id="getScoreCounts" parameterClass="map" resultClass=
> "java.util.HashMap">
> select * from
> <dynamic>
> <iterate open="(" close=")" conjunction="),(" property="scoreCodes">
> select count($scoreCodes[]$) as $scoreCodes[]$
> from $tableName$
> where $scoreCodes[]$ is not null and record_type = #recordType#
> </iterate>
> </dynamic>
> </select>
>
> The result is the same without the <dynamic> tag. The logged statement
> looks correct, but the SQL error references a column from a previous
> execution.
>
> For example, in the logs I'll see something like
>
> select * from ( select count(COL_A) as COL_A from TABLE_X where
> COL_A is not null and record_type = ? ),( select count(COL_B) as COL_B
> from TABLE_A where COL_B is not null and record_type = ? )
>
> this statemnet will succeed. then I'll see something like
>
> select * from ( select count(COL_A) as COL_A from TABLE_YY where
> COL_A is not null and record_type = ? )
>
> and that will cause a SQL error:
>
> check the result mapping for the 'COL_B' property.
> Cause: java.sql.SQLException: Invalid column name
>
>
> Since there is no 'COL_B' in that statement, this is causing me a lot of
> grief; I would sure appreciate some insight on this.
>
> BTW I'm using version 2.3.0.677, with Spring 2.0.4
>
> Thanks,
> Dave Derry
>
> We must begin not just to act, but to think, for there is no better slave
> than the one who believes his slavery to be freedom, and we are in
> no greater peril than when we cannot see the chains on our minds
> because there are yet no chains on our feet.
> -- Michael Reid
>
>
> This message contains information from Equifax Inc. which may be
> confidential and privileged. If you are not an intended recipient, please
> refrain from any disclosure, copying, distribution or use of this
> information and note that such actions are prohibited. If you have
> received this transmission in error, please notify by e-mail
> postmaster@equifax.com.
>
>
>